Home » SQL & PL/SQL » SQL & PL/SQL » error while executing function (Oracle 10g)
error while executing function [message #359052] Thu, 13 November 2008 08:55 Go to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Hi,



create or replace package pkg_refcursor
as
type rc is ref cursor;
end;


SQL> 
  1  create or replace function rcreturn return pkg_refcursor.rc
  2  as
  3  rf pkg_refcursor.rc;
  4  begin
  5  open rf for select ad from test_mine;
  6  return rf;
  7* end rcreturn;
SQL> /

Function created.

SQL> set  serveroutput on;
SQL> select rcreturn from dual;
select rcreturn from dual
       *
ERROR at line 1:
ORA-00902: invalid datatype


I am getting the above error. Please suggest where I am doing wrong.
The table description is as follows..
SQL> desc mine_test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 A                                                  NUMBER
 B                                                  VARCHAR2(10)

SQL> desc test_mine;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 AD                                                 NUMBER

SQL> select * from test_mine;

        AD
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
9 rows selected.



Regards,
Pointers.
Re: error while executing function [message #359055 is a reply to message #359052] Thu, 13 November 2008 09:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t as select level id from dual connect by level < 10;

Table created.

SQL> create or replace package pkg_refcursor
  2  as
  3  type rc is ref cursor;
  4  end;
  5  /

Package created.

SQL> create or replace function rcreturn return pkg_refcursor.rc
  2  as
  3   rf pkg_refcursor.rc;
  4  begin
  5   open rf for select id from t;
  6   return rf;
  7  end rcreturn;
  8  /

Function created.

SQL> select rcreturn from dual;
RCRETURN
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1
        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.


1 row selected.

Regards
Michel
Re: error while executing function [message #359056 is a reply to message #359052] Thu, 13 November 2008 09:10 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
But i am getting error.....
Re: error while executing function [message #359057 is a reply to message #359056] Thu, 13 November 2008 09:26 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Do you get the error when you run the same code as Michel?
Previous Topic: Bitmap Index
Next Topic: Simple Select Statement
Goto Forum:
  


Current Time: Sat Dec 10 18:23:45 CST 2016

Total time taken to generate the page: 0.08821 seconds