Create .CSV file in Oralce PL SQL

Requirement: To generate delimited output of SQL query using Oracle PL SQL

Solution :

Please find below complete package code.

This package can be used to write delimited output to DBMS output, Oracle Apps Log/Output or File Directory.

Usage

Call the procedure xxtk_generate_csv with mandatory parameter values.

PROCEDURE xxtk_generate_csv (

p_query VARCHAR2,

p_delimiter varchar2,

p_file_handle number,

p_dir varchar2 DEFAULT NULL,

p_filename varchar2 DEFAULT NULL,

p_cur_qry VARCHAR2 DEFAULT 'Q'

) ;

First parameter is SQL query as Varchar2 data type, second is delimiter(tab or comma or other) and third is file handle i.e where to write output.

Example 1

Consider below query

select a "col1", b from (select 1 a, level b from dual connect by level < 4)

Comma separated Output of this query is required

[sourcecode language="sql"]
DECLARE
--q operator is available in 11i to escape quotes withing string
l_qry varchar2(1000):=q'[select a "col1", b from (select 1 a, level b from dual connect by level < 4)]';
BEGIN
--write result of l_qry as comma separated values to dbms output
xxtk_generate_csv_pkg.xxtk_generate_csv
(l_qry,',',xxtk_generate_csv_pkg.write_to_db_out);
END;
[/sourcecode]

Q operator is used to escape quote pairs appearing in data. Otherwise, we need to escape each quote separately.

DBMS Output

"col1","B"

1,1

1,2

1,3

Example 2

4 and 5 parameter are used to specify directory location and file name where to write output.

6 parameter p_cur_qry is used when passing cursor handle.

When input parameters are required for query either we can

• pass query text(first parameter)  with concatenated variable values (like where select ....where dept_no='||p_dept_no||'..' or


• pass parsed query with bind variable and send the parsed query handle (following example)

Second option is better because using bind variable is always preferable instead of concatenating variables in SQL query.

In case when SQL query is already parsed  with bind variable values.

[sourcecode language="sql"]

DECLARE
cursor_name   INTEGER         DEFAULT DBMS_SQL.open_cursor;
l_qry varchar2(1000):=q'[select *  from scott.emp where deptno =:p_dept_no and hiredate > :p_hire_date]';
BEGIN
--get parsed query handle in cursor_name
DBMS_SQL.parse (cursor_name, l_qry, DBMS_SQL.native);
--set value of dept_no to 20
DBMS_SQL.bind_variable (cursor_name, ':p_dept_no',20 );
--set hire date
DBMS_SQL.bind_variable (cursor_name, ':p_hire_date','01-JAN-1981' );

xxtk_generate_csv_pkg.xxtk_generate_csv(p_query=>cursor_name,p_delimiter=>',',

p_file_handle=>xxtk_generate_csv_pkg.write_to_db_out,p_cur_qry=>'C' );
END;
[/sourcecode]

Output

[sourcecode]

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7788,SCOTT,ANALYST,7566,09-DEC-82,3000,,20
7876,ADAMS,CLERK,7788,12-JAN-83,1100,,20
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20
[/sourcecode]

2 GB File limit on writing file using Oracle SQLPlus in Unix

Requirement

Dump of huge data is required from oracle database. Say, dump of GL data (specific query output and not complete table dump) is required for particular month.

I simply spool the output of query and what I observe is - it always produces 2GB output file, for few months I tested, without giving any error message or warning.

So, I google for "2GB Unix oracle SQLPlus" and I find number of links mentioning this problem

unix 2GB oracle export

One of the solution involves using named pipe.

A named pipe doesn't write data to disk, but instead write to buffer in memory. Writer writes at one end and Reader reads from other end.

Named Pipe

• named pipe is actually file in file-system


• Like unnamed pipe it is  used for Inter process Communication


• Unlike unnamed pipe it is system-persistent, that is, it exists beyond process life and has to be created and deleted explicitly.


• Process that reads or writes to pipe blocks until the other end of pipes performs read or write operation on same

Create a named pipe, redirect data written to it to sed command.

Removes extra tabs coming in spool file(spool usually gives extra tabs when colsep is used), and then pipe output to compress command which writes compressed data to disk.

Sqlplus will write data at one end to named pipe and following command will write compressed data to disk.

cat /tmp/aug11_pipe | compress  > gl_aug11_pipe.csv