CODEWITHSUNDEEP
oracle-databaseplsqloracle12c
Mayank
Mayank

Reputation: 1392

How to loop through records of stored procedure with out type SYS_REFCURSOR

I am new to oracle. The problem is there is an existing procedure that has following parameters

   P_ENTT        IN     PURCHASEORDER.POCODE%TYPE,
   P_LOCCODE     IN     LOCATION.LOCATIONCODE%TYPE,
   P_PROCESSID   IN     VARCHAR,
   V_TAXREF         OUT SYS_REFCURSOR

I am writing a new procedure that will loop through the records returned by the above procedure. I have tried using Loop keyword like below

FOR rec IN (OLDProcedure(Old_params))
  LOOP
     -- some calculations

  END LOOP;

But unable to implement the same. How to loop through returned records in this scenario with in a stored procedure.

Upvotes: 0

Views: 1125

Answers (2)

Steven Feuerstein
Steven Feuerstein

Reputation: 1974

As @XING implies in the above answer, and I will make explicit, you cannot use a cursor for loop with a cursor variable. So you open the cursor variable, and then use a simple loop with explicit fetch, exit-when and close statements.

But then you might ask: why doesn't @XING close the cursor? Since it is declared locally, when the block terminates the cursor variable will automatically be closed by the PL/SQL engine.

Thanks, PL/SQL!

Upvotes: 1

XING
XING

Reputation: 9886

Rewrite your procedure on the basis of below shown Anonymous block. Pease read the comments inline to understand.

DECLARE
  --Variable of type of your procedure `OUT` paramater
  var sys_refcursor;
 --Columns selected in your sys_refcursor query
  var1 NUMBER;
  var2 NUMBER;
  var3 NUMBER;
BEGIN
 --call your existing procedure in new procedure
 myProc( P_ENTT =>1,  V_TAXREF => var); --Pass other parameters as well
  --Loop to get your records
  LOOP
   --Fetch records of your sys_refcursor to variables
    FETCH var INTO var1,var2,var3;
    EXIT  WHEN var%notfound;
    --Display your records 
    dbms_output.put_line(var1);
  END LOOP;
END;

Upvotes: 2

Related Questions