Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PRO*C, Dynamic Sql, stored procedure and Cursor

254968Sep 12 2002
Hi,
I want to call a stored procedure returning a cursor from a pro*c program and store the results in a C struct. That's easy...
But now, i want to pass the procedure name and params as parameters.
I try this (from Method 3 but i have an ORA-01002 error):
...
EXEC SQL BEGIN DECLARE SECTION;
...
short v1 = 1;

sql_cursor m_sql_cursor;

typedef struct orga {
varchar val1[70];
long val2;
} Organisation;
Organisation orga_rs[30];

...
EXEC SQL END DECLARE SECTION;

void main() {
...
strcpy((char*)myProc.arr,"begin thePKG.PS_LIST(:p1, :res_cursor)");
myProc.len = strlen((char*)myProc.arr);

EXEC SQL ALLOCATE :m_sql_cursor;

EXEC PREPARE S FROM :myProc;

EXEC DECLARE localCurs CURSOR FOR S;

EXEC SQL OPEN localCurs USING :v1, :m_sql_cursor;

EXEC SQL WHENEVER NOT FOUND DO break;

for(;;) {
EXEC SQL FETCH localCurs INTO :orga_rs; (*)
}
...

}
(*) At this point, i have an ORA-01002 -FETCH OUT OF SEQUENCE-

How can i get the results (a cursor) of my stored procedure whith this king of dynamic SQL (Method 3) ?
Is it possible ?
I need your help.......... Thanks in advance,

Alban
PS : Oracle 8.1.7 on OpenVMS
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 10 2002
Added on Sep 12 2002
0 comments
316 views