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.

Answers


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;

Need Your Help

Sr Programming Language Install On Ubuntu 14.04 Compiler Errors

c++ c linux ubuntu gcc

The SR Programming language is no longer being maintained, so I realize my chances of finding help on this issue might be slim. However, I need the language installed for classroom purposes and can't

calligraphy fonts on a website

html css fonts photoshop

if i want to have some text show up in a calligraphy font, how do i know how it will render of the users computer. How do i know what fonts that person has on the computer or does it matter? any ...