Re: Passing variable from SQL to PL/SQL

Tom did provide you with the right answer.

If you still plan to use your way (assuming you don't care about the out values) .. you should realize that exec command should be in one line. To give that command on multiple lines, you need to end line with line continuation character "-"  execute <package>.<procedure>('&var1',-

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

Anurag

"srinivasarao panganamamula" <spanganamamula_at_cinci.rr.com> wrote in message news:grhV9.5266$i73.1006377_at_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 - 22:50:53 CST