Oracle EXECUTE IMMEDIATE with variable number of binds possible?

oracle plsql dynamic-sql
Kosi2801 picture Kosi2801 · Jun 17, 2009 · Viewed 29.9k times · Source

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.

Answer

Steve Broberg picture Steve Broberg · Jun 17, 2009

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.

Related questions

ORA-01747: invalid user.table.column, table.column, or column specification

Get the above error when the execute immediate is called in a loop Update CustomersPriceGroups set 1AO00=:disc Where cuno=:cuno Parameters: disc=66 cuno=000974 Update CustomersPriceGroups set 1AP00=:disc Where cuno=:cuno Parameters: disc=70.5 cuno=000974 Update CustomersPriceGroups set 1AQ00=:disc …

Read More
PL/SQL - Optional conditions in where-clause - without dynamic sql?

I have a query where not all conditions are necessary. Here's an example of what it looks like when all conditions are used: select num from (select distinct q.num from cqqv q where q.bcode = '1234567' --this is …

Read More
How to solve ORA-29471 on dbms_sql.open_cursor?

I'm using Oracle 11.2.0.1.0 and am trying to get the dbms_sql package to work. However, I keep getting the ORA-29471 error, as shown below: DECLARE c INTEGER; BEGIN c := dbms_sql.open_cursor(); END; ORA-29471: DBMS_SQL access denied ORA-06512: …

Read More