Re: Dynamic SQL using OCI?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/09/16
Message-ID: <3601c0d5.79661136@192.86.155.100>#1/1

A copy of this was sent to Arun Jamwal <arun_at_quality.com> (if that email address didn't require changing) On Tue, 15 Sep 1998 17:54:41 -0700, you wrote:

>Hi,
>I was wondering if it is possible to execute
>dynamic SQL queries using OCI?
>

yes, in fact its the *only* way to execute sql. OCI is a C Call Level Interface. There is no such thing as a 'static' sql request in OCI.

>I have done it using Pro*C. However, I could
>not execute dynamic stored procedures using
>Pro*C, therefore, I am opting for OCI.
>

Why not? Its very easy to dynamically call PL/SQL from Pro*C (easier I believe then using OCI). You will only need to use OCI if the pl/sql you are calling accepts PL/SQL Tables as inputs or outputs (you cannot dynamically call a pl/sql routine that takes tables in Pro*C, you must use OCI for that). Other then that, its straight forward. Here are 2 examples of how to do it:

void process(void )
{
EXEC SQL BEGIN DECLARE SECTION;

VARCHAR     str[512];
VARCHAR     something[512];

EXEC SQL END DECLARE SECTION;     strcpy( something.arr, "Some Data Here" );     something.len = strlen( something.arr );

    strcpy( str.arr, "begin dbms_output.put_line( :something ); end;" );     str.len = strlen( str.arr );

    EXEC SQL PREPARE S FROM :str;
    EXEC SQL EXECUTE S USING :something; }

Alternatively, if you plan on calling this routine many times and want to avoid the parse/reparse/reparse it would be doing, you could code:

void process(void )
{
EXEC SQL BEGIN DECLARE SECTION;

VARCHAR        str[512];
VARCHAR        something[512];

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

    if ( firstTime )
    {

        strcpy( str.arr, "begin dbms_output.put_line( :something ); \
                                :something := 'Ok, did it'; \
                            end;" );
        str.len = strlen( str.arr );

        EXEC SQL PREPARE S FROM :str;
        EXEC SQL DECLARE C CURSOR FOR S;
        firstTime = 0;

    }

    strcpy( something.arr, "Some Data Here" );     something.len = strlen( something.arr );

    EXEC SQL OPEN C USING :something;
    printf( "%.*s\n", something.len, something.arr ); }

The cursor will be bound to the parsed statement once this way and you would just be executing the pl/sql block over and over again by 'opening' the cursor with different bind variables...

>Any tips, feedback would be greatly appreciated!
>
>Thanks in advance.
>
>Arun.
 

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 Wed Sep 16 1998 - 00:00:00 CDT