Oracle EXECUTE IMMEDIATE with variable number of binds possible?

Question

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.

Catania

Related: Why cannot I use bind variables in DDL/SCL statements in dynamic SQL? – Trilly

Answer 1

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.

Brian

Answer 2

You could also work around this problem by using a WITH statement. Generally using DBMS_SQL is better, but sometimes this is a simpler way:

BEGIN
    EXECUTE IMMEDIATE 'WITH var AS (SELECT :var FROM dual) SELECT SYSDATE FROM DUAL WHERE (SELECT * FROM var) = (SELECT * FROM var)' USING 1;
END;

Emancipator

This is my favourite answer. I'd name it, e.g. with vars as ( select :1 v_surname, :2 v_forename from dual ) select person_id from people where surname = (select v_surname from vars) and forename = (select v_forename from vars) – Legwork

Answer 3

This Thread on AskTom covers the subject in details.

In your case if you want to pass one parameter or none, you could build two queries that have a single parameter and in one of these query it is not used (i-e the predicate is always true) like this:

-- query1
SELECT * FROM DUAL WHERE dummy = :x;

-- query2
SELECT * FROM DUAL WHERE nvl(:x, 1) IS NOT NULL;

You can probably refine the predicate so that the optimizer will understand that it is always true.

Dunaj

The problem is that the incoming SELECT statements are not known except that a single parameter (an ID) is used at least once or even several times. The number of binds is not known. – Catania

You will have to use dbms_sql if the number of parameters is unknown – Dunaj

Answer 4

One can use dbms_sql like Steve Broberg explained but the resulting cursor can't be consumed (read) in a lot of clients. Oracle 11 has added a conversion function (dbms_sql.to_refcursor) that makes it possible to convert a dbms_sql cursor to a ref cursor but for some reason one can't consume this converted ref cursor in a .Net application. One can consume a normal ref cursor in .net but not a ref cursor that used to be dbms_sql cursor.

So what kind of client will be consuming this cursor?

Silicon

It will be used in subsequent PL/SQL, so no problem with clients. – Catania

Answer 5

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:

• Assign one bind variable a varying number of times to dynamically generated SQL

Kantor