I am trying to load data from a simple text file (variable length data
with fields comma separated) into a table.

If the value (number) in one of the fields is more than 9999.9999, the
corresponding column should be loaded with 0 (zero) instead of the
original number in the field.

    my_column decimal external
        "decode(sign(9999.9999 - :my_column), -1, 0, :my_column)"

Note the colon prefexing :my_column. Sign(x) yields -1 if x < 0, 0 if x=0, and
+1 if x>0. So the decode(..) would return 0 if :my_column exceeds 9999.9999.

Or, better, to avoid rounding problems:

    my_column decimal external
        "decode(sign(10000 - :my_column), 1, :my_column, 0)"

If my_column is 10000 then Sign(0) yields 0, so the decode returns 0. If
my_column > 10000 then Sign(x) returns -1, which also tells decode to return
0.

Note that if your refer to :my_column in another part of your loader script,
you will be dealing with the value as found in your data file, not with the
value that will be loaded into the database. So, if the code above would load
my_column with zero, then still in another part you would get the original
value:

    my_total char
        ":my_column + :my_other_column"

Of course, if values exceeding 10000 should always be set to zero, then a
database trigger could do the job as well:

    create or replace trigger biu_my_table
        before insert or update on my_table
        for each row
    as
        if :new.my_column >= 10000 then
            :new.my_column := 0;
        end if;
    end biu_my_table;
    /
    show errrors

Arjan.

I'm trying to import data in a tablespace where data already exist. I got my
data to import in a .txt file with his description in a .ctl file.
when I use sqlloader, I get this message: for insert option, table must be
empty.
what can I do ?

Assuming you need to overwrite the data already in the table with the data
provided to you in the .txt file, you need either to truncate your table
manually using sqlplus or place the replace keyword immediately after the
load data line in your control file. Of course replace is only one of the
alternatives, append is also possible, insert being the default. Insert will
only work when there are no rows in the table. Otherwise you will get the
error you just got.
I can not know whether you need to append or replace data, so I can't
provide you with the final answer.

Sybrand Bakker, Oracle DBA

Make sure you have APPEND option and not REPLACE nor INSERT in your CTL file.