0

I have an Oracle stored procedure which accepts/returns a REF CURSOR. The resultset contains lots of columns, say 20 of them. I know how to loop the cursor and display it on the screen (see attached).

My question is: is there anyway I can eliminate the code in my DECLARE where I had to define TYPE RECORD. If the returned result contains 50 columns, this becomes tedious. Are there any other options or a more simplified version that someone can show me? Ultimately I am looking for the least amount of code to loop through the returned cursor and display the values on the screen.

Thanks!

-- Oracle procedure

Get_Student(ioStu_cursor IN OUT REF CURSOR, iDate IN DATE)

-- PL/SQL to display records in cursor

SET SERVEROUTPUT ON;
declare 
  c_cursor SYS_REFCURSOR;

  type a is record
  (
        col1 number(10,0);
        col2
        ...
        col20
  );

  c_record a;

begin  
  Get_Student(c_cursor, to_date('26-apr-2016', 'dd-mon-yyyy'));  

  loop
    fetch c_cursor into c_record;    
    exit when c_cursor%NOTFOUND;
    dbms_output.put_line('col1: ' || c_record.col1);    
  end loop;
cableload
  • 4,215
  • 5
  • 36
  • 62
sydney
  • 131
  • 8
  • 19
  • Display the values on the screen where - in a specific client? In SQL Developer and SQL\*Plus [you can do this](http://stackoverflow.com/a/8618084/266304); don't know if it works in any other clients. – Alex Poole Apr 27 '16 at 23:27
  • I followed your link and was able to execute the following: var r refcursor; exec myPackage.mySPTest(P_NOTIFICATION_ID => 1975357, P_CURSOR => :r); print r; Thanks – sydney Apr 29 '16 at 12:21
  • Possible duplicate of [How to see refcursor result/output in Oracle SQL Developer?](http://stackoverflow.com/questions/8610495/how-to-see-refcursor-result-output-in-oracle-sql-developer) – Alex Poole Apr 29 '16 at 12:36

0 Answers0