Home » RDBMS Server » Server Utilities » SQL loader cannot check for nulls
| SQL loader cannot check for nulls [message #375977] |
Mon, 15 December 2008 09:33  |
david_g
Messages: 4
Registered: December 2008
|
Junior Member |
|
|
There seems to be a problem with SQL loader and checking for nulls. If you run the control file below on the table:
create table person
(
first_name varchar2(50),
last_name varchar2(50)
)
;
Then it will not insert anything; but if you invert the when clause from "=" to "!=" or "<>" then it will insert one record.
Has anyone else come across this problem before?
options (skip=1)
load data
infile *
append
into table person when full_name=''
fields terminated by ','
trailing nullcols
(
first_name,
last_name,
full_name boundfiller
)
begindata
first_name, last_name, full_name
jim, floss
x,x,jim floss
|
|
|
|
|
|
|
|
| Re: SQL loader cannot check for nulls [message #376021 is a reply to message #375977] |
Mon, 15 December 2008 13:19  |
 |
Barbara Boehmer
Messages: 9092
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I don't see any way to get the when clause to work. I can think of two possible workarounds. Either you can load the data into a staging table, then only insert the rows where full_name is null or you could add a full_name column to the person table with a constraint to check that full_name is null, load the data, then drop the person column, as demonstrated below.
-- test.ctl:
options (skip=1)
load data
infile *
append
into table person
fields terminated by ','
trailing nullcols
(
first_name,
last_name,
full_name
)
begindata
first_name, last_name, full_name
jim, floss
x,x,jim floss
-- initial table:
SCOTT@orcl_11g> create table person
2 (
3 first_name varchar2(50),
4 last_name varchar2(50)
5 )
6 /
Table created.
-- load process:
SCOTT@orcl_11g> alter table person add (full_name varchar2(1))
2 /
Table altered.
SCOTT@orcl_11g> alter table person
2 add constraint check_full_name_null
3 check (full_name is null)
4 /
Table altered.
SCOTT@orcl_11g> host sqlldr scott/tiger control=test.ctl log=test.log
SCOTT@orcl_11g> alter table person drop column full_name
2 /
Table altered.
-- results:
SCOTT@orcl_11g> select * from person
2 /
FIRST_NAME
--------------------------------------------------
LAST_NAME
--------------------------------------------------
jim
floss
SCOTT@orcl_11g>
|
|
|
|
| Re: SQL loader cannot check for nulls [message #376039 is a reply to message #376021] |
Mon, 15 December 2008 14:00 |
 |
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
This is an example based on Scott's schema; BLANKS option works fine.
However, I am not able to reproduce the same with this "person" example; obviously, there *must* be something what makes the difference, but I don't know what it is ...
Here it is, once again: this works:SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- --------------------------------------------------
10 ACCOUNTING New York
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> $type dept.ctl
load data
infile *
append
into table dept
when loc = blanks
fields terminated by ','
(deptno, dname, loc)
begindata
1,BI Team, Zagreb
11,IT Team, Zagreb
21,BI Team,
22,IT Team,Split
SQL>
SQL> $sqlldr scott/tiger control=dept.ctl log=dept.log
SQL*Loader: Release 10.2.0.1.0 - Production on Pon Pro 15 20:53:34 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 3
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- --------------------------------------------------
10 ACCOUNTING New York
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
21 BI TEAM
SQL>
And this does NOT work:SQL> desc person
Name Null? Type
----------------------------------------- -------- ----------------------------
FIRST_NAME VARCHAR2(20)
LAST_NAME VARCHAR2(20)
FULL_NAME VARCHAR2(20)
SQL> select * from person;
no rows selected
SQL> $type person.ctl
load data
infile *
append
into table person
when full_name = blanks
fields terminated by ','
(first_name, last_name, full_name)
begindata
Little, Foot, Littlefoot
Big, Foot,
Micro, Space, Microspace
SQL>
SQL> $sqlldr scott/tiger control=person.ctl log=person.log
SQL*Loader: Release 10.2.0.1.0 - Production on Pon Pro 15 20:55:07 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> select * from person;
no rows selected
SQL>
Log files: a working one:Table DEPT, loaded when LOC = BLANKS
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO FIRST * , CHARACTER
DNAME NEXT * , CHARACTER
LOC NEXT * , CHARACTER
Record 1: Discarded - failed all WHEN clauses.
Record 2: Discarded - failed all WHEN clauses.
Record 4: Discarded - failed all WHEN clauses.
Table DEPT:
1 Row successfully loaded.
0 Rows not loaded due to data errors.
3 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null. Non-working one:Table PERSON, loaded when FULL_NAME = BLANKS
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
FIRST_NAME FIRST * , CHARACTER
LAST_NAME NEXT * , CHARACTER
FULL_NAME NEXT * , CHARACTER
Record 1: Discarded - failed all WHEN clauses.
Record 2: Discarded - failed all WHEN clauses.
Record 3: Discarded - failed all WHEN clauses.
Table PERSON:
0 Rows successfully loaded.
0 Rows not loaded due to data errors.
3 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Now, I know I'm not very smart, but - what is going on here?!? What makes the difference?
P.S. Forgot to mention my database version: 10.2.0.1.0
[Updated on: Mon, 15 December 2008 14:01] Report message to a moderator |
|
|
|
| Re: SQL loader cannot check for nulls [message #376045 is a reply to message #376039] |
Mon, 15 December 2008 14:50 |
|
Barbara Boehmer
Messages: 9092
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Littlefoot,
I had assumed that the problem was due to not being able to evaluate the boundfiller in the same manner as a regular table column. However, in testing with 11g, I now see that having a null column, no spaces, without an ending delimiter and not using trailing nullcols, results in it failing the when column_name = blanks and no rows are loaded, including using your first example. Since I seem to remember that such things used to work, I was thinking it was an 11g bug. However, when you posted that one works for you and one does not, now I don't know what to think. Since I do not see any differences and cannot reproduce, since both fail on my system, I will have to leave it to those of you who can reproduce in 10g to find the differences. All I can suggest is to start with what works, then change one tiny thing at a time and retest until you find which thing makes the difference.
Barbara
|
|
|
|
|
|
|
|
|
|
|
|
| Re: SQL loader cannot check for nulls [message #376133 is a reply to message #375977] |
Tue, 16 December 2008 04:17 |
david_g
Messages: 4
Registered: December 2008
|
Junior Member |
|
|
Seems like the DEPT example works because a space is placed after "
21,BI Team, ".
So I guess to get the example to work above change it to:
begindata
first_name, last_name, full_name
jim, floss,
x,x,jim floss
Notice the extra space at the end of the first data line.
Problem is I do need to use trailing nullcols. This option seems to produce ",,,," rather than ", , , , ".
Still seems inconsistent when <> and != blanks or '' still evaluates as expected.
|
|
|
|
Goto Forum:
Current Time: Fri May 03 12:52:00 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
