Passing the result of a subquery as a parameter to PL/SQL package
This seems like such a basic question, but I didn't find anything similar on here...
I am currently generating
execSELECT ('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-00103were 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
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.