title: Binding variables in dynamic PL/SQL
body: <p>I have a dynamic PL/SQL that will construct the SELECT statement based on what the searching criteria input from the users,likes: </p>
  
  <pre><code>     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;
  </code></pre>
  
  <p>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 <a href="http://www.dba-oracle.com/plsql/t_plsql_dynamic_binds.htm" rel="nofollow noreferrer">http://www.dba-oracle.com/plsql/t_plsql_dynamic_binds.htm</a> 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 </p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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 :</p>
  
  <pre><code>open refcursor for select .... from
  </code></pre>
  
  <p>The oracle version that I use is 10g and it seems that the oracle 10g does not have DBMS_Sql.To_Refcursor() </p>