0

I know the following works (this would be part of the code inside a stored procedure):

sql_string := 'INSERT INTO my_table (data_id, data_col) SELECT SYS_GUID(),''{"json_id1":'' || (:value_1 - val_from_view_1 + :i - 1) || ''}'' FROM my_view WHERE col_1 <= :value_2';

FOR i IN 1..N LOOP
    EXECUTE IMMEDIATE sql_string
    USING IN OUT input_value_1, i, input_value_2;
END LOOP;

My question is, since that sql_string comes as an input parameter to my stored procedure, how do I deal with a variable number of parameters for the USING clause? That is, how do I cover the above case, a case where the sql_string is such that I have something like this:

EXECUTE IMMEDIATE sql_string
USING IN OUT input_value_1, i, input_value_2, ..., input_value_N;

and everything in between?

PS: I'm not married to EXECUTE IMMEDIATE, so if there's a better way to do this, feel free to suggest it.

Octavio Kidd
  • 5
  • 4
  • Can you explain what are you trying to achieve here? – z m Mar 29 '17 at 22:28
  • I want to be able to execute a SQL statement that comes as an input parameter to a stored procedure. That SQL statement in turn can ha a variable number of parameters, some which depend on values calculated outside or inside the loop, or even the index of the loop the EXECUTE IMMEDIATE is contained in. – Octavio Kidd Mar 30 '17 at 00:40
  • From your example I don't see any reason to use dynamic SQL at all. Did you try static command? E.g. `INSERT INTO my_table (data_id, data_col) SELECT SYS_GUID(), '{"json_id1":' || (input_value_1 - val_from_view_1 + i - 1) || '}' FROM my_view WHERE col_1 <= input_value_2;` – Wernfried Domscheit Mar 30 '17 at 05:51
  • The SQL statement will be dynamically generated and can vary widely in size (and of course complexity). The above is just a simple example, but the JSON this generates per record can be any size depending on the type of object it's associated with. – Octavio Kidd Mar 30 '17 at 13:47

0 Answers0