dump.sql

View All Scripts Login to Run Script

Script Name dump.sql
Description This script dumps a table to CSV and creates SQL Loader control and parameter files. Be careful with LOBs.
Area SQL General / Data Manipulation
Contributor Jared Still
Created Thursday October 15, 2015

Statement 1

-- dump.sql - jared still
-- jkstill@gmail.com
--
-- dump a table to a comma delimited ascii file
-- only drawback is line length is likely to be padded with
-- quite a few spaces if the 'set trimspool on' option is
-- not in your version of SQLPLUS
--
-- also builds a control file and a parameter file for SQL*LOADER
--
-- 08/28/2000 - use defined variables for quotes and commas
--
-- This is the script found in Oracle Note 1050919.6
-- I wrote it in 1994, and it has been enhanced a few times since then
--


set trimspool on

Statement 2
```
set serverout on
```
Statement 3
```
clear buffer
```
Statement 4
```
undef dumpfile
```
Statement 5
```
undef dumptable
```
Statement 6
```
undef dumpowner
```
Statement 7
```
var maxcol number
```
Statement 8
```
var linelen number
```
Statement 9
```
var dumpfile char(40)
```
Statement 10
```
col column_id noprint
```

Statement 11

set pages0 feed off termout on echo off verify off

Statement 12
```
set term on
```
Statement 13
```
prompt Owner of table to dump:
```
Statement 14
```
set term off feed off
```

Statement 15

col cdumpowner noprint new_value dumpowner

Statement 16
```
select '&1' cdumpowner from dual
```
Statement 17
```
set term on 
```
Statement 18
```
prompt Table to dump:
```
Statement 19
```
set term off feed off
```

Statement 20

col cdumptable noprint new_value dumptable

Statement 21
```
select '&2' cdumptable from dual
```
Statement 22
```
set term on feed off
```

Statement 23

begin

	select max(column_id) into :maxcol
	from all_tab_columns
	where table_name = rtrim(upper('&dumptable'))
	and owner = rtrim(upper('&dumpowner'));

	select sum(data_length) + ( :maxcol * 3 ) into :linelen
	from all_tab_columns
	where table_name = rtrim(upper('&dumptable'))
	and owner = rtrim(upper('&dumpowner'));

end;

Statement 24
```
print linelen
```
Statement 25
```
print maxcol
```
Statement 26
```
spool ./_dump.sql
```
Statement 27
```
select 'set trimspool on' from dual
```

Statement 28

select 'set termout off pages 0 heading off echo off' from dual

Statement 29

select 'set line ' || :linelen from dual

Statement 30

select 'spool ' || lower('&dumptable') || '.txt' from dual

Statement 31
```
select 'select' || chr(10) from dual
```
Statement 32
```
define squote=chr(39)
```
Statement 33
```
define dquote=chr(34)
```
Statement 34
```
define comma=chr(44)
```

Statement 35

select '   ' || &&squote || &&dquote  || &&squote || ' || ' ||  
	'replace(' || column_name || &&comma || &&squote ||  &&dquote || &&squote || ') '
	|| ' ||' || &&squote || '",' || &&squote || ' || ', 
	column_id
from all_tab_columns
where table_name = upper('&dumptable')
and owner = upper('&dumpowner')
and column_id < :maxcol
union
select '   ' || &&squote || &&dquote  || &&squote || ' || ' ||  
	'replace(' || column_name  || &&comma || &&squote ||  &&dquote || &&squote || ') '
	|| ' ||' || &&squote || &&dquote || &&squote ,
	column_id
from all_tab_columns
where table_name = upper('&dumptable')
and owner = upper('&dumpowner')
and column_id = :maxcol
order by 2

Statement 36

select 'from &dumpowner..&dumptable' from dual

Statement 37
```
select '/' from dual
```
Statement 38
```
select 'spool off' from dual
```
Statement 39
```
spool off
```
Statement 40
```
@./_dump
```
Statement 41
```
set line 79
```
Statement 42
```
spool _dtmp.sql
```

Statement 43

select 'spool ' || lower('&dumptable') || '.par' from dual

Statement 44
```
spool off
```
Statement 45
```
@./_dtmp
```

Statement 46

select 'userid = ' || user || chr(10) ||
	'control = ' || lower('&dumptable') || '.ctl' || chr(10) ||
	'log = ' || lower('&dumptable') || '.log' || chr(10) ||
	'bad = ' || lower('&dumptable')|| '.bad' || chr(10)
from dual

Statement 47
```
spool _dtmp.sql
```

Statement 48

select 'spool ' || lower('&dumptable') || '.ctl' from dual

Statement 49
```
spool off
```
Statement 50
```
@./_dtmp
```

Statement 51

select 'load data' || chr(10) ||
	'infile ' || &&squote || lower('&dumptable') || '.txt' || &&squote || chr(10) ||
	'into table &dumptable' || chr(10) ||
	'fields terminated by ' || &&squote || &&comma || &&squote || 
	' optionally enclosed by ' || &&squote || &&dquote || &&squote  || chr(10)
from dual

Statement 52
```
select '(' from dual
```

Statement 53

select '   ' || column_name || &&comma , 
	column_id
from all_tab_columns
where table_name = upper('&dumptable')
and owner = upper('&dumpowner')
and column_id < :maxcol
union
select '   ' || column_name, column_id
from all_tab_columns
where table_name = upper('&dumptable')
and owner = upper('&dumpowner')
and column_id = :maxcol
order by 2

Statement 54
```
select ')' from dual
```
Statement 55
```
spool off
```
Statement 56
```
undef 1 2
```
Statement 57
```
exit
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database