Dynamic SQL - for newbies

articles: 

Comments

I created the following function returning REF CURSOR. If I call this function from a SQL statement, it is showing an error : inconsistent datatype. expected number got cursor.

Please tell me the solution.

create or replace
 function jarek_refcursor return sys_refcursor
  is
     c1 sys_refcursor;
   begin
      open c1 for select * from tab;
      return c1;
 end;
 /
 
 select jarek_refcursor from dual;

Hi,
I think that this is wrong place to ask such questions. Please use your-favorite-web-search-engine and/or forum in the future.
But this time I will give you this answer: What are going to return? A table? A row? Or a-java-class-that-has-opened-cursor-and-you-can-iterate-over-the-elements? Anyway - I feel that you have wrong approach here. If you really need to work on this - use DBMS_SQL or similar things. Create a cursor handle and pass it. And note that it seems strange for me... I would suggest that you re-check your problem and solve this in a different way.