Re: Utility to dump an Oracle table

From: Bob Swisshelm <swisshelm_at_lilly.com>
Date: 10 Feb 95 06:25:21 EST
Message-ID: <1995Feb10.062522.3950_at_inet.d48.lilly.com>

In article <D3nIxw.C8D_at_da_vinci.ecte.uswc.uswest.com> Susan Wagner, smwagne_at_abu writes:

>DB2 has a utility - I think it's called DSNUTIL. It will download a DB2
>table in fixed column flat file format. Does anyone know if there is an 
>Oracle utility like this?   We do not want to use export because direct
>load is faster than import and we are dealing with very large volumes.
>

Here is a sqlplus script that I wrote that might work for you.

Note that SQLPLUS dumps data much slower that EXP. So, using EXP/IMP might be faster than using SQLPLUS / SQLLOAD with direct path load.

---

REM BUILD_CTL.SQL
REM
REM This script builds an SQLPLUS dump script and a SQLLOADER control file
REM for any table or view. This version was written for ORACLE7. REM

REM Author:	Bob Swisshelm
REM		Eli Lilly and Company
REM		Lilly Corporate Center
REM		Indianapolis, IN 48285
REM
REM Internet:	swisshelm_at_lilly.com

REM
REM Modification log
REM

REM 09-Jan-95	Swisshelm	SQL*Plus variables to calculate and set the
REM				line size, rather than a temporary file.
REM 21-Apr-94	Swisshelm	SQL*Plus puts a sign character in front of
REM				formatted number fields. Account for it.
REM 20-Apr-94	Swisshelm	Reset SQL*Plus parameters to default values
REM 				before exiting
REM 20-Apr-94	Swisshelm	Use TERMOUT appropriately to remove blank
REM				lines and error messages from the display
REM				Indent the column list in the SELECT statement
REM				of the dump file.
REM 19-Apr-94	Swisshelm	Use precision and scale in the column format
REM 				of any number fields that have precision and
REM				scale
REM 19-Nov-93	Swisshelm	add sequence numbers to each temp record
REM				to maintain their insert order
REM 18-Nov-93	Swisshelm	set the default spacing to 0, but allow
REM				it to be overridden
REM 18-Nov-93	Swisshelm	increase the size of the working strings
REM				set the linesize of the output file based on
REM 				the maximum line size of the data.
REM				break the date information onto two lines so
REM				that it won't take up so much space.
REM 08-Nov-93	Swisshelm	lowercase all file names for UNIX compatibility
REM 08-Nov-93	Swisshelm	Fixed bugs:
REM				Have dump script set NUMWIDTH and LONG
REM				Uppercase the table owner and name
REM 07-Nov-93	Swisshelm	Created

REM
REM Parameters:
REM view_prefix
REM Use DBA_TAB_COLUMNS or ALL_TAB_COLUMNS to determine the structure
REM of the table? Defaults to ALL.
REM t_owner
REM The owner of the table. Defaults the the current user. REM t_name
REM The name of the table.
REM control_file
REM The file name for the SQLLOADER control file that will be created.
REM dump_script_file
REM The file name for the SQLPLUS script file that will be created. This
REM script should be run to dump the table data to a flat file that can
REM be loaded using the control file. REM dump_data_file
REM The file name of the data file that will be created when the SQLPLUS
REM script file is run.
REM max_record_length
REM The maximum record length of the dump data file. If this length is

REM      less than the length of the logical record, SQLPLUS will wrap the
REM      data to multiple records. The SQLLOADER control file will
include the
REM      CONCATENATE clause to put the records back together. The default 
REM 	 value of 0 for max_record_length means that no wrapping will be

