ref cursor and plsql tables ?

Go through this... it is lengthy but worth ...


Using a Collection Instead of a Temporary Table in Complex Reports
Compliments of Zlatko Sirotic, (zlatko.sirotic@iii.hr.
Sometimes there is a query in our reports that is so complex that we have to use a temporary table (in an 8i database the most effective type would be a GLOBAL TEMPORARY TABLE). Without such a table, we couldn't do what we intended to, or we could do it but the solution would be overly complicated to design and difficult to maintain. Unfortunately, loading the temporary table (and processing it) may be more time consuming.
Sometimes we can avoid the use of a temporary table by using a REF CURSOR, especially if we use a dynamic REF CURSOR. However, REF CURSORs can't always replace a temporary table. Wouldn't it be nice if we could use a collection instead of temporary tables in our reports?
The reports are not set up to work directly with collections, but we can use collections indirectly in two ways:
·      Our report can be based on a VIEW that uses the collection.
·      Our report can be based on a REF CURSOR that uses the collection.
Here we will discuss the second method, (how to base a report on a REF CURSOR that uses collections), using a TABLE collection. The example is actually so simple that we really don't need a temporary table at all. The goal of this example is to show the method as simply as possible.
The method consists of three steps:
1.      Load a collection with data from one or more tables
 
2.      Execute complex processing on the collection
 
3.      Open a REF CURSOR variable which uses a collection as follows:
4.      OPEN ref_cursor_variable FOR
   SELECT ... FROM TABLE (CAST (collection_variable AS collection_type)) ...
or even simpler (this works only with 9i, and not with 8i):
OPEN ref_cursor_variable FOR
   SELECT ... FROM TABLE (collection_variable) ...
and send the REF CURSOR to the Reports
Even if DEPT is a common relational table (neither an object or object-relational table), we must first create the following OBJECT TYPE:
CREATE OR REPLACE TYPE dept_obj_t AS OBJECT (
   deptno NUMBER (2),
   dname  VARCHAR2 (14)
)
/
Then we can create the following TABLE TYPE:
CREATE OR REPLACE TYPE dept_tab_t AS TABLE OF dept_obj_t
/
Now we can create a package for the report:
CREATE OR REPLACE PACKAGE collection_reports_rc IS
   TYPE dept_rec_t IS RECORD (
      deptno dept.deptno%TYPE,
      dname  dept.dname%TYPE);
   TYPE dept_rc_t IS REF CURSOR RETURN dept_rec_t;
   FUNCTION dept_query RETURN dept_rc_t;
END;
/

CREATE OR REPLACE PACKAGE BODY collection_reports_rc IS
   FUNCTION dept_query RETURN dept_rc_t IS
      l_dept_tab dept_tab_t;
      l_dept_rc  dept_rc_t;
      l_counter  NUMBER := 1;
   BEGIN
      -- ***** step A *****

      -- 1.version: CAST (MULTISET ...
      SELECT CAST (MULTISET (SELECT deptno, dname
                               FROM dept)
                   AS dept_tab_t)
        INTO l_dept_tab
        FROM DUAL;

      -- 2.version: BULK COLLECT
      /*
      SELECT dept_obj_t (deptno, dname)
        BULK COLLECT INTO l_dept_tab
        FROM dept;
      */

      -- 3.version: FOR LOOP
      /*
      l_dept_tab := dept_tab_t ();
      FOR dept IN (
         SELECT deptno, dname
           FROM dept)
      LOOP
         l_dept_tab.EXTEND;
         l_dept_tab (l_counter) := dept_obj_t (dept.deptno, dept.dname);
         l_counter := l_counter + 1;
      END LOOP;
      */

      -- ***** step B (SOME PROCESSING) *****

      -- ***** step C *****

      OPEN l_dept_rc FOR
         SELECT deptno, dname
           FROM TABLE (CAST (l_dept_tab AS dept_tab_t)) -- in 9i works without CAST
          ORDER BY deptno;

      RETURN l_dept_rc;
   END;
END;
/
The previous package used a static REF CURSOR, therefore we couldn't send a dynamic WHERE condition to it. The next package version also uses a static REF CURSOR, but a WHERE condition is being sent to it. The condition is not used in the REF CURSOR, but in the SELECT command which loads the collection with data from the database.
CREATE OR REPLACE PACKAGE collection_reports2_rc IS
   TYPE dept_rec_t IS RECORD (
      deptno dept.deptno%TYPE,
      dname  dept.dname%TYPE);
   TYPE dept_rc_t IS REF CURSOR RETURN dept_rec_t;
   FUNCTION dept_query (p_where VARCHAR2) RETURN dept_rc_t;
END;
/

CREATE OR REPLACE PACKAGE BODY collection_reports2_rc IS
   FUNCTION dept_query (p_where VARCHAR2) RETURN dept_rc_t IS
      l_dept_tab  dept_tab_t;
      l_dept_rc   dept_rc_t;
      l_statement VARCHAR2 (32000);
   BEGIN
      -- ***** step A *****

      -- 1.version: CAST (MULTISET ...
      l_statement :=
      ' SELECT CAST (MULTISET (SELECT deptno, dname
                                 FROM dept
                                WHERE ' || NVL (p_where, '1 = 1') || ')
                     AS dept_tab_t)
          FROM DUAL';

      EXECUTE IMMEDIATE l_statement INTO l_dept_tab;

      -- 2.version: BULK COLLECT (but this don't compile in Oracle 8i)
      /*
      l_statement :=
      ' SELECT dept_obj_t (deptno, dname)
          FROM dept
         WHERE ' || NVL (p_where, '1 = 1');

      EXECUTE IMMEDIATE l_statement BULK COLLECT INTO l_dept_tab;
      */

      -- ***** step B (SOME PROCESSING) *****

      -- ***** step C *****

      OPEN l_dept_rc FOR
         SELECT deptno, dname
           FROM TABLE (CAST (l_dept_tab AS dept_tab_t)) -- in 9i works without CAST
          ORDER BY deptno;

      RETURN l_dept_rc;
   END;
END;
/
If for any reason we'd need a dynamic REF CURSOR, here is a package showing this. In this case, the package must have both static and dynamic REF CURSOR types - static for the Reports and dynamic for the OPEN ref_cursor FOR '...' statement. (See July's Tip of the Month: Dynamic Table in the Second Query with Oracle Reports).
CREATE OR REPLACE PACKAGE collection_reports_dyn_rc IS
   TYPE dept_rec_t IS RECORD (
      deptno dept.deptno%TYPE,
      dname  dept.dname%TYPE);
   TYPE dept_rc_t IS REF CURSOR RETURN dept_rec_t;
   TYPE dept_dyn_rc_t IS REF CURSOR;
   FUNCTION dept_query (p_where VARCHAR2) RETURN dept_dyn_rc_t;
END;
/

CREATE OR REPLACE PACKAGE BODY collection_reports_dyn_rc IS
   FUNCTION dept_query (p_where VARCHAR2) RETURN dept_dyn_rc_t IS
      l_dept_tab dept_tab_t;
      l_dept_rc  dept_dyn_rc_t;
   BEGIN
      -- ***** step A *****

      SELECT CAST (MULTISET (SELECT deptno, dname
                               FROM dept)
                   AS dept_tab_t)
        INTO l_dept_tab
        FROM DUAL;

      -- ***** step B (SOME PROCESSING) *****

      -- ***** step C *****

      OPEN l_dept_rc FOR
         'SELECT deptno, dname
            FROM TABLE (CAST (:l_dept_tab AS dept_tab_t)) -- in 9i works without CAST
           WHERE ' || NVL (p_where, '1 = 1') ||
         ' ORDER BY deptno'
         USING l_dept_tab;

      RETURN l_dept_rc;
   END;
END;
/
We must emphasize that the collection is not always a better solution than the temporary table. Specifically, when a collection must be loaded with a very large amount of data, the collection might be a worse solution as it requires a lot of RAM memory.


Rgds,
Nazim M