Re: Dynamic stored procedure using OCI?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 08 Oct 1998 12:52:45 GMT
Message-ID: <361cb43e.2975829@192.86.155.100>

A copy of this was sent to Arun Jamwal <arun_at_quality.com> (if that email address didn't require changing) On Wed, 07 Oct 1998 10:54:39 -0700, you wrote:

>Hi,
>Does anyone know how to execute stored procedures
>dynamically (ie., don't know the procedure you will be calling
>until runtime, not at precompile time) using OCI?
>
>I would really appreciate any help!
>
>Thanks,
>Arun.

this is sort of confusing -- all OCI performed SQL statements are in strings. None of them are known until runtime -- OCI does not have a 'precompile' step, only a compile step.

If you take the file $ORACLE_HOME/rdbms/demo/cdemo2.c and make a 'small' modification to it, you'll have a pretty large example. cdemo2 works a little like sqlplus. Unfortunately, by default it looks for a semi colon as the statement terminator and submits the sql statement as soon as it sees one. this makes typing a pl/sql block into cdemo2 impossible.

If you edit cdemo2.c and look for:

        if (*cp == ';')
        {
            *cp = '\0';
            break;
        }

and change that to something like:

        if (*cp == '!')
        {
            *cp = '\0';
            break;
        }

then, you'll be able to use cdemo2 to submit pl/sql blocks as well as SQL statements...

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 Oct 08 1998 - 07:52:45 CDT