Exporting data from Table to CSV file

At a beautyful day, your manager said that: "give me the log of database". Of course, you can not tell your manager that the log is real large to select and read. I'm sure that you must perform the task cheerfully. And you'll think of exporting log data from tables to csv files. If that's what you're thinking about. Then the following is what you must do to finish your task.
I'm not the author. I only cache them here and retest.

CREATE OR REPLACE PACKAGE csv AS -- -------------------------------------------------------------------------- -- Name         : http://oracle-base.com/dba/miscellaneous/cvs.sql -- Author       : Tim Hall -- Description  : Basic CSV API. For usage notes see: --                  http://oracle-base.com/articles/9i/GeneratingCSVFiles.php -- --                  CREATE OR REPLACE DIRECTORY dba_dir AS '/u01/app/oracle/dba/'; --                  ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; -- --                  EXEC csv.generate('DBA_DIR', 'generate.csv', p_query => 'SELECT * FROM emp'); -- -- Requirements : UTL_FILE, DBMS_SQL -- Ammedments   : --   When         Who       What --   ===========  ========  ================================================= --   14-MAY-2005  Tim Hall  Initial Creation -- --------------------------------------------------------------------------   PROCEDURE generate (p_dir     IN  VARCHAR2,                       p_file    IN  VARCHAR2,                       p_query   IN  VARCHAR2); END csv; / SHOW ERRORS CREATE OR REPLACE PACKAGE BODY csv AS -- -------------------------------------------------------------------------- -- Name         : http://oracle-base.com/dba/miscellaneous/cvs.sql -- Author       : Tim Hall -- Description  : Basic CSV API. For usage notes see: --                  http://oracle-base.com/articles/9i/GeneratingCSVFiles.php -- --                  CREATE OR REPLACE DIRECTORY dba_dir AS '/u01/app/oracle/dba/'; --                  ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; -- --                  EXEC csv.generate('DBA_DIR', 'generate.csv', p_query => 'SELECT * FROM emp'); -- -- Requirements : UTL_FILE, DBMS_SQL -- Ammedments   : --   When         Who       What --   ===========  ========  ================================================= --   14-MAY-2005  Tim Hall  Initial Creation -- --------------------------------------------------------------------------   g_sep         VARCHAR2(5)  := ',';   PROCEDURE generate (p_dir     IN  VARCHAR2,                       p_file    IN  VARCHAR2,                       p_query   IN  VARCHAR2) AS     l_cursor    PLS_INTEGER;     l_rows      PLS_INTEGER;     l_col_cnt   PLS_INTEGER;     l_desc_tab  DBMS_SQL.desc_tab;     l_buffer    VARCHAR2(32767);     l_file      UTL_FILE.file_type;   BEGIN     l_cursor := DBMS_SQL.open_cursor;     DBMS_SQL.parse(l_cursor, p_query, DBMS_SQL.native);     DBMS_SQL.describe_columns (l_cursor, l_col_cnt, l_desc_tab);     FOR i IN 1 .. l_col_cnt LOOP       DBMS_SQL.define_column(l_cursor, i, l_buffer, 32767 );     END LOOP;     l_rows := DBMS_SQL.execute(l_cursor);     l_file := UTL_FILE.fopen(p_dir, p_file, 'w', 32767);     -- Output the column names.     FOR i IN 1 .. l_col_cnt LOOP       IF i > 1 THEN         UTL_FILE.put(l_file, g_sep);       END IF;       UTL_FILE.put(l_file, l_desc_tab(i).col_name);     END LOOP;     UTL_FILE.new_line(l_file);     -- Output the data.     LOOP       EXIT WHEN DBMS_SQL.fetch_rows(l_cursor) = 0;       FOR i IN 1 .. l_col_cnt LOOP         IF i > 1 THEN           UTL_FILE.put(l_file, g_sep);         END IF;         DBMS_SQL.COLUMN_VALUE(l_cursor, i, l_buffer);         UTL_FILE.put(l_file, l_buffer);       END LOOP;       UTL_FILE.new_line(l_file);     END LOOP;     UTL_FILE.fclose(l_file);   EXCEPTION     WHEN OTHERS THEN       IF UTL_FILE.is_open(l_file) THEN         UTL_FILE.fclose(l_file);       END IF;       IF DBMS_SQL.is_open(l_cursor) THEN         DBMS_SQL.close_cursor(l_cursor);       END IF;       RAISE;   END generate; END csv; / SHOW ERRORS exec sys.csv.generate('DUMP_DIR', 'testcsv.dat', 'select username, account_status from dba_users');

The result you'll get is the file as bellow:

If you want to split the output to many file:

