Using a Ref Cursor returned from a function 1

[BJCooperIT]

We have a function that is part of a database package. This function returns a Ref Cursor. I am coding a different package and need to use this function. I have searched the existing threads and not found clear-cut answers.

This, although riddled with errors, is what I would like to logically accomplish:

declare
type    V_CURSOR    is ref cursor;
        V_REC       UTIL_PKG.F_GET_LODGING%rowtype;
begin
    V_CURSOR := UTIL_PKG.F_GET_LODGING(4568,2006);
    loop
        fetch V_CURSOR
        into  V_REC;
        exit when V_CURSOR%notfound;
        DBMS_OUTPUT.PUT_LINE(V_REC.CHECK_OUT_TIME);
    end loop;
    close V_CURSOR;
end;

1. What is the syntax of the PL/SQL to use this function and process the rows returned from the Ref Cursor?

2. Can I declare a %rowtype based on the function's Ref Cursor data?

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[Thargy]

bj,

I know you're an experienced DBA, so if I'm teaching you to suck eggs, please excuse me.

You can't declare a strongly typed ref cursor against a function, but you can against a rowtype specified in a package.

I recommend strongly typed, as it lets the compiler do some of the donkey work for you.

First off, I create a package to contain the type definition and a function to populate the ref cursor. assuming a scott/tiger login etc.

CREATE OR REPLACE PACKAGE Bj_Demo
AS

TYPE bj_cursor_type IS REF CURSOR RETURN EMP%ROWTYPE;

FUNCTION test_ref_cur RETURN bj_cursor_type;

END Bj_Demo;

the body is equally advanced stuff, to wit:-

CREATE OR REPLACE PACKAGE BODY Bj_Demo
AS

/************************************************************************
************************************************************************/
FUNCTION test_ref_cur
  RETURN bj_cursor_type
IS

RETVAL bj_cursor_type;

BEGIN

  OPEN retval
   FOR SELECT * FROM EMP;
  RETURN retval;
  
END test_ref_cur;
/************************************************************************
************************************************************************/
END Bj_Demo;
/

This just creates a function that returns a ref cursor on the emp table (purely for demo purposes).
Now create a separate package to call the function, and read the ref cursor results into something useful

CREATE OR REPLACE PROCEDURE Bj_Proc (p_NumRecs PLS_INTEGER)
  IS
 p_retcur Bj_Demo.bj_cursor_type;
 at_rec   EMP%ROWTYPE;
BEGIN
  p_retcur := Bj_Demo.test_ref_cur;

  FOR i IN 1 .. p_NumRecs
  LOOP
    FETCH p_retcur
    INTO at_rec;

    dbms_output.put_line(at_rec.ename ||' - ' || at_rec.job);
  END LOOP;
END Bj_Proc;
/

Plug in a value of 17 and exec the procedure from SQL plus. It will work.

caveats:- I'm at home using 10g, can't guarantee anything on 9i.

I refer to the emp table in the procedure - you may not have that privilege. I've got to go now, so I'll polish this a bit later on. Is this what you want bj, or do you intend to go the weakly typed route?

Regards

Tharg

Grinding away at things Oracular

 

[BJCooperIT]

Tharg,
Thanks so much for the help... and the compliment. I am a developer with some DBA skills and I get my experience the hard way: trial & error. Until a few months ago, Ref Cursors were just something I played with once to figure out how I could use them. Since then I have written several for Oracle Reports, but this is the first time I have tried to use one coming back from a function.

I got your sample working when using a ROWTYPE%. Unfortunately, the real function returns columns from ACCOM and another table. Unless I create a view, it does not appear I can use a ROWTYPE% so that I do not have to be concerned when the contents of the Ref Cursor change, which they do - constantly. If I have to know all the columns in the cursor and their source tables, the advantage of using this function is lost in my package. This was not my design, I am just on the receiving end as these functions were created for the JAVA developers.

This is what I arrived at:

CREATE OR REPLACE PACKAGE TEST_REF_CURSOR_PKG
AS
    type        ACCOM_Cursor_Type is ref cursor return ACCOM%rowtype;
    function    FRC_ACCOM return ACCOM_Cursor_Type;
    
END TEST_REF_CURSOR_PKG;
/
CREATE OR REPLACE PACKAGE BODY TEST_REF_CURSOR_PKG 
AS
    FUNCTION FRC_ACCOM
        RETURN ACCOM_Cursor_Type
    IS  RETVAL ACCOM_Cursor_Type;
    BEGIN    
      OPEN retval
       for  select * from ACCOM
            where  2006 between FROM_YEAR and TO_YEAR
            order by ti_id;
      RETURN retval;   
    END FRC_ACCOM;

END TEST_REF_CURSOR_PKG;
/

CREATE OR REPLACE PROCEDURE P_TEST_REF_CURSOR_ACCOM (p_NumRecs PLS_INTEGER)
  IS
 P_RETCUR       TEST_REF_CURSOR_PKG.ACCOM_Cursor_Type;
 ACCOM_rec      ACCOM%ROWTYPE;
 V_NAME         varchar2(100);
 V_TYPE         varchar2(5);
 V_FROM_DATE    date;
 V_TO_DATE      date;
BEGIN
  p_retcur := TEST_REF_CURSOR_PKG.FRC_ACCOM;
    
  FOR i IN 1 .. p_NumRecs
  LOOP
    FETCH p_retcur
    into  ACCOM_rec;
    dbms_output.put_line(ACCOM_rec.ti_id);
  END LOOP;

END P_TEST_REF_CURSOR_ACCOM;/

Thanks for helping take some of the mystery out of it.

 

[Thargy]

bj,

thanks for the clarification. I posted last night in haste. I never for one moment thought that I had finished the job.

Today I will look at two possible solutions for a generic function, i.e. declaring a RECORD and strongly typing against it, and declaring a subtype, and then strongly typing against it. Also, my procedure was poor, in as much as you have to declare an integer to 'guess' the number of rows in advance.

I want to get this loading automatically into a collection, regardless of the number of rows in the cursor, i.e. make it a soft-coded full load.

Thanks for tidying up after me, I'll have another bash when work permits.

Regards

Tharg.

Grinding away at things Oracular

 

[Thargy]

BJ,

I've done some digging and found the following article

http://www.oracle.com/technology/oramag/oracle/06-mar/o26plsql.html

which is written by one S. Feuerstein esquire, who is rumoured to be knowledgable is these things.

He does a weakly typed SYS_REFCURSOR solution, and ends up with all the data in a collection to boot!

I've yet to work through this in detail, but it appears to do what you need. Please let me know whether or not this helps, and if it does, an example?

Regards

Tharg

Grinding away at things Oracular