Re: selecting from functions returning resultsets

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 10 May 2005 14:01:24 -0700
Message-ID: <1115758884.271681.260410@f14g2000cwb.googlegroups.com>

You cannot use the TABLE function on a REF CURSOR. Please see the demonstration below in which I have first reproduced your error, then corrected it, then provided an additional method.

scott_at_ORA92> -- reproduction of error:
scott_at_ORA92> CREATE OR REPLACE package StatView as   2 type cursorType is ref cursor;
  3 function AktLonStatVirk

  4  	 (pAar	   IN  smallint,
  5  	  pHalvaar IN  smallint)
  6  	 return cursortype;
  7    PROCEDURE LonStatAfdBrVirk
  8  	 (pAar	   IN  smallint,
  9  	  pHalvaar IN  smallint,
 10  	  pResult  OUT cursortype);

 11 end;
 12 /

Package created.

scott_at_ORA92> SHOW ERRORS
No errors.
scott_at_ORA92> CREATE OR REPLACE package body StatView as   2 function AktLonStatVirk

  3  	 (pAar	   IN  smallint,
  4  	  pHalvaar IN  smallint)
  5  	 return cursortype
  6    IS
  7  	 pResult Cursortype;
  8    BEGIN
  9  	 open pResult for
 10  	 select * from emp where deptno > pAar and empno > pHalvaar;
 11  	 Return pResult;

 12 END;
 13 PROCEDURE LonStatAfdBrVirk

 14  	 (pAar	   IN  smallint,
 15  	  pHalvaar IN  smallint,
 16  	  pResult  OUT cursortype)

 17 IS
 18 BEGIN

 19  	 open pResult for
 20  	 select dept.dname, stat.ename
 21  	 from	dept,
 22  		table (AktLONSTATVIRK (pAar, pHalvAar)) Stat
 23  	 where	dept.deptno = stat.deptno;

 24 END LonStatAfdBrVirk;
 25 end;
 26 /

Warning: Package Body created with compilation errors.

scott_at_ORA92> SHOW ERRORS
Errors for PACKAGE BODY STATVIEW:

LINE/COL ERROR

---

---

20/5 PL/SQL: SQL Statement ignored
22/12 PL/SQL: ORA-22905: cannot access rows from a non-nested table

         item

scott_at_ORA92> -- correction of error:
scott_at_ORA92> CREATE OR REPLACE TYPE rectype AS OBJECT

  2    (EMPNO	       NUMBER,
  3  	ENAME	       VARCHAR2(10),
  4  	JOB	       VARCHAR2(9),
  5  	MGR	       NUMBER,
  6  	HIREDATE       DATE,
  7  	SAL	       NUMBER,
  8  	COMM	       NUMBER,
  9  	DEPTNO	       NUMBER);

 10 /

Type created.

scott_at_ORA92> SHOW ERRORS
No errors.
scott_at_ORA92> CREATE OR REPLACE TYPE tabletype AS TABLE OF rectype;   2 /

Type created.

scott_at_ORA92> SHOW ERRORS
No errors.
scott_at_ORA92> CREATE OR REPLACE package StatView as   2 type cursorType is ref cursor;
  3 function AktLonStatVirk

  4  	 (pAar	   IN  smallint,
  5  	  pHalvaar IN  smallint)
  6  	 return tabletype;
  7    PROCEDURE LonStatAfdBrVirk
  8  	 (pAar	   IN  smallint,
  9  	  pHalvaar IN  smallint,
 10  	  pResult  OUT cursortype);

 11 end;
 12 /

Package created.

scott_at_ORA92> SHOW ERRORS
No errors.
scott_at_ORA92> CREATE OR REPLACE package body StatView as   2 function AktLonStatVirk

  3  	 (pAar	   IN  smallint,
  4  	  pHalvaar IN  smallint)
  5  	 return tabletype
  6    IS
  7  	 pResult tabletype := tabletype();
  8    BEGIN
  9  	 FOR rec IN
 10  	   (select * from emp where deptno > pAar and empno > pHalvaar)
 11  	 LOOP
 12  	   pResult.EXTEND();
 13  	   pResult(pResult.COUNT) := rectype (rec.empno,
 14  					      rec.ename,
 15  					      rec.job,
 16  					      rec.mgr,
 17  					      rec.hiredate,
 18  					      rec.sal,
 19  					      rec.comm,
 20  					      rec.deptno);
 21  	 END LOOP;
 22  	 Return pResult;

 23 END;
 24 PROCEDURE LonStatAfdBrVirk

 25  	 (pAar	   IN  smallint,
 26  	  pHalvaar IN  smallint,
 27  	  pResult  OUT cursortype)

 28 IS
 29 BEGIN

 30  	 open pResult for
 31  	 select dept.dname, stat.ename
 32  	 from	dept,
 33  		table (AktLONSTATVIRK (pAar, pHalvAar)) Stat
 34  	 where	dept.deptno = stat.deptno;

 35 END LonStatAfdBrVirk;
 36 end;
 37 /

