pro*c dynamic pl/sql question

[DoraC]

Hi,

I'm trying to use dynamic pl/sql in a pro*C application (environment = HPUX Oracle 9i), and am encountering problems when I try to use cursors. Evidently it IS possible to use cursors in this way - after googling I found several examples which provided the foundation for my efforts.

The syntax works fine for a standard, non-cursor approach:

char myQuery[80] = "select count(*) from all_tables"; 
... 
EXEC SQL AT sa_db_name 
BEGIN 
EXECUTE IMMEDIATE :myQuery INTO :iCount; 
END; 
END-EXEC;

but fails for the cursor approach (utilizing the exact same connection logic as in the above example - in fact the programs are nearly identical):

char myQuery[80] = "select table_name from all_tables"; 
... 
EXEC SQL PREPARE S FROM :myQuery; 
EXEC SQL DECLARE C CURSOR FOR S; 
EXEC SQL OPEN C; 
EXEC SQL WHENEVER NOT FOUND DO break; 
for (iCounter = 0; iCounter < 10; iCounter++) { 
EXEC SQL FETCH C INTO :output; 
printf(&quot;data is: .%s.\n&quot;, output); 
}

When this second version is run, I receive an

ORA-01012: not logged on.

error message.

What is it about this usage of a cursor that results in a "not logged on" error message?

Thank you!!! this really has me stumped at work, and
those darn deadlines are closing in...
dora c

 

[lewisp]

I havent used Pro*C for some time. Do you (the programmer) still have to manually null terminate character strings returned by Oracle? If so, could you have data overrun that is causing a spurios error from the parser?

In the past I've always had to do this after a fetch into character strings:

[tt]output.arr[output.len] = '\0';[/tt]

 

[lewisp]

Oh, and by the way, have you defined the output variable in an EXEC SQL BEGIN DELCARE section? That way you will get the Oracle equivalent variable which is, in fact, a structure as shown in my previous post.