SQL Loader leading zeros

[taz75]

I have a field that contains leading zeros that I would like to load into a numeric field.

When I try and load it I get the following error:

Record 15846: Rejected - Error on table <table_name>, column <column_name>.
ORA-01722: invalid number

How can I get SQL Loader to accept that this is a number and load it into my table?

I have tried to "to_number" this field, but then I am not allowed to use DIRECT_LOAD.

Any advice would be appreciated.
Thanks
Laura

 

[oradba101]

Hi, Laura

I do not believe you will be able to do this into Oracle as Oracle does not recognize numbers that begin with 0. It will only recognize these as character columns. Why would this column need to be a number?

Regards,


William Chadbourne
Oracle DBA

 

[taz75]

I am happy to lose the leading zeros when it is stored in my table.

As a work around I'll have to add a expression into the load and take off the DIRECT_LOAD option, but I was hoping that there was a better way.

Thanks for replying
Laura

 

[taz75]

I've found that this was actually a problem with the data, I stupidly assumed it would be in the format I had specified.

So in fact it does load data with leading zeros into numeric fields sucessfully.

Sorry,
Laura