In article <8mvcq9$l4a$1_at_news7.svr.pol.co.uk>,
"BvN" <van_niekerk_bernard_at_hotmail.com> wrote:
> 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
>
>
Check out the utility developed by Tom Kyte at
http://osi.oracle/com/~tkyte
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Fri Aug 11 2000 - 00:00:00 CDT
