Re: OCI application-Procedure problem...

From: Gert Rijs <nospampleasegem_at_wirehub.nl>
Date: 14 Jul 1998 22:48:37 GMT
Message-ID: <01bdaf76$cff7d7d0$0100007f@gertrijs>

Jacques Sireude <sireude_at_eiffel.com> wrote in article <6ofur1$d74$1_at_news.rain.org>...
> Hi,
> I would like to do so:
>
> sql_statement= "create procedure (name VARCHAR2, tmp_row DB_BOOK%rowtype)
as
> begin select * into tmp_row from DB_BOOK where AUTHOR = name; end;";
>
> then:
>
> oparse (&cda, sql_statement...)
>
> then
>
> odescr, odefin with the same CDA.
>
> then oexec (&cda).
>
> Everything is working fine...
> But when I want to execute the procedure:
>
> sql_statement="execute DB_BOOK_PROC ('sireude', tmp_row)"
> then
> oparse, odescr, odefin, oexec... In fact I do not know how to do...
>
> Thanks for any help... and sorry if it is not clear enough...
>
> Jacques.

[posted & mailed]

You should:
sql_statement = "begin db_book_proc('sireude', tmp_row); end;" instead of "exec db_book_proc..."
exec is a SQL*PLUS command (actually a shortcut for having to type "begin ... end;".
BUT
you can not bind a %rowtype to a struct using C(++): you must 'pass' each variable 'stand-alone', like:
sql_statement =

"declare "
" tmp_row db_book%row_type; "
"begin "
"  tmp_row.booknum := :mybooknum; "
"  tmp_row.author  := :myauthor; "
"  db_book_proc('sireude', tmp_row); "
"  :mybooknum := tmp_row.booknum; "
"  :myauthor := tmp_row.author; "
"end;";

and then after oparse, obindra your c-fields to :mybooknum and :myauthor, finally oexec.
No need to call odefin when you execute pl/sql, all bound fields are input and output automatically.

hope this helps
--
reply address is altered to keep the spam down remove the nospamplease part to reply... Gert Rijs
gem at wirehub.nl
www.wirehub.nl/~gem Received on Tue Jul 14 1998 - 17:48:37 CDT