Passing varchar2 parameter to plsql procedure from the Select statement

Question

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?

Answer 1

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.

declare
  my_var table_name.column_name%Type;
begin

  select column_name
  into   my_var
  from   table_name
  where  ...;

  plsql_procedure(parameter_name => my_var);

end;
/