top of page

BULK COLLECT using CURSOR FOR LOOP

Writer's picture: Rumesh Aponso (RMAX)Rumesh Aponso (RMAX)
DECLARE
   rec_limit_     NUMBER := 100;
   fetch_limit_   CONSTANT PLS_INTEGER DEFAULT 9;

   CURSOR get_co_info IS 
      SELECT t.order_no,
             t.contract,
             t.customer_no,
             t.currency_code
      FROM   customer_order_tab t;

   TYPE customer_order_list_tab IS TABLE OF get_co_info%ROWTYPE INDEX BY BINARY_INTEGER;
   customer_order_list         customer_order_list_tab;
   
   PROCEDURE Print_Info (
      rec_ IN get_co_info%ROWTYPE )
   IS
   BEGIN
      Dbms_Output.Put_Line(rec_.order_no    || ' - ' || 
                           rec_.contract    || ' - ' || 
                           rec_.customer_no || ' - ' || 
                           rec_.currency_code);
   END Print_Info;
BEGIN
   OPEN get_co_info;

   LOOP
      FETCH get_co_info BULK COLLECT INTO customer_order_list LIMIT fetch_limit_;
      EXIT WHEN rec_limit_ <= 0;

      Dbms_Output.Put_Line('Retrieved: ' || customer_order_list.COUNT);

      FOR i_ IN 1 .. customer_order_list.COUNT LOOP
         Print_Info(customer_order_list(i_));
      END LOOP;
      
      rec_limit_ := rec_limit_ - fetch_limit_;
   END LOOP;

   CLOSE get_co_info;
END;

5 views0 comments

Related Posts

See All

Fnd Dynamic Tab Page in IEE Client

[FndWindowRegistration("YVVDA_COMPANY_ADDR", "YvvdaCompanyAddr", FndWindowRegistrationFlags.HomePage)] [FndDynamicTabPage("frmCompanyAddr...

LISTAGG in PLSQL

Example 1: SELECT DISTINCT LISTAGG(t.commission_receiver, ';') WITHIN GROUP (ORDER BY t.commission_receiver) FROM...

Get Foundation Error from ORA Error

FUNCTION Strip_Ora_Error ( sqlerrm_        IN VARCHAR2,    sqlcode_        IN NUMBER DEFAULT NULL,    strip_ora_only_ IN BOOLEAN DEFAULT...

Comments


Copyright © 2025 RMAXOneNote

  • Online CV
  • LinkedIn
  • Youtube
  • GitHub
  • Blogger
bottom of page