top of page

BULK COLLECT with Custom SQL Statement

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

   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
   sql_stmt_ := 'SELECT t.order_no, '     ||
                '       t.contract, '     ||
                '       t.customer_no, '  ||
                '       t.currency_code ' ||
                'FROM   customer_order_tab t ' ||
                'WHERE  ROWNUM <= 10';
   
   EXECUTE IMMEDIATE sql_stmt_ BULK COLLECT INTO customer_order_list;
   
   FOR i_ IN 1 .. customer_order_list.COUNT LOOP
      Print_Info(customer_order_list(i_));
   END LOOP;
END;

2 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