Re: SQL*Loader - Input Parameter

"Malka Friedman" <malkaf_at_amdocs.com> wrote in message news:958637165.942559_at_soint7...
> Hello,
>
> I have to create a CTL that will load a file into a table, and into one of
> the fields it will insert constant according to an input parameter - I have
> to load several times and each time with diffeent value.
> Example:
> INSERT
> INTO TABLE CUST
> (
>
> RECORD_TYPE DEFAULT %1 // Thats what I am
> imagine...:-), How can it be done?
> EMP_NUM POSITION(5:14) INTEGER EXTERNAL
> EMP_NAME POSITION(15:23) CHAR
> NULLIF ( LEGACY_CORPORATE_CD = BLANKS ),
> EMP_LAST_NAME POSITION(24:31) CHAR
> NULLIF ( LEGACY_REPORTING_CD = BLANKS ),
> )
>
> Please help,
> It is very urgent,
> Malka

If I understand correctly what you're asking, it looks like your input parameter is position 1-4 in the data file and you want to dynamically do something based on that?

If so, you can use decode (and other SQL*Plus functions) in your .ctl file to do that:

(RECORD_TYPE POSITION(1:4) CHAR "decode(:RECORD_TYPE, '1111','AAAA','2222','BBBB','ZZZZ')",
EMP_NUM POSITION(1:5),
etc.)

This will call the decode function which will, in the above example, evaluate your RECORD_TYPE and if it's '1111', it'll insert 'AAAA', if it's '2222', it'll insert 'BBBB', otherwise 'ZZZZ'. (Basically decode is an if-then, if-then, else type of thing.) I just used that as an arbitrary example.

It's been a long time (my memory is fuzzy) and I might not have the syntax exactly right but I think the syntax was this:

COLUMN_NAME POSITION(<from>:<to>) <datatype> "<expression>" where the expression is included in double-quotes.

HTH, Matt Received on Thu May 18 2000 - 00:00:00 CEST