OraFAQ Forum: Server Utilities » SQL*Loader-350 Syntax error when parsing data type

Home » RDBMS Server » Server Utilities » SQL*Loader-350 Syntax error when parsing data type (sql loader, Solaris)

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

SQL*Loader-350 Syntax error when parsing data type [message #485278]

Tue, 07 December 2010 04:11

Janex
Messages: 2
Registered: December 2010

Junior Member

I ran the following control file in sql loader:

LOAD DATA
INFILE "gateway.csv"
truncate
INTO TABLE GATEWAY
Fields terminated by ","
Optionally enclosed by '"'
trailing nullcols
(
GWCID,
GATEWAYNAME,
GATEWAYADDRESS,
GATEWAYPROTOCOL INTERGER,
GATEWAYPROTVERS,
GATEWAYPROTPORT INTERGER,
GATEWAYHEARTBEAT INTERGER,
GATEWAYCONNSEL INTERGER,
GATEWAYADJACENTMIDDLEBOXID INTERGER,
PROFILE_NAME,
PROFILE_NUMBER INTERGER,
NODE_NAME,
NODE_NUMBER INTERGER,
PMG,
NE_ID INTERGER,
SUBNET_MANAGER_ID,
)

and I got the following error:
zcyds891:/opt/oracle> sqlldr gwcem/gwcem@pfs control=gateway.ctl log=/tmp/ldr.log bad=/tmp/bad.log

SQL*Loader: Release 9.2.0.8.0 - Production on Tue Dec 7 05:07:59 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL*Loader-350: Syntax error at line 12.
Expecting "," or ")", found "INTERGER".
GATEWAYPROTOCOL INTERGER,
^
and I couldn't find anything to help me to troubleshoot this error. Could someone please explain what is wrong and how I can fix it?

The current OS is Solaris, SunOS 5.10.
Thanks in advance.

Report message to a moderator

Re: SQL*Loader-350 Syntax error when parsing data type [message #485279 is a reply to message #485278]

Tue, 07 December 2010 04:16

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

There's no INTERGER datatype.

Report message to a moderator

Re: SQL*Loader-350 Syntax error when parsing data type [message #485325 is a reply to message #485279]

Tue, 07 December 2010 09:23

Janex
Messages: 2
Registered: December 2010

Junior Member

Thanks your reply very much.

After modifying the data type, another problem occurs.
The following is the output from log file.
SQL*Loader: Release 9.2.0.8.0 - Production on Tue Dec 7 23:04:56 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Control File:   /tmp/control.ctl
Data File:      /tmp/test.csv
  Bad File:     /tmp/bad.log
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table C20_EPIDGRPENTRY, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
GWCID                               FIRST  4000   ,  O(X00) CHARACTER
GATEWAYNAME                          NEXT  4000   ,  O(X00) CHARACTER
EPIDGRPNAME                          NEXT  4000   ,  O(X00) CHARACTER
EPIDGENERATIONDESC                   NEXT     4           INTEGER
NODENO                               NEXT     4           INTEGER
FIRSTTN                              NEXT     4           INTEGER
NOOFPORTS                            NEXT     4           INTEGER
V52INTERFACEID                       NEXT     4           INTEGER
V52LINKID                            NEXT     4           INTEGER
V5UALINKID                           NEXT     4           INTEGER
PRIINTERFACEID                       NEXT     4           INTEGER
EPIDGRPTYPE                          NEXT  4000   ,  O(X00) CHARACTER
NODE_NAME                            NEXT  4000   ,  O(X00) CHARACTER

Record 1: Rejected - Error on table C20_EPIDGRPENTRY, column GWCID.
second enclosure string not present
Record 2: Rejected - Error on table C20_EPIDGRPENTRY, column GWCID.
second enclosure string not present

Table C20_EPIDGRPENTRY:
  0 Rows successfully loaded.
  2 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Space allocated for bind array:                 240504 bytes(12 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             2
Total logical records rejected:         2
Total logical records discarded:        0

Run began on Tue Dec 07 23:04:56 2010
Run ended on Tue Dec 07 23:04:57 2010

Elapsed time was:     00:00:00.19
CPU time was:         00:00:00.02

The following is data in /tmp/test.csv.
GWC-172,MGTRK,E1/01/20,3,712,1,31,-1,-1,-1,-1,ISUP,null,
GWC-172,sipgw,TEST/000/0,6,713,1,1023,-1,-1,-1,-1,POTS,TEST 00 0,

I'm confused with the error "second enclosure string not present
". Is there anything wrong with data file?
Could someone please help to tell me how to fix it?

Thanks in advance.

[mod-edit: code tags added by bb; next time please add them yourself]

[Updated on: Tue, 07 December 2010 12:42] by Moderator

Report message to a moderator

Re: SQL*Loader-350 Syntax error when parsing data type [message #485359 is a reply to message #485325]

Tue, 07 December 2010 12:56

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

Senior Member

What did you use instead of " in optionally enclosed by '"'?

Whatever you used there, it is saying that it found a starting one, but not an ending one, like

,"data,

instead of

,"data",

You either need to use the right character or hexidecimal representation or remove the optionally enclosed by clause altogether.

Report message to a moderator

Previous Topic:	import failed
Next Topic:	Calling SQL*Loader utility with alias instead of password

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 22:31:36 CST 2025