Better performing sql code for extracting data to a file?
I work with datasets that exceeds the millions on a daily basis, most of my work I do at this stage is to write code for extracting the data from the databases into files for other users to use in their business intelligence divisions.
The code that I am currently using is a basic FOR loop which looks like this:
for x in cursor_body loop utl_file.put_line(out_file_filename, x.data_line); end loop;
This takes hours to complete at this stage and I need something that moves a bit faster since this will run on a weekly basis and we have other unix bash jobs that has to run as well.
I've read up on some solutions using a little C or Java to do more efficient date extracts, but I am wondering if there isn't a PL/SQL or SQL way of doing it faster.
Your inputs will be highly appreciated. Thank you.
Maybe it works better when you write everything into a CLOB and then write it to disc:
DECALRE UTF8 BOOLEAN TRUE; BUFFER VARCHAR2(4096 CHAR); offset PLS_INTEGER := 1; FileLength PLS_INTEGER; amount BINARY_INTEGER := 1024; fhandle UTL_FILE.FILE_TYPE; TXT CLOB; BEGIN FOR aLine IN cursor_body LOOP DBMS_LOB.WRITEAPPEND(TXT, LENGTH(aLine.data_line), aLine.data_line); END LOOP; FileLength := DBMS_LOB.GETLENGTH(TXT); IF UTF8 THEN fhandle := UTL_FILE.FOPEN_NCHAR(FolderName, FileName, open_mode => 'w', max_linesize => 32000); ELSE fhandle := UTL_FILE.FOPEN(FolderName, FileName, open_mode => 'w', max_linesize => 32000); END IF; LOOP EXIT WHEN offset > FileLength; DBMS_LOB.READ(TXT, amount, offset, BUFFER); IF UTF8 THEN UTL_FILE.PUT_NCHAR(fhandle, BUFFER); ELSE UTL_FILE.PUT(fhandle, BUFFER); END IF; UTL_FILE.FFLUSH(fhandle); offset := offset + amount; END LOOP; UTL_FILE.FCLOSE (fhandle); DBMS_LOB.FREETEMPORARY(TXT); END;