Write a function/procedure to combine around 150k rows into one and send it as output to be able to download from UI

Question

I would like to get some inputs on the best approach to do the below mentioned scenario. I am just looking for an alternative best approach here, not to debug the error.

I have a select statement that produces around 150K rows with around 10 columns. I need to concatenate the columns with a tab delimiter and then loop through each row and combine all together into a single row. This column value is being called using a function and is downloaded from UI.

current approach: write a pipelined function to get it as clob output and use this to download from UI.

TYPE OUT_REC_CSV_TYP IS  RECORD
    ( object_status                    VARCHAR2        ( 4000    ) ,
     extract_csv                    CLOB    ) ;

TYPE OUT_REC_CSV_TABTYP IS TABLE OF OUT_REC_CSV_TYP;

FUNCTION GET_CSV_EXTRACT (P_DATE_REPORTED   IN VARCHAR2,
                          P__USER_ID        IN NUMBER DEFAULT NULL)
    RETURN OUT_REC_CSV_TABTYP
    PIPELINED
IS
    V_OUT_REC         OUT_REC_CSV_TYP;
    V_OUT_REC_EMPTY   OUT_REC_CSV_TYP;
BEGIN
    V_OUT_REC := V_OUT_REC_EMPTY;
    V_OUT_REC.OBJECT_STATUS := NULL;                         --- ADDING HEADER

    SELECT 'COLUMN_A' || CHR (9) || 'COLUMN_B'     AS extract_csv --have around 15 columns
      INTO V_OUT_REC.extract_csv
      FROM DUAL;

    FOR i IN (SELECT 'COLUMN_A' || CHR (9) || 'COLUMN_B'     AS extract_csv
                FROM (WITH
                          TABLE_A AS (SELECT * FROM table_1),
                          TABLE_B AS (SELECT * FROM table_2)
                      SELECT COLUMN_A, COLUMN_B
                        FROM TABLE_A, TABLE_B
                       WHERE TABLE_A.COLUMN_NAME = TABLE_B.COLUMN_NAME))
    LOOP
        V_OUT_REC.extract_csv :=
            V_OUT_REC.extract_csv || CHR (10) || i.extract_csv;
    END LOOP;

    PIPE ROW (V_OUT_REC);
    RETURN;
END GET_CSV_EXTRACT;

select extract_csv from TABLE(PACKAGE_NAME.GET_CSV_EXTRACT('04/19/2021','1'));

I might have worded it wrongly. expected output: all rows combine into one separated by a new line COL_A COL_B COL_C COL_D COL_E 155189 TEST TEST TEST ABCD 127557 TEST TEST TEST ABCD ....... say around 150K rows combined

This approach sometime throw an error and works after couple of tries to download this clob value text file.

Answer 1

(150K rows) * (10 columns) in a single line? Is that what you are saying? Who can ever understand what's written in there (mind TAB as a column separator, along with possible NULL values).

On the other hand, code you posted looks like you're having every row in its own line; this:

V_OUT_REC.extract_csv := V_OUT_REC.extract_csv || CHR (10) || i.extract_csv;

I might be wrong about it, but I'd say that your words don't match your code.

---

Therefore, how about another approach? SQL*Plus and its spool command. Something like this:

SQL> set linesize 100
SQL> set pagesize 0
SQL> set colsep "       "    --> this is (double quotes) (pressed TAB on keyboard) (double quotes)
SQL> spool test.txt
SQL> select * from dept;
        10      ACCOUNTING      NEW YORK
        20      RESEARCH        DALLAS
        30      SALES           CHICAGO
        40      OPERATIONS      BOSTON

SQL> spool off;

As simple as that.

---

Or, if it has to be a stored procedure, I'd rather think of UTL_FILE which creates the file. This approach, though, requires access to a directory (which usually resides on a database server).

SQL> declare
  2    l_handle  utl_file.file_type;
  3    l_delim   varchar2(20) := chr(9);   -- TAB character
  4  begin
  5    l_handle := utl_file.fopen('EXT_DIR',
  6                               'test.txt',
  7                               'w');
  8
  9    for cur_r in (select deptno, dname, loc
 10                  from dept)
 11    loop
 12      utl_file.put_line(l_handle, cur_r.deptno || l_delim ||
 13                                  cur_r.dname  || l_delim ||
 14                                  cur_r.loc);
 15    end loop;
 16
 17    utl_file.fclose(l_handle);
 18  exception
 19    when others then
 20      utl_file.fclose(l_handle);
 21      raise;
 22  end;
 23  /

PL/SQL procedure successfully completed.

SQL> $type c:\temp\test.txt            --> because c:\temp is where EXT_DIR directory points to
10      ACCOUNTING      NEW YORK
20      RESEARCH        DALLAS
30      SALES   CHICAGO
40      OPERATIONS      BOSTON

SQL>