| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Problem with stored procedure and ProC
A copy of this was sent to Alan Barclay
<barclaya_at_NO-SPAM.aberdeen.geoquest.slb.com>
(if that email address didn't require changing)
On Wed, 08 Apr 1998 12:13:45 +0100, you wrote:
>Hi,
>
>I would appreciate some help from anybody who is familiar will calling
>stored procedures from ProC.
>
>The problem for me is that the stored procedure which I need to call
>takes 3 IN arguments (easy) and 1 OUT argument (hard). Below is the code
>
>which I am using, but always it complains about the OUT variable either
>not being bound or there being no SELECT. This all seems so simple but
>neither of the two versions of code works!
>
From the proc manual on binding to pl/sql routines:
<quote>
VARCHAR Pseudotype
Recall from Chapter 3 that you can use the VARCHAR datatype to declare variable–length character strings. If the VARCHAR is an input host variable, you must tell Oracle what length to expect. So, set the length component to the actual length of the value stored in the string component.
If the VARCHAR is an output host variable, Oracle automatically sets the length component. However, to use a VARCHAR output host variable in your PL/SQL block, you must initialize the length component before entering the block. So, set the length component to the declared (maximum) length of the VARCHAR, as shown here:
int emp_number;
varchar emp_name[10];
float salary;
...
emp_name.len = 10; /* initialize length component */
EXEC SQL EXECUTE
BEGIN
SELECT ename, sal INTO :emp_name, :salary
FROM emp
WHERE empno = :emp_number;
...
END;
END–EXEC;
</quote>
so, your variable is an OUTPUT host variable. to use a varchar output host variable in your pl/sql block, you must initialize the length component...
You need to add:
>Thank in advance for any ideas.
>
>// TRY 1
> EXEC SQL BEGIN DECLARE SECTION;
> char *bla;
> char *start;
> char *end;
> varchar op[ 1024 ];
> EXEC SQL END DECLARE SECTION;
>
>bla = "bla";
>start = "01-APR-98";
>end = "01-APR-98";
>
op.len = 1024;
> EXEC SQL EXECUTE
> BEGIN
> sp_tv( :bla, :start, :end, :op );
> END;
> END-EXEC;
>
>sqlca.sqlerrm.sqlerrmc[ sqlca.sqlerrm.sqlerrml - 1 ] = '\0';
>printf("%s\n", sqlca.sqlerrm.sqlerrmc );
>
>printf("[%s] %d\n", op, sqlca.sqlcode );
>
>// TRY 2
>sprintf( sql, "BEGIN sp_tv( 'bla', '01/01/1990 00:00:00', '02/01/1990
>00:00:00', :op ); END;" );
>
>EXEC SQL DECLARE CD STATEMENT;
>EXEC SQL PREPARE CD FROM :sql;
>EXEC SQL EXECUTE CD USING :op;
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
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 Apr 09 1998 - 21:18:02 CDT
 |
 |