SQL*Plus で文字列だけにダブルクォートをつけて csv 形式で出力する SQL

OTN Japan - Code Tips に make_csv.sql てのがころがってた。こりゃ便利じゃ。

以下、http://otn.oracle.co.jp/otn_pl/otn_tool/code_detail?n_code_id=764 からの抜粋。

set pagesize 0
set linesize 32767
set linesize &MAX_LINE_SIZE
set trimspool on
set feedback off
set verify off
ACCEPT DT_FMT CHAR PROMPT 'DATETIME FORMAT:'
ALTER SESSION  SET NLS_DATE_FORMAT = '&DT_FMT';

ACCEPT TABLE CHAR PROMPT 'TABLE NAME:'
ACCEPT FILE CHAR PROMPT 'OUTPUT FILE NAME:'

set termout off
spool tmp_csv.sql

SELECT 'SELECT ' FROM DUAL;
SELECT CHR(9)
    || DECODE( DATA_TYPE, 'VARCHAR2', '''"'' || REPLACE( '
                         , 'CHAR'    , '''"'' || REPLACE( '
                                     , NULL )
    || COLUMN_NAME
    || DECODE( DATA_TYPE, 'VARCHAR2', ', ''"'', ''""'' ) || ''",'' ||'
                        , 'CHAR'    , ', ''"'', ''""'' ) || ''",'' ||'
                                    , ' || '','' ||' )
 FROM USER_TAB_COLUMNS
 WHERE TABLE_NAME = UPPER( '&TABLE' )
   AND COLUMN_ID != ( SELECT MAX( COLUMN_ID ) FROM USER_TAB_COLUMNS
                        WHERE TABLE_NAME = UPPER( '&TABLE' ) )
 ORDER BY COLUMN_ID;
SELECT CHR(9)
    || DECODE( DATA_TYPE, 'VARCHAR2', '''"'' || REPLACE( '
                        , 'CHAR'    , '''"'' || REPLACE( '
                                    , NULL )
    || COLUMN_NAME
    || DECODE( DATA_TYPE, 'VARCHAR2', ', ''"'', ''""'' ) || ''"'''
                        , 'CHAR'    , ', ''"'', ''""'' ) || ''"'''
                                    , NULL )
 FROM USER_TAB_COLUMNS
 WHERE TABLE_NAME = UPPER( '&TABLE' )
   AND COLUMN_ID = ( SELECT MAX( COLUMN_ID ) FROM USER_TAB_COLUMNS
                        WHERE TABLE_NAME = UPPER( '&TABLE' ) );
SELECT ' FROM &TABLE;' FROM DUAL;

spool off

spool &FILE
@tmp_csv
spool off
set termout on

使ってみるとこんな感じ。

% sqlplus scott/tiger @make_csv.sql
Enter value for max_line_size:
DATETIME FORMAT:YYYY-MM-DD HH24:MI:SS
TABLE NAME:EMP
OUTPUT FILE NAME:emp.csv
SQL> exit
% cat emp.csv
7369,"SMITH","CLERK",7902,1980-12-17 00:00:00,800,,20
7499,"ALLEN","SALESMAN",7698,1981-02-20 00:00:00,1600,300,30
7521,"WARD","SALESMAN",7698,1981-02-22 00:00:00,1250,500,30
7566,"JONES","MANAGER",7839,1981-04-02 00:00:00,2975,,20
7654,"MARTIN","SALESMAN",7698,1981-09-28 00:00:00,1250,1400,30
7698,"BLAKE","MANAGER",7839,1981-05-01 00:00:00,2850,,30
7782,"CLARK","MANAGER",7839,1981-06-09 00:00:00,2450,,10
7788,"SCOTT","ANALYST",7566,1987-04-19 00:00:00,3000,,20
7839,"KING","PRESIDENT",,1981-11-17 00:00:00,5000,,10
7844,"TURNER","SALESMAN",7698,1981-09-08 00:00:00,1500,0,30
7876,"ADAMS","CLERK",7788,1987-05-23 00:00:00,1100,,20
7900,"JAMES","CLERK",7698,1981-12-03 00:00:00,950,,30
7902,"FORD","ANALYST",7566,1981-12-03 00:00:00,3000,,20
7934,"MILLER","CLERK",7782,1982-01-23 00:00:00,1300,,10

追記:
ちょっと変更してWHERE句を書けるようにした。

set pagesize 0
set linesize 32767
set linesize &MAX_LINE_SIZE
set trimspool on
set feedback off
set verify off
ACCEPT DT_FMT CHAR PROMPT 'DATETIME FORMAT:'
ALTER SESSION  SET NLS_DATE_FORMAT = '&DT_FMT';

ACCEPT TABLE CHAR PROMPT 'TABLE NAME:'
ACCEPT QUERY CHAR PROMPT 'QUERY(e.g. "ENAME = ''''SCOTT''''"):'
ACCEPT FILE CHAR PROMPT 'OUTPUT FILE NAME:'
DEFINE QUERY = &QUERY || 'AND 1=1'

set termout off
spool tmp_csv.sql

SELECT 'SELECT ' FROM DUAL;
SELECT CHR(9)
    || DECODE( DATA_TYPE, 'VARCHAR2', '''"'' || REPLACE( '
                         , 'CHAR'    , '''"'' || REPLACE( '
                                     , NULL )
    || COLUMN_NAME
    || DECODE( DATA_TYPE, 'VARCHAR2', ', ''"'', ''""'' ) || ''",'' ||'
                        , 'CHAR'    , ', ''"'', ''""'' ) || ''",'' ||'
                                    , ' || '','' ||' )
 FROM USER_TAB_COLUMNS
 WHERE TABLE_NAME = UPPER( '&TABLE' )
   AND COLUMN_ID != ( SELECT MAX( COLUMN_ID ) FROM USER_TAB_COLUMNS
                        WHERE TABLE_NAME = UPPER( '&TABLE' ) )
 ORDER BY COLUMN_ID;
SELECT CHR(9)
    || DECODE( DATA_TYPE, 'VARCHAR2', '''"'' || REPLACE( '
                        , 'CHAR'    , '''"'' || REPLACE( '
                                    , NULL )
    || COLUMN_NAME
    || DECODE( DATA_TYPE, 'VARCHAR2', ', ''"'', ''""'' ) || ''"'''
                        , 'CHAR'    , ', ''"'', ''""'' ) || ''"'''
                                    , NULL )
 FROM USER_TAB_COLUMNS
 WHERE TABLE_NAME = UPPER( '&TABLE' )
   AND COLUMN_ID = ( SELECT MAX( COLUMN_ID ) FROM USER_TAB_COLUMNS
                        WHERE TABLE_NAME = UPPER( '&TABLE' ) );
SELECT ' FROM &TABLE WHERE &QUERY;' FROM DUAL;

spool off

spool &FILE
@tmp_csv
spool off
set termout on

実行してみる。

% sqlplus scott/tiger @make_csv.sql
Enter value for max_line_size:
SP2-0267: linesize option 0 out of range (1 through 32767)
DATETIME FORMAT:YYYY-MM-DD HH24:MI:SS
TABLE NAME:EMP
QUERY(e.g. "ENAME = ''SCOTT''"): "ENAME = ''SCOTT''"
OUTPUT FILE NAME:emp.csv
SQL> exit
% cat emp.csv
7788,"SCOTT","ANALYST",7566,87,3000,,20