Re: Creating a Stored Procedure from Pro*C

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 21 Jan 1999 19:31:03 GMT
Message-ID: <36aa7fa5.14499328@192.86.155.100>

A copy of this was sent to matthew_at_mattshouse.com (if that email address didn't require changing) On Thu, 21 Jan 1999 17:22:44 GMT, you wrote:

>Is it possible to create a stored procedure from Pro*C? Here is an example of
>what I would like to do:
>
>EXEC SQL
> CREATE OR REPLACE PROCEDURE :buf1 (
> :buf2
> END :buf1;
>
>:buf2 is the procedure code, while :buf1 is the name of the procedure.
>

you cannot 'bind' that kind of stuff. You can only 'bind' values where a character string constant could go (eg: you cannot 'bind' a column name in a sql select list, you can bind a value in a where clause though).

Identifiers cannot be 'bound' to.

One way to do this is:

static void process( char * proc_name, char * proc_body ) {
VARCHAR sqlstmt[1024];

    sprintf( sqlstmt.arr, "create or replace procedure %s as begin %s end %s;",

                           proc_name, proc_body, proc_name );

    sqlstmt.len = strlen(sqlstmt.arr);

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

    EXEC SQL EXECUTE IMMEDIATE :sqlstmt; }

>Thanks,
>
>Matthew
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

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

--
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 Jan 21 1999 - 13:31:03 CST