Re: SQLLOAD - WHEN clause query.

From: Steven Pilgrim <steve_at_llap.demon.co.uk>
Date: 1995/09/11
Message-ID: <810850213.22968_at_llap.demon.co.uk>#1/1


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,

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 Received on Mon Sep 11 1995 - 00:00:00 CEST

Original text of this message