done.
REM date_format
REM The date format to use when dumping and loading data for DATE fields.
REM The default is 'DD-MON-YYYY HH24:MI:SS' to include time data. REM n_width
REM The field width for numeric data. Defaults to 10. REM long_width
REM The field width for long data. Defaults to 200. REM spaces
REM The number of spaces between data fields. Defaults to 0. REM
REM Notes:
REM
REM The dump script sets the heading for any NUMERIC columns to 'A'. This is
REM done because SQLPLUS will make a numeric column as wide as its heading,
REM even if the heading is longer than NUMWIDTH, and even if headings are off.
REM
REM The default date format of DD-MON-YY is changed to DD-MON-YYYY HH24:MI:SS
REM to also dump the time. The procedure makes an attempt to compute the REM maximum width that a date field could need, and causes all dates to be
REM displayed using that width. I make no guarantees if you use bizarre REM date formats.
REM
REM I have not tested this script against tables that have RAW or LONGRAW REM datatypes.
REM
REM If the record length is very long (3000 or so), you may not be able to REM open the dump file on VMS using TPU or EVE. I think that this is because
REM SQLPLUS spool files on VMS have the Stream_LF format. This procedure REM uses the parameter max_record_length to determine whether or not to wrap
REM the logical rows to multiple physical rows. The control file uses the REM CONCATENATE option to combine the physical records into one logical record.
REM
set termout off
set echo off
set verify off
set feedback off
set sqlcase mixed
REM
REM Define SQLPLUS columns so that we can use a SELECT statement to assign REM default values to parameters that were not entered. REM

col control_file 	new_value 	control_file 		noprint
col dump_script_file 	new_value 	dump_script_file 	noprint
col dump_data_file 	new_value 	dump_data_file 		noprint
col max_record_length 	new_value 	max_record_length 	noprint
col view_prefix 	new_value 	view_prefix 		noprint
col t_owner 		new_value 	t_owner 		noprint
col t_name 		new_value 	t_name 			noprint
col date_format 	new_value 	date_format 		noprint
col n_width 		new_value 	n_width 		noprint
col long_width 		new_value 	long_width 		noprint
col spaces 		new_value 	spaces 			noprint

select user t_owner from dual;

set termout on
accept view_prefix -

       prompt 'Enter the TAB_COLUMNS view to use (ALL): ' accept t_owner -

       prompt 'Enter the table or view owner (&t_owner): ' accept t_name -

       prompt 'Enter the table or view name: ' accept control_file -

       prompt 'Enter the control file name (&t_name..ctl): ' accept dump_script_file -

       prompt 'Enter the dump script file name (&t_name._dump.sql): ' accept dump_data_file -

       prompt 'Enter the dump data file name (&t_name..dat): ' accept max_record_length -

       prompt 'Enter the maximum record length for the dump file (0):' accept date_format -

       prompt 'Enter the date format (DD-MON-YY HH24:MI:SS): ' accept n_width -

       prompt 'Enter the numeric width (10): ' accept long_width -

       prompt 'Enter the long width (200): ' accept spaces -

       prompt 'Enter the number of spaces between fields (0): '

REM
REM Set defaults for the fields that were not entered. REM
set termout off

select	nvl('&view_prefix','ALL') 			view_prefix,
	nvl('&t_owner',user) 				t_owner,
	nvl('&control_file','&t_name..ctl') 		control_file,
	nvl('&dump_script_file','&t_name._dump.sql')	dump_script_file,
	nvl('&dump_data_file','&t_name..dat') 		dump_data_file,
	nvl('&max_record_length','0') 			max_record_length,
	nvl('&date_format','DD-MON-YY HH24:MI:SS') 	date_format,
	nvl('&n_width','10') 				n_width,
	nvl('&long_width','200') 			long_width,
	nvl('&spaces','0') 				spaces
	from dual;

REM
REM Set the file names to lower case
REM

select	lower('&control_file')		control_file,
	lower('&dump_script_file')	dump_script_file,
	lower('&dump_data_file') 	dump_data_file
	from dual;

REM
REM Table1 will have one row for each column in the table. It will the REM column definition for both the dump script and for control file. REM Turn off TERMOUT so that error do not show up on the screen REM
drop sequence ctl_temp_insert_seq;
create sequence ctl_temp_insert_seq;
REM
drop table ctl_temp_table1;
create table ctl_temp_table1

	( column_id 	number,
	  column_select varchar2(200),
	  column_def 	varchar2(200),
	  comma_field 	varchar2(1),
	  comma_field2 	varchar2(1));

REM
REM This table will hold the contents of the dump script file. REM
drop table ctl_temp_table2;
create table ctl_temp_table2

	( column_id	number,
	  column_name 	varchar2(200));

REM
REM This table will be used to build the SQLLOADER control file. REM
drop table ctl_temp_table3;
create table ctl_temp_table3

	( column_id	number,
	  column_name 	varchar2(200));

REM Here is the PL/SQL block that drives the whole process. declare

  date_width 		number;
  date_format 		varchar2(50) 	:= '&date_format';
  n_width 		number 		:= &n_width;
  long_width 		number 		:= &long_width;
  spaces		number 		:= &spaces;
  start_pos 		number 		:= 1;
  end_pos 		number;
  column_select 	varchar2 (200);
  column_def 		varchar2 (200);
  comma_field		varchar2 (1);
  datestuff 		varchar2 (100);
  datestuff2 		varchar2 (100);
  t_name 		varchar2(30) 	:= upper('&t_name');
  t_owner 		varchar2(30) 	:= upper('&t_owner'); 
  test_date 		date;
  temp_id		number;
  concat_num 		number;
  max_record_length 	number 		:= &max_record_length;
  number_format		varchar2(30);
  nines			varchar2(40)   
			 := '9999999999999999999999999999999999999999';
  tmp_precision		number;
  tmp_scale		number;
  cursor tmp_cursor (towner varchar2, tname varchar2) is
	 select column_id, column_name, data_type, data_length, data_precision,
		data_scale
	 from &view_prefix._tab_columns
	 where owner = towner and
	       table_name = tname
	 order by column_id;
begin
/*                                                                       

*/

/* we need to find out the maximum length of a date field using the supplied */
/* date format. Hopefully this date is a good test.

*/

/*

*/

  test_date := to_date ('28-SEP-3777 23:00','DD-MON-YYYY HH24:MI');   date_width := length(to_char(test_date,date_format));   for tmp_record in tmp_cursor(t_owner,t_name) LOOP

• By default, use the column name in the SELECT inside the dump file column_select := tmp_record.column_name;
  • Enclose the column name in double quotes in the control file column_def := '"' || tmp_record.column_name || '"'; comma_field := ','; datestuff := ' '; datestuff2 := ' '; if tmp_record.data_type = 'DATE' then end_pos := start_pos + date_width - 1;
  • specify the DATE format and the NULLIF clause for DATE fields
• DATE "date_format" NULLIF "column_name" = BLANKS comma_field := ' '; datestuff := ' DATE "' || date_format || '"'; datestuff2 := ' NULLIF "' || tmp_record.column_name || '" = BLANKS';
  • Have to use TO_CHAR in the dump file to get the proper date format
• substr(to_char(column_name,'date_format'),1,date_width) column_select := 'substr(to_char(' || tmp_record.column_name || ',''' || date_format || '''),1,' || date_width || ')'; elsif tmp_record.data_type = 'NUMBER' then number_format := null; tmp_precision := tmp_record.data_precision; tmp_scale := tmp_record.data_scale; if tmp_precision is null then end_pos := start_pos + n_width - 1; else if tmp_scale = 0 then number_format := ' format ' || substr(nines,1,tmp_precision);
  • add one to the length for the sign end_pos := start_pos + tmp_precision; else number_format := ' format ' || substr(nines,1,tmp_precision - tmp_scale) || '.' || substr(nines,1,tmp_scale) ;
  • add 2 to the length for the sign and the decimal end_pos := start_pos + tmp_precision + 1; end if; end if;
    • need to set column heading in the dump file insert into ctl_temp_table2 values ( ctl_temp_insert_seq.nextval, 'column "' || tmp_record.column_name || '" heading a' || number_format); elsif tmp_record.data_type = 'LONG' then end_pos := start_pos + long_width - 1; else end_pos := start_pos + tmp_record.data_length - 1; end if;
      • build the rest of the control file column definition column_def := column_def || ' POSITION (' || start_pos || ':' || end_pos || ')' || datestuff; insert into ctl_temp_table1 values ( ctl_temp_insert_seq.nextval, column_select, column_def, comma_field, ','); if tmp_record.data_type = 'DATE' then insert into ctl_temp_table1 values (ctl_temp_insert_seq.nextval, null, datestuff2, ',', ','); end if; start_pos := end_pos + spaces + 1; -- get ready for the next column end LOOP; /*
        */
        /* We're getting ready to build the dump file script, so the last column's */ /* comma needs to be changed to a space.
        */
        /*
        */
        update ctl_temp_table1 set comma_field = ' ' where column_id = (select max(column_id) from ctl_temp_table1); update ctl_temp_table1 set comma_field2 = ' ' where column_id = (select max(column_id) from ctl_temp_table1 where column_select is not null); /*
        */
        /* If they did not enter a maximum record length, set it to the end_pos.
        */
        /*
        */
        if max_record_length <= 0 then max_record_length := end_pos; end if; /*
        */
        /* Build the dump file script
        */
        /*
        */
        insert into ctl_temp_table2 values (ctl_temp_insert_seq.nextval,'set space &spaces'); insert into ctl_temp_table2 values (ctl_temp_insert_seq.nextval,'set linesize ' || max_record_length); insert into ctl_temp_table2 values (ctl_temp_insert_seq.nextval,'set pagesize 0'); insert into ctl_temp_table2 values (ctl_temp_insert_seq.nextval,'set feedback off'); insert into ctl_temp_table2 values (ctl_temp_insert_seq.nextval,'set termout off'); insert into ctl_temp_table2 values (ctl_temp_insert_seq.nextval,'set numwidth &n_width'); insert into ctl_temp_table2 values (ctl_temp_insert_seq.nextval,'set long &long_width'); insert into ctl_temp_table2 values (ctl_temp_insert_seq.nextval,'spool &dump_data_file'); insert into ctl_temp_table2 values (ctl_temp_insert_seq.nextval,'select'); select ctl_temp_insert_seq.nextval into temp_id from dual; insert into ctl_temp_table2 select temp_id + column_id/10000, ' ' || column_select || comma_field2 from ctl_temp_table1 where column_select is not null; insert into ctl_temp_table2 values (ctl_temp_insert_seq.nextval, 'from ' || t_owner || '.' || t_name || ';'); insert into ctl_temp_table2 values (ctl_temp_insert_seq.nextval,'spool off'); insert into ctl_temp_table2 values (ctl_temp_insert_seq.nextval,'set feedback on'); insert into ctl_temp_table2 values (ctl_temp_insert_seq.nextval,'set termout on'); /*
        */
        /* Build the first part of the control file.
        */
        /*
        */
        insert into ctl_temp_table3 values (ctl_temp_insert_seq.nextval,'load data'); insert into ctl_temp_table3 values (ctl_temp_insert_seq.nextval,'infile &dump_data_file'); insert into ctl_temp_table3 values (ctl_temp_insert_seq.nextval,'badfile '|| lower(t_name) || '.bad'); insert into ctl_temp_table3 values (ctl_temp_insert_seq.nextval,'discardfile '|| lower(t_name) || '.dis'); insert into ctl_temp_table3 values (ctl_temp_insert_seq.nextval,'insert'); insert into ctl_temp_table3 values (ctl_temp_insert_seq.nextval, 'concatenate '||ceil(end_pos/max_record_length)); insert into ctl_temp_table3 values (ctl_temp_insert_seq.nextval, 'into table ' || t_owner || '.' || t_name); insert into ctl_temp_table3 values (ctl_temp_insert_seq.nextval,'('); /*
        */
        /* Now the last column should be followed with a ')'
        */
        /*
        */
        update ctl_temp_table1 set comma_field = ')' where column_id = (select max(column_id) from ctl_temp_table1); end; . / set pagesize 0 set termout off set feedback off column line_size noprint new_value line_size select max(length(column_def)+1) line_size from ctl_temp_table1; set linesize &line_size spool &control_file select column_name from ctl_temp_table3 order by column_id; select column_def || comma_field from ctl_temp_table1 order by column_id; spool off select max(length(column_name)) line_size from ctl_temp_table2; set linesize &line_size spool &dump_script_file select column_name from ctl_temp_table2 order by column_id; spool off drop sequence ctl_temp_insert_seq; drop table ctl_temp_table1; drop table ctl_temp_table2; drop table ctl_temp_table3; set feedback on set termout on set linesize 80 set pagesize 15

        ---

Bob Swisshelm                | swisshelm_at_Lilly.com     | 317 276 5472
Eli Lilly and Company        | Lilly Corporate Center  | Indianapolis, IN

46285 Received on Fri Feb 10 1995 - 12:25:21 CET