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?

Diluent

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

Staceestacey

The problem is that I have more complex Select statement. I just simplified it for this example. – Diluent

Answer 2

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.

Hedvige

Even if Oracle allowed the use of a query as a parameter I'd avoid using such a technique for the sake of clarity. The only way that you can pass a query result directly into a pl/sql object is when using pipelined functions, but that's rather specialised. – Staceestacey

That's right @DavidAldridge! Even I don't try to put select statements into procedures' parameter lists when I code for real purposes, neither have I seen anybody doing the same. – Hedvige

Answer 3

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;

Africander

This only works if the called subroutine is a function, AND it you're calling that function from within doubled-parentheses in a SELECT. If you try DECLARE i integer; BEGIN i := foo((SELECT 'somestring' FROM DUAL)); DBMS_OUTPUT.PUT_LINE(i); END; it still errors off. Replacing the parenthesized SELECT with 'somestring' causes the above to work. – Nathanielnathanil

Yes, and the reason is that replacing a VARCHAR with a SELECT works in SQL but not in PL/SQL. There is a difference between SQL and PL/SQL and this is one example where the difference is evident. – Ahriman

I messed around with this in PL/SQL various ways - tried putting the SELECT FOO((SELECT 'somestring'... in a cursor, etc, and still couldn't get it to work. Maybe in Oracle 12..? :-) – Nathanielnathanil

You can work around it by creating a wrapper: SELECT BAR('somestring')... where BAR is function BAR(s varchar2) return integer is x vharchar2; begin SELECT s into x from dual; return foo(x); end; – Ahriman

Answer 4

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.

Motmot