OraFAQ Forum: Server Utilities » sql loader problem: trailing nullcols

Home » RDBMS Server » Server Utilities » sql loader problem: trailing nullcols

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql loader problem: trailing nullcols [message #143620]

Fri, 21 October 2005 04:54

juicyapple
Messages: 92
Registered: October 2005

Member

I try to load data in wmship2.txt to table ship_to, after loading success, I found that the field which should be null is filled with the next data, the command trailing nullcols seem does not work...

for example, the column addr4 is null but it is replace by the next column(state) and the state column is replace by the next column(region).

1000 SG. CHUA "LOT 1279, KM 1" JALAN SG. CHUA "43000, KAJANG SELANGOR, MY" SEL KR

load data
infile 'wmship2.txt'
badfile 'wmship.bad'
truncate
into table ship_to
fields terminated by '\t' optionally enclosed by '"'
trailing nullcols

(
cmpy constant "AA",
cmpyName constant "BB",
code1 constant "CC",
code2 constant "DD",
ship_to,
name,
addr1,
addr2,
addr3,
addr4,
state,
region
)

Can anyone shows me what mistake I have made?

Thanks.

Report message to a moderator

Re: sql loader problem: trailing nullcols [message #143687 is a reply to message #143620]

Fri, 21 October 2005 08:24

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Trailing nulls is for the end of a line, end of a record, not for in the middle of a record. If you have a missing field in your data then you still need to have an extra delimiter to account for it, to let oracle put in the null for that column.

I usually prefer delimiters that you can see, such as comma. You would then have back to back commas if you had a missing value.

Report message to a moderator

Re: sql loader problem: trailing nullcols [message #143756 is a reply to message #143620]

Fri, 21 October 2005 20:33

juicyapple
Messages: 92
Registered: October 2005

Member

I have try to use comma as delimiter, but it return a lot of error message as below

Record 1: Rejected - Error on table SHIP_TO, column name.
no terminator found after TERMINATED and ENCLOSED field

Record 8: Rejected - Error on table SHIP_TO, column addr2.
no terminator found after TERMINATED and ENCLOSED field

...

but when I check the source file, the comma exist after each column...

line 1 in text file:
135144,ANSEL SDN BHD,"LOT 8A,",KULIM INDUSTRIAL ESTATE,"09000 KULIM, KEDAH, MY",04-4891033,KE,O

line 8:
128793,COMMECTS SDN BHD,"NO: 1,RU,JLN AU 5,",TAMAN LEMBAH KERAMAT,"54200, KUALA LUMPUR W., MY",03-41089618,KUL,KR

what does the message means actually??

Thanks.

Report message to a moderator

Previous Topic:	Creating a test database as an exact replica of my production database
Next Topic:	SQL*Loader-350: Syntax error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 09 00:59:50 CST 2024