「SQL*Plus」の編集履歴（バックアップ）一覧に戻る

SQL*Plus - (2007/06/13 (水) 23:43:20) のソース

-SQL*Plus でテーブル名を指定してcsv出力(OTN Japan > Code Tips# 764)
 $ sqlplus scott/tiger
 SQL>@mk_csv.sql
 SQL>32767 
 SQL> DATETIME FORMAT:YYYY-MM-DD HH24:MI:SS
 SQL> TABLE NAME:user_tables
 SQL> OUTPUT FILE NAME:user_tables.csv

 ----- mk_csv.sql ここから
 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
 ----- mk_csv.sql ここまで