Wednesday, June 27, 2012

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]



Full Source Code

[sourcecode language="SQL"]

CREATE OR REPLACE PACKAGE APPS.xxtk_generate_csv_pkg
AS
--WRITE_TO_FILE number
write_to_app_out NUMBER := 0;
write_to_app_log NUMBER := 1;
write_to_db_out NUMBER := 2;
write_to_dir NUMBER :=3;
l_output utl_file.file_type;
PROCEDURE write_to_file (p_text VARCHAR2, p_file_handle NUMBER);
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') ;

END xxtk_generate_csv_pkg;
/

CREATE OR REPLACE PACKAGE BODY APPS.xxtk_generate_csv_pkg
AS
-- ====================================================
-- Name: xxtk_generate_csv_pkg
--
-- This package is used to generate delimited data for input query
-- Can be used to write to directory file, fnd ouput/log file or dbms_ouput file
-- using global values defined for file handle
--
-- ====================================================
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'
)
AS
cur_handle INTEGER DEFAULT DBMS_SQL.open_cursor;
col_cnt NUMBER;
rec_query_col DBMS_SQL.desc_tab;
col_list VARCHAR2 (3000);
col_seperator VARCHAR2 (5);
err_msg VARCHAR2 (1000);
l_columnvalue VARCHAR2 (4000);
l_status INTEGER;
l_separator VARCHAR2 (5) := NULL;
out_line VARCHAR2 (10000) := NULL;
BEGIN
-- open file if want to write csv to directory
IF p_file_handle = xxtk_generate_csv_pkg.write_to_dir
THEN
l_output := UTL_FILE.fopen (p_dir, p_filename, 'w', 32000);
END IF;

-- when query is passed in that case only parse needs to be done
IF p_cur_qry = 'Q'
THEN
-- parse input query. errors if input sql query is wrong
BEGIN
DBMS_SQL.parse (cur_handle, p_query, DBMS_SQL.native);
EXCEPTION
WHEN OTHERS
THEN
err_msg := SQLERRM;
write_to_file
( 'Error in xxtk_generate_csv_pkg parsing input query'
|| err_msg,
xxtk_generate_csv_pkg.write_to_app_log
);
RAISE;
END;
-- when p_cur_qry flag is C then p_query should contain cursor number of parsed sql
ELSIF p_cur_qry = 'C'
THEN
cur_handle := p_query;
ELSE
write_to_file
( 'Not valid value passed for p_cur_qry',
xxtk_generate_csv_pkg.write_to_app_log
);
END IF;

-- get column details from parsed query
-- populates desc.tab type variable rec_query_col
BEGIN
DBMS_SQL.describe_columns (cur_handle, col_cnt, rec_query_col);
EXCEPTION
WHEN OTHERS
THEN
err_msg := SQLERRM;
write_to_file
( 'Error in xxtk_generate_csv_pkg using describe column'
|| err_msg,
xxtk_generate_csv_pkg.write_to_app_log
);
RAISE;
--raise_application_error;
END;

--prepare column header name in out_line
-- gets alias also if available in input query
FOR i IN 1 .. col_cnt
LOOP
-- write header column name inside double quote
out_line :=
out_line || l_separator || '"' || rec_query_col (i).col_name
|| '"';

--defines column varchar(4000) to store temp values for each
BEGIN
DBMS_SQL.define_column (cur_handle, i, l_columnvalue, 4000);
EXCEPTION
WHEN OTHERS
THEN
err_msg := SQLERRM;
write_to_file
( 'Error in xxtk_generate_csv_pkg using define_column. Might be because column value more than 4000'
|| err_msg,
xxtk_generate_csv_pkg.write_to_app_log
);
RAISE;
END;

l_separator := p_delimiter;
END LOOP;

--write header
write_to_file (out_line, p_file_handle);
out_line := NULL;

--execute the input query
BEGIN
l_status := DBMS_SQL.EXECUTE (cur_handle);
EXCEPTION
WHEN OTHERS
THEN
err_msg := SQLERRM;
write_to_file ( 'Error in xxtk_generate_csv_pkg using Execute'
|| err_msg,
xxtk_generate_csv_pkg.write_to_app_log
);
RAISE;
END;

--write data
LOOP
EXIT WHEN (DBMS_SQL.fetch_rows (cur_handle) <= 0);
l_separator := '';

FOR i IN 1 .. col_cnt
LOOP
BEGIN
DBMS_SQL.COLUMN_VALUE (cur_handle, i, l_columnvalue);
EXCEPTION
WHEN OTHERS
THEN
err_msg := SQLERRM;
write_to_file
( 'Error in xxtk_generate_csv_pkg using define_column. Might be because column value more than 4000'
|| err_msg,
xxtk_generate_csv_pkg.write_to_app_log
);
RAISE;
END;

-- out_line := out_line || l_separator || l_columnvalue;
out_line := out_line || l_separator || l_columnvalue;
l_separator := p_delimiter;
END LOOP;

write_to_file (out_line, p_file_handle);
out_line := NULL;
END LOOP;

DBMS_SQL.close_cursor (cur_handle);

IF p_file_handle = xxtk_generate_csv_pkg.write_to_dir
THEN
UTL_FILE.fclose (l_output);
END IF;
EXCEPTION
WHEN OTHERS
THEN
IF UTL_FILE.is_open (l_output)
THEN
UTL_FILE.fclose (l_output);
END IF;

RAISE;
END xxtk_generate_csv;

PROCEDURE write_to_file (p_text VARCHAR2, p_file_handle NUMBER)
AS
BEGIN
CASE
WHEN p_file_handle = write_to_app_out
THEN
fnd_file.put_line (fnd_file.output, p_text);
WHEN p_file_handle = write_to_app_log
THEN
fnd_file.put_line (fnd_file.LOG, p_text);
WHEN p_file_handle = write_to_db_out
THEN
DBMS_OUTPUT.put_line (p_text);
WHEN p_file_handle = write_to_dir
THEN
UTL_FILE.put_line (l_output, p_text);
END CASE;
END;
END xxtk_generate_csv_pkg;
/[/sourcecode]

No comments:

Post a Comment