Home » SQL & PL/SQL » SQL & PL/SQL » converting a cursor into a table? (oracle 10g)
converting a cursor into a table? [message #382923] Mon, 26 January 2009 06:54 Go to next message
gamba
Messages: 10
Registered: June 2008
Junior Member
hi, I have a function that returns a sys_refcursor - let's call it GetCursorFunction();
I'd like to do something like:
select * from GetCursorFunction;
is that possible?

thanks.
Re: converting a cursor into a table? [message #382925 is a reply to message #382923] Mon, 26 January 2009 07:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create or replace function get_curs return sys_refcursor
  2  is
  3     rc sys_refcursor;
  4  begin
  5     open rc for select * from emp;
  6     return rc;
  7  end;
  8  /

Function created.

SQL> select get_curs from dual;
GET_CURS
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1
     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-1980        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-1981       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-1981       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-1981       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-1981       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-1981       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-1981       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-1987       3000                    20
      7839 KING       PRESIDENT            17-NOV-1981       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-1981       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-1987       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-1981        950                    30
      7902 FORD       ANALYST         7566 03-DEC-1981       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-1982       1300                    10

14 rows selected.


1 row selected.

Regards
Michel
Re: converting a cursor into a table? [message #382926 is a reply to message #382923] Mon, 26 January 2009 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or you can use a pipelined function:
SQL> create or replace type mytype as object (
  2    empno number,
  3    ename varchar2(30)
  4  )
  5  /

Type created.

SQL> create or replace type myarray as table of mytype
  2  /

Type created.

SQL> create or replace function get_emp
  2  return myarray
  3  pipelined
  4  is
  5  begin
  6    for rec in (select empno, ename from emp) loop
  7      pipe row (mytype(rec.empno, rec.ename));
  8    end loop;
  9  end;
 10  /

Function created.

SQL> select * from table(get_emp);
     EMPNO ENAME
---------- ------------------------------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

Regards
Michel
Re: converting a cursor into a table? [message #382932 is a reply to message #382926] Mon, 26 January 2009 08:05 Go to previous messageGo to next message
gamba
Messages: 10
Registered: June 2008
Junior Member
thanks for the reply.

the problem is, it isn't really like a table. sqlplus does show (while others like TOAD do not) but I can't do table operations with it.

for example:
create table MyTable
(
    id number,
    value varchar2(100)
);

create function MyFunction
return sys_refcursor
is
   cur sys_refcursor;
begin
   open cur for
       select 1, 'one' from dual;
   return cur;
end;

insert into MyTable
   select MyFunction from dual;


that does not work. it treats the cursor returned as a single value...
Re: converting a cursor into a table? [message #382935 is a reply to message #382932] Mon, 26 January 2009 08:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not possible to see a (ref) cursor as a SQL object, you have to handle it with a procedural language (PL/SQL or so).
If you need this, you have to use pipelined function.

Regards
Michel
Re: converting a cursor into a table? [message #382941 is a reply to message #382935] Mon, 26 January 2009 08:44 Go to previous messageGo to next message
gamba
Messages: 10
Registered: June 2008
Junior Member
I see.
the problem is, a pipelined function requires I define a type for each different cursor. is that right?

for example:
for a table (id number, value varchar2) I'd need a different function and type than table(name varchar2, birthdate date)
yes?
Re: converting a cursor into a table? [message #382942 is a reply to message #382941] Mon, 26 January 2009 08:59 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes. A pipelined function is strongly typed.

Regards
Michel
Previous Topic: Stored Queries
Next Topic: Help with query
Goto Forum:
  


Current Time: Sat Dec 03 01:07:40 CST 2016

Total time taken to generate the page: 0.14222 seconds