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 Go to next message
mist598
Messages: 1188
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8890
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 #672299 is a reply to message #672298] Wed, 10 October 2018 09:47 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Thank you so much!

How to write code if i have another table XXC_TEST_3? same code ? I mean POSITION(1) correct?
Re: Rows not loaded because all WHEN clauses were failed. [message #672300 is a reply to message #672298] Wed, 10 October 2018 10:15 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
I 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.

[Updated on: Wed, 10 October 2018 11:18]

Report message to a moderator

Re: Rows not loaded because all WHEN clauses were failed. [message #672302 is a reply to message #672300] Wed, 10 October 2018 12:15 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Could any one please help me?
Re: Rows not loaded because all WHEN clauses were failed. [message #672303 is a reply to message #672302] Wed, 10 October 2018 12:25 Go to previous messageGo to next message
BlackSwan
Messages: 26201
Registered: January 2009
Location: SoCal
Senior Member
mist598 wrote on Wed, 10 October 2018 10:15
Could any one please help me?
Why do you continue to use "RECORD" after being warned not to do so?

We can't say what is wrong since you have NOT shown us what you now have & what was done to cause the error.

IMO, it is silly & foolish to attempt to load multiple tables from a single file.

Alternatively, you could simply map file to EXTERNAL TABLE & then do necessary SQL to move the data in desired tables.
Re: Rows not loaded because all WHEN clauses were failed. [message #672304 is a reply to message #672302] Wed, 10 October 2018 12:40 Go to previous messageGo to next message
joy_division
Messages: 4902
Registered: February 2005
Location: East Coast USA
Senior Member
mist598 wrote on Wed, 10 October 2018 13:15
Could 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 #672309 is a reply to message #672299] Wed, 10 October 2018 14:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8890
Registered: November 2002
Location: California, USA
Senior Member
mist598 wrote on Wed, 10 October 2018 07:47
Thank you so much!

How to write code if i have another table XXC_TEST_3? same code ? I mean POSITION(1) correct?
Correct.
Re: Rows not loaded because all WHEN clauses were failed. [message #672310 is a reply to message #672300] Wed, 10 October 2018 14:50 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8890
Registered: November 2002
Location: California, USA
Senior Member
mist598 wrote on Wed, 10 October 2018 08:15
I 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.



Re: Rows not loaded because all WHEN clauses were failed. [message #672316 is a reply to message #672310] Thu, 11 October 2018 01:14 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Thank you Barbara so much , yes these are reserved words , DESC, RECORD,

I changed RECORD to RECORD_TYPE.

Thank you so much!

[Updated on: Thu, 11 October 2018 01:18]

Report message to a moderator

Re: Rows not loaded because all WHEN clauses were failed. [message #672317 is a reply to message #672316] Thu, 11 October 2018 03:01 Go to previous message
gazzag
Messages: 993
Registered: November 2010
Location: Bristol, UK
Senior Member
In future avoid any word in the following list:
SELECT keyword
FROM v$reserved_words
ORDER BY keyword;
Previous Topic: Export database
Next Topic: LDAP how to get all listed entries
Goto Forum:
  


Current Time: Fri Oct 19 23:19:30 CDT 2018