Re: ref cursor called from Pro*C

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 15 Sep 1999 07:53:14 -0400
Message-ID: <B4jfN8+Q9FHh8ZTLUswjF7DjHlwP@4ax.com>

A copy of this was sent to paul cluiss <paul_cluiss_at_intervoice.com> (if that email address didn't require changing) On Tue, 14 Sep 1999 16:49:40 -0500, you wrote:

>Hello, everyone.
>
>I'm writing a Pro*C function called fetchAlerts() which needs to managed
>an array of SQL_CURSORs. The motivation is to allow multiple clients to
>have their own independent queries, each going through fetchAlerts().
>Through the use of a flag which can be FETCH_BEG, FETCH_CONT, or
>FETCH_END, each client can direct fetchAlerts() to (re)start, continue
>or terminate that client's query. The client supplies its index into an
>array of SQL_CURSORs. The index is like a handle into the cursor
>array. That way each client gets its own cursor so none of the queries
>will stomp on top of the other ones.
>This sounds like a great idea, but I'm having trouble implementing it.
>I can declare an array of SQL_CURSORs, but cannot pass the value of one
>of them to my stored procedure. I have Pro*C code like this:
>
>int fetchAlerts (int nCursor, int howMany, int fetchFlag)
>{
> EXEC SQL BEGIN DECLARE SECTION;
> static SQL_CURSOR myCursors[MAXALERTCURSORS];
> EXEC SQL END DECLARE SECTION;
>.
>.
>.
>EXEC SQL EXECUTE
> BEGIN
> fraud.open_alert_cursor(:myCursors(nCursor));
> END;
> END-EXEC;
>.
>.
>.
>} /* fetchAlerts() */
>
>Unfortunately the Pro*C preprocessor give me the following error for the
>open cursor line:
>Error at line 171, column 40 in file fraud.pc
>
>fraud.open_alert_cursor(:myCursors(nCursor));
>.......................................1
>PLS-S-00201, identifier 'NCURSOR' must be declared
>

Well -- the () is a pl/sql way of subscripting... C would use [] (hence that error). HOWEVER, the precompiler isn't going to like an array of cursors anyway. You would have to use a SCALAR (but you can still use arrays). Consider this example:

static void process2()
{
EXEC SQL BEGIN DECLARE SECTION;

    VARCHAR     ename[10][50];
    int         empno[10];

    SQL_CURSOR my_cursor[10];
    SQL_CURSOR the_Cursor;

    VARCHAR sql_stmt[512];
EXEC SQL END DECLARE SECTION;

    int         i;
    int         j;

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

    for( i = 0; i < sizeof(my_cursor)/sizeof(my_cursor[0]); i++ )     {

        the_Cursor = my_cursor[i];
        EXEC SQL ALLOCATE :the_Cursor;
        my_cursor[i] = the_Cursor;
        printf( "allocated\n" );

    }

    for( i = 0; i < sizeof(my_cursor)/sizeof(my_cursor[0]); i++ )     {

        the_Cursor = my_cursor[i];
        EXEC SQL EXECUTE BEGIN
            proc( :the_Cursor );
        END; END-EXEC;
        my_cursor[i] = the_Cursor;

    }

    for( i = 0; i < sizeof(my_cursor)/sizeof(my_cursor[0]); i++ )     {

        the_Cursor = my_cursor[i];
        EXEC SQL FETCH :the_Cursor INTO :ename, :empno;
        my_cursor[i] = the_Cursor;

        for( j = 0; j < sqlca.sqlerrd[2]; j++ )
            printf( "%.*s (%d)\n", ename[j].len, ename[j].arr, empno[j] );

    }

    for( i = 0; i < sizeof(my_cursor)/sizeof(my_cursor[0]); i++ )     {

        the_Cursor = my_cursor[i];
        EXEC SQL CLOSE :the_Cursor;
        my_cursor[i] = the_Cursor;

    }
}

Just get the i'th array element, put it in a scalar, use it, and then put it back in the array. For some statements pro*c will accept the my_cursors[i], for others it won't. For some - it'll accept my_cursors[i] but do the "wrong thing". I would recommend that you always save it to a scalar -- use it -- and then put it back in the array regardless of the EXEC SQL statement (as above).

>I have tried a variety of other syntactically different ways to express
>this statement, but each results in its own unique syntax error. I
>understand that the array indexing in PL/SQL uses parenthesis, as
>opposed to brackets [], and that bound variables need to be preceded
>with a colon, but in my case I've got two variables and when I put a
>colon before nCursors I get the following error:
>Semantic error at line 171, column 41, file fraud.pc:
>
>fraud.open_alert_cursor(:myCursors(:nCursor));
>........................................1
>PCC-S-02322, found undefined identifier
>
>fraud.open_alert_cursor(:myCursors(:nCursor));
>
>Any help would be greatly appreciated.
>
>Paul Cluiss
>Dallas, Texas

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Sep 15 1999 - 06:53:14 CDT