Re: Dynamic SQL method 4

I cannot remember the exact reference, but essentially ORACLE only allows one cursor to be active at a time. The unique naming of cursors would certainly lead to believe otherwise. This is not a problem specific to dynamic SQL, rather to ORACLE itself. PL/SQL gives some relief on this and the DBMS_SQL package provides some dynamic capabilities which are worth looking at.

Raju Jagapathi <jagapathi_at_lamhra.soc.dec.com> wrote:

>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