Passing the result of a subquery as a parameter to PL/SQL package
<p>This seems like such a basic question, but I didn't find anything similar on here... </p>

<p>I am currently generating <code>exec</code> statements by concatenating various strings and columns like this:</p>

<pre><code>SELECT ('exec my_package.my_procedure(input_var1 =&gt; ''' || columnA || ''', input_var2 =&gt; [... ... ...] || ''');') As sql_stmt FROM myTable;
</code></pre>

<p>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):</p>

<pre><code>input_var_my_id =&gt; (select NVL(MAX(my_id)+1,1) from  someTable where [various conditions] )
</code></pre>

<p>After I added that parameter, I am getting a <code>PLS-00103</code> error, saying that the symbol SELECT was encountered when ( - + case mod new not null [....]
 were expected.</p>

<p>The generated exec statement looks like this:</p>

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

<p>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.</p>

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