Re: Q. Pro/C and Stored Procedures

A copy of this was sent to "Kim Limbrick" <kim_at_kiml.demon.co.uk> (if that email address didn't require changing) On Sat, 17 Apr 1999 00:29:23 +0100, you wrote:

>I can't seem to get what should be a trivial piece of code working and have
>been going around in circles with the documentation. I have the following
>code in my C program which works fine..
>
>EXEC SQL EXECUTE
> BEGIN
> calcDate('01-Jan-1999', '01-Dec-1999', :returnDate);
> END;
> END-EXEC;
>
>- calcDate is a stored procedure.
>
>If I want to do this dynamically I am led to believe that all I have to do
>it set a string host variable to the PL/SQL block and PREPARE then EXECUTE
>it. When I do that I get runtime errors indicating a SQL error. What I'm not
>sure about is how much of the above PL/SQL block do I include in the
>string?. Any ideas?
>

Here is one example of a way to do that:

static void process( int mgr, int level ) {
EXEC SQL BEGIN DECLARE SECTION;

    VARCHAR     date_str[45];
    VARCHAR     sql_stmt[255];

        "begin calcDate('01-Jan-1999', '01-Dec-1999', :b1 ); end;" );     sql_stmt.len = strlen( sql_stmt.arr );

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();     EXEC SQL PREPARE S FROM :sql_stmt;
    EXEC SQL DECLARE C CURSOR FOR S;     EXEC SQL OPEN C USING :date_str;

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

the OPEN actually executes the statement, everytime you open it, it would bind all inputs, run the procedure and get all inputs for you. You use the USING clause with the dynamic sql (it doesn't do it by name when you use dynamic sql)....

My procedure to test with was:

create or replace procedure calcDate( d1 in date, d2 in date, d3 out date ) as
begin

        d3 := SYSDATE + ( d2-d1 );
end;
/

>Many thanks
>
>
 

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