0

in Oracle, is it possible to have a dynamic number of bind variables in a EXECUTE IMMEDIATE dynamic SQL?

For example, in a given situation i might want to do something like:

execute immediate stmt using lv_name;

But, assuming that stmt is some string that i fetch from some configuration table, one day i might add an additional parameter and would have to recompile the PL/SQL unit to add another parameter, like:

execute immediate stmt using lv_name, lv_age;

...

is it possible in some way to do something like

execute immediate stmt using array_params[] ?

where array params is an array or some sort of structure in PL/SQL that i can handle dynamically? Or receive as a varchar2 parameter and split in some way into an array of tokens?

Thanks!

APC
  • 144,005
  • 19
  • 170
  • 281
user1508072
  • 217
  • 1
  • 3
  • no you would need to use DBMS_SQL package – OldProgrammer Mar 28 '22 at 01:30
  • 3
    Dynamic SQL is intended to solve a certain set of use cases. It is not a magic bullet which allows us to replace our entire code base with a config table and a call to EXECUTE IMMEDIATE. Dynamic SQL is hard to implement because it turns compilation errors into runtime errors: increased flexibility comes at the price of unreliability. Think about it this way: how would you **guarantee** that `array_params[0]` is always `lv_name` and `array_params[1]` is always `lv_age`? – APC Mar 28 '22 at 02:07
  • What is the use case of such scenario? You may generate dynamic SQL that executes dynamic SQL with required number of bind variables. But I suspect that a SQL statement and a list of bind variables doesn't come from nothing. Then it would be better to perform binding in the application that generates this code. – astentx Mar 28 '22 at 06:36

0 Answers0