How to loop a SYS_REFCURSOR with multiple tables?

Question

sorry for the kind of broad title to the question, but I didn't find a way to simplify it further.

I'm creating a package for a client, this package has a procedure which returns a SYS_REFCURSOR. The query that feeds it is a multi-table select with about 20 fields. On a second procedure I need to call this one, and loop through the results to feed other locations.

I'm having problems using the into part of the loop. I can't use table%ROWTYPE. I tried to declare an OBJECT on the procedure but it was not allowed. Will I have to do a FETCH XXX INTO LIST_OF_INDVIDUAL_VARIABLES?

DECLARE
    dt_field TABLE1.dt_field%TYPE;
    p_resultset SYS_REFCURSOR;
    p_individual OBJECT ( 
     FIELD0         DATE,
     FIELD1         TABLE.FIELD1%TYPE,
     FIELD2         TABLE.FIELD2%TYPE,
     FIELD3         TABLE.FIELD3%TYPE,
     FIELD4         TABLE.FIELD4%TYPE
 );

 BEGIN
    PACKAGE.PROCEDURE1(dt_field);
    dbms_output.put_line(dt_field);
    PACKAGE.PROCEDURE2(dt_field, p_resultset);

   LOOP 
     -- this do not work
     FETCH p_resultset INTO p_individual;
     EXIT WHEN p_resultset%NOTFOUND;
     -- DO STUFF ON EACH RETURNED ROW
   END LOOP;
   CLOSE p_resultset;      
 END;

"I tried to declare an OBJECT on the procedure but it was not allowed" As a result of your enterprise policy? By lack of required permissions? Or does it simply produce some kind of error? — Sylvain Leroux
– Sylvain Leroux, Commented Oct 9, 2014 at 20:50
Take a look at this answer by Alex Poole maybe: stackoverflow.com/a/11233929/2363712 — Sylvain Leroux
– Sylvain Leroux, Commented Oct 9, 2014 at 20:53
You'd need to declare a record type in PL/SQL, not an object, and then an instance of that record type. Showing the error you get would be helpful. I'm not sure if you can fetch a ref cursor into a record type, off-hand. — Alex Poole
– Alex Poole, Commented Oct 9, 2014 at 21:31

Alex Poole · Accepted Answer · 2014-10-10 08:51:37Z

3

Following up on my comment, you can't create an object type in PL/SQL, you need to declare a record type, and then an instance of that type:

DECLARE
  type r_individual is record (
    FIELD0         DATE,
    FIELD1         TABLE1.FIELD1%TYPE,
    FIELD2         TABLE1.FIELD2%TYPE,
    FIELD3         TABLE1.FIELD3%TYPE,
    FIELD4         TABLE1.FIELD4%TYPE
  );
  p_individual r_individual;
...

And you can fetch a ref cursor into a record type. Your version errors on the object declaration, not the fetch.

Here's a more complete example based on what's in your question; I don't have your procedures so I'm creating a dummy result set with the same types, and used a dummy table for the %TYPE declarations:

create table table1(dt_field date, field1 number, field2 varchar2(1),
  field3 number, field4 varchar2(1));

set serveroutput on
DECLARE
  type r_individual is record (
    FIELD0         DATE,
    FIELD1         TABLE1.FIELD1%TYPE,
    FIELD2         TABLE1.FIELD2%TYPE,
    FIELD3         TABLE1.FIELD3%TYPE,
    FIELD4         TABLE1.FIELD4%TYPE
  );
  dt_field TABLE1.dt_field%TYPE;
  p_resultset SYS_REFCURSOR;
  p_individual r_individual;

BEGIN
  -- Don't have this package
  -- PACKAGE.PROCEDURE1(dt_field);
  -- dbms_output.put_line(dt_field);
  -- PACKAGE.PROCEDURE2(dt_field, p_resultset);

  -- Dummy result set for demo instead
  OPEN p_resultset FOR q'[select sysdate, 1, 'A', 3, 'C' from dual]'
    || q'[ union all select sysdate, 2, 'B', 4, 'D' from dual]';

  LOOP 
    FETCH p_resultset INTO p_individual;
    EXIT WHEN p_resultset%NOTFOUND;
    -- DO STUFF ON EACH RETURNED ROW
    DBMS_OUTPUT.PUT_LINE(p_individual.field1
      || ':' || p_individual.field2);
  END LOOP;
  CLOSE p_resultset;
END;
/

Which gets:

anonymous block completed
1:A
2:B

answered Oct 10, 2014 at 8:51

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sol Over a year ago

Thanks mate, that was exactly where I failed. Moving from MySQL\MariaDB is a big endeavor. Still trying to find my way.

user3402040 Over a year ago

Appears : expression 'P_INDIVIDUAL' in the INTO list is of wrong type

Alex Poole Over a year ago

@delive - appears where? This code runs without error in SQL*Plus and SQL Developer, again 11g and 12c. What is the full ORA- or PLS- error message? Or is your client/IDE generating that itself? If you have a problem this doesn't fix, you'll need to ask a new question.

user3402040 Over a year ago

@Alex Poole the error appears in FETCH p_resultset INTO p_individual; I have multiples tables and I get this error. For example you have in this code "TABLE1." I have "TABLE1." "TABLE2." "TABLE3." with rowtype

Alex Poole Over a year ago

@delive - then you haven’t adapted this to match your query properly. Like I said, ask a new question, showing your tables, your version of the code and the issues you are having.

Collectives™ on Stack Overflow

How to loop a SYS_REFCURSOR with multiple tables?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related