Re: Help for sqlldr problem

You can't pass parameters to SQL*Loader, which may be avaluated in the column-descriptions.

One solution is a trigger on table TEMP, which implements the logic for you.

For your :var1: you may put it into a small "parameter"-table, which is evaluated by the trigger.

Hope that helps.

On Wed, 02 Jan 2002 17:49:23 +0800, Violin <violin.hsiao_at_mail.pouchen.com.tw> wrote:

>Hi ,
>
>I want to test how to load data like the situation.
>
>SQL> desc temp
> Name Null? Type
> ------------------------------- -------- ----
> COL1 NUMBER
> COL2 CHAR(4)
> COL3 CHAR(4)
>
>SQL>
>
>sqlldr control file (test.ctl) :
>LOAD DATA
>REPLACE
>INTO TABLE temp
>(
>col1 POSITION (001:003) CHAR,
>col2 POSITION (005:008) CHAR,
>col3 POSITION (010:013) CHAR
>)
>
>sqlldr command:
>sqlldr scott/tiger control=test.ctl data=test.dat log=test.log
>
>And test data (test.dat) :
>1 AA AA
>2 BB BB
>3 CC CC
>
>And of course it is loaded completely,
>SQL> select * from temp;
>
> COL1 COL2 COL3
>---------- ---- ----
> 1 AA AA
> 2 BB BB
> 3 CC CC
>
>But my situation is , IF in test.dat ,
>COL1 is empty , (position 001:003 are blanks)
>and I want to pass in an variable for deciding the COL1's value.
>
>For example , if :var1>=1 and :var1 <=5 , then COL1 = 1.
>if :var >= 6 and :var1 <=10 , then COL1 = 2 ,
>if :var1>=11 and :var1<=15 , then COL1 = 3
>So maybe COL1 is 1 or 2 or 3 , but it is decided according to :var1.
>
>But I don't know how to define and pass a variable in sqlldr?
>Or how can I do for this?
>
>Thanks in advance!
>
>Violin.
>violin.hsiao_at_mail.pouchen.com.tw

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Wed Jan 02 2002 - 03:52:38 CST