PRO*C, Dynamic Sql, stored procedure and Cursor
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