Fetch out of sequence error in pro C recursive calls

From: OraNewBie <kk2die4_at_gmail.com>
Date: 13 Oct 2006 04:50:10 -0700
Message-ID: <1160740210.424166.278790@m73g2000cwd.googlegroups.com>

Hi All,
I am trying to call a function recursively that creates an SQL statement and executes it
and then calls itself. I get a fetch out of sequence error. When I tried to modify the code
below for my use with

EXEC SQL DECLARE STMT STATEMENT;
EXEC SQL EXECUTE BEGIN OPEN :dyn_cursor for STMT; END; END-EXEC;
EXEC SQL PREPARE STMT FROM :dynaString;

I get this error. How do I make a dynamic cursor that executes a dynamic select
query ?

Error at line 945, column 49 in file oracle.pc EXEC SQL EXECUTE BEGIN OPEN :dyn_cursor for STMT;

................................................1

PLS-S-00201, identifier 'STMT' must be declared Error at line 945, column 29 in file oracle.pc

I found the following peice of code here in an older thread. My problem is , how to make it work for a dynamic SQL query.

Thanks a lot
A newbie.

---

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;

} Received on Fri Oct 13 2006 - 06:50:10 CDT