3 Replies Latest reply: Feb 20, 2011 11:37 AM by Barbara Boehmer RSS

    how to use a collection in procedure

    769348
      I am having code as:

      type cur_list is ref cursor;
      type TypeList IS TABLE OF tab1.col1%TYPE;

      proc (list in TypeList , p_cur out cur_list ) is
      begin
      open cur_list for select * from tab1 where col1 in (select col1 from list);
      end;


      but.... while opening cursor it is giving error as : PL/SQL types cannot be used in sql statement.

      Please help me.
        • 1. Re: how to use a collection in procedure
          Barbara Boehmer
          In order to use the list in a sql select statement, the typelist that the list is based on must be a sql type created in sql outside of the procedure, not pl/sql within the procedure, as demonstrated below. There are also other corrections, such as opening p_cur and using table on the list.
          SCOTT@orcl_11gR2> create table tab1
            2    (col1  number,
            3       col2  number)
            4  /
          
          Table created.
          
          SCOTT@orcl_11gR2> insert all
            2  into tab1 values (1, 4)
            3  into tab1 values (1, 5)
            4  into tab1 values (2, 6)
            5  into tab1 values (2, 7)
            6  into tab1 values (3, 8)
            7  select * from dual
            8  /
          
          5 rows created.
          
          SCOTT@orcl_11gR2> create or replace type list_typ as object
            2    (col1   number);
            3  /
          
          Type created.
          
          SCOTT@orcl_11gR2> create or replace type typelist as table of list_typ;
            2  /
          
          Type created.
          
          SCOTT@orcl_11gR2> create or replace procedure test_proc
            2    (list  in  typelist,
            3       p_cur out sys_refcursor)
            4  is
            5  begin
            6    open   p_cur for
            7    select *
            8    from   tab1
            9    where  col1 in
           10             (select col1
           11              from   table (list));
           12  end test_proc;
           13  /
          
          Procedure created.
          
          SCOTT@orcl_11gR2> show errors
          No errors.
          SCOTT@orcl_11gR2> variable results refcursor
          SCOTT@orcl_11gR2> set autoprint on
          SCOTT@orcl_11gR2> declare
            2    list typelist := typelist();
            3  begin
            4    list.extend;
            5    list(list.last) := list_typ (1);
            6    list.extend;
            7    list(list.last) := list_typ (2);
            8    test_proc (list, :results);
            9  end;
           10  /
          
          PL/SQL procedure successfully completed.
          
          
                COL1       COL2
          ---------- ----------
                   1          5
                   1          4
                   2          7
                   2          6
          
          4 rows selected.
          
          SCOTT@orcl_11gR2>
          • 2. Re: how to use a collection in procedure
            Solomon Yakobson
            Barbara,
            type TypeList IS TABLE OF tab1.col1%TYPE;
            SQL equivalent would be (assuming tab1.col1%TYPE is NUMBER)
            TABLE OF NUMBER
            not table of objects. Also, you could use MEMBER OF instead of IN subquery.

            SY.
            • 3. Re: how to use a collection in procedure
              Barbara Boehmer
              Solomon Yakobson wrote:
              Barbara,
              type TypeList IS TABLE OF tab1.col1%TYPE;
              SQL equivalent would be (assuming tab1.col1%TYPE is NUMBER)
              TABLE OF NUMBER
              not table of objects. Also, you could use MEMBER OF instead of IN subquery.

              SY.
              Good points. The following code is updated to include those corrections.
              SCOTT@orcl_11gR2> create table tab1
                2    (col1  number,
                3       col2  number)
                4  /
              
              Table created.
              
              SCOTT@orcl_11gR2> insert all
                2  into tab1 values (1, 4)
                3  into tab1 values (1, 5)
                4  into tab1 values (2, 6)
                5  into tab1 values (2, 7)
                6  into tab1 values (3, 8)
                7  select * from dual
                8  /
              
              5 rows created.
              
              SCOTT@orcl_11gR2> create or replace type typelist as table of number;
                2  /
              
              Type created.
              
              SCOTT@orcl_11gR2> create or replace procedure test_proc
                2    (list  in  typelist,
                3       p_cur out sys_refcursor)
                4  is
                5  begin
                6    open   p_cur for
                7    select *
                8    from   tab1
                9    where  col1 member of list;
               10  end test_proc;
               11  /
              
              Procedure created.
              
              SCOTT@orcl_11gR2> show errors
              No errors.
              SCOTT@orcl_11gR2> variable results refcursor
              SCOTT@orcl_11gR2> set autoprint on
              SCOTT@orcl_11gR2> exec test_proc (typelist (1, 2), :results)
              
              PL/SQL procedure successfully completed.
              
              
                    COL1       COL2
              ---------- ----------
                       1          4
                       1          5
                       2          6
                       2          7
              
              4 rows selected.
              
              SCOTT@orcl_11gR2>