Dynamic SQL method 4

Dynamic SQL method 4 allows one to process a query from a database where the names of the colums to be fetched and the where clause are not known at the compile time.

We are facing a problem with "cursor" names with method four. I am wondering if any one else has faced the same problem and if they know any better/elegant solution.

We have developed a module which given a "select" statment, fetches the elements from the database and returns the rows one by one.

Some of the typical exec sql statements of the module are :

    exec sql prepare STMT from :sqlString;     exec sql declare CUR1 cursor for STMT;     exec sql open CUR1;
.....
.....

Based on the first row returned if we want to execute another query, we have to call the same SQL procedure with a different "select" statement. But in doing so the cursor ( CUR1 ) is getting set to tbe new cursor and after executing the second query, if we want to use access the second row of the first query, we get the error ORA-1002, fetch out of sequence.

One solution to the problem is to have another module doing the same but with a different cursor name ( can be CUR2 in this case ). Duplicating entire code just for a different cursor name is not looking elegant.

The problem is boiling down to "not having dynamic cursor names".

Is it possible to have multiple cursors in operation simultaneously whithout having separate functions ? Received on Mon Aug 19 1996 - 00:00:00 CEST