Re: SQLLOAD - WHEN clause query.
Date: 1995/09/12
Message-ID: <434060$6f9_at_inet-nntp-gw-1.us.oracle.com>#1/1
steve_at_llap.demon.co.uk (Steven Pilgrim) wrote:
>Andy Hardy <aph_at_ahardy.demon.co.uk> wrote:
>>Hi,
>>I have a database with a couple of hundred identical tables (each
>>represents a class of object, such as BOLT, SCREW, etc).
>>I'd like to use the INTO table WHEN clause to specify which of the
>>tables the input data is to be placed - the information can be gathered
>>from a single column.
>>I *think* that the only way I can do this is with...
>> INTO TABLE CLIP WHEN (10:20) = 'CLIP'
>>
>>for each target table. I'm likely to make mistakes when creating a 250
>>table WHEN clause!
>>
>>I'd like to be able to do something like...
>> INTO TABLE (10:20)
>>Is there a simple way of doing this?
>>I'm new to Oracle, using 7.1.6 on HP UX - please take pity.
>>Andy
>>--
>>Andy Hardy (Internet: aph_at_ahardy.demon.co.uk, CIS: 100015,2603)
>>PGP key available on request
>>===============================================================
>>Everyone talks about apathy, but no one _does anything about it.
>Andy,
Another idea (on any platform) is to use the data dictionary to generate the sqlloader script. I am assuming all of the tables have the same structure and you want to load a fixed length file using positional notation. If so, then in sql*plus:
SQL> create table sqlloader_help
2 as
3 select column_name, column_id, 0 start_pos, 0 end_pos
4 from user_tab_columns where table_name= 'A';
'A' is the name of one of your tables. Now, update the start_pos and end_pos for each of the columns in the above table then run the following script:
set heading off
set feedback off
set linesize 80
column c1 format a75 word_wrap
column c2 format a1 noprint
column c3 format 9 noprint
spool tmp.ctl
prompt LOAD DATA
select
decode( a.column_id, 1, ' INTO TABLE "' || b.table_name || '"' ||
' WHEN (10:20) = '''||
rpad( b.table_name, 11 ) || '''' ||
chr(10) || ' (' ,
',' ) || '"'||a.column_name||'" position(' ||
start_pos || ':' || end_pos || ')' c1,
b.table_name c2,
a.column_id c3
from sqlloader_help a, user_tables b
where length( table_name ) <= 11
union
select ')' c1, table_name c2, 999 c3 from user_tables where length( table_name ) <= 11
order by 2, 3
/
spool off
============================= EOF =========================================
Will create a sqlloader script that loads your data (called tmp.ctl).
Hope this helps.
>Can think of a way around the SQL*loader syntax. Howvwer since you are
>on unix why not have a file containing the 250 names. Then run the
>through a shell script that will do a 'sed' on the sqlloader ie:
>replace a key word with the type.
>The shell script can then run the load 250 times
>example:
> for a in `cat data.dat`
> do
> sed 's/keyword/$a/g' file1.ctl > file2.ctl
> echo running "$a ...."
> sqlldr un/pw file2
> done
>Regards,
>Steve
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Received on Tue Sep 12 1995 - 00:00:00 CEST