Re: dbms_sql and stored procedures

From: Peter Schneider <peter.schneider_at_okay.net>
Date: Sat, 22 Aug 1998 00:29:31 GMT
Message-ID: <6rl3dl$dt0$1@trader.ipf.de>

Hi Piotrek,

On Thu, 20 Aug 1998 10:05:56 +0200, Piotrek <piotrek_at_wasko.gliwice.pl> wrote:

>Kevin P. Fleming wrote:
>
>> "execute" is an SQL*Plus command, not a PL/SQL command. To do this =
properly,
>> your v_proc variable should be built like this:
>>
>> v_proc := 'begin; ' || c1rec.proc_name || '; end';

                     ^                               ^

I suggest that you put this semicolon .............here and it will work ;-)

>> RichFord wrote in message <35DB4AF2.AD555C00_at_netcom.com>...
>>
>>
>
>would you tell how put some parameters into selected procedure, and how =
extract
>results ....
> thanks

For using parameters, you have two options:

• you can concatenate in-parameters directly (as literals) into the statement string
• for in-out or out parameters, you can use bind variables, e.g.

   v_proc := 'BEGIN ' || proc_name || '(:v1, :v2); END;';

and before executing, use DBMS_SQL.BIND_VARIABLE to specify input parameter values. After executing, you can restrieve out parameter values with DBMS_SQL.VARIABLE_VALUE.

If you want to call a function this way, you would similarly write it like this:

   v_proc := 'BEGIN :v := ' || my_function || '(:p1, :p2); END;';

then bind p1 and p2, and after calling get v with DBMS_SQL.VARIABLE_VALUE. HTH,
Peter

--
Peter Schneider
peter.schneider_at_okay.net Received on Fri Aug 21 1998 - 19:29:31 CDT