Re: Passing variable from SQL to PL/SQL

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 15 Jan 2003 06:27:44 -0800
Message-ID: <b03r50028q8@drn.newsguy.com>

In article <de8V9.2567$i73.655000_at_twister.neo.rr.com>, "srinivasarao says...
>
>Hello gurus,
>
>I have a simple SQL script which does the following:
>
>/-- Begin --/
>accept <var1> char prompt 'Value for <var1>'
>accept <var2> char prompt 'Value for <var2>'
>accept <var3> char prompt 'Value for <var3>'
>accept <var4> char prompt 'Value for <var4>'
>accept <var5> char prompt 'Value for <var5>'
>...
>...
>execute <package>.<procedure>(&var1, &var2, &var3...);
>/--End --/
>
>Tha procedure in the package is defined to take a list of inout variables.
>

you'll need to use bind variables then, this should work:

ops$tkyte_at_ORA817DEV> create or replace procedure swapem( x in out varchar2, y in out varchar2 )
  2 as
  3 l_tmp varchar2(255) default x;
  4 begin

  5      x:=y;
  6      y:=l_tmp;

7 end;
8 /

Procedure created.

ops$tkyte_at_ORA817DEV>
ops$tkyte_at_ORA817DEV> accept v1 char prompt 'Value for v1 ' Value for v1 Hello
ops$tkyte_at_ORA817DEV> accept v2 char prompt 'Value for v2 ' Value for v2 World

ops$tkyte_at_ORA817DEV>
ops$tkyte_at_ORA817DEV> variable v1 varchar2(255);
ops$tkyte_at_ORA817DEV> variable v2 varchar2(255);
ops$tkyte_at_ORA817DEV>
ops$tkyte_at_ORA817DEV> exec :v1 := '&v1'; :v2 := '&v2';

PL/SQL procedure successfully completed.

World

Hello

ops$tkyte_at_ORA817DEV> set autoprint on
ops$tkyte_at_ORA817DEV>
ops$tkyte_at_ORA817DEV> exec swapem(:v1,:v2);

PL/SQL procedure successfully completed.

Hello

World

>The PL/SQL procedure that I call from within the SQL script does DML
>(insert/delete/update)
>on a table. Above script does not work. Fails with the following error...
>
>***************************************
>BEGIN <package>.<procedure>('value1',; END;
>
> *
>ERROR at line 1:
>ORA-06550: line 1, column 51:
>PLS-00103: Encountered the symbol ";" when expecting one of the following:
>( - + case mod not null others <an identifier>
><a double-quoted delimited-identifier> <a bind variable> avg
>count current exists max min prior sql stddev sum variance
>execute forall merge time timestamp interval date
><a string literal with character set specification>
><a number> <a single-quoted SQL string> pipe
>***************************************
>
>Looks like the substitution is not taking place...but if I change the
>procedure to a function in the
>package definition and try it as follows,
>
>/-- Begin --/
>accept <var1> char prompt 'Value for <var1>'
>accept <var2> char prompt 'Value for <var2>'
>accept <var3> char prompt 'Value for <var3>'
>accept <var4> char prompt 'Value for <var4>'
>accept <var5> char prompt 'Value for <var5>'
>...
>...
>select <package>.<function>(&var1, &var2, &var3...) from dual;
>/--End --/
>
>This also fails because I can not do any DML from within a SELECT.
>
>Any ideas/suggestions?
>
>Thanks in advance.
>Sceenu.
>
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Wed Jan 15 2003 - 08:27:44 CST