既存表からSQL*Loaderのデータファイルを作成する

目的

検証や移行用に既存表に格納されたデータをSQL*Loaderのデータファイルとしてテキストファイルに出力する方法

SQLファイル

事前に下記の内容のSQLファイルを準備

accept table_name prompt ' Enter Table Name : '
set heading off
set verify off
set feed off
set linesize 120
set pause off
set echo off
set termout off
set pagesize 0
col dummy noprint
spool &table_name..sql
select 0 dummy, 'select' from dual
union
select column_id dummy,
'''"'''||'||'||column_name||'||'||'''",''||'
from all_tab_columns
where table_name = upper('&table_name' )
union
select 944 dummy, ''' ''' from dual
union
select 999 dummy, 'from &table_name;' from dual
/
spool off
spool &table_name
start &table_name
spool off
exit

実行例

対象表のオーナーでSQL*Plus接続し、SQLファイル実行

SQL> show user
USER is "SCOTT"

SQL> @data_unload.sql
 Enter Table Name : EMP
Disconnected from Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

2ファイル(制御ファイル/データファイル)が作成される

$ ls  
EMP.lst  EMP.sql
$ cat EMP.lst
"7369","SMITH","CLERK","7902","17-DEC-80","800","","20",
"7499","ALLEN","SALESMAN","7698","20-FEB-81","1600","300","30",
"7521","WARD","SALESMAN","7698","22-FEB-81","1250","500","30",
"7566","JONES","MANAGER","7839","02-APR-81","2975","","20"
"7654","MARTIN","SALESMAN","7698","28-SEP-81","1250","1400","30",
"7698","BLAKE","MANAGER","7839","01-MAY-81","2850","","30",
"7782","CLARK","MANAGER","7839","09-JUN-81","2450","","10",
"7788","SCOTT","ANALYST","7566","09-DEC-82","3000","","20",
"7839","KING","PRESIDENT","","17-NOV-81","5000","","10",
"7844","TURNER","SALESMAN","7698","08-SEP-81","1500","0","30",
"7876","ADAMS","CLERK","7788","12-JAN-83","1100","","20",
"7900","JAMES","CLERK","7698","03-DEC-81","950","","30",
"7902","FORD","ANALYST","7566","03-DEC-81","3000","","20",
"7934","MILLER","CLERK","7782","23-JAN-82","1300","","10",
$ cat EMP.sql
selec
'"'||EMPNO||'",'||
'"'||ENAME||'",'||
'"'||JOB||'",'||
'"'||MGR||'",'|
'"'||HIREDATE||'",'|
'"'||SAL||'",'||
'"'||COMM||'",'||
'"'||DEPTNO||'",'||
' '
from EMP;

SQLファイル実行時に入力したテーブル名で2ファイルが作成される