Re: Passing variable from SQL to PL/SQL

From: srinivasarao panganamamula <spanganamamula_at_cinci.rr.com>
Date: Wed, 15 Jan 2003 18:07:08 GMT
Message-ID: <grhV9.5266$i73.1006377@twister.neo.rr.com>

Thanks for the suggestions, Thomas...but I did not use bind variables for this.
I figured out that in my call to the PL/SQL program if I do not give all the variables in a
single line, it is not accepting the input variables...in other words, after prompting for and
accepting the user input, if try the call as follows:

execute <package>.<procedure>('&var1',

                                                    '&var2',
                                                    '&var3',
                                                    ..........)

then I run into a problem I described in my earlier post...but if I try the above as

execute <package>.<procedure>('&var1', '&var2', '&var3'...), it works fine.

I did not give the statements exactly as I ran them in my program, in my earlier post. I did
not think that putting the variables in a seperate lines would make a difference for the call.
But looks like it does.

Thank you very much for your time.

Sceenu.

"Thomas Kyte" <tkyte_at_oracle.com> wrote in message news:b03r50028q8_at_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.

>
>

> V2
> ----------
> World

>
>

> V1
> ----------
> 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.

>
>

> V2
> ----------
> Hello

>
>

> V1
> ----------
> 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 - 12:07:08 CST