Binding variables in dynamic PL/SQL

Question

I have a dynamic PL/SQL that will construct the SELECT statement based on what the searching criteria input from the users,likes:

     l_sql := 'SELECT *  INTO  FROM TABLEA WHERE 1=1 ';

      IF in_param1 IS NOT NULL THEN
        l_sql := l_sql || 'AND column1 = in_param1 ';
      END IF;

      IF in_param2 IS NOT NULL THEN
        l_sql := l_sql || 'AND column2 = in_param2 ';
      END IF;
      ...................................

     IF in_paramXX IS NOT NULL THEN
        l_sql := l_sql || 'AND columnXX = in_paramXX ';
      END IF;

To reduce the hard parse overhead , I consider to use the binding variables. However , it is difficult to manage when supplying the actual values to the binding variables as there are so many binding variables and combination of the generated SELECT statement . I cannot use the method of DBMS_SESSION.set_context() introduced at http://www.dba-oracle.com/plsql/t_plsql_dynamic_binds.htm because my account has no right to use this package. Besides , I want the generated SQL only contains the conditions on the fields that the user did not leave empty. So I cannot change the dynamic SQL to something likes

SELECT *  INTO  FROM TABLEA WHERE 1=1
and ( in_param1 is NULL or  column1 = in_param1)
and ( in_param2 is NULL or  column2 = in_param2)
...............................................
and ( in_paramXX is NULL or  columnXX = in_paramXX)

So , I want to try to use the DBMS_SQL method .Can anyone give an example about how to use DBMS_SQL to call dynamic SQL with binding variables? Especially , how can I get the result executed from DBMS_SQL.execute() to the SYS_REFCURSOR , something like :

open refcursor for select .... from

The oracle version that I use is 10g and it seems that the oracle 10g does not have DBMS_Sql.To_Refcursor()

Answer 1

In 10g a DBMS_SQL cursor can't be changed into a Ref Cursor. Going through a result set through DBMS_SQL is tortuous since, as well as looping through the rows, you also have to loop through the columns in a row.

I want the generated SQL only contains the conditions on the fields that the user did not leave empty

Is that purely for performance reasons ? If so, I suggest you work out what the practical execution plans are and use separate queries for them.

For example, say I'm searching on people and the parameters are first_name, last_name. gender, date_of_birth. The table has indexes on (last_name,first_name) and (date_of_birth), so I only want to allow a query if it specifies either last_name or date_of_birth.

IF :p_firstname IS NOT NULL and :p_lastname IS NOT NULL THEN
  OPEN cur FOR 
    'SELECT * FROM PEOPLE WHERE last_name=:a AND first_name=:b AND
     (date_of_birth = :c or :c is NULL) AND (gender = :d or :d IS NULL)' USING ....
ELSIF :p_lastname IS NOT NULL THEN
  OPEN cur FOR 
    'SELECT * FROM PEOPLE WHERE last_name=:a AND 
     (date_of_birth = :c or :c is NULL) AND (gender = :d or :d IS NULL)' USING ....
ELSIF :p_dateofbirth IS NOT NULL THEN
  OPEN cur FOR 
    'SELECT * FROM PEOPLE WHERE date_of_birth=:a AND 
     (first_name=:b OR :b IS NULL) AND (gender = :d or :d IS NULL)' USING ....
ELSE
  RAISE_APPLICATION_ERROR(-20001,'Last Name or Date of Birth MUST be supplied);
END IF;