Dynamic number of bind variables in dynamic sql - Open Cursor

Question

In My stored procedure, I have a query which is dynamic - the number of conditions in where clause varies depending on the input parameter.

    in params - x, y, z

    searchsql := 'select select1, select2, select3 from tableA where 1 = 1 and ';

    if(x is not null) then
       searchSql := searchSql || PKG_COMMON.GET_SQL_BINDTXTFLD(x,'select1','a');
       -- above package will return  AND upper(select1) like upper(:a)
       cursorParams := cursorParams || ':' || x || ',';
    end if;

    if(y is not null) then
       searchSql := searchSql || PKG_COMMON.GET_SQL_BINDTXTFLD(y,'select2','b');
       -- above package will return  AND upper(select2) like upper(:b)
       cursorParams := cursorParams || ':' || y || ',';
    end if;

--I am trimming the last comma of the cursor param

     SELECT SUBSTR(cursorParams, 1, INSTR(cursorParams , ',', -1)-1)
            INTO cursorParams FROM dual;

    open resultCursor for searchSql using cursorParams

Now, i have this above cursor which needs to be opened using params passed, however in this case the number of params depends on how is sql is formed. So i am dynamically forming the bind variables using cursorParams variable

But the values are not binding, but set only to the first param

how to bind properly, i already tried execute immediate option

I think this is a similar question to one below. https://stackoverflow.com/questions/2514254/how-can-i-create-a-dynamic-where-clause — Jignesh, Sep 18 '17 at 19:27
@Jignesh, This is dynamic query and not dynamic bind variable with dynamic query — Srikanth Balaji, Sep 18 '17 at 23:34
@SrikanthA This might be a duplicate of https://stackoverflow.com/q/7816402/409172 My answer to that question might apply here. — Jon Heller, Sep 21 '17 at 02:48

score 2 · Answer 1 · answered Sep 21 '17 at 20:37

Here is a simple example of what you are asking using DBMS_SQL to dynamically build and bind.

DECLARE
  i_owner       all_objects.owner%TYPE := 'SYS';
  i_object_name all_objects.object_name%TYPE := 'DUAL';
  i_object_type all_objects.object_type%TYPE := NULL;

  v_statement VARCHAR2(4000);
  v_cursor    BINARY_INTEGER := dbms_sql.open_cursor;
  v_rows      BINARY_INTEGER;
  v_result    SYS_REFCURSOR;

  v_owner       all_objects.owner%TYPE;
  v_object_name all_objects.object_name%TYPE;
  v_object_type all_objects.object_type%TYPE;
BEGIN
  v_statement := 'select owner, object_name, object_type from all_objects where 1 = 1';

  IF i_owner IS NOT NULL THEN
    v_statement := v_statement ||
                   q'[ and owner like upper(:owner) || '%']';
  END IF;

  IF i_object_name IS NOT NULL THEN
    v_statement := v_statement ||
                   q'[ and object_name like upper(:object_name) || '%']';
  END IF;

  IF i_object_type IS NOT NULL THEN
    v_statement := v_statement ||
                   q'[ and object_type like upper(:object_type) || '%']';
  END IF;

  dbms_output.put_line(v_statement);

  dbms_sql.parse(c             => v_cursor,
                 STATEMENT     => v_statement,
                 language_flag => dbms_sql.native);

  IF i_owner IS NOT NULL THEN
    dbms_sql.bind_variable(c     => v_cursor,
                           NAME  => 'owner',
                           VALUE => i_owner);
  END IF;

  IF i_object_name IS NOT NULL THEN
    dbms_sql.bind_variable(c     => v_cursor,
                           NAME  => 'object_name',
                           VALUE => i_object_name);
  END IF;

  IF i_object_type IS NOT NULL THEN
    dbms_sql.bind_variable(c     => v_cursor,
                           NAME  => 'object_type',
                           VALUE => i_object_type);
  END IF;

  v_rows := dbms_sql.execute(c => v_cursor);

  IF v_rows >= 0 THEN
    v_result := dbms_sql.to_refcursor(cursor_number => v_cursor);

    LOOP
      FETCH v_result
        INTO v_owner,
             v_object_name,
             v_object_type;
      EXIT WHEN v_result%NOTFOUND;

      dbms_output.put_line(v_owner || ' ' || v_object_name || ' ' ||
                           v_object_type);
    END LOOP;
  END IF;
END;
/

score 1 · Accepted Answer · answered Sep 20 '17 at 23:00

1

What's wrong with:

select select1, select2, select3 
from tableA 
where (:a is null
  OR upper(select1) like upper(:a))
and (:b is null
  OR upper(select2) like upper(:b))
and (:c is null
  OR upper(select3) like upper(:c));

(Assuming that you have bind peeking disabled)

answered Sep 20 '17 at 23:00

symcbean

47,736
6
59
94

I'll tell you what's wrong. Been using this approach here and there, it messes up the execution plan, having difficulties picking up proper indexes. So your answer is syntactically valid, but I have to go back using DBMS_SQL to improve performance, although that does not look as beautiful as your solution – prof Dec 10 '22 at 20:27

score 0 · Answer 3 · answered Sep 20 '17 at 22:40

http://docs.oracle.com/cloud/latest/db121/ARPLS/d_sql.htm

Native Dynamic SQL is an alternative to DBMS_SQL that lets you place dynamic SQL statements directly into PL/SQL blocks. In most situations, Native Dynamic SQL is easier to use and performs better than DBMS_SQL. However, Native Dynamic SQL itself has certain limitations:

There is no support for so-called Method 4 (for dynamic SQL statements with an unknown number of inputs or outputs)

Dynamic number of bind variables in dynamic sql - Open Cursor

3 Answers3