Alex Rivera | Logout

Oracle EXECUTE IMMEDIATE with variable number of binds possible?

Asked 2009-06-17T15:45:04.147
12

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.

oracle-database plsql dynamic-sql
Edit
Report

1 Answer

1

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 actually ran into this exact same issue a couple of days ago, and a friend shared with me a way to do exactly that with EXECUTE IMMEDIATE.

It involves generating a PLSQL block as opposed to the SQL block itself. When using EXECUTE IMMEDIATE with a block of PLSQL code, you can bind variables by name as opposed to just by position.

Check out my example/code and on my own similar question/answer thread:

answered 2011-05-18T20:58:37.670

Your Answer