Re: Recursion and cursor name - ORA-01002 fetch out of sequence

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/12/01
Message-ID: <3484cb8f.2534294@inet16>#1/1

On Mon, 01 Dec 1997 07:37:37 +0100, Dietmar Leibecke <dleibeck_at_debis.com.---> wrote:

>Keith Boulton wrote:
>
>> I just tried this with oracle 8 and it worked ok except that it
>> eventually hit the too many open cursors error.
>
> Keith,
>
>thanks for your input. I'm sorry, I forgot to mention, that I'm using Pro*C 2.2.3.0
>and Oracle Server 7.3.3 running on Digital UNIX 4.0. As our database server is not
>available this morning, I couldn't test your PL/SQL code to see, if Oracle 7.3 is
>able to handle recursion in PL/SQL.
>
>Do you have any more information on this topic?
>
>TIA,
A static cursor in PRO*C is global to the source code file, in general, pro*c does not follow the scoping rules for C. If you declare a cursor in one procedure, that cursor is available to all procedures physically located below that one in the source code file.

Your recursive routine is opening and closing the only instance of the cursor that exists.

So, how to do this in Pro*C? One way is below. YOu can use cursor variables. If you build the following routine and call it with:

  process(0,0);

It'll build a tree like:

 KING

      JONES
           SCOTT
                ADAMS
           FORD
                SMITH
      BLAKE
           ALLEN
           WARD
           MARTIN
           TURNER
           JAMES
      CLARK
           MILLER

from the scott.emp table. It recursively calls itself for every record found and builds the subtrees. It dynamically allocates cursors to do this. Note that in order to embedded PL/SQL in pro*c as I have done, you have to use sqlcheck=semantics to precompile it.

static void process( int empno, int depth ) {
EXEC SQL BEGIN DECLARE SECTION;

    SQL_CURSOR  my_cursor;
    VARCHAR     ename[40];
    int         new_empno;

EXEC SQL END DECLARE SECTION;       EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();  

    EXEC SQL ALLOCATE :my_cursor;  

    EXEC SQL EXECUTE BEGIN

        if ( :empno = 0 ) then
            open :my_cursor for
                SELECT ENAME, EMPNO FROM EMP WHERE MGR IS NULL;
        else
            open :my_cursor for
                SELECT ENAME, EMPNO FROM EMP WHERE MGR = :empno;
        end if;

    END; END-EXEC;       for( ;; )
    {

        EXEC SQL WHENEVER NOTFOUND DO break;
        EXEC SQL FETCH :my_cursor INTO :ename, new_empno;
 
        printf( "%*.*s %.*s\n", depth, depth, " ", ename.len, ename.arr );
        process( new_empno, depth+5 );

    }
 EXEC SQL CLOSE :my_cursor;
}  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Dec 01 1997 - 00:00:00 CST