Sql – Passing varchar2 parameter to plsql procedure from the Select statement


Executing the following statement in Oracle Toad

exec plsql_procedure(select 'somestring' from dual);

trhows the following exception:

ORA-06550: line 1, column 33:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

( ) – + case mod new not null

Procedure takes one VARCHAR2 parameter. How it can be fixed?

Best Solution

Unless your use of the select from dual is just an example of something else, leave out the selection.

exec plsql_procedure('somestring');

Otherwise, select into a variable and pass that to the procedure.

  my_var table_name.column_name%Type;

  select column_name
  into   my_var
  from   table_name
  where  ...;

  plsql_procedure(parameter_name => my_var);

Related Question