Re: SQL*Loader conditions

From: Ron Reidy <rereidy_at_indra.com>
Date: Thu, 18 Oct 2001 15:40:26 -0600
Message-ID: <3BCF4C4A.13AC8A73_at_indra.com>


Sybrand Bakker wrote:
>
> "Kevin Gao" <kgao_at_iname.com> wrote in message
> news:27f584d1.0110181150.2f21017e_at_posting.google.com...
> > I want to filter out some records during the loading process based on
> > conditions like SUBSTR(column1,12,2)='CA' or
> > SUBSTR(column3,40,2)='-L'. SQL*Loader allows you to add condition
> > using WHEN clause. But only simple conditions like column1 = 2 (or
> > >,<,!=) are allowed. Is there any workaround about this? I've tried
> > using CHECK constraints, it works but slow down the whole process a
> > lot ('cause I have over 3 million records need be loaded everyday and
> > of that 20% will be filtered out).
> >
> > Thanks.
> >
> > -Kevin
>
> Several workarounds exists, both of a fundamental nature
> 1 From your conditions it looks like you have concatenated several columns
> in one column. It is much easier to concatenate for reports and the like,
> than to 'parse' concatenated columns. I seriously think you should
> reconsider, because I think you will soon regret you set up the table in
> this fashion
> 2 You should never ever directly load in the target table. Always use a
> staging table. In this case Sql*loader will get all records, and your sql
> script to copy the data over will deal with the appropiate filtering.
>
> Hth,
>
> Sybrand Bakker
> Senior Oracle DBA
Or you can filter the data prior to getting to SQL*Loader (use\ing a named pipe on UNIX).

-- 
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.
Received on Thu Oct 18 2001 - 23:40:26 CEST

Original text of this message