Re: PRO*C and scope of Cursors

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 06 Aug 1998 14:42:28 GMT
Message-ID: <35cec00f.6701546_at_192.86.155.100>

A copy of this was sent to David Barnes <dvb_at_dial.pipex.com> (if that email address didn't require changing) On Thu, 06 Aug 1998 11:20:50 +0100, you wrote:

>Given the following structure:
>
>
>function a()
>{
> stuff = b( foo );
>}
>
>[Pro*c]
>
>function b( args )
>{
> EXEC SQL DECLARE
> ...
> EXEC SQL ...
> b0 = args;
> etc ...
> If ( first time )
> EXEC SQL PREPARE xxx from "Select 'x' from dual WHERE DUMMY =:b0";
> EXEC SQL DECLARE C CURSOR FOR xxx;
> END IF;
>
> EXEC SQL OPEN C
>
> EXEC SQL FETCH FROM C
>
> etc..
>
>}
>
>I want to repeatedly call b() from a() but passing in different values
>which are
>stuffed into bind variables referenced in the CURSOR. So to avoid
>re-parsing I want to re-OPEN C with the different values. BUT.... when
>I re-call b() then the re-OPEN of the CURSOR fails unless I do another
>PREPARE. I.e. can I have a static/persistent cursor that can be called
>from outside and *NOT* be re-parsed. ??? This all works fine if it's
>done within scope i.e. all inside the same 'C/PC' file but doing
>everything in one file is not viable for any large application.
>
>TIA,
>David Barnes.

Are you sure something else isn't happening? On 7.3 I tested the following 2 source code files which give me the following output:

$ ./test

Connected to ORACLE as user: scott/tiger

• Prepared and Declare
• Open Using %A% ALLEN WARD MARTIN BLAKE CLARK ADAMS JAMES
• Closing Up...

  ---

• Open Using %K% BLAKE CLARK KING
• Closing Up...

So, I prepared but once and opened it 2 times with different inputs....

• main.pc --------------------------------------- #include <stdio.h> #include <string.h>

static char * USERID = NULL;

#define SQLCA_INIT
EXEC SQL INCLUDE sqlca;

void sqlerror_hard()
{

    EXEC SQL WHENEVER SQLERROR CONTINUE;     printf("\nORACLE error detected:");
    printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);

    EXEC SQL ROLLBACK WORK RELEASE;
    exit(1);
}

main( argc, argv )
int argc;
char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[50];
EXEC SQL END DECLARE SECTION;     strcpy( oracleid.arr, "scott/tiger" );     oracleid.len = strlen( oracleid.arr );

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

    EXEC SQL CONNECT :oracleid;
    printf("\nConnected to ORACLE as user: %s\n\n", oracleid.arr);

    process( "%A%" );

    printf( "--------------------\n" );

    process( "%K%" );

    EXEC SQL COMMIT WORK RELEASE;
    exit(0);
}

======================= EOF ==================================
------------------ test.pc -----------------------------------

#include <stdio.h>
#include <string.h>

EXEC SQL INCLUDE sqlca;

extern void sqlerror_hard(void);

void process( char * ename_like )
{
EXEC SQL BEGIN DECLARE SECTION;

    VARCHAR     ename[50];
    VARCHAR     ename_like_bv[50];
    VARCHAR     sqlstmt[255];

EXEC SQL END DECLARE SECTION; static int first_time = 1;

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

    strcpy( ename_like_bv.arr, ename_like );     ename_like_bv.len = strlen( ename_like_bv.arr );

    if ( first_time )
    {

        first_time = !first_time;
        sprintf( sqlstmt.arr, "select ename from emp where ename like :b1");
        sqlstmt.len = strlen( sqlstmt.arr );

        printf( "**** Prepared and Declare\n" );
        EXEC SQL PREPARE S FROM :sqlstmt;
        EXEC SQL DECLARE C CURSOR FOR S;

    }

    printf( "**** Open Using %s \n", ename_like_bv.arr );     EXEC SQL OPEN C USING :ename_like_bv;

    for( ;; )
    {

        EXEC SQL WHENEVER NOTFOUND DO break;
        EXEC SQL FETCH C INTO :ename;

        printf( "%.*s\n", ename.len, ename.arr );

    }
    printf( "**** Closing Up...\n" );
    EXEC SQL CLOSE C;
}

======================= EOF ===================================



 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

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 Thu Aug 06 1998 - 16:42:28 CEST