Passing the result of a subquery as a parameter to PL/SQL package

Question

This seems like such a basic question, but I didn't find anything similar on here...

I am currently generating

exec

statements by concatenating various strings and columns like this:

SELECT ('exec my_package.my_procedure(input_var1 => ''' || columnA || ''', input_var2 => [... ... ...] || ''');') As sql_stmt FROM myTable;

This worked just fine until I decided to add another input parameter that should get its value from a subquery like this (I left out the irrelevant parts, it's the same query as above just with an added parameter):

input_var_my_id => (select NVL(MAX(my_id)+1,1) from  someTable where [various conditions] )

After I added that parameter, I am getting a

PLS-00103

error, saying that the symbol SELECT was encountered when ( - + case mod new not null [....]
were expected.

The generated exec statement looks like this:

 my_package.my_procedure(input_var1 => 'whatever', input_var_my_id => (select NVL(MAX(my_id)+1,1) from  someTable where [various conditions] ));

The subquery itself is valid, if I copy it from the generated statement and execute it, I am getting a single dataset as a result - just as expected.

Is it not possible to pass the result of a subquery as a parameter to a PL/SQL package? Are there any workarounds? Thanks for your help

Answer 1

No, it isn't possible to have a query inside the actual parameter list. You can call some functions directly but only those implemented in PL/SQL. You can't switch to an SQL context. You can see that with a simpler example:

exec dbms_output.put_line(select user from dual);

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

You will have to put the query result into a local variable, which is going to be ugly in your generated code; it needs to end up as:

declare
  l_input_var_my_id someTable.my_id%type;
begin
  select NVL(MAX(my_id)+1,1) into l_input_var_my_id from someTable where [various conditions];
  my_package.my_procedure(input_var_my_id => l_input_var_my_id, ...);
end;

You can put all of that in an exec:

exec declare l_input_var_my_id someTable.my_id%type; begin select NVL(MAX(my_id)+1,1) into l_input_var_my_id from someTable where [various conditions];  my_package.my_procedure(input_var_my_id => l_input_var_my_id, ...); end;

... and then generate that from your original query.

But as exec is just a shorthand for an anonymous block and has to be on one line by default, it might be clearer to generate the block - so have that without the exec and with a / on a line on its own. Depends how you plan to call the result, and whether it needs to be easy to read.