SQLLDR and NULL values

SQL*Loader is fast, but it has very limited data manipulation ability.  The approach I take with troublesome data files is to create a temporary table with same structure as the data file, load that table with SQL*Loader, then write a PL\SQL procedure to move the data from there to where it really needs to go.

Yes, I agree, thats what I do too, but we cannot even load this data to a temp table since it overlooks the NULL values.  How can we tune it so that it takes notice of the NULLs?

Is the data fixed-length (I am guessing not)?  What kind of column separator does the data have.  From the brief example you gave, I don't see how any program could handle the data correctly.  Can you post a sample line or two of the data, and your table definition?

Yes, the data if fixed length

If the data is fixed length, then handling nulls should be very easy, like this example from my ORACLE_HOME\rdbms80\loader\ulcase2.ctl file:
LOAD DATA
INFILE 'ulcase2.dat'
INTO TABLE EMP

( EMPNO    POSITION(01:04) INTEGER EXTERNAL,
  ENAME    POSITION(06:15) CHAR,
  JOB      POSITION(17:25) CHAR,
  MGR      POSITION(27:30) INTEGER EXTERNAL,
  SAL      POSITION(32:39) DECIMAL EXTERNAL,
  COMM     POSITION(41:48) DECIMAL EXTERNAL,
  DEPTNO   POSITION(50:51) INTEGER EXTERNAL)


If the fourth column, mgr, contains 'NULL' in positions 27-30, the following fields will still load into the correct columns.

No, they don't.  SQLLDR overlooks the NULLs.

shahgols:

I can't understand exactly what your NULL values within a file means:

Does your file look as the following ?

1234NULLXYZ  -> with the word "NULL" or with an special ASCII caracter ?

As you wrote, NULL seems to appear in your file, then, add the following sintax in the correct field:

For instance:
.....
MGR      POSITION(27:30) INTEGER EXTERNAL NULLIF MGR='NULL',
....

please, let me know if i'm wrong.

Hope this helps, SIDCAP

Interested as well!

I had some time to do a test today, and the results were exactly as I expected, SQL*Loader handled the "null" just fine.
 
Here is what I did:
create table test_table
(col_a  varchar2(8),
 col_b  varchar2(8),
 col_c  number(6,0),
 col_d  varchar2(8));

-- Here is my data file:
ABCDxxxx1234XYZ
ABCCNULL5678XYY
AABBnull5432YYZ

-- This is the control file:
LOAD DATA
INFILE 'testdata.txt'
INTO TABLE TEST_TABLE
(COL_A    POSITION(01:04) CHAR,
 COL_B    POSITION(05:08) CHAR,
 COL_C    POSITION(09:12) INTEGER EXTERNAL,
 COL_D    POSITION(13:16) CHAR)

After I ran SQL*Loader with this control file, here are the results from SQL*Plus:
SQL> select * from test_table;

COL_A    COL_B        COL_C COL_D
-------- -------- --------- ------
ABCD     xxxx          1234 XYZ
ABCC     NULL          5678 XYY
AABB     null          5432 YYZ

The data is exactly as I expected it to be.  The "nulls" were not skipped.  What is different in your data, control file or table?

The NULL in my example is an ASCII value.  Somehow SQLLDR overlooks that value and sees the whole row without any nulls which means that the characters get misplaced with SQLLDR.  I hope I have answered your questions.  Thank you.

It looks like you may need to pre-process your data file with a text editor, a hex editor, or possibly a system utility (depending on your O/S) or some other program.  The text editor that I use (WinEdit from WilsonWindowWare) recognizes the null character in data files and optionally replaces it with another character.  That may be an option for you.  Maybe even Excel could work, since it can open ASCII files and write them back out.  

Yes, I agree with markgeer, It's that you'll have to do.

A good way to solve it could be write a simple C program replacing the conflicting characters.