Hopefully SQL script attached.
--
Todd Pepling, Oracle DBA
420 Vineyard Dr.
Gibsonia, PA 15044
email: [Email address protected]
home: 724-444-6403
cell: 412-779-1597
SET TERMOUT OFF
SET ECHO OFF
/*
*================================================================================
* Name: generate_csv_file.sql
* Written by Todd Pepling.
*
* Purpose: Creates a CSV (comma seperated value) flat file from a table.
* Character columns will be enclosed in double-quotes, but number
* columns won't be.
*
* Usage: Call from SQLPlus using a DBA account.
* Ex. To create a csv file of the SCOTT.EMP table,
* |-------------------------------------------------
* |SQL> generate_csv_file.sql SCOTT EMP
* |-------------------------------------------------
*
* Dependencies: 1) This may produce a file bigger than your operating system
* can support (i.e. 4G on Win2k FAT partition). You should
* estimate the table's size prior to running this if you have
* file size limit concerns.
* 2) Oracle 9i, but may work for other versions.
* 3) Designed to work with CHAR, CLOB, DATE, ,NUMBER, RAW, and VARCHAR2
* data types. Does not work with LONGs. If you want, you can
* create a new table without LONGs from a table with LONGs and
* run against that, *OR* in 9i you can convert the LONG to a LOB
* with an ALTER TABLE .. MODIFY statement.
*
* Revision Log: (most recent at top)
* 11-16-04 tcp: Got it working again. Made perttier, added usage notes.
* 01-02-03 tcp: Added SET TERMOUT OFF/ON.
* 07-08-02 tcp: Added set trimspool.
* 07-03-02 tcp: Created.
*==============================================================================*/
SET TERMOUT ON
/*
*------------------------------------------------------------
* Define v_SCHEMA and TABLE_NAM based on parameters passed in.
*----------------------------------------------------------*/
DEFINE v_SCHEMA=&1
DEFINE v_TABLE=&2
/*
*------------------------------------------------------------
* Save current SQLPlus settings in a file.
*----------------------------------------------------------*/
COL JUNK NEW_VALUE v_RESTORE_SQLPLUS_SETTINGS
SELECT
'tmprestore_sqlplus_settings.'||USER||'.'||TO_CHAR(SYSDATE,'YYYY-MM-DD_HH24MISS')||'.tmp' JUNK
FROM
DUAL
;
STORE SET &v_RESTORE_SQLPLUS_SETTINGS REPLACE
/*
*------------------------------------------------------------
* Column JUNK1 stores the name of the .csv file we want to create
* based on values passed in for v_SCHEMA and TABLE_NAM.
* This is the one want to keep.
*
* Column JUNK2 stores the name of the temporary file that
* runs the SQL need to popluate v_CSV_FILE.
* This one should be destroyed after this script runs.
*----------------------------------------------------------*/
COL JUNK1 NEW_VALUE v_CSV_FILE
COL JUNK2 NEW_VALUE v_TMP_FILE
SELECT
SYS_CONTEXT('USERENV','DB_NAME')||'.'||UPPER('&v_SCHEMA')||'.'||UPPER('&v_TABLE')||'.'
||TO_CHAR(SYSDATE,'YYYY-MM-DD_HH24MISS')||'.csv' JUNK1
,SYS_CONTEXT('USERENV','DB_NAME')||'.'||UPPER('&v_SCHEMA')||'.'||UPPER('&v_TABLE')||'.'
||TO_CHAR(SYSDATE,'YYYY-MM-DD_HH24MISS')||'.tmp' JUNK2
FROM
DUAL
;
/*
*------------------------------------------------------------
* Create the TMP_CSV_FILE table that'll house
* the SQL statements needed to generate the *.csv file.
*----------------------------------------------------------*/
CREATE TABLE TMP_CSV_FILE (
COLUMN_LIST CLOB
,SELECT_STATEMENT CLOB
);
/*
*------------------------------------------------------------
* Generate the SQL statements used to create the *.csv file,
* and insert them into the TMP_CSV_FILE table.
*----------------------------------------------------------*/
DECLARE
v_COLUMNSIZE1 NUMBER := 0;
v_COLUMNSIZE2 NUMBER := 0;
v_LINESIZE NUMBER := 0;
v_CAT VARCHAR2(2) := '||';
v_COLUMN_NAME DBA_TAB_COLUMNS.COLUMN_NAME%TYPE;
v_DATA_TYPE VARCHAR2(8);
v_COLUMN_LIST VARCHAR2(32000) := 'SELECT ''';
v_SELECT_STATEMENT VARCHAR2(32000) := 'SELECT ';
v_COUNT NUMBER;
CURSOR C1 IS
SELECT COLUMN_NAME
,SUBSTR(DATA_TYPE,1,8)
FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME=upper('&v_TABLE')
AND OWNER=upper('&v_SCHEMA')
ORDER BY COLUMN_ID
;
BEGIN
INSERT INTO TMP_CSV_FILE
VALUES (v_COLUMN_LIST,v_SELECT_STATEMENT)
;
COMMIT;
SELECT COUNT(COLUMN_NAME) INTO v_COUNT
FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME=UPPER('&v_TABLE')
AND OWNER=UPPER('&v_SCHEMA')
;
OPEN C1;
LOOP
FETCH C1 INTO v_COLUMN_NAME,v_DATA_TYPE;
EXIT WHEN C1%NOTFOUND;
SELECT DATA_LENGTH into v_COLUMNSIZE1
FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME=UPPER('&v_TABLE')
AND OWNER=UPPER('&v_SCHEMA')
AND COLUMN_NAME=v_COLUMN_NAME
;
SELECT LENGTH(COLUMN_NAME) into v_COLUMNSIZE2
FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME=UPPER('&v_TABLE')
AND OWNER=UPPER('&v_SCHEMA')
AND COLUMN_NAME=v_COLUMN_NAME
;
IF v_COLUMNSIZE1 > v_COLUMNSIZE2 THEN
v_LINESIZE := v_LINESIZE + v_COLUMNSIZE1 + 3;
ELSE
v_LINESIZE := v_LINESIZE + v_COLUMNSIZE2 + 3;
END IF;
IF C1%ROWCOUNT = 1 THEN
IF v_DATA_TYPE = 'CHAR' or v_DATA_TYPE = 'VARCHAR2' or v_DATA_TYPE = 'RAW' or v_DATA_TYPE = 'CLOB' THEN
v_SELECT_STATEMENT := v_SELECT_STATEMENT||CHR(39)||CHR(34)||CHR(39)||v_CAT||'rtrim('||v_COLUMN_NAME||')'||v_CAT||CHR(39)||CHR(34)||CHR(39);
ELSIF v_DATA_TYPE = 'DATE' THEN
v_SELECT_STATEMENT := v_SELECT_STATEMENT||CHR(39)||CHR(34)||CHR(39)||v_CAT||'to_char('||v_COLUMN_NAME||',''MM/DD/YYYY HH24:MI:SS'')'||v_CAT||CHR(39)||CHR(34)||CHR(39);
ELSIF v_DATA_TYPE = 'NUMBER' THEN
v_SELECT_STATEMENT := v_SELECT_STATEMENT||v_COLUMN_NAME;
ELSE
DBMS_OUTPUT.PUT_LINE('-- Can not create a spool file with "'||v_DATA_TYPE||'" data type.');
CLOSE C1;
EXIT;
END IF;
v_COLUMN_LIST := v_COLUMN_LIST||'"'||v_COLUMN_NAME||'"';
UPDATE TMP_CSV_FILE
SET COLUMN_LIST=v_COLUMN_LIST, SELECT_STATEMENT=v_SELECT_STATEMENT
;
COMMIT;
ELSE
v_COLUMN_LIST := v_COLUMN_LIST||',';
v_SELECT_STATEMENT := v_SELECT_STATEMENT||v_CAT||''','''||v_CAT;
IF v_DATA_TYPE = 'CHAR' or v_DATA_TYPE = 'VARCHAR2' or v_DATA_TYPE = 'RAW' or v_DATA_TYPE = 'CLOB' THEN
v_SELECT_STATEMENT := v_SELECT_STATEMENT||CHR(39)||CHR(34)||CHR(39)||v_CAT||'rtrim('||v_COLUMN_NAME||')'||v_CAT||CHR(39)||CHR(34)||CHR(39);
ELSIF v_DATA_TYPE ='DATE' THEN
v_SELECT_STATEMENT := v_SELECT_STATEMENT||CHR(39)||CHR(34)||CHR(39)||v_CAT||'to_char('||v_COLUMN_NAME||',''MM/DD/YYYY HH24:MI:SS'')'||v_CAT||CHR(39)||CHR(34)||CHR(39);
ELSIF v_DATA_TYPE = 'NUMBER' THEN
v_SELECT_STATEMENT := v_SELECT_STATEMENT||v_COLUMN_NAME;
ELSE
DBMS_OUTPUT.PUT_LINE('-- Can not create a spool file with "'||v_DATA_TYPE||'" data type.');
CLOSE C1;
EXIT;
END IF;
v_COLUMN_LIST := v_COLUMN_LIST||'"'||v_COLUMN_NAME||'"';
UPDATE TMP_CSV_FILE
SET COLUMN_LIST=v_COLUMN_LIST, SELECT_STATEMENT=v_SELECT_STATEMENT
;
COMMIT;
END IF;
END LOOP;
CLOSE C1;
v_COLUMN_LIST := v_COLUMN_LIST||''' FROM DUAL;';
v_SELECT_STATEMENT := v_SELECT_STATEMENT||' FROM &v_SCHEMA..&v_TABLE;';
UPDATE TMP_CSV_FILE
SET COLUMN_LIST=v_COLUMN_LIST, SELECT_STATEMENT=v_SELECT_STATEMENT
;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('-- ERROR!: '||substr(SQLERRM,1,244));
END;
/
/*
*------------------------------------------------------------
* Generate the temporary SQL script (TMPSQLSCRIPT) used to
* create the *.csv file.
*----------------------------------------------------------*/
SET ECHO OFF
SET FEED OFF
SET HEAD OFF
SET LINES 32000
SET LONG 32000
SET PAGES 0
SET TIME OFF
SET TIMING OFF
SET TRIMSPOOL ON
SET VERIFY OFF
COL JUNK3 FORMAT A2498
spool &v_TMP_FILE
SELECT 'spool &v_CSV_FILE' AS JUNK3 FROM DUAL;
SELECT COLUMN_LIST AS JUNK3 FROM TMP_CSV_FILE;
SELECT SELECT_STATEMENT AS JUNK3 FROM TMP_CSV_FILE;
SELECT 'spool off' AS JUNK3 FROM DUAL;
spool off
/*
*------------------------------------------------------------
* Now run TMPSQLSCRIPT to create the *.csv file.
*----------------------------------------------------------*/
@&v_TMP_FILE
/*
*------------------------------------------------------------
* Restore your original SQL*Plus settings.
*----------------------------------------------------------*/
@&v_RESTORE_SQLPLUS_SETTINGS
/*
*------------------------------------------------------------
* Cleanup.
*----------------------------------------------------------*/
DROP TABLE TMP_CSV_FILE;
host del /F /Q &v_TMP_FILE
host rm &v_TMP_FILE
host del /F /Q &v_RESTORE_SQLPLUS_SETTINGS
host rm &v_RESTORE_SQLPLUS_SETTINGS
UNDEFINE JUNK
UNDEFINE JUNK1
UNDEFINE JUNK2
UNDEFINE JUNK3
UNDEFINE v_CSV_FILE
UNDEFINE v_SCHEMA
UNDEFINE v_TABLE
UNDEFINE v_TMP_FILE
Oracle LazyDBA home page