CODEWITHSUNDEEP
sqloracle-databasestored-procedures
trpt4him
trpt4him

Reputation: 1906

Looping over cursor returned from procedure with many tables

I'm working with a stored procedure that I didn't write, which is long and contains numerous columns and joins. The procedure returns a cursor, which the application server (.NET, incidentally) picks up and iterates through.

I'm trying to intercept the cursor using SQLPlus and PL/SQL but I'm having a hard time trying to figure out how to set up the script. Here's what I have so far:

DECLARE
    cur sys_refcursor;
BEGIN
  adv_schema.report_proc('CAL','01-JAN-2011','01-JAN-2012','Y',cur);
  OPEN cur FOR --??????
  LOOP
    FETCH cur INTO column1, column2;
    EXIT WHEN cur%NOTFOUND;
    DBMS_OUTPUT.Put_Line ('First Name: '||column1||' Last Name: '||column2);
  END LOOP;
END;

/

What do I put in the OPEN statement? All the examples I've seen of how to do this are overly simplified examples where some table 't' is created right there in the PL/SQL block, then a cursor is opened with a query to that table, to loop over. What about when a procedure returns a cursor to a complex query with multiple tables?

Upvotes: 1

Views: 3850

Answers (2)

Nick Krasnov
Nick Krasnov

Reputation: 27251

As @Justin has already mentioned, there is no need to open the cursor returned by the report_proc procedure, you only need to fetch from that cursor. Ref cursors are weak cursors(basically cursors that return no type) and in order to fetch from a weakly typed cursor, you need to know what to fetch. When you know what type a cursor returns you can declare a local structure to fetch into, like so:

DECLARE
  -- example of record 
  -- in your case you have to know exactly
  -- how many column and of which datatype your ref cursor returns
   type T_Record is record(
     column_1 number,
     column_2 number
   );
   l_record T_Record;
   cur sys_refcursor;
BEGIN
    adv_schema.report_proc('CAL','01-JAN-2011','01-JAN-2012','Y',cur);
    LOOP
       FETCH cur 
        INTO l_record;
        EXIT WHEN cur%NOTFOUND;
       DBMS_OUTPUT.Put_Line ('First Name: '||l_record.column1
                           ||' Last Name: '||l_record.column2);
    END LOOP;
END;

Also, if you simply need to print the content of a ref cursor, you can do it in SQL*Plus as follows:

SQL> variable cur refcursor;

SQL> exec adv_schema.report_proc('CAL','01-JAN-2011','01-JAN-2012','Y', :cur);

And then use print command to print the refcursor cur:

SQL> print cur;

Do I need to fetch every column that the cursor returns, or can I fetch a subset, say the first three.

No, you fetch everything, you cannot be selective in what column to fetch. It's not impossible however, but it will involve using dbms_sql package, specifically dbms_sql.describe_columns procedure to get information about columns for a cursor.

Just for consideration, If you know that a specific column is definitely present in a cursor, you could use xmlsequence() function to fetch a specific column specifying its name in the extract() function: 

SQL> declare
  2    type T_List is table of varchar2(123);
  3    l_names   T_List;
  4    l_ref_cur sys_refcursor;
  5  
  6  begin
  7    open l_ref_cur
  8     for select first_name, last_name
  9           from employees
 10          where rownum <= 5;
 11  
 12   SELECT t.extract('ROW/FIRST_NAME/text()').getstringval()
 13     bulk collect into l_names
 14     FROM table(xmlsequence(l_ref_cur)) t;
 15  
 16    for indx in l_names.first..l_names.last
 17    loop
 18      dbms_output.put_line(l_names(indx));
 19    end loop;
 20  end;
 21  /

Result:

Ellen
Sundar
Mozhe
David
Hermann
PL/SQL procedure successfully completed

Upvotes: 4

Justin Cave
Justin Cave

Reputation: 231651

Assuming that the report_proc procedure is returning the cursor (i.e. the fourth parameter is defined as an OUT SYS_REFCURSOR), there is no need to OPEN the cursor in your code. The procedure is already opening it. You just need to fetch from it.

Upvotes: 5

Related Questions