OraFAQ Forum: Server Utilities » getting error when loading the data. Any idea

Home » RDBMS Server » Server Utilities » getting error when loading the data. Any idea

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

getting error when loading the data. Any idea [message #158477]

Sat, 11 February 2006 16:14

pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville

Senior Member

I am trying to load a file which has plan text without complexity. But getting error as below.
Any idea, where I am going wrong.

Error Description:
========
SQL*Loader: Release 10.2.0.1.0 - Production on Sat Feb 11 15:43:43 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL*Loader-350: Syntax error at line 12.
Expecting valid column specification, "," or ")", found keyword terminated.
TERMINATED BY '|'
^

===========
Control file structure

LOAD DATA
INFILE 'D:\SampleTest.txt'
BADFILE 'D:\SampleTest.bad'
DISCARDFILE 'D:\SampleTest.dsc'

INTO TABLE "SAMPLE_TEST"

(FLD1 CHAR
TERMINATED BY '|'
,
FLD2 VARCHAR
TERMINATED BY '|'
,
FLD3 CHAR
)
====
Sample data file

"51|D|D0115"
"51|Y|D0999"
"51|X|D0908"
"51|Z|D0908"

Please help.

R

Report message to a moderator

Re: getting error when loading the data. Any idea [message #158481 is a reply to message #158477]

Sat, 11 February 2006 18:17

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

You will need to eliminate the beginning and ending double quotes one way or another, either by taking a substring or replacing or trimming or using fixed position instead of delimiters. The following would require that all of the data for fld3 are only 5 characters long.

LOAD DATA
INFILE 'D:\oracle\SampleTest.txt'
BADFILE 'D:\SampleTest.bad'
DISCARDFILE 'D:\SampleTest.dsc'
INTO TABLE "SAMPLE_TEST"
FIELDS TERMINATED BY '|'
(fld1 "SUBSTR (:fld1, 2)",
fld2,
fld3 "SUBSTR (:fld3, 1, 5)")

Report message to a moderator

Re: getting error when loading the data. Any idea [message #158506 is a reply to message #158477]

Sun, 12 February 2006 09:07

pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville

Senior Member

Thank you for the suggestion. It did work for me after I removed the double quotes in the beginging and at the end of the record. Also I noticed that you don't need to give the SUBSTR clause, in stead add "trailing nullcols".
Here is the modified one.

Thanks for all the help. Appreciate it.

LOAD DATA
INFILE 'D:\oracle\SampleTest.txt'
BADFILE 'D:\SampleTest.bad'
DISCARDFILE 'D:\SampleTest.dsc'
INTO TABLE "SAMPLE_TEST"
FIELDS TERMINATED BY '|' trailing nullcols
(fld1,
fld2,
fld3 )

Report message to a moderator

Previous Topic:	Passing Varaibles to the control file in SQL Loader
Next Topic:	sql loader issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 05 18:43:14 CDT 2024