Wednesday, November 7, 2007

Testing results of REF CURSOR in PL/SQL and Toad

I'm new to .Net development with Oracle procedures and today I just discovered the PL/SQL to call a procedure that returns a REF CURSOR and print the output in Toad. 
 
Many thanks to Littlefoot for the answer to making this work in Toad:
In such a case, check that DBMS Output is enabled (turn red button to a green one) and, possibly, set "Frequency of polling" to a minumum value (2 sec) - otherwise, you'll have to wait (default) 5 seconds for anything to appear.
 
DECLARE
  CUR1  AMR_PACK.PROJECT_CUR;
   C1    NUMBER;
   C2    VARCHAR2(10);
   C3    VARCHAR2(100);
   C4    VARCHAR2(20);
   T1    VARCHAR2(100);
   T2    NUMBER;
   P1    VARCHAR2(100);
   P2    NUMBER;
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
 
  EXTRACT_APPS_SUPPORTED('',OUT_CURSOR => CUR1);
 
  LOOP
    FETCH CUR1 INTO C1,
    C2,
    C3,
    C4,
    T1,
    T2,
    P1,
    P2;
   
    EXIT WHEN CUR1%NOTFOUND;
   
    DBMS_OUTPUT.PUT_LINE('base_id: ' || C1);
  END LOOP;
 
  CLOSE CUR1;
END;
 

No comments: