Oracle CSV出力

仕事メモ

prompt カンマ区切りテキストにアンロードする
accept table_name prompt 'アンロードするテーブル名は？ :'
accept owner prompt 'アンロードするテーブルの所有者は？ :'

select sum(DATA_LENGTH) "類推されるデータ長"
from all_tab_columns
where table_name = upper('&table_name')
and owner = upper('&owner');

accept rec_len prompt 'アンロード時のデータ長は？（上記より多めに） :'

set heading off
set verify off
set feed off
set pause off
set echo off
set trims on
set termout off
set pagesize 0

define delim = ','

col dummy noprint
col a newline

spool &table_name.2csv.sql
---- UNLOAD START ----

prompt ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';;

prompt set verify off
prompt set feed off
prompt set heading off
prompt set pagesize 0

prompt prompt ********************************
prompt prompt &table_name UNLOAD

prompt select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') || ' START' from dual;;

prompt set pause off
prompt set echo off
prompt set trims on
prompt set termout off
prompt set linesize &rec_len

prompt spool &table_name..csv

prompt select

select decode(column_id,1,'','''&delim''||') ||
column_name || '||'
from all_tab_columns
where table_name = upper('&table_name')
and owner = upper('&owner')
order by column_id
/
prompt ''
prompt from &owner..&table_name;;

prompt spool off

prompt set termout on

prompt select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') || ' END' from dual;;

prompt prompt ********************************

prompt prompt rows of &owner..&table_name
prompt select count(*) from &owner..&table_name;;

prompt prompt lines of &table_name..csv
prompt ho wc -l &table_name..csv

prompt prompt ********************************
prompt set pagesize 14
prompt set heading on
prompt exit;;
---- UNLOAD END ----
spool off

set heading on
set termout on
set linesize 200
set pagesize 24
set feed on

prompt ***************************************
prompt File '&table_name.2csv.sql' Created.
prompt ***************************************
--@@&table_name.2csv.sql

undef table_name
undef owner
undef rec_len