| SQL*Loader-350: Syntax error at line 9. Expecting "(" WHEN clause [message #668619] |
Wed, 07 March 2018 04:50  |
 |
mist598
Messages: 1195
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 #668632 is a reply to message #668626] |
Wed, 07 March 2018 07:17  |
Solomon Yakobson
Messages: 3303
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 #668636 is a reply to message #668630] |
Wed, 07 March 2018 08:04 |
|
mist598
Messages: 1195
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
Can you please help me on this?
-
Attachment: Test.csv
(Size: 0.15KB, Downloaded 2730 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 #668639 is a reply to message #668638] |
Wed, 07 March 2018 08:26 |
|
mist598
Messages: 1195
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
