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