Package body created.

scott_at_ORA92> SHOW ERRORS
No errors.
scott_at_ORA92> SELECT * FROM TABLE (StatView.AktLonStatVirk (1, 1))   2 /

     EMPNO ENAME      JOB              MGR HIREDATE               SAL
    COMM     DEPTNO

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

• ---------- 7369 SMITH CLERK 7902 WED 17-DEC-1980 800 20 7499 ALLEN SALESMAN 7698 FRI 20-FEB-1981 1600 300 30 7521 WARD SALESMAN 7698 SUN 22-FEB-1981 1250 500 30 7566 JONES MANAGER 7839 THU 02-APR-1981 2975 20 7654 MARTIN SALESMAN 7698 MON 28-SEP-1981 1250 1400 30 7698 BLAKE MANAGER 7839 FRI 01-MAY-1981 2850 30 7782 CLARK MANAGER 7839 TUE 09-JUN-1981 2450 10 7788 SCOTT ANALYST 7566 THU 09-DEC-1982 3000 20 7839 KING PRESIDENT TUE 17-NOV-1981 5000 10 7844 TURNER SALESMAN 7698 TUE 08-SEP-1981 1500 0 30 7876 ADAMS CLERK 7788 WED 12-JAN-1983 1100 20 7900 JAMES CLERK 7698 THU 03-DEC-1981 950 30 7902 FORD ANALYST 7566 THU 03-DEC-1981 3000 20 7934 MILLER CLERK 7782 SAT 23-JAN-1982 1300 10

14 rows selected.

scott_at_ORA92> SET AUTOPRINT ON
scott_at_ORA92> VARIABLE g_ref REFCURSOR
scott_at_ORA92> EXECUTE StatView.LonStatAfdBrVirk (1, 1, :g_ref)

PL/SQL procedure successfully completed.

DNAME ENAME
-------------- ----------

ACCOUNTING     CLARK
ACCOUNTING     KING
ACCOUNTING     MILLER
RESEARCH       SMITH
RESEARCH       ADAMS
RESEARCH       FORD
RESEARCH       SCOTT
RESEARCH       JONES
SALES          ALLEN
SALES          BLAKE
SALES          MARTIN
SALES          JAMES
SALES          TURNER
SALES          WARD

14 rows selected.

scott_at_ORA92> -- another way:
scott_at_ORA92> CREATE OR REPLACE package StatView as   2 type cursorType is ref cursor;
  3 PROCEDURE LonStatAfdBrVirk

  4  	 (pAar	   IN  smallint,
  5  	  pHalvaar IN  smallint,
  6  	  pResult  OUT cursortype);

  7 end;
  8 /

Package created.

scott_at_ORA92> SHOW ERRORS
No errors.
scott_at_ORA92> CREATE OR REPLACE package body StatView as   2 PROCEDURE LonStatAfdBrVirk

  3  	 (pAar	   IN  smallint,
  4  	  pHalvaar IN  smallint,
  5  	  pResult  OUT cursortype)

  6 IS
  7 BEGIN

  8  	 open pResult for
  9  	 select dept.dname, stat.ename
 10  	 from	dept,
 11  		(select * from emp where deptno > pAar and empno > pHalvaar)
Stat
 12  	 where	dept.deptno = stat.deptno;

 13 END LonStatAfdBrVirk;
 14 end;
 15 /

Package body created.

scott_at_ORA92> SHOW ERRORS
No errors.

scott_at_ORA92> SET AUTOPRINT ON
scott_at_ORA92> VARIABLE g_ref REFCURSOR
scott_at_ORA92> EXECUTE StatView.LonStatAfdBrVirk (1, 1, :g_ref)

PL/SQL procedure successfully completed.

DNAME ENAME
-------------- ----------

ACCOUNTING     MILLER
ACCOUNTING     KING
ACCOUNTING     CLARK
RESEARCH       FORD
RESEARCH       ADAMS
RESEARCH       SCOTT
RESEARCH       JONES
RESEARCH       SMITH
SALES          JAMES
SALES          TURNER
SALES          BLAKE
SALES          MARTIN
SALES          WARD
SALES          ALLEN

14 rows selected. Received on Tue May 10 2005 - 16:01:24 CDT