Writing large volume of Data to File from Oracle Tables using PLSQL in Oracle apps

In this post I have given script to write large volume of Data to file. There are 3 methods which ever is working we can use it.

1. Using UTL_FILE Utility if it exports as expected

set serveroutput on;
/
DECLARE
    l_file  utl_file.file_type;
    CURSOR c_item IS SELECT inventory_item_id, enabled_flag, segment1, description
    FROM
        mtl_system_items_b
    WHERE
        organization_id = 0;  --Org ID 0 is the Master Item
    cr_item c_item%rowtype;
BEGIN
    l_file := utl_file.fopen('TEST', 'ITEM_EXPORT.csv', 'w', 32767); -- TEST is the Directory to be created in dba_directories
  -- Write header
    utl_file.put_line(l_file, 'INVENTORY_ITEM_ID'|| '|' || 'ENABLED_FLAG' || '|'   || 'SEGMENT1' || '|' || 'DESCRIPTION');

    dbms_output.put_line('Header Complete - After Writing Lines   ');
    OPEN c_item;
    LOOP
        FETCH c_item INTO cr_item;
        EXIT WHEN c_item%notfound;
        utl_file.put_line(l_file, cr_item.inventory_item_id
                                  || '|'
                                  || cr_item.enabled_flag
                                  || '|'
                                  || cr_item.segment1
                                  || '|'
                                  || cr_item.description);

    END LOOP;

    dbms_output.put_line('Writing Lines Completed   ');
    utl_file.fclose(l_file);

END;

2. Using UTL_FILE Utility with limiting the batch size

set serveroutput on;
/
DECLARE
    l_file  utl_file.file_type;
    CURSOR c_item IS SELECT inventory_item_id, enabled_flag, segment1, description
    FROM
        mtl_system_items_b
    WHERE
        organization_id = 0;  --Org ID 0 is the Master Item
    cr_item c_item%rowtype;
    l_batch_size        NUMBER :=30000;
       TYPE t_data_tab IS TABLE OF c_item%ROWTYPE INDEX BY PLS_INTEGER;
        l_data_tab          t_data_tab;
BEGIN
    l_file := utl_file.fopen('TEST', 'ITEM_EXPORT.csv', 'w', 32767);
  -- Write header
    utl_file.put_line(l_file, 'INVENTORY_ITEM_ID'|| '|' || 'ENABLED_FLAG' || '|'   || 'SEGMENT1' || '|' || 'DESCRIPTION');

    dbms_output.put_line('Header Complete - After Writing Lines   ');
    OPEN c_item;
    LOOP
--        FETCH c_item INTO cr_item;
            FETCH c_item BULK COLLECT INTO l_data_tab LIMIT l_batch_size;
            EXIT WHEN l_data_tab.COUNT = 0;  
            FOR i IN 1..l_data_tab.COUNT LOOP
            utl_file.put_line(l_file, cr_item.inventory_item_id
                                  || '|'
                                  || cr_item.enabled_flag
                                  || '|'
                                  || cr_item.segment1
                                  || '|'
                                  || cr_item.description);

    END LOOP;
  END LOOP;
        
    dbms_output.put_line('Writing Lines Completed   ');
    utl_file.fclose(l_file);

END;

3. Using DBMS_XSLPROCESSOR Utility

set serveroutput on;
/
DECLARE
--    l_file  utl_file.file_type;
     v_clob      CLOB := EMPTY_CLOB();
    CURSOR c_item IS SELECT inventory_item_id, enabled_flag, segment1, description
    FROM
        mtl_system_items_b
    WHERE
        organization_id = 0;  --Org ID 0 is the Master Item  
        l_counter NUMBER := 0;
BEGIN

  -- Write header
      v_clob := to_char('INVENTORY_ITEM_ID|ENABLED_FLAG|SEGMENT1|DESCRIPTION')|| CHR(10);  --CHR(10) Instructs Utility for next line

    dbms_output.put_line('Header Complete - Before Writing Lines   ');
    for X in c_item loop
--     FND_FILE.PUT_LINE(FND_FILE.LOG,'Inside Promotion Consumer Loooping '||c3_r.eventID  );
   --     FETCH c3 INTO c3_r;
   --     EXIT WHEN c3%NOTFOUND;
    v_clob := v_clob||to_char(X.INVENTORY_ITEM_ID) || '|' || to_char(X.ENABLED_FLAG) || '|' || to_char(X.SEGMENT1) || '|' || to_char(X.DESCRIPTION)|| CHR(10);
    l_counter := l_counter+1;
    END LOOP;
    DBMS_XSLPROCESSOR.CLOB2FILE(v_clob, 'TEST', 'ITEM_EXPORT.csv');  --TEST is the Directory to be created in dba_directories, ITEM_EXPORT.csv is the file name
    dbms_output.put_line('Writing Lines Completed   ');

END;