How to Export Table Data to Flat File :: Oracle UnLoader, Text File Exporter

As every developer knows, data always transfers between platforms. One advantage of XML is also exchanging and/or transferring data. One day, your boss may want to see some sales data in excel sheet. So, if you have a program that exports data in xls format, this will not be problem; but if you do not have one, you may some how fullfill the requirements, if you do not want loose your job :)
 
Using SQL*Plus may be one of solutions. If you have to do it programatically, you can use Oracle's scheduler. First you have to create an SQL file that specifies how exporting will be. One example may be:
set heading off;
set feedback off;
spool e:\log.txt
select * from &1;
spool off;
exit;
If you save above code E:\exp_table.sql as, you are ready to execute scheduler:
--create program
BEGIN
DBMS_SCHEDULER.create_program(program_name => 'EXP_DATA_PRG',
program_type => 'EXECUTABLE',
program_action => 'E:\oracle\product\10.2.0\db_2\BIN\sqlplus.exe hr/hr@ORCL@"E:\exp_table.sql" jobs',
number_of_arguments => 0,
enabled => FALSE,
comments => 'Export data Program');
END;
/

--create job
BEGIN
DBMS_SCHEDULER.create_job(job_name => 'EXP_DATA_JOB',
program_name => 'EXP_DATA_PRG',
start_date => NULL,
repeat_interval => NULL,
end_date => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => 'Export data Job');
END;
/
--enable program and job
BEGIN
DBMS_SCHEDULER.enable(NAME => 'EXP_DATA_PRG');
DBMS_SCHEDULER.enable(NAME => 'EXP_DATA_JOB');
END;
/
--run job
BEGIN
DBMS_SCHEDULER.RUN_JOB(job_name => 'EXP_DATA_JOB',use_current_session => TRUE);
END;
/
After job executes, e:\log.txt will be your export file. You can play with parameters, as your requirements.
 
As you think (like me) this is not a good solution. So, i started to write some codes to generate more flexible text exporter for Oracle. I developed a package, UTL_EXP, for this job.
There are two program in this package. Specs are below:
FUNCTION ExportTableData
(
--table name that will be exported
pis_TableName IN ALL_TABLES.TABLE_NAME%TYPE,
--filter criteria,if exists
pis_WhereCondition IN VARCHAR2 DEFAULT NULL,
--if table has more columns how fields will be seperated
pis_FieldDelimiter IN VARCHAR2 DEFAULT ',',
--row delimiter, CHR(10) for newline
pis_RecordDelimiter IN VARCHAR2 DEFAULT CHR(10),
--if some columns will not be included
pis_ExceptedFieldList IN VARCHAR2 DEFAULT NULL,
--1 for fields enclosed by ", such as "column_value"
pin_FieldEnclosedByQuote IN NUMBER DEFAULT 0,
--if column name will not be included, set as 0
pin_IncludeColumnName IN NUMBER DEFAULT 0,
--if field names included
pis_FieldNameDelimiter IN VARCHAR2 DEFAULT ':'
) RETURN CLOB;
PROCEDURE ExportFile
(
--oracle directory name
pis_DirectoryName in VARCHAR2,
--file name
pis_FileName in VARCHAR2,
--table name that will be exported
pis_TableName IN ALL_TABLES.TABLE_NAME%TYPE,
--filter criteria,if exists
pis_WhereCondition IN VARCHAR2 DEFAULT NULL,
--if table has more columns how fields will be seperated
pis_FieldDelimiter IN VARCHAR2 DEFAULT ',',
--row delimiter, CHR(10) for newline
pis_RecordDelimiter IN VARCHAR2 DEFAULT CHR(10),
--if some columns will not be included
pis_ExceptedFieldList IN VARCHAR2 DEFAULT NULL,
--1 for fields enclosed by ", such as "column_value"
pin_FieldEnclosedByQuote IN NUMBER DEFAULT 0,
--if column name will not be included, set as 0
pin_IncludeColumnName IN NUMBER DEFAULT 0,
--if field names included
pis_FieldNameDelimiter IN VARCHAR2 DEFAULT ':'
) ;
 
On exporting data you have some options:

• specify some criterias
• not to export some columns
• field and row delimiters
• put column values between quotes
• include table column name

A simple demostration will show how to use UTL_EXP package.
 
SQL> drop table t;
Table dropped
SQL> create table t(i number, v varchar2(12), d date);
Table created
SQL> insert into t values(1,'test data 1', sysdate + 1);
1 row inserted
SQL> insert into t values(2,'test data 2', sysdate + 2);
1 row inserted
SQL> insert into t values(3,'test data 3', sysdate + 3);
1 row inserted
SQL> SELECT * FROM t;
I V D
---------- ------------ -----------
1 test data 1 08.07.2007
2 test data 2 09.07.2007
3 test data 3 10.07.2007
SQL> set long 10000000;
SQL> SELECT UTL_EXP.ExportTableData('t','i > 0',';',chr(10),'i',1, 0,null) FROM dual;
UTL_EXP.EXPORTTABLEDATA('T','I
--------------------------------------------------------------------------------
"test data 1";"08/07/2007";
"test data 2";"09/07/2007";
"test data 3";"10/07/2007";

SQL> DECLARE
2 c CLOB;
3 BEGIN
4 c := UTL_EXP.ExportTableData('t','i > 0',';',chr(10),'i',1, 0,null);
5 utl_utl.PutLine(c);
6 END;
7 /
PL/SQL procedure successfully completed
SQL> SELECT DIRECTORY_NAME FROM all_directories;
DIRECTORY_NAME
------------------------------
DATA_PUMP_DIR
ORACLECLRDIR
TEST_FILE_DIR
 
SQL> BEGIN
2 UTL_EXP.ExportFile('TEST_FILE_DIR', 't.csv','t','i > 0',';',chr(10),'i',1, 0,null);
3 END;
4 /
PL/SQL procedure successfully completed
SQL>
After executing the procedure t.csv will contain:
"test data 1";"08/07/2007";
"test data 2";"09/07/2007";
"test data 3";"10/07/2007";
You can generate more complex export files with UTL_EXP package.