12 Replies Latest reply: Jul 31, 2012 7:27 PM by rp0428 RSS

    Creating a collection from a list and joining the list to a database table

    ltps
      I would like to have opinions on good ways to process rows, based on a provided list of key values, joining the collected list against a source table to retrieve additional information related to the key. In this simple example, the procedure accepts a list of employee numbers. The goal is to print a list of names associated with those numbers. The method is to materialize the list of employee numbers as rows and join those rows to a source table to get the names. I have used BULK COLLECT. I don't know if this is a good approach and I would value suggestions. I would also like to understand why we cannot cast PLSQL tables using a type defined in the procedure's specification (why the type needs to exist as an object before we can cast it, like this:

      SELECT * FROM TABLE ( CAST ( SOME_FUNCTION(&some parameter) AS SOME_TYPE ) );

      Anyway, here is my demo SQL, which you should be able to execute against the SCOTT schema without any changes. Thanks for your help!

      declare

      type employee_numbers is table of emp.empno%type index by binary_integer;

      type employee_names is table of emp.ename%type index by binary_integer;

      type employees_record is record (empno employee_numbers, person_name employee_names);

      records employees_record;

      employees_cursor sys_refcursor;

      employee_number_list varchar2(30) default '7369,7499,7521';


      begin

      open employees_cursor for

      with t as (
      select regexp_substr(employee_number_list, '[^,]+', 1, level) as employee_number
      from dual
      connect by regexp_substr(employee_number_list, '[^,]+', 1, level) is not null
      ) select emp.empno, emp.ename
      from t join emp on (emp.empno = t.employee_number)
      order by 2
      ;


      fetch employees_cursor bulk collect into records.empno, records.person_name;

      dbms_output.put_line('number of records: '||records.empno.count());

      for i in 1 .. records.empno.count

      loop

      dbms_output.put_line(chr(39)||records.empno(i)||chr(39)||','||chr(39)||records.person_name(i)||chr(39));

      end loop;

      end;
        • 1. Re: Creating a collection from a list and joining the list to a database table
          Venkadesh Raja
          Please Use Pure SQL , BULK COLLECT is slow processing and it consumes huge memory expensive.

          Always SQL is faster than PL SQL
          • 2. Re: Creating a collection from a list and joining the list to a database table
            damorgan
            BULK COLLECT is definitely the way to go provided you need to do conditional processing. In terms of how to use that list of values as a selection criteria look at these demos.

            http://184.72.121.76/reference/conditions.html

            The demos titled
            "Complex IN Demo Using CAST"
            and
            "Complex IN Demo Using MEMBERSHIP OF"
            • 3. Re: Creating a collection from a list and joining the list to a database table
              Solomon Yakobson
              All that can be achieved by simple:
              select  emp.empno,
                      emp.ename
                from  emp
                where empno in (select * from table(sys.OdciNumberList(7369,7499,7521)))
              /
              
                   EMPNO ENAME
              ---------- ----------
                    7369 SMITH
                    7499 ALLEN
                    7521 WARD
              
              SQL> 
              Where sys.OdciNumberList is predefined VARRAY type. Or you can create your own nested table type and use the above method or MEMBER OF:
              SQL> create or replace
                2    type NumberList
                3      as table of number
                4  /
              
              Type created.
              
              SQL> select  emp.empno,
                2          emp.ename
                3    from  emp
                4    where empno in (select * from table(NumberList(7369,7499,7521)))
                5  /
              
                   EMPNO ENAME
              ---------- ----------
                    7369 SMITH
                    7499 ALLEN
                    7521 WARD
              
              SQL> select  emp.empno,
                2          emp.ename
                3    from  emp
                4    where empno member of NumberList(7369,7499,7521)
                5  /
              
                   EMPNO ENAME
              ---------- ----------
                    7369 SMITH
                    7499 ALLEN
                    7521 WARD
              
              SQL> 
              SY.
              • 4. Re: Creating a collection from a list and joining the list to a database table
                ltps
                Thank you, all. I might have made my demo too simple. The goal is not simple selection; the goal is to collect rows for complex processing not shown in the demo.
                • 5. Re: Creating a collection from a list and joining the list to a database table
                  damorgan
                  We can only respond to what you post ... none of us can read your monitor or your mind. ;-)
                  • 6. Re: Creating a collection from a list and joining the list to a database table
                    rp0428
                    >
                    I might have made my demo too simple.
                    The goal is not simple selection; the goal is to collect rows for complex processing not shown in the demo.
                    >
                    I think we assumed that you already know how to join two tables together. If you don't then please say so.

                    The other responders have shown you how to treat the collection as a table. So just join that 'table' to the other table(s) that you need to get data from.
                    >
                    I would also like to understand why we cannot cast PLSQL tables using a type defined in the procedure's specification (why the type needs to exist as an object before we can cast it, like this:

                    SELECT * FROM TABLE ( CAST ( SOME_FUNCTION(&some parameter) AS SOME_TYPE ) );
                    >
                    Well how can you cast something that doesn't exist?

                    The object type has to be accessible to the SQL engine and types defined only in PL/SQL are only accessible to the PL/SQL engine; the SQL engine knows nothing about them.
                    • 7. Re: Creating a collection from a list and joining the list to a database table
                      ltps
                      Thank you. Would the following make sense as a technical explanation? Associative arrays (declared in PLSQL, as opposed to created as objects) do not have constructors, which means that we cannot initialize them with a string of values (1,2,3,4,5), because the PLSQL engine would not know what to do with those values, how to associate them. In other words, associative arrays are about pairs of values, not simple strings of values.
                      • 8. Re: Creating a collection from a list and joining the list to a database table
                        rp0428
                        >
                        Would the following make sense as a technical explanation?
                        >
                        A technical explanation of what? Your statement about associative arrays has NOTHING to do with anything in this thread at all so it doesn't even make sense in the context of this thread.

                        See Chap 5 Using PL/SQL Collections and Records in the PL/SQL Language doc
                        http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/collections.htm

                        See Table 5-1 for a description of the three PL/SQL collection types.

                        You did make a couple of true statements about associative arrays but those still have no bearing on the question you posted.
                        >
                        Associative arrays (declared in PLSQL, as opposed to created as objects) do not have constructors,
                        >
                        True they do not have constructors - as documented in the link above in 'Initializing and Referencing Collections'
                        >
                        You must explicitly call a constructor for each varray and nested table variable. Associative arrays, the third kind of collection, do not use constructors.
                        >
                        And this is also correct
                        >
                        associative arrays are about pairs of values, not simple strings of values.
                        >
                        But this statment about associative arrays is incorrect
                        >
                        we cannot initialize them with a string of values (1,2,3,4,5), because the PLSQL engine would not know what to do with those values, how to associate them.
                        >
                        Sure you can initialize them. But you must provide both the index (key) and the value. So if the string of values is (1,2,3,4,5) you must provide an index or key to associate with each of those values. Then you can use the pairs of values to initialize the array.
                        • 9. Re: Creating a collection from a list and joining the list to a database table
                          ltps
                          This is very helpful. Thank you. I created a little test out of your MEMBER OF example, trying t_deptno as a local PLSQL type rather than a schema-level type. Of course the procedure failed. But the error message really helped me understand what's happening here. (I modified the objects in your example a bit to support my own SCOTT objects.) Thank you for your help.

                          DECLARE
                          type t_deptno is table of number(10);
                          depts t_deptno;
                          BEGIN
                          SELECT deptno BULK COLLECT INTO depts
                          FROM dept
                          WHERE dname IN ('ACCOUNTING','RESEARCH');
                          FOR i in 1 .. 10000 LOOP
                          FOR rec IN (SELECT * FROM emp e WHERE e.deptno MEMBER OF (depts))
                          LOOP
                          NULL;
                          END LOOP;
                          END LOOP;
                          END;

                          PLS-00642: Local collection types not allowed in SQL statements

                          Cause: A locally-defined (i.e. not schema level) collection type was used in a SQL statement. The type must be defined in a schema to be accepted in a SQL statement.

                          Action: Define the collection type in your schema, not inside a PL/SQL subprogram
                          • 10. Re: Creating a collection from a list and joining the list to a database table
                            ltps
                            Sorry for not being more clear. It looks like I have confirmation that BULK COLLECT is a good way to go collect rows for processing. And I have a much better idea now of where to use different types of arrays. Thank you everyone for your help today.
                            • 11. Re: Creating a collection from a list and joining the list to a database table
                              rp0428
                              >
                              The demos titled
                              "Complex IN Demo Using CAST"
                              >
                              The second demo is invalid and requires dynamic sql. It will give ORA-01722: invalid number ORA-06512: at line 5
                              DECLARE
                               i     PLS_INTEGER;
                               InStr VARCHAR2(20) := '10,30';
                              BEGIN
                                SELECT COUNT(*)
                                INTO i
                                FROM emp
                                WHERE deptno IN (InStr);
                              
                                dbms_output.put_line(i);
                              END;
                              /
                              • 12. Re: Creating a collection from a list and joining the list to a database table
                                rp0428
                                >
                                It looks like I have confirmation that BULK COLLECT is a good way to go collect rows for processing
                                >
                                Well maybe and maybe not. Bear in mind that those demos were only basic demos for the purpose of illustrating how functionality CAN be used. They do not tell you WHEN to use them.

                                BULK COLLECT uses expensive PGA memory and unless you know that only a small number of rows will be collected you can have some serious memory issues. Any heavy duty use of BULK COLLECT should generally have a LIMIT clause to limit the number of elements in the collection for each loop iteration.

                                Always use SQL if possible.

                                Also, for your use case you might be bette served using a PIPELINED function. Instead of collecting ALL rows into a nested table as in your example a PIPELINED function returns one row at a time but is still used as if it were a table using the same TABLE operator.

                                Here is simple example code for a PIPELINED function
                                -- type to match emp record
                                create or replace type emp_scalar_type as object
                                  (EMPNO NUMBER(4) ,
                                   ENAME VARCHAR2(10),
                                   JOB VARCHAR2(9),
                                   MGR NUMBER(4),
                                   HIREDATE DATE,
                                   SAL NUMBER(7, 2),
                                   COMM NUMBER(7, 2),
                                   DEPTNO NUMBER(2)
                                  )
                                  /
                                 
                                -- table of emp records
                                create or replace type emp_table_type as table of emp_scalar_type
                                  /
                                
                                -- pipelined function 
                                
                                 
                                create or replace function get_emp( p_deptno in number )
                                  return emp_table_type
                                  PIPELINED
                                  as
                                   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
                                    emp_cv EmpCurTyp;
                                    l_rec  emp%rowtype;
                                  begin
                                    open emp_cv for select * from emp where deptno = p_deptno;
                                    loop
                                      fetch emp_cv into l_rec;
                                      exit when (emp_cv%notfound);
                                      pipe row( emp_scalar_type( l_rec.empno, LOWER(l_rec.ename),
                                          l_rec.job, l_rec.mgr, l_rec.hiredate, l_rec.sal, l_rec.comm, l_rec.deptno ) );
                                    end loop;
                                    return;
                                  end;
                                  /
                                  
                                select * from table(get_emp(20))