Re: Executing stored procedures in dynamic SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 05 Jul 1998 18:36:50 GMT
Message-ID: <35a6c75c.27879758@192.86.155.100>

A copy of this was sent to "Dolf-Jan Mulder" <djmulder_at_advantage.nl> (if that email address didn't require changing) On 5 Jul 1998 11:24:53 GMT, you wrote:

>Hello,
>
>I want to execute procedures in dynamic SQL. The name of the procedures are
>stored in my application database. Depending on the situation a want to
>execute one of them. It would be nice if I could execute the procedure in
>dynamic SQL and get the results back in my program. Is this possible? Does
>somebody have a sample?
>
>DJ
>

Here is a proc example, you didn't specify the client tool/language you are using tho...

this example shows a STATIC invocation of a pl/sql block and a dynamic (from a string) invocation of the same pl/sql block.

Hope this helps, it shows setting inputs and outputs to/from anonymous pl/sql blocks...

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

    VARCHAR     plsqlstmt[512];
    VARCHAR     some_str[255];
    int         status;

EXEC SQL END DECLARE SECTION; #define STMT "\
begin \

    dbms_output.enable; \
    dbms_output.put_line( :some_str ); \
    dbms_output.get_line( :some_str, :status ); \

    :some_str := upper(:some_str); \
end;"

    strcpy( some_str.arr, "This is some data" );     some_str.len = strlen( some_str.arr );     EXEC SQL EXECUTE
    BEGIN

        dbms_output.enable;
        dbms_output.put_line( :some_str );
        dbms_output.get_line( :some_str, :status );
        :some_str := upper(:some_str);

    END;
    END-EXEC;     printf( "Some String = '%.*s'\n", some_str.len, some_str.arr );     printf( "Status = %d\n", status );

    strcpy( some_str.arr, "This is some data" );     some_str.len = strlen( some_str.arr );

    strcpy( plsqlstmt.arr, STMT );
    plsqlstmt.len = strlen( plsqlstmt.arr );

    EXEC SQL PREPARE PLSQL_STMT FROM :plsqlstmt;
    EXEC SQL DECLARE PLSQL_CURS CURSOR FOR PLSQL_STMT;
    EXEC SQL OPEN    PLSQL_CURS USING :some_str, :status;

    printf( "Some String = '%.*s'\n", some_str.len, some_str.arr );
    printf( "Status      = %d\n", status );

}  

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 Sun Jul 05 1998 - 13:36:50 CDT