Oracle bug (w/ Pro*C)???

From: Ken Frank <kenf_at_usinternet.com>
Date: 1997/06/27
Message-ID: <33b3ced7.0@news.usinternet.com>#1/1

Is there something unusual about the following code? For those not interested in wading through about 100 lines of snipped-up code, I'll outline the problem first:

1. Open up a cursor (a simple query with a union all to a 3-table-joined query).
2. fetch from cursor.
3. after first fetch, a simple query with a join and the min() aggregates.
4. next fetch from cursor -- blows up with: SQL-02134 Invalid runtime context.

...
char *get_shift_info(int p_shift)
{

[snipped declarations]

    shift = p_shift;
    exec sql

	select	min(d.description), min(s.start_dt), min(s.end_dt)
	into	:descr		:descr_null,
		:start_dt	:start_dt_null,
		:end_dt		:end_dt_null 
	from	shift_def d, shift s
	where	s.shift_code	= :shift
	  and	d.shift_code	= s.shift_code;

    if (sqlca.sqlcode != 0) return NULL; [more snippage dealing with potential null values]

    /* this actually gets formatted a bit, but for simplicity... */     sprintf(tmp, "%s (%s - %s)", descr, start_dt, end_dt);     return tmp;
}

int list_emp_timesheet(char *p_emp_id, int p_shift)
{

    [snipped declarations]

    exec sql declare lab_csr cursor for

	select	start_time, end_time, ind_acct, 0, ' ',
		location, duration, source, ' '
	from	acc_lab_chg
	where	emp_id = :emp_id
	  and	start_time between :p_dt1 and :p_dt2
	UNION ALL
	select	a.start_time, a.end_time, ' ', a.wo_no, b.job,
		b.location, a.duration, b.source, u.unit_no
	from	o_labor_chg a, o_wo b, unit_main u
	where	a.wo_no = b.wo_no
	  and	b.unit_id = u.unit_id
	  and	a.emp_id = :emp_id
	  and	a.start_time between :dt1 and :dt2
	order by 1, 4;

    exec sql open lab_csr;
    rows_fetched = 0;
    while (sqlca.sqlcode == 0) {

	exec sql fetch lab_csr into ...;
	if (sqlca.sqlcode != 0) break;

	rows_fetched++;
	if (rows_fetched == 1) {
	    /*
	    | there's going to be data for this employee, so we
	    | need to get their "shift-description"...
	    */
	    strcpy(shift_descr, get_shift_info(shift));
	}

	printf("...omitted large printf...\n");

    }
}
...

This thing keeps puking on the SECOND fetch! The error message I'm getting out of sqlca is "SQL-02134 Invalid runtime context". A colleague tells me that his reference manual says:

	cause: The runtime context associated with an executable
		SQL statement was not properly allocated.
	action: Rewrite you application to execute the EXEC SQL
		CONTEXT ALLOCATE statement before executing any
		SQL statements.

Program flow is:

1) fetch, (sqlca.sqlcode is ZERO)
2) select-on shift_def/shift tables (sqlca.sqlcode is ZERO)
3) fetch, (poof, somthing nasty happened to my cursor!)

I am not doing any commits/rollbacks here (besides that'd be a different error)!

This seems like a BUG to me! This code works fine as-is under Ingres, and under Oracle, if I simplify either of those select statements (the cursor's query or the shift_def/shift query), it works. Can anyone tell me why Oracle is having trouble with this? I've got a workaround (there CAN'T be more than 255 shifts anyway, so I'm just preloading them all into an array before anything else, but I just had to vent my frustrations here, as I've wasted so much time on it).

Thanks

PS - El-cheapo anti-spam measures in effect:

	my email isn't really kenf_at_usinternet.com (used to be!)
	instead, you can reply to ken AT mn.csoft.com

OR, just reply to the group! I read here as often as I can. Received on Fri Jun 27 1997 - 00:00:00 CDT