Home » RDBMS Server » Server Utilities » Rows not loaded because all WHEN clauses were failed.
Rows not loaded because all WHEN clauses were failed. [message #672296] |
Wed, 10 October 2018 09:08  |
 |
mist598
Messages: 1195 Registered: February 2013 Location: Hyderabad
|
Senior Member |
|
|
Hi All,
I have 2 Oracle tables , inserting using SQL Loader, but getting an error.
Create table XXC_TEST
(
RECORD varchar2(10),
DESC varchar2(150),
ENAME varchar2(10)
)
Create table XXC_TEST_2
(
RECORD varchar2(10),
DESC varchar2(150),
EMP_FLAG varchar2(1),
DEPT_FLAG varchar2(1)
)
OPTIONS (DIRECT=TRUE)
LOAD DATA
INFILE $1
APPEND
INTO TABLE XXC_TEST
WHEN RECORD = 'EMP'
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( RECORD CHAR "LTRIM(RTRIM(:RECORD))",
DESC CHAR "LTRIM(RTRIM(:DESC))" ,
ENAME CHAR "LTRIM(RTRIM(:ENAME))"
)
INTO TABLE XXC_TEST_2
WHEN (RECORD = 'DEPT')
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(RECORD CHAR "LTRIM(RTRIM(:RECORD))",
DESC CHAR "LTRIM(RTRIM(:DESC))" ,
EMP_FLAG CHAR "LTRIM(RTRIM(:EMP_FLAG))",
DEPT_FLAG CHAR "LTRIM(RTRIM(:DEPT_FLAG))"
)
SAMPLE.csv
==========
"EMP","20% PARTS DISCOUNT 20","ABC",
"DEPT","20% PARTS DISCOUNT 20",,"Y"
Table "XXC"."XXC_TEST_2":
0 Rows successfully loaded.
0 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.
The data is inserted to XXC_TEST , but not in XXC_TEST_2.
Can you please help me?
Thank you
|
|
|
Re: Rows not loaded because all WHEN clauses were failed. [message #672298 is a reply to message #672296] |
Wed, 10 October 2018 09:29   |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
For every when clause after the first when clause, you need to use POSITION(1) for the first field. Otherwise, it expects the next fields after the positions of the fields in the previous when clause.
Also, RECORD has special meaning, so if it is a column name, then you need to enclose it within double quotes.
Please see the demonstration below.
-- sample data:
SCOTT@orcl_12.1.0.2.0> host type sample.csv
"EMP","20% PARTS DISCOUNT 20","ABC",
"DEPT","20% PARTS DISCOUNT 20",,"Y"
-- control file:
SCOTT@orcl_12.1.0.2.0> host type test.ctl
OPTIONS (DIRECT=TRUE)
LOAD DATA
APPEND
INTO TABLE XXC_TEST
WHEN "RECORD" = 'EMP'
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( RECORD CHAR "LTRIM(RTRIM(:RECORD))",
DESC CHAR "LTRIM(RTRIM(:DESC))" ,
ENAME CHAR "LTRIM(RTRIM(:ENAME))"
)
INTO TABLE XXC_TEST_2
WHEN ("RECORD" = 'DEPT')
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(RECORD POSITION(1) CHAR "LTRIM(RTRIM(:RECORD))",
DESC CHAR "LTRIM(RTRIM(:DESC))" ,
EMP_FLAG CHAR "LTRIM(RTRIM(:EMP_FLAG))",
DEPT_FLAG CHAR "LTRIM(RTRIM(:DEPT_FLAG))"
)
-- tables:
SCOTT@orcl_12.1.0.2.0> Create table XXC_TEST
2 (
3 RECORD varchar2(10),
4 "DESC" varchar2(150),
5 ENAME varchar2(10)
6 )
7 /
Table created.
SCOTT@orcl_12.1.0.2.0> Create table XXC_TEST_2
2 (
3 RECORD varchar2(10),
4 "DESC" varchar2(150),
5 EMP_FLAG varchar2(1),
6 DEPT_FLAG varchar2(1)
7 )
8 /
Table created.
-- load:
SCOTT@orcl_12.1.0.2.0> host sqlldr scott/tiger control=test.ctl data=sample.csv log=test.log
SQL*Loader: Release 12.1.0.2.0 - Production on Wed Oct 10 07:22:08 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Direct
Load completed - logical record count 2.
Table XXC_TEST:
1 Row successfully loaded.
Table XXC_TEST_2:
1 Row successfully loaded.
Check the log file:
test.log
for more information about the load.
-- results:
SCOTT@orcl_12.1.0.2.0> select * from xxc_test
2 /
RECORD
----------
DESC
--------------------------------------------------------------------------------
ENAME
----------
EMP
20% PARTS DISCOUNT 20
ABC
1 row selected.
SCOTT@orcl_12.1.0.2.0> select * from xxc_test_2
2 /
RECORD
----------
DESC
--------------------------------------------------------------------------------
E D
- -
DEPT
20% PARTS DISCOUNT 20
Y
1 row selected.
|
|
|
|
|
|
|
Re: Rows not loaded because all WHEN clauses were failed. [message #672304 is a reply to message #672302] |
Wed, 10 October 2018 12:40   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
mist598 wrote on Wed, 10 October 2018 13:15Could any one please help me?
And it is pretty rude to demand people help you for free and wonder why you are not getting in answer in two hours. You know people have jobs that pay them money. Yes, Barbara may be retired, but she doesn't owe you anything.
And DESC is a reserved word, so subtly pointed out by Barbara by putting it in double quotes too.
[Updated on: Wed, 10 October 2018 12:42] Report message to a moderator
|
|
|
|
Re: Rows not loaded because all WHEN clauses were failed. [message #672310 is a reply to message #672300] |
Wed, 10 October 2018 14:50   |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
mist598 wrote on Wed, 10 October 2018 08:15I am getting below error.
SQL*Loader: Release 10.1.0.5.0 - Production on Wed Oct 10 11:11:11 2018
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL*Loader-403: Referenced column "RECORD" not present in table XXC_TEST.
The error message is telling you that it cannot find a column named "RECORD" in the table XXC_TEST. This may be because the case (upper and lower) don't match between the column name in your SQL*Loader control file and the column name in your table. For example, "RECORD" does not match "record" or "Record". When you put things in quotes it preserves the case. Without the quotes, it does not, and eliminates the issue of matching cases. The only reason you needed to use quotes around "RECORD" is because it is a word that has special meaning in SQL*Loader. It is best to avoid using such words, including "DESC" which is a SQL*Plus command, then you don't need the quotes, and it avoids these errors. It is impossible to guess exactly what you have done when you have made unknown changes that you did not post. If you cannot figure it out from what I have just said, then you need to post a copy and paste of an actual run, similar to what I did, including the create table statements.
As others have pointed out, although I am retired, I do not sit at my computer all day, so responses may take a while and you should heed the advice of other experts in the meanwhile.
|
|
|
|
|
Goto Forum:
Current Time: Mon Mar 03 17:00:32 CST 2025
|