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.

I'm not sure what the question is here. If you want help debugging the error, we're going to need more information such as the error. And a reproducible test case that we can run locally. If you're looking for best practices, I'd take a huge step back and explain what the actual requirements are because this looks to be a really weird approach. Is the middle tier then concatenating the various rows together to generate a file that is then sent to the client machine? — Justin Cave, Apr 19 '21 at 17:57
The error happens when we are trying to download from UI. We are not exactly sure if it's with the file size or with the traffic as it works sometimes without any issue and sometimes we have try 2/3 times. The below loop adds new row as new line to the first row and so on. At the end of loop we have all rows combined into one with new line separator and then pipe the entire clob LOOP V_OUT_REC.extract_csv := V_OUT_REC.extract_csv || CHR (10) || i.extract_csv; END LOOP; PIPE ROW (V_OUT_REC); — B Red, Apr 19 '21 at 18:08
You understand, right, that debugging an unknown error from an unknown component that can't be reliably reproduced and may be the result of load on the system is not something that anyone here is going to be able to do, right? — Justin Cave, Apr 19 '21 at 18:11
What error do you get? An oracle error? A network error on the client side? Besides that, you may create a function that returns a CLOB, without using a pipelined function. `SELECT PACKAGE_NAME.GET_CSV_EXTRACT('04/19/2021','1') FROM DUAL;` should get you the same result, but with some less overhead. And maybe the error disappear. :-) — D. Mika, Apr 20 '21 at 07:56

score 0 · Accepted Answer · answered Apr 19 '21 at 18:18

(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>

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 — B Red, Apr 19 '21 at 18:22
Perhaps you'd want to fix that in the original message you posted, just to avoid confusion. — Littlefoot, Apr 19 '21 at 18:26
I am adding a header at the beginning. so each column is separated by a tab delimiter — B Red, Apr 19 '21 at 18:27
Hi, how do we retrieve the file from the oracle database located on Linux server and send the file to caller. Created a directory and used the UTL_FILE as mentioned above to save the file. Thanks. — B Red, Apr 21 '21 at 14:32
I was able to find the solution. https://oracle-base.com/articles/8i/import-clob — B Red, Apr 21 '21 at 18:36
OK; I'm glad you found what you're looking for. Thank you for letting us know. — Littlefoot, Apr 21 '21 at 20:09
quick question: In this utl_file.put_line(l_handle, cur_r.deptno || l_delim || cur_r.dname || l_delim || cur_r.loc); does it write to file line by line or will it do it inmemory and write everything at once. Thanks — B Red, Apr 29 '21 at 19:25

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

1 Answers1