Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    184

    Unanswered: REF_CURSOR return dynamic sql results

    I need to create a function that can return/display the results of a dyanamic SQL statement and haven't had much luck.

    To start with, I have a really simple function:
    Code:
    CREATE OR REPLACE FUNCTION myproc
    RETURN SYS_REFCURSOR IS sqlOuter SYS_REFCURSOR;
    BEGIN
    declare
         sqlOuter      varchar(12000);
         rs sys_refcursor;
    
         begin
              sqlOuter := 'SELECT * FROM mytable';
              OPEN rs FOR sqlOuter;
         end;
    END;
    When I execute "select myproc from dual;" I receive an error that
    ORA-06503: PL/SQL: Function returned without value
    for the
    OPEN rs FOR sqlOuter;
    line.

    What am I missing here? Any help would be appreciated.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You don't have a RETURN statement in your function, among other errors. Try this version:
    Code:
    SQL> select * from mytable;
    
    D
    -----------
    23-OCT-2008
    
    SQL> create or replace function myproc
      2  return sys_refcursor is
      3      sqlOuter varchar(12000);
      4      rs sys_refcursor;
      5  begin
      6      sqlOuter := 'select * from mytable';
      7      open rs for sqlouter;
      8      return rs;
      9  end;
     10  /
    
    Function created.
    
    SQL> select myproc from dual;
    
    MYPROC
    --------------------
    CURSOR STATEMENT : 1
    
    CURSOR STATEMENT : 1
    
    D
    -----------
    23-OCT-2008

  3. #3
    Join Date
    May 2004
    Posts
    184
    Tony,

    Thank you. That definitely removed the error and a cursor object is returned.

    What should I look at if I want to have the results of the cursor records returned and not just the cursor object itself? I.e., I want to see the rows and columns displayed?

    The data ultimately is just going to form a 'view' for a report. The only reason I'm using pl/sql is because there will be various WHERE conditions that can be passed in to the function/procedure.

    Thank you again. I appreciate the help.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can use a PIPIELINED function that returns a collection:
    Code:
    SQL> create type mytable_rec is object(d date);
      2  /
    
    Type created.
    
    SQL> create type mytable_tab is table of mytable_rec;
      2  /
    
    Type created.
    
    SQL> create or replace function myproc
      2  return mytable_tab pipelined is
      3      rs sys_refcursor;
      4      r mytable%rowtype;
      5  begin
      6      open rs for 'select * from mytable';
      7      loop
      8         fetch rs into r;
      9         exit when rs%notfound;
     10         pipe row(mytable_rec(r.d));
     11      end loop;
     12      return;
     13  end;
     14  /
    
    Function created.
    
    SQL> select * from table(myproc);
    
    D
    -----------
    23-OCT-2008

  5. #5
    Join Date
    May 2004
    Posts
    184
    Tony,

    Thank you again. I had started with the pipelined functions documentation, but that seemed inefficient to have to loop through the cursor, and I assumed I was missing some other 'simpler' process.

    Thank you. I'll go back to the pipelined methodology and work through that.

    Regards,

    Robert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •