Re: Extract table data to file in ascii format
From: <jl34778_at_corp02.d51.lilly.com>
Date: 28 Feb 94 12:09:29 EST
Message-ID: <1994Feb28.120929.1_at_corp02.d51.lilly.com>
Date: 28 Feb 94 12:09:29 EST
Message-ID: <1994Feb28.120929.1_at_corp02.d51.lilly.com>
In article <1994Feb25.153257.1_at_corp02.d51.lilly.com>, jl34778_at_corp02.d51.lilly.com writes:
> > If you're interested, send me an email note and I'll send it to you. I have a > version that works for ORACLE6 and another that works for ORACLE7.
Actually, rather than have everyone send me a request, here are the scripts.
--
Bob Swisshelm | swisshelm_at_Lilly.com | 317 276 5472
Eli Lilly and Company | Lilly Corporate Center | Indianapolis, IN 46285
------------------------------------------------------------------------------
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 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 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;
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
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
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;
cursor tmp_cursor (towner varchar2, tname varchar2) is
select column_id, column_name, data_type, data_length
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
end_pos := start_pos + n_width - 1;
-- 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');
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 = ' ', comma_field2 = ' '
where column_id = (select max(column_id) from ctl_temp_table1);
/* */
/* 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
spool set__line__size.tmp
select 'set linesize '||max(length(column_def)+1)
from ctl_temp_table1;
spool off
_at_set__line__size.tmp
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
spool set__line__size.tmp
select 'set linesize '||max(length(column_name))
from ctl_temp_table2;
spool off
_at_set__line__size.tmp
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
------------------------------------------------------------------------------
REM BUILD_CTL6.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 ORACLE6.
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 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
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 echo off
set verify off
set feedback off
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
select user t_owner from dual;
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): '
REM
REM Set defaults for the fields that were not entered.
REM
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
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
drop table ctl_temp_table1;
create table ctl_temp_table1
( column_id number,
column_select char(100),
column_def char(100),
comma_field char(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_name char(100));
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_name char(100));
REM Here is the PL/SQL block that drives the whole process.
declare
date_width number;
date_format char(50) := '&date_format';
n_width number := &n_width;
long_width number := &long_width;
start_pos number := 1;
end_pos number;
column_select char (100);
column_def char (100);
datestuff char (60);
t_name char(30) := upper('&t_name');
t_owner char(30) := upper('&t_owner');
test_date date;
concat_num number;
max_record_length number := &max_record_length;
cursor tmp_cursor (towner char, tname char) is
select column_id, column_name, data_type, data_length
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 || '"';
datestuff := ' ';
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
datestuff := ' DATE "' || date_format || '" 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,' || to_char(date_width) || ')';
elsif tmp_record.data_type = 'NUMBER' then
end_pos := start_pos + n_width - 1;
-- need to set column heading in the dump file
insert into ctl_temp_table2 values
('column "'||tmp_record.column_name||'" heading a');
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 (' || to_char(start_pos) || ':' ||
to_char(end_pos) || ')' || datestuff;
insert into ctl_temp_table1 values
(tmp_record.column_id, column_select, column_def, ',');
start_pos := end_pos + 2; -- 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);
/* */
/* 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
('set space 1');
insert into ctl_temp_table2 values
('set linesize ' || to_char(max_record_length));
insert into ctl_temp_table2 values
('set pagesize 0');
insert into ctl_temp_table2 values
('set feedback off');
insert into ctl_temp_table2 values
('set termout off');
insert into ctl_temp_table2 values
('set numwidth &n_width');
insert into ctl_temp_table2 values
('set long &long_width');
insert into ctl_temp_table2 values
('spool &dump_data_file');
insert into ctl_temp_table2 values
('select');
insert into ctl_temp_table2
select column_select || comma_field
from ctl_temp_table1;
insert into ctl_temp_table2 values
('from ' || t_owner || '.' || t_name || ';');
insert into ctl_temp_table2 values
('spool off');
insert into ctl_temp_table2 values
('set feedback on');
insert into ctl_temp_table2 values
('set termout on');
/* */
/* Build the first part of the control file. */
/* */
insert into ctl_temp_table3 values
('load data');
insert into ctl_temp_table3 values
('infile '|| lower(t_name) || '.dat');
insert into ctl_temp_table3 values
('badfile '|| lower(t_name) || '.bad');
insert into ctl_temp_table3 values
('discardfile '|| lower(t_name) || '.dis');
insert into ctl_temp_table3 values
('insert');
insert into ctl_temp_table3 values
('concatenate '|| to_char(ceil(end_pos/max_record_length)));
insert into ctl_temp_table3 values
('into table ' || t_owner || '.' || t_name);
insert into ctl_temp_table3 values
('(');
/* */
/* 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 linesize 132
set pagesize 0
set termout off
set feedback off
spool &control_file
select * from ctl_temp_table3;
select column_def || comma_field
from ctl_temp_table1;
spool off
spool &dump_script_file
select * from ctl_temp_table2;
spool off
drop table ctl_temp_table1;
drop table ctl_temp_table2;
drop table ctl_temp_table3;
set feedback on
set termout on
Received on Mon Feb 28 1994 - 18:09:29 CET