Home » RDBMS Server » Server Utilities » SQL*Loader-350: Syntax error at line 9. Expecting "(" WHEN clause
SQL*Loader-350: Syntax error at line 9. Expecting "(" WHEN clause [message #668619] Wed, 07 March 2018 04:50 Go to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Hi All,

I have SQL loader file(CTL) which is used to insert data into Header and Line tables using WHEN clause.
LEVEL	Supplier Name	Supplier Site
Header	TEST CORPORATION	TEST3COM01remit
LEVEL	Item	Quantity
Line	3RDA003422	2
Line	3RDA003423	1
Line	3RDA003424	3


CTL file
Options(errors=10000,skip=1)
LOAD DATA
INFILE /u01/app/oracle/file1.csv    
REPLACE     
INTO TABLE XXC_HEADER    
fields terminated by ',' 
optionally enclosed by '"'       
trailing nullcols
WHEN LEVEL1 = 'Header'
(
LEVEL1 CHAR NULLIF LEVEL1 = BLANKS "RTRIM(:LEVEL1)",
Supplier_Name  CHAR NULLIF Supplier_Name = BLANKS "RTRIM(:Supplier_Name)",
Supplier_Site  CHAR NULLIF Supplier_Site = BLANKS "RTRIM(:Supplier_Site)"
)
INTO TABLE XXC_LINE    
fields terminated by ',' 
optionally enclosed by '"'       
trailing nullcols
WHEN LEVEL1 = 'Line'
(
LEVEL1 CHAR NULLIF LEVEL1 = BLANKS "RTRIM(:LEVEL1)",
item  CHAR NULLIF item = BLANKS "RTRIM(:item)",
Quantity          
)

SQL*Loader-350: Syntax error at line 9.
Expecting "(", found keyword when.
WHEN LEVEL1 = 'Header'
^
Re: SQL*Loader-350: Syntax error at line 9. Expecting "(" WHEN clause [message #668620 is a reply to message #668619] Wed, 07 March 2018 04:57 Go to previous messageGo to next message
Michel Cadot
Messages: 66515
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL*Loader question is NOT a SQL or PL/SQL question.

Re: SQL*Loader-350: Syntax error at line 9. Expecting "(" WHEN clause [message #668621 is a reply to message #668620] Wed, 07 March 2018 04:59 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
SQL*Loader question is NOT a SQL or PL/SQL question.
Yes you right, it was my mistake.
Can you please help me to move to SQL Loader Section?
Re: SQL*Loader-350: Syntax error at line 9. Expecting "(" WHEN clause [message #668622 is a reply to message #668619] Wed, 07 March 2018 05:48 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Could you please help me on this?
Re: SQL*Loader-350: Syntax error at line 9. Expecting "(" WHEN clause [message #668623 is a reply to message #668622] Wed, 07 March 2018 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 66515
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In the correct forum you have examples of the usage of this feature (multiple WHEN), so the advantage to post in the correct section.

Re: SQL*Loader-350: Syntax error at line 9. Expecting "(" WHEN clause [message #668626 is a reply to message #668623] Wed, 07 March 2018 06:18 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
I have tried but no luck... Sad
Re: SQL*Loader-350: Syntax error at line 9. Expecting "(" WHEN clause [message #668628 is a reply to message #668623] Wed, 07 March 2018 06:36 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
I have tried but no luck.. Sad
Re: SQL*Loader-350: Syntax error at line 9. Expecting "(" WHEN clause [message #668630 is a reply to message #668628] Wed, 07 March 2018 06:55 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10690
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
There are several issues with your control file.
To start with, you seem to use a tab delimited file not a comma separated.
Always do post your table DDL.
oracle@kapi#cat somectl.ctl
Options(errors=10000,skip=1)
LOAD DATA
INFILE 'file1.csv'
REPLACE
INTO TABLE HEADER
WHEN (LEVEL2 = 'Header')
fields terminated by '\t'
optionally enclosed by '"'
trailing nullcols
(
LEVEL2 CHAR NULLIF LEVEL2 = BLANKS "RTRIM(:LEVEL2)",
Supplier_Name  CHAR NULLIF Supplier_Name = BLANKS "RTRIM(:Supplier_Name)",
Supplier_Site  CHAR NULLIF Supplier_Site = BLANKS "RTRIM(:Supplier_Site)"
)
INTO TABLE LINE
WHEN (LEVEL2 = 'Line')
fields terminated by '\t'
optionally enclosed by '"'
trailing nullcols
(
LEVEL2 position(1) CHAR NULLIF LEVEL2 = BLANKS "RTRIM(:LEVEL2)",
item  CHAR NULLIF item = BLANKS "RTRIM(:item)",
Quantity CHAR NULLIF Quantity = BLANKS "RTRIM(:Quantity)"
)
oracle@kapi#
oracle@kapi#
oracle@kapi#sqlldr dbadmin/xxxx control=somectl.ctl

SQL*Loader: Release 12.2.0.1.0 - Production on Wed Mar 7 07:49:21 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 5

Table HEADER:
  1 Row successfully loaded.

Table LINE:
  3 Rows successfully loaded.

Check the log file:
  somectl.log
for more information about the load.
Re: SQL*Loader-350: Syntax error at line 9. Expecting "(" WHEN clause [message #668632 is a reply to message #668626] Wed, 07 March 2018 07:17 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2865
Registered: January 2010
Location: Connecticut, USA
Senior Member
I:\>type c:\temp\file1.ctl
Options(errors=10000,skip=1)
LOAD DATA
INFILE "c:\temp\file1.csv"
REPLACE
INTO TABLE XXC_HEADER
WHEN LEVEL1 = 'Header'
fields terminated by '\t'
optionally enclosed by '"'
trailing nullcols
(
LEVEL1 CHAR NULLIF LEVEL1 = BLANKS "RTRIM(:LEVEL1)",
Supplier_Name  CHAR NULLIF Supplier_Name = BLANKS "RTRIM(:Supplier_Name)",
Supplier_Site  CHAR NULLIF Supplier_Site = BLANKS "RTRIM(:Supplier_Site)"
)
INTO TABLE XXC_LINE
WHEN LEVEL1 = 'Line'
fields terminated by '\t'
optionally enclosed by '"'
trailing nullcols
(
LEVEL1 POSITION(1) CHAR NULLIF LEVEL1 = BLANKS "RTRIM(:LEVEL1)",
item  CHAR NULLIF item = BLANKS "RTRIM(:item)",
Quantity
)

I:\>sqlldr control=c:\temp\file1.ctl log=c:\temp\file1.log
Username:scott@pdb1sol122
Password:

SQL*Loader: Release 12.2.0.1.0 - Production on Wed Mar 7 08:09:29 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 4
Commit point reached - logical record count 5

Table XXC_HEADER:
  1 Row successfully loaded.

Table XXC_LINE:
  3 Rows successfully loaded.

Check the log file:
  c:\temp\file1.log
for more information about the load.

I:\>sqlplus scott@pdb1sol122

SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 7 08:09:50 2018

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

Enter password:
Last Successful login time: Wed Mar 07 2018 08:09:35 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> set linesize 132
SQL> select * from xxc_header;

LEVEL1                         SUPPLIER_NAME                  SUPPLIER_SITE
------------------------------ ------------------------------ ------------------------------
Header                         TEST CORPORATION               TEST3COM01remit

SQL> select * from xxc_line;

LEVEL1                         ITEM                             QUANTITY
------------------------------ ------------------------------ ----------
Line                           3RDA003422                              2
Line                           3RDA003423                              1
Line                           3RDA003424                              3

SQL>

SY.
Re: SQL*Loader-350: Syntax error at line 9. Expecting "(" WHEN clause [message #668635 is a reply to message #668632] Wed, 07 March 2018 07:57 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2865
Registered: January 2010
Location: Connecticut, USA
Senior Member
Also, NULLIF LEVEL1 = BLANKS doesn't make much sense since record with anything other than HEADER/LINE will be discarded.

SY.
Re: SQL*Loader-350: Syntax error at line 9. Expecting "(" WHEN clause [message #668636 is a reply to message #668630] Wed, 07 March 2018 08:04 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Thank you very much!

CREATE TABLE HEADER
(
LEVEL1 VARCHAR2(50)
,Supplier_Name VARCHAR2(100)
,Supplier_Site VARCHAR2(100)
);

CREATE TABLE LINE
(
LEVEL1 VARCHAR2(50)
,Item VARCHAR2(100)
,QUANTITY NUMBER
);

CTL file
Options(errors=10000,skip=1)
LOAD DATA
INFILE '/xxc/app/fs2/EBSapps/appl/xbol/12.0.0/sql/Test.csv'
REPLACE
INTO TABLE XXC_HEADER
WHEN (LEVEL1 = 'Header')
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(
LEVEL1 CHAR NULLIF LEVEL1 = BLANKS "RTRIM(:LEVEL1)",
Supplier_Name  CHAR NULLIF Supplier_Name = BLANKS "RTRIM(:Supplier_Name)",
Supplier_Site  CHAR NULLIF Supplier_Site = BLANKS "RTRIM(:Supplier_Site)"
)
INTO TABLE XXC_LINE
WHEN (LEVEL1 = 'Line')
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(
LEVEL1 position(1) CHAR NULLIF LEVEL1 = BLANKS "RTRIM(:LEVEL1)",
Item  CHAR NULLIF Item = BLANKS "RTRIM(:Item)",
QUANTITY CHAR NULLIF QUANTITY = BLANKS "RTRIM(:QUANTITY)"
)

Error Log


SQL*Loader: Release 10.1.0.5.0 - Production on Wed Mar 7 06:19:49 2018

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

Control File:   Test.ctl
Data File:      /xxc/app/fs2/EBSapps/appl/xbol/12.0.0/sql/Test.csv
  Bad File:     Test.bad
  Discard File:  none specified
 
 (Allow all discards)

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

Table XXC_HEADER, loaded when LEVEL1 = 0X486561646572(character 'Header')
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
LEVEL1                              FIRST     *   ,  O(") CHARACTER            
    NULL if LEVEL1 = BLANKS
    SQL string for column : "RTRIM(:LEVEL1)"
SUPPLIER_NAME                        NEXT     *   ,  O(") CHARACTER            
    NULL if SUPPLIER_NAME = BLANKS
    SQL string for column : "RTRIM(:Supplier_Name)"
SUPPLIER_SITE                        NEXT     *   ,  O(") CHARACTER            
    NULL if SUPPLIER_SITE = BLANKS
    SQL string for column : "RTRIM(:Supplier_Site)"

Table XXC_LINE, loaded when LEVEL1 = 0X4c696e65(character 'Line')
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
LEVEL1                                  1     *   ,  O(") CHARACTER            
    NULL if LEVEL1 = BLANKS
    SQL string for column : "RTRIM(:LEVEL1)"
ITEM                                 NEXT     *   ,  O(") CHARACTER            
    NULL if ITEM = BLANKS
    SQL string for column : "RTRIM(:Item)"
QUANTITY                             NEXT     *   ,  O(") CHARACTER            

Record 2: Discarded - failed all WHEN clauses.
Record 3: Rejected - Error on table XXC_LINE, column QUANTITY.
ORA-01722: invalid number

Record 4: Rejected - Error on table XXC_LINE, column QUANTITY.
ORA-01722: invalid number

Record 5: Rejected - Error on table XXC_LINE, column QUANTITY.
ORA-01722: invalid number


Table XXC_HEADER:
  1 Row successfully loaded.
  0 Rows not loaded due to data errors.
  4 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


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


Space allocated for bind array:                  99072 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          1
Total logical records read:             5
Total logical records rejected:         3
Total logical records discarded:        1

Run began on Wed Mar 07 06:19:49 2018
Run ended on Wed Mar 07 06:19:49 2018

Elapsed time was:     00:00:00.44
CPU time was:         00:00:00.02

Data is loaded in Header table , but not in LIne table
Can you please help me on this?
  • Attachment: Test.csv
    (Size: 0.15KB, Downloaded 516 times)

[Updated on: Wed, 07 March 2018 08:24]

Report message to a moderator

Re: SQL*Loader-350: Syntax error at line 9. Expecting "(" WHEN clause [message #668638 is a reply to message #668636] Wed, 07 March 2018 08:20 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10690
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Can you please help me on this?
How exactly?
And you are now using a CSV. Thats it!.
Do you understand the difference between
fields terminated by '\t'
and

fields terminated by ',' 





Re: SQL*Loader-350: Syntax error at line 9. Expecting "(" WHEN clause [message #668639 is a reply to message #668638] Wed, 07 March 2018 08:26 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Yes you are correct and updated with ',' but still getting below error.

Header data is loaded and Line is not loaded.

SQL*Loader: Release 10.1.0.5.0 - Production on Wed Mar 7 06:19:49 2018

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

Control File:   Test.ctl
Data File:     /xxc/app/fs2/EBSapps/appl/xbol/12.0.0/sql/Test.csv
  Bad File:     Test.bad
  Discard File:  none specified
 
 (Allow all discards)

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

Table XXC_HEADER, loaded when LEVEL1 = 0X486561646572(character 'Header')
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
LEVEL1                              FIRST     *   ,  O(") CHARACTER            
    NULL if LEVEL1 = BLANKS
    SQL string for column : "RTRIM(:LEVEL1)"
SUPPLIER_NAME                        NEXT     *   ,  O(") CHARACTER            
    NULL if SUPPLIER_NAME = BLANKS
    SQL string for column : "RTRIM(:Supplier_Name)"
SUPPLIER_SITE                        NEXT     *   ,  O(") CHARACTER            
    NULL if SUPPLIER_SITE = BLANKS
    SQL string for column : "RTRIM(:Supplier_Site)"

Table XXC_LINE, loaded when LEVEL1 = 0X4c696e65(character 'Line')
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
LEVEL1                                  1     *   ,  O(") CHARACTER            
    NULL if LEVEL1 = BLANKS
    SQL string for column : "RTRIM(:LEVEL1)"
ITEM                                 NEXT     *   ,  O(") CHARACTER            
    NULL if ITEM = BLANKS
    SQL string for column : "RTRIM(:Item)"
QUANTITY                             NEXT     *   ,  O(") CHARACTER            

Record 2: Discarded - failed all WHEN clauses.
Record 3: Rejected - Error on table XXC_LINE, column QUANTITY.
ORA-01722: invalid number

Record 4: Rejected - Error on table XXC_LINE, column QUANTITY.
ORA-01722: invalid number

Record 5: Rejected - Error on table XXC_LINE, column QUANTITY.
ORA-01722: invalid number


Table XXC_HEADER:
  1 Row successfully loaded.
  0 Rows not loaded due to data errors.
  4 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


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


Space allocated for bind array:                  99072 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          1
Total logical records read:             5
Total logical records rejected:         3
Total logical records discarded:        1

Run began on Wed Mar 07 06:19:49 2018
Run ended on Wed Mar 07 06:19:49 2018

Elapsed time was:     00:00:00.44
CPU time was:         00:00:00.02

Re: SQL*Loader-350: Syntax error at line 9. Expecting "(" WHEN clause [message #668640 is a reply to message #668639] Wed, 07 March 2018 08:34 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10690
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Options(errors=10000,skip=1)
LOAD DATA
INFILE 'Test.csv'
REPLACE
INTO TABLE HEADER
WHEN (LEVEL1 = 'Header')
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(
LEVEL1 CHAR NULLIF LEVEL1 = BLANKS "RTRIM(:LEVEL1)",
Supplier_Name  CHAR NULLIF Supplier_Name = BLANKS "RTRIM(:Supplier_Name)",
Supplier_Site  CHAR NULLIF Supplier_Site = BLANKS "RTRIM(:Supplier_Site)"
)
INTO TABLE LINE
WHEN (LEVEL1 = 'Line')
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(
LEVEL1 position(1) CHAR NULLIF LEVEL1 = BLANKS "RTRIM(:LEVEL1)",
Item  CHAR NULLIF Item = BLANKS "RTRIM(:Item)",
QUANTITY INTEGER NULLIF QUANTITY = BLANKS "RTRIM(:QUANTITY)"
)
Re: SQL*Loader-350: Syntax error at line 9. Expecting "(" WHEN clause [message #668641 is a reply to message #668640] Wed, 07 March 2018 08:40 Go to previous message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Thanks much Mahesh now data is loading... Smile
Previous Topic: Exporting from Physical standby
Next Topic: Issue with SQL LOADER Control file [merged two by jd]
Goto Forum:
  


Current Time: Tue Aug 20 05:02:54 CDT 2019