Home » SQL & PL/SQL » SQL & PL/SQL » Create view from the result of a function which returns ref cursor
Create view from the result of a function which returns ref cursor [message #245426] Sun, 17 June 2007 00:56 Go to next message
newtooracle123
Messages: 24
Registered: May 2007
Junior Member
Hi,

I have a function which returns a ref cursor. I want to create a view on the result set of this function. How can I achieve this?
Re: Create view from the result of a function which returns ref cursor [message #245430 is a reply to message #245426] Sun, 17 June 2007 01:58 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Looks like it cannot be done:

SQL> CREATE OR REPLACE FUNCTION myrefcursor RETURN sys_refcursor IS
  2    c1 sys_refcursor;
  3  BEGIN
  4    OPEN c1 FOR SELECT * FROM tab;
  5    RETURN c1;
  6  END;
  7  /

Function created.


SQL> SELECT myrefcursor FROM dual WHERE rownum < 5;

MYREFCURSOR
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
...


SQL> CREATE VIEW myrefcursorview AS SELECT myrefcursor from dual;
CREATE VIEW myrefcursorview AS SELECT myrefcursor from dual
                                      *
ERROR at line 1:
ORA-02345: cannot create a view with column based on CURSOR operator


Re: Create view from the result of a function which returns ref cursor [message #245431 is a reply to message #245426] Sun, 17 June 2007 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Afaik, you can't use a ref cursor in a view.
You can use pipelined function instead.

Regards
Michel
Re: Create view from the result of a function which returns ref cursor [message #245432 is a reply to message #245430] Sun, 17 June 2007 02:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ooops, sorry Frank I was writing my message and made another thing during 1/2 hour before clicking on submit and so I didn't see yours before.

Regards
Michel

[Updated on: Sun, 17 June 2007 02:17]

Report message to a moderator

Re: Create view from the result of a function which returns ref cursor [message #245433 is a reply to message #245426] Sun, 17 June 2007 03:19 Go to previous messageGo to next message
newtooracle123
Messages: 24
Registered: May 2007
Junior Member
Pipelines worked for me.

Thank u
Re: Create view from the result of a function which returns ref cursor [message #245434 is a reply to message #245433] Sun, 17 June 2007 03:26 Go to previous message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Newtooracle123, can you please post your solution?
Previous Topic: procedure
Next Topic: index in oracle
Goto Forum:
  


Current Time: Sat Dec 10 08:58:28 CST 2016

Total time taken to generate the page: 0.09257 seconds