Solved

ref cursor and plsql tables  ?

Posted on 2004-04-23
4
5,498 Views
Last Modified: 2007-12-19
I have doubt regarding  ref cursor and plsql tables ?
these are the two topics which i could not get into my mind ? i have read and
searched for many materials and books ? non of them could easily put it into my brain ? can someone help me with  simple explanation with simple example ? how and where these topics could be used ?

actual to begin with, i have no idea of object types  in oracle ?
how could i imagine

for example :-

type xyz as record of tablename%rowtype
type xxx as table  of  tablename indexed by binary_integer


these things make me confuse. please help
0
Comment
Question by:kandati
4 Comments
 

Accepted Solution

by:
uTab earned 25 total points
ID: 10901914


This can be used when you need to return information for a block that requires initial filtering of the information of manipulation of the data in some way that could cause a reduction in system performance if done in a sql statement. For example: you have a block in your form that is based on a table. The user needs to view only a specific rows based upon a dynamic setting.  In other words the user has the option of deciding what they want to filter. These values can be stored in the system and passed to the pkg that sets up the ref_cursor or plsql tables in a when-new-form-instance. If this were all done in the when new form instance it could be quite a load on the system as users increase.  
0
 
LVL 2

Assisted Solution

by:n4nazim
n4nazim earned 25 total points
ID: 10906321

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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now