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 Go to next message
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 #375980 is a reply to message #375977] Mon, 15 December 2008 09:36 Go to previous messageGo to next message
david_g
Messages: 4
Registered: December 2008
Junior Member
I've also tried:

when full_name=BLANKS
Re: SQL loader cannot check for nulls [message #375994 is a reply to message #375980] Mon, 15 December 2008 10:04 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Not sure I understand your requirement.
Please post the expected output (after loading into the table).
Re: SQL loader cannot check for nulls [message #376021 is a reply to message #375977] Mon, 15 December 2008 13:19 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
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 Go to previous messageGo to next message
Littlefoot
Messages: 20897
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
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 #376051 is a reply to message #376045] Mon, 15 December 2008 15:38 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, I spent at least an hour modifying this and that (terminated fields with whitespaces, commas, trailing nullcols, fillers, specified field datatypes ... different combinations of logical and out-of-sense parameters), but couldn't find that tiny piece of information which would reveal the solution.

If someone finds the way to make it work, I'd really be interested in seeing it.
Re: SQL loader cannot check for nulls [message #376054 is a reply to message #376051] Mon, 15 December 2008 15:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
I don't know if I made my suggestion clear or not. What I was suggesting was that you start with the one that works for you and gradually change one thing at a time until it matched the one that doesn't work. Are you saying that you did that and once it was the same as the one that didn't previously work, it still worked?
Re: SQL loader cannot check for nulls [message #376055 is a reply to message #376054] Mon, 15 December 2008 16:14 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No; I managed to load the DEPT table successfully. The same script, with a different table (PERSON) didn't work, no matter what I changed.

I have even tried the CTAS and used "CREATE TABLE person AS SELECT * FROM dept WHERE 1 = 2", modified the "person.ctl" control file to use different column names - no luck either.

It's a mystery to me (and almost midnight, so - I'm off to bed).
Re: SQL loader cannot check for nulls [message #376129 is a reply to message #375977] Tue, 16 December 2008 03:36 Go to previous messageGo to next message
david_g
Messages: 4
Registered: December 2008
Junior Member
Thanks for all your replies. This problem has been a real headache. I think I will resort to external tables if i can't get this working.

I should have mentioned that this is on 10g.
Re: SQL loader cannot check for nulls [message #376133 is a reply to message #375977] Tue, 16 December 2008 04:17 Go to previous message
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.
Previous Topic: Import in windows
Next Topic: Expdp from EE impdp to SE
Goto Forum:
  


Current Time: Tue Dec 06 16:03:34 CST 2016

Total time taken to generate the page: 0.06599 seconds