Hi
I wrote this script in about 15 mins and got it working once!!! Used it on
a table that was 56 columns wide and +/-1000000 rows deep. Don't know if
this is even the right version, but it was something along this lines.. It
worked all right. If you can figure out what it does and how it works you
can have it.
------------------------------------GrabBuilder-----------------------------
- GrabBuilder.sql Version 1.0
--
- This script will build a script that has to be run to generate a script
- which has then to be run in order to generate an SQL*Loader File.
--
- Bernard van Niekerk
- 01/08/2000
--
- Modification History
- Uhu ... none
--
set echo off
prompt -- Remember to execute the output from this script to capture the
data
prompt -- from the chosen database. The result from this result will then
generate
prompt -- the SQL*Loader file that can be used.
prompt -- Ensure that your Screen Buffer Width in the Enviroment setting is
BIG.(1000)
ACCEPT TABLENAME CHAR PROMPT 'What is the TABLENAME = '
ACCEPT WHERECLAUSE CHAR PROMPT 'What is the WHERE clause (enter->NULL) = '
set verify off
set termout off
set feedback off
set linesize 1000
set pagesize 0
set heading off
spool grab_&&TABLENAME..sql
prompt -- Grab_TABLENAME.sql
prompt -- This file is built from the SQL Script GrabBuilder.
prompt -- It will grabs all the stuff from a table as layed out in the
GrabBuilder script,
prompt -- to a file that can be used to import , with SQL*Loader,
prompt set linesize 1000
prompt set pagesize 0
prompt set heading off
prompt set verify off
prompt set echo off
prompt set feedback off
prompt set termout off
prompt spool data_&&TABLENAME..txt
prompt
prompt prompt LOAD DATA
prompt prompt INFILE *
prompt prompt APPEND INTO TABLE &&TABLENAME
prompt prompt FIELDS TERMINATED BY '|'
prompt prompt (
--All but last column
prompt select a.COLUMN_NAME||','
prompt from ALL_TAB_COLUMNS a
prompt where a.TABLE_NAME = UPPER('&&TABLENAME')
prompt and a.COLUMN_ID < (select MAX(COLUMN_ID) from ALL_TAB_COLUMNS where
TABLE_NAME = UPPER('&&TABLENAME'))
prompt and a.DATA_TYPE <> 'DATE'
prompt order by a.COLUMN_ID ;;
--Dates
prompt select a.COLUMN_NAME|| ' DATE(10) "dd-mm-yyyy",'
prompt from ALL_TAB_COLUMNS a
prompt where a.TABLE_NAME = UPPER('&&TABLENAME')
prompt and a.COLUMN_ID < (select MAX(COLUMN_ID) from ALL_TAB_COLUMNS where
TABLE_NAME = UPPER('&&TABLENAME'))
prompt and a.DATA_TYPE = 'DATE'
prompt order by a.COLUMN_ID ;;
--Last column
prompt select b.COLUMN_NAME
prompt from ALL_TAB_COLUMNS b
prompt where b.TABLE_NAME = UPPER('&&TABLENAME')
prompt and b.COLUMN_ID = (select MAX(COLUMN_ID) from ALL_TAB_COLUMNS where
TABLE_NAME = UPPER('&&TABLENAME'))
prompt and b.DATA_TYPE <> 'DATE';;
--Dates
prompt select b.COLUMN_NAME ||' DATE(10) "dd-mm-yyyy"'
prompt from ALL_TAB_COLUMNS b
prompt where b.TABLE_NAME = UPPER('&&TABLENAME')
prompt and b.COLUMN_ID = (select MAX(COLUMN_ID) from ALL_TAB_COLUMNS where
TABLE_NAME = UPPER('&&TABLENAME'))
prompt and b.DATA_TYPE = 'DATE';;
prompt prompt )
prompt prompt BEGINDATA
prompt select
-- All but last column
select a.COLUMN_NAME||'||''|''||'
from ALL_TAB_COLUMNS a
where a.TABLE_NAME = UPPER('&&TABLENAME')
and a.COLUMN_ID < (select MAX(COLUMN_ID) from ALL_TAB_COLUMNS where
TABLE_NAME = UPPER('&&TABLENAME'))
and a.DATA_TYPE <> 'DATE'
order by a.COLUMN_ID;
--Dates
select 'TO_CHAR('||a.COLUMN_NAME||',''dd-mm-yyyy'')'||'||''|''||'
from ALL_TAB_COLUMNS a
where a.TABLE_NAME = UPPER('&&TABLENAME')
and a.COLUMN_ID < (select MAX(COLUMN_ID) from ALL_TAB_COLUMNS where
TABLE_NAME = UPPER('&&TABLENAME'))
and a.DATA_TYPE = 'DATE'
order by a.COLUMN_ID;
-- Last column
select a.COLUMN_NAME||'||''|'''
from ALL_TAB_COLUMNS a
where a.TABLE_NAME = UPPER('&&TABLENAME')
and a.COLUMN_ID = (select MAX(COLUMN_ID) from ALL_TAB_COLUMNS where
TABLE_NAME = UPPER('&&TABLENAME'))
and a.DATA_TYPE <> 'DATE';
-- Dates
select 'TO_CHAR('||a.COLUMN_NAME||',''dd-mm-yyyy'')'||'||''|'''
from ALL_TAB_COLUMNS a
where a.TABLE_NAME = UPPER('&&TABLENAME')
and a.COLUMN_ID = (select MAX(COLUMN_ID) from ALL_TAB_COLUMNS where
TABLE_NAME = UPPER('&&TABLENAME'))
and a.DATA_TYPE = 'DATE';
prompt from &&TABLENAME
prompt &&WHERECLAUSE;;
prompt spool off
prompt set linesize 200
prompt set pagesize 0
prompt set heading on
prompt set verify on
prompt set echo on
prompt set feedback on
prompt set termout on
spool off
set linesize 200
set pagesize 0
set heading on
set verify on
set feedback on
set termout on
set echo on
prompt Grab_&&TABLENAME..sql has been build
prompt Execute it to build SQL*Loader
Received on Fri Aug 11 2000 - 00:00:00 CDT