CREATE OR REPLACE PACKAGE csv AS -- -------------------------------------------------------------------------- -- Name         : http://oracle-base.com/dba/miscellaneous/cvs.sql -- Author       : Tim Hall -- Description  : Basic CSV API. For usage notes see: --                  http://oracle-base.com/articles/9i/GeneratingCSVFiles.php -- --                  CREATE OR REPLACE DIRECTORY dba_dir AS '/u01/app/oracle/dba/'; --                  ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; -- --                  EXEC csv.generate('DBA_DIR', 'generate.csv', p_query => 'SELECT * FROM emp'); -- -- Requirements : UTL_FILE, DBMS_SQL -- Ammedments   : --   When         Who       What --   ===========  ========  ================================================= --   14-MAY-2005  Tim Hall  Initial Creation -- --------------------------------------------------------------------------   PROCEDURE generate (p_dir     IN  VARCHAR2,                       p_file    IN  VARCHAR2,                       p_query   IN  VARCHAR2,                       p_row_per_file IN  NUMBER :=0); END csv; / CREATE OR REPLACE PACKAGE BODY csv AS -- -------------------------------------------------------------------------- -- Name         : http://oracle-base.com/dba/miscellaneous/cvs.sql -- Author       : Tim Hall -- Description  : Basic CSV API. For usage notes see: --                  http://oracle-base.com/articles/9i/GeneratingCSVFiles.php -- --                  CREATE OR REPLACE DIRECTORY dba_dir AS '/u01/app/oracle/dba/'; --                  ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; -- --                  EXEC csv.generate('DBA_DIR', 'generate.csv', p_query => 'SELECT * FROM emp'); -- -- Requirements : UTL_FILE, DBMS_SQL -- Ammedments   : --   When         Who       What --   ===========  ========  ================================================= --   14-MAY-2005  Tim Hall  Initial Creation -- --------------------------------------------------------------------------   g_sep         VARCHAR2(5)  := ',';   PROCEDURE generate (p_dir     IN  VARCHAR2,                       p_file    IN  VARCHAR2,                       p_query   IN  VARCHAR2,                       p_row_per_file IN  NUMBER :=0) AS     l_cursor    PLS_INTEGER;     l_rows      PLS_INTEGER;     l_col_cnt   PLS_INTEGER;     l_desc_tab  DBMS_SQL.desc_tab;     l_buffer    VARCHAR2(32767);     l_file      UTL_FILE.file_type;     i_row       PLS_INTEGER;     i_page      PLS_INTEGER;   BEGIN     l_cursor := DBMS_SQL.open_cursor;     DBMS_SQL.parse(l_cursor, p_query, DBMS_SQL.native);     DBMS_SQL.describe_columns (l_cursor, l_col_cnt, l_desc_tab);     FOR i IN 1 .. l_col_cnt LOOP       DBMS_SQL.define_column(l_cursor, i, l_buffer, 32767 );     END LOOP;     l_rows := DBMS_SQL.execute(l_cursor);     i_page := 0;     l_file := UTL_FILE.fopen(p_dir, TO_CHAR(i_page)||p_file, 'w', 32767);     -- Output the column names.     FOR i IN 1 .. l_col_cnt LOOP       IF i > 1 THEN         UTL_FILE.put(l_file, g_sep);       END IF;       UTL_FILE.put(l_file, l_desc_tab(i).col_name);     END LOOP;     UTL_FILE.new_line(l_file);     -- Output the data.     i_row := 0;     LOOP       EXIT WHEN DBMS_SQL.fetch_rows(l_cursor) = 0;             FOR i IN 1 .. l_col_cnt LOOP         IF i > 1 THEN           UTL_FILE.put(l_file, g_sep);         END IF;         DBMS_SQL.COLUMN_VALUE(l_cursor, i, l_buffer);         UTL_FILE.put(l_file, l_buffer);       END LOOP;       UTL_FILE.new_line(l_file);             i_row := i_row+1 ;       -- New file with column header       IF nvl(p_row_per_file,0) >= 10 THEN         IF MOD(i_row, p_row_per_file)=0 THEN             i_page := i_page + 1;                         UTL_FILE.fclose(l_file);             l_file := UTL_FILE.fopen(p_dir, TO_CHAR(i_page)||p_file, 'w', 32767);                         -- Output the column names.             FOR i IN 1 .. l_col_cnt LOOP               IF i > 1 THEN                 UTL_FILE.put(l_file, g_sep);               END IF;               UTL_FILE.put(l_file, l_desc_tab(i).col_name);             END LOOP;             UTL_FILE.new_line(l_file);         END IF;       END IF;     END LOOP;     UTL_FILE.fclose(l_file);   EXCEPTION     WHEN OTHERS THEN       IF UTL_FILE.is_open(l_file) THEN         UTL_FILE.fclose(l_file);       END IF;       IF DBMS_SQL.is_open(l_cursor) THEN         DBMS_SQL.close_cursor(l_cursor);       END IF;       RAISE;   END generate; END csv; /