CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
JSantos
JSantos

Reputation: 1708

Declaring SYS_REFCURSOR and ROWTYPE in stored procedure

So I have a stored procedure ONE

create or replace PROCEDURE ONE
(
   A in number
   B in number
   ...
   ZZ out SYS_REFCURSOR
) IS
  SOME_OTHER_STUFF
BEGIN
   ...
END

This returns a sys_refcursor with some data I need. I then have a stored procedure TWO

create or replace PROCEDURE TWO
(
   A in number
   B in number
   ...
   ZZ out SYS_REFCURSOR
) IS
     Count_Records Sys_Refcursor;
     l_rec Count_Records%rowtype;
BEGIN
    /* get some data from the ONE stored procedure to use on this procedure*/
    ONE(A,B,...Count_Records)

    Loop
       fetch count_records into l_rec;
       Exit When count_records%Notfound;
       If  l_rec.TT_RAW > MAX_RECORDS  Then
         Raise To_Many_Results;
       End If;
    End Loop;

END

I'm getting an error when I try to declare the rowtype l_rec Count_Records%rowtype;

How can I declare this variable? The problem is that TT_RAW which is the column I'm trying to get is a SUM and the cursor is not mapped to any table it includes a lot of calculation.

Thanks

Upvotes: 2

Views: 9556

Answers (2)

woot
woot

Reputation: 7606

You can fetch into a list of variables instead, or create your own record type: 

...
fetch count_records into v_field1, v_field2, v_field3
...

Upvotes: 1

Allan
Allan

Reputation: 17429

The whole point of sys_refcursor is that it's dynamically defined. The rowtype declaration, on the other hand has to be static (if it's not, the compiler can't tell if the fields referenced from it are valid). This is the definition of a use case where you should be defining your own strongly typed ref cursor, instead of using sys_refcursor.

Below is a very simple example:

CREATE OR REPLACE PACKAGE BODY rc_example IS
   PROCEDURE two (a IN NUMBER, b IN NUMBER);
END rc_example;
/

CREATE OR REPLACE PACKAGE BODY rc_example IS
   TYPE one_record IS RECORD (FIRST_VALUE VARCHAR2 (10));

   TYPE one_cursor IS REF CURSOR
      RETURN one_record;

   --Could alternately be declared using a table's rowtype:
   --TYPE one_cursor is ref cursor return some_table%rowtype;

   PROCEDURE one (a IN NUMBER, b IN NUMBER, zz OUT one_cursor) IS
   BEGIN
      OPEN zz FOR SELECT 'test' FROM DUAL;
   END one;

   PROCEDURE two (a IN NUMBER, b IN NUMBER) IS
      count_records SYS_REFCURSOR;
      l_rec count_records%ROWTYPE;
   BEGIN
      one (a, b, count_records);

      LOOP
         FETCH count_records INTO l_rec;

         DBMS_OUTPUT.put_line (l_rec.FIRST_VALUE);
         EXIT WHEN count_records%NOTFOUND;
      END LOOP;
   END two;
END rc_example;
/

Upvotes: 8

Related Questions