This seems like such a basic question, but I didn't find anything similar on here...
I am currently generating
exec
SELECT ('exec my_package.my_procedure(input_var1 => ''' || columnA || ''', input_var2 => [... ... ...] || ''');') As sql_stmt FROM myTable;
input_var_my_id => (select NVL(MAX(my_id)+1,1) from someTable where [various conditions] )
PLS-00103
my_package.my_procedure(input_var1 => 'whatever', input_var_my_id => (select NVL(MAX(my_id)+1,1) from someTable where [various conditions] ));
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.