Oracle EXECUTE IMMEDIATE with variable number of binds possible?
dynamic-sql, oracle, plsql
Solution
You can't do this with `EXECUTE IMMEDIATE`. However, you can do this by using Oracle's `DBMS_SQL` package. The Database Application Developer's Guide has a comparison between the `EXECUTE IMMEDIATE` you're familiar with and `dbms_sql` methods. This page documents `DBMS_SQL`, but has some examples (linked above) that should get you started (example 1 is a simple case of running a statement that could have an arbitrary number of bind variables). `DBMS_SQL` is a lot more cumbersome from a coding perspective, but it will allow you to do just about anything you can conceive.
Multiple instances of the bind variable occurring in the SQL are allowed. However, you will have to know the name being used as the bind variable (e.g. :var in your case) in order to pass it into `DBMS_SQL.BIND_VARIABLE`.
Problem
I need to use dynamic SQL execution on Oracle where I do not know the exact number of bind variables used in the SQL before runtime. Is there a way to use a variable number of bind variables in the call to `EXECUTE IMMEDIATE` somehow? More specifically, I need to pass one parameter into the unknown SQL but I do not know how often it will be used there. I tried something like ``` EXECUTE IMMEDIATE 'SELECT SYSDATE FROM DUAL WHERE :var = :var' USING 1; ``` But it threw back with `ORA-01008: not all variables bound.`