Oracle create cvs file for oracle table

coma separate data
Comma Separated data
Comma Separated Variable (CSV) files )


from web
http://www.oracle-base.com/dba/miscellaneous/cvs.sql





CREATE OR REPLACE PACKAGE csv AS
--
-- 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 :

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://www.oracle-base.com/dba/miscellaneous/cvs.sql
-- Author : DR Timothy S Hall
-- Description : Basic CSV API. For usage notes see:
--
-- 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



CREATE OR REPLACE DIRECTORY EXTRACT_DIR AS '/tmp';
GRANT READ, WRITE ON DIRECTORY EXTRACT_DIR TO SCOTT;
GRANT EXECUTE ON UTL_FILE TO SCOTT;


EXEC csv.generate('EXTRACT_DIR', 'emp.csv', p_query => 'SELECT * FROM ANUJ where rownum<20');





SQL> !cat /tmp/emp.csv
OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME
SYS,ICOL$,,20,2,TABLE,15-AUG-2009 00:16:51,15-AUG-2009 00:29:27,2009-08-15:00:16:51,VALID,N,N,N,1,
SYS,I_USER1,,46,46,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,CON$,,28,28,TABLE,15-AUG-2009 00:16:51,21-MAR-2011 15:03:17,2009-08-15:00:16:51,VALID,N,N,N,1,
SYS,UNDO$,,15,15,TABLE,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,1,
SYS,C_COBJ#,,29,29,CLUSTER,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,5,
SYS,I_OBJ#,,3,3,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,PROXY_ROLE_DATA$,,25,25,TABLE,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,1,
SYS,I_IND1,,41,41,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,I_CDEF2,,54,54,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,I_OBJ5,,40,40,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,I_PROXY_ROLE_DATA$_1,,26,26,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,FILE$,,17,17,TABLE,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,1,
SYS,UET$,,13,8,TABLE,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,1,
SYS,I_FILE#_BLOCK#,,9,9,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,I_FILE1,,43,43,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,I_CON1,,51,51,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,I_OBJ3,,38,38,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,I_TS#,,7,7,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,I_CDEF4,,56,56,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,


or


>>>>>cvs.sql


set pagesize 0
set verify off
set feedback off
set linesize 130
accept owner prompt 'Enter table owner -> '
accept tblname prompt 'Enter table name -> '
spool csv2.sql

select 'select ' from sys.dual;
select decode(column_id,1,column_name,
'||'',''||'||column_name)
from sys.dba_tab_columns
where table_name = upper('&&tblname')
and owner = upper('&&owner')
order by column_id;
select 'from &&owner..&&tblname;'
from sys.dual;
spool off
undefine owner
undefine tblname




SQL> @cvs.sql
Enter table owner -> SYS
Enter table name -> ANUJ
select
OWNER
||','||OBJECT_NAME
||','||SUBOBJECT_NAME
||','||OBJECT_ID
||','||DATA_OBJECT_ID
||','||OBJECT_TYPE
||','||CREATED
||','||LAST_DDL_TIME
||','||TIMESTAMP
||','||STATUS
||','||TEMPORARY
||','||GENERATED
||','||SECONDARY
||','||NAMESPACE
||','||EDITION_NAME
from SYS.ANUJ;




spool anujcvs.txt

---- run this sql then
select
OWNER
||','||OBJECT_NAME
||','||SUBOBJECT_NAME
||','||OBJECT_ID
||','||DATA_OBJECT_ID
||','||OBJECT_TYPE
||','||CREATED
||','||LAST_DDL_TIME
||','||TIMESTAMP
||','||STATUS
||','||TEMPORARY
||','||GENERATED
||','||SECONDARY
||','||NAMESPACE
||','||EDITION_NAME
from SYS.ANUJ;
spool off ;