Passing varchar2 parameter to plsql procedure from the Select statement

4

votes

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?

sqloracleplsqltoad

3

votes

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;
/

2

votes

I have tried a few approaches. Those include:

exec plsql_procedure((select 'somestring' from dual));

EXECUTE IMMEDIATE ('testProc('''||(SELECT 'Testing' FROM dual)||''')');

EXECUTE IMMEDIATE ('EXEC testProc('''||(SELECT 'Testing' FROM dual)||''')');

None of the above worked. Looks like Oracle doesn't allow SELECT statements in the arguments list under any circumstances.

One thing I did was store the SELECT query result into a variable and use it later like follows:

CREATE OR REPLACE PROCEDURE testProc (
   testVar IN VARCHAR2 -- Proc accepts VARCHAR2
) IS
BEGIN
    dbms_output.put_line(testVar);
END;
/

SET SERVEROUTPUT ON
DECLARE
  testVarIn VARCHAR(2000); -- Declare the variable
BEGIN
  SELECT 'Testing' INTO testVarIn FROM dual;
  -- Accept its value using SELECT ... INTO 

  testProc(testVarIn); -- Use the variable value to pass to the proc
END;
/

Output:

PROCEDURE testProc compiled
anonymous block completed
Testing

When things don't happen, we have to live with the alternatives. Our end goal is just to get the job done. It doesn't matter what we do for getting it done.

1

votes

It is possible for functions, but not for procedures. Note that the select needs to be enclosed in parenthesis, hence the double parenthesis in the select below.

create or replace function foo (x varchar2) return integer is
begin 
  --Do various stuff;
  return 1;
end;
/

select foo((select 'somestring' from dual)) from dual;

0

votes

One thing to keep in mind is that a relational query does not return a VALUE - it returns a TABLE. Even though we might know due to the context (such as selecting a literal value from DUAL) that a particular SELECT will only return a single row, the database does not know that and will assume that the SELECT will return a table with multiple rows. Even SELECT 1 FROM DUAL returns a table - this table has a single column and a single row, but it's still a table. :-)

Share and enjoy.

Passing varchar2 parameter to plsql procedure from the Select statement

4 Answers