Passing variable from SQL to PL/SQL

From: srinivasarao panganamamula <spanganamamula_at_cinci.rr.com>
Date: Wed, 15 Jan 2003 07:38:49 GMT
Message-ID: <de8V9.2567$i73.655000@twister.neo.rr.com>

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.

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. Received on Wed Jan 15 2003 - 01:38:49 CST