I am facing a significant challenge.

I have several procedures in my database that export data from queries using UTL_FILE. However, UTL_FILE has a limitation of 32KB per file, which prompted me to come up with a solution: I implemented a loop where, in each iteration, the .txt file is incremented with data no larger than 32KB. This approach worked well for a while, but after modifying my queries, the data size increased to over 10 million lines. As a result, my original looping strategy has become impractical, as it requires running the SQL query, creating the .txt file, saving up to 32KB of data, and closing the file during each iteration.

Considering that my query only takes about 5 minutes to execute, the time required for each loop becomes excessive, especially when I have to run 50 to 100 iterations. This means each iteration takes at least 5 minutes, multiplied by the number of loops, leading to hours of processing time.

Currently, I have 9 queries, and the total export process takes approximately 8 to 9 hours. I need to find a way to significantly reduce this time. For context, I created an export program in Python, and the same data exports take less than 1 hour.

Could you suggest a faster alternative to export large amounts of data into .txt files from Oracle?

Example:

PROCEDURE EMPLOYEE IS
    limite    INTEGER;
    counter   INTEGER := 0;
    step      INTEGER := 500;
    file      UTL_FILE.FILE_TYPE;
    name_file VARCHAR(50) := 'X_TEST_' || TO_CHAR(sysdate, 'YYYYMMDDHH24MISS')||'_fornecedores.txt';
  BEGIN
    file := UTL_FILE.FOPEN(location => 'MYDIR',
                           filename => name_file,
                           open_mode => 'w');

    SELECT COUNT(*)
      INTO limite
      FROM table f
      LEFT JOIN table_2 c
        ON f.seqfornecedor = c.seqfornecedor
       AND c.indprincipal = 'S'
      JOIN table_3 g
        ON f.seqfornecedor = g.seqpessoa
     WHERE f.statusgeral = 'A'
       AND f.dtaalteracao = TRUNC(sysdate - 1);

      
    WHILE counter <= limite LOOP
      file := UTL_FILE.FOPEN(location => 'MYDIR',
                             filename => name_file,
                             open_mode => 'a');
      FOR k IN (
                SELECT desc_fornec,
                       cod,
                       fantasia,
                       cpf_cnpj,
                       contato,
                       emailcontato,
                       fixo_1,
                       fixo_2
                  FROM (SELECT g.seqpessoa || '_' || g.nomerazao AS desc_fornec,
                               g.seqpessoa AS cod,
                               g.fantasia,
                               CASE
                                 WHEN g.fisicajuridica = 'J' THEN
                                  LPAD(g.nrocgccpf, 12, '0') || LPAD(g.digcgccpf, 2, '0')
                                 WHEN g.fisicajuridica = 'F' THEN
                                  LPAD(g.nrocgccpf, 9, '0') || LPAD(g.digcgccpf, 2, '0')
                               END as CPF_CNPJ,
                               f.nomecontato AS contato,
                               f.emailcontato,
                               1 AS FIXO_1,
                               0 AS FIXO_2,
                               ROW_NUMBER() OVER(ORDER BY g.seqpessoa) AS row_num
                          FROM table f
                          LEFT JOIN table_2 c
                            ON f.seqfornecedor = c.seqfornecedor
                           AND c.indprincipal = 'S'
                          JOIN table_3 g
                            ON f.seqfornecedor = g.seqpessoa
                         WHERE f.statusgeral = 'A'
                           AND f.dtaalteracao = TRUNC(sysdate - 1))
                 WHERE row_num > counter
                   AND row_num <= counter + step
               )
          
        LOOP
          UTL_FILE.PUTF(file,
                        k.desc_fornec || ';' ||
                        k.cod || ';' ||
                        k.fantasia || ';' ||
                        K.CPF_CNPJ || ';' ||
                        k.contato || ';' ||
                        k.emailcontato || ';' ||
                        k.fixo_1 || ';' ||
                        k.fixo_2 || UTL_TCP.CRLF);
      END LOOP;
      UTL_FILE.FCLOSE(file);

      counter := counter + step;
    END LOOP;
  END;