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. stackoverflow.com/questions/2514254/… — Jignesh
– Jignesh, Commented Sep 18, 2017 at 19:27
@Jignesh, This is dynamic query and not dynamic bind variable with dynamic query — Srikanth Balaji
– Srikanth Balaji, Commented Sep 18, 2017 at 23:34
@SrikanthA This might be a duplicate of stackoverflow.com/q/7816402/409172 My answer to that question might apply here. — Jon Heller
– Jon Heller, Commented Sep 21, 2017 at 2:48

Kevin Seymour · Accepted Answer · 2017-09-21 20:37:29Z

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;
/

symcbean · Accepted Answer · 2017-09-20 23:00:55Z

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, 2017 at 23:00

symcbean

48.4k6 gold badges63 silver badges99 bronze badges

1 Comment

prof Over a year ago

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

Dr Y Wit · Accepted Answer · 2017-09-20 22:40:46Z

0

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)

answered Sep 20, 2017 at 22:40

Dr Y Wit

2,03811 silver badges16 bronze badges

Collectives™ on Stack Overflow

Dynamic number of bind variables in dynamic sql - Open Cursor

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related