2 Replies Latest reply on Mar 21, 2014 7:35 AM by Karthick2003

    Passing array in where statement

    00125

      Hi,

       

               I have a requirement where input is given as array values and fetch records from tables and return it through cursor.

       

      I go through this requirement with global temporary table by pushing values into temp table and returned through ref cursor.After going through some blogs I have read that using dynamic query and Global temporary table gives lack in performance.

       

      Anybody give solution for this reqirement.

       

      My table:-

      create table test(id number,value_1 varchar2(10),value_2 varchar2(10),constraint test_pk primary key(id,value_1));

      create global temporary table test_temp(id number,value_1 varchar2(10),value_2 varchar2(10) on commit preserve rows;

       

      insert into test values(1,'AB','AB');

      insert into test values(1,'AC','AB');

      insert into test values(1,'AD','AB');

      insert into test values(1,'AE','AB');

      insert into test values(2,'AB','AB');

      insert into test values(2,'AC','AB');

      insert into test values(2,'AD','AB');

      insert into test values(2,'AE','AB');

      insert into test values(3,'AB','AB');

      insert into test values(3,'AC','AB');

      insert into test values(3,'AD','AB');

      insert into test values(3,'AE','AB');

      COMMIT;

       

      My procedure:-

      create or replace package test_pkg is

      type t_id is table of test.id%type index by pls_integer;

      procedure test_proc(p_id in t_id,p_cur out ref_cursor);

      end test_pkg;

       

      create or replace package body test_pkg is

      procedure test_proc(p_id in t_id,p_cur out ref_cursor) is

      type t_value is table of test%rowtype index by pls_integer;

      v_value t_value;

      begin

      for i in 1..p_id.count loop

      select id,value_1,value_2 into v_value from test where id=p_id(i);

      forall i in 1..v_value.count

      insert into test_temp values (v_value(i));

      commit;

      end loop;

      open p_cur_out for

      select * from test_temp;

      end test_proc;

      end test_pkg;

       

      Please guide me how to directly use array variable in select statement and retrieve values like this

       

      open p_cur_out for

      select * from test where id=p_id(i);

       

      Please do let me know if u need any further details.

       

      thanks.

        • 1. Re: Passing array in where statement
          Nimish Garg

          can you try like following example

           

          create or replace type my_type is table of number;

          /

           

           

          create or replace function test_proc(x my_type)

          return number

          is

            sal_sum number;

          begin

            select sum(sal) into sal_sum from emp where empno in (select * from table(x));

            return sal_sum;

          end;

          /

           

           

          select test_proc(my_type(7369,7499,7521,7566,7654)) from dual

           

           

          SQL> select test_proc(my_type(7369,7499,7521,7566,7654)) from dual;

          TEST_PROC(MY_TYPE(7369,7499,7521,7566,7654))

          --------------------------------------------

                                                 11225

          1 person found this helpful
          • 2. Re: Passing array in where statement
            Karthick2003

            May be something like this can be done

             

            SQL> create or replace type test_id_tbl as table of number
              2  /

             

            Type created.

             

            SQL> create or replace procedure test_get
              2  (
              3    p_id_list in  test_id_tbl
              4  , p_out     out sys_refcursor
              5  )
              6  as
              7  begin
              8    open p_out for
              9    select id
            10         , value_1
            11         , value_2
            12      from test
            13     where id in (
            14                    select column_value
            15                      from table(p_id_list)
            16                 );
            17  end;
            18  /

             

            Procedure created.

             

            SQL> var rc refcursor
            SQL>
            SQL> exec test_get(test_id_tbl(1,2), :rc)

             

            PL/SQL procedure successfully completed.

             

            SQL> print rc

                    ID VALUE_1    VALUE_2
            ---------- ---------- ----------
                     1 AE         AB
                     1 AD         AB
                     1 AC         AB
                     1 AB         AB
                     2 AE         AB
                     2 AD         AB
                     2 AC         AB
                     2 AB         AB

             

            8 rows selected.

             

            SQL> exec test_get(test_id_tbl(1,3), :rc)

             

            PL/SQL procedure successfully completed.

             

            SQL> print rc

             

                    ID VALUE_1    VALUE_2
            ---------- ---------- ----------
                     1 AE         AB
                     1 AD         AB
                     1 AC         AB
                     1 AB         AB
                     3 AE         AB
                     3 AD         AB
                     3 AC         AB
                     3 AB         AB

             

            8 rows selected.