SQLLDR and NULL values

Hi all, we are trying to load this file that contains NULL values.  SQLLDR ignores the NULLs and moves the next field over.  In other words, it replaces the NULL value with the value that is following it.  Here is an example, let say I have the following format:

1234NULLXYZ

What SQLLDR does makes the above record:

1234XYZ.

How can I go around this?  I don't want to replace the NULL with another value, what I want is to somehow make SQLLDR to see the NULL.  Thank you.
shahgolsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark GeerlingsDatabase AdministratorCommented:
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.
0
shahgolsAuthor Commented:
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?
0
Mark GeerlingsDatabase AdministratorCommented:
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?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

shahgolsAuthor Commented:
Yes, the data if fixed length
0
Mark GeerlingsDatabase AdministratorCommented:
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.
0
shahgolsAuthor Commented:
No, they don't.  SQLLDR overlooks the NULLs.
0
sidcapCommented:
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
0
NaelCommented:
Interested as well!
0
Mark GeerlingsDatabase AdministratorCommented:
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?
0
shahgolsAuthor Commented:
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.
0
Mark GeerlingsDatabase AdministratorCommented:
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.  
0
sidcapCommented:
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.
0
Mark GeerlingsDatabase AdministratorCommented:
If you are like me and you don't know C (or VB or any other 3GL language) you should be able to do this with Oracle's utl_file.  To do this, set up a procedure that opens the data file for reading and another output file for writing, then read the data file one line at a time and check the length of the input data.  If it is the full length, then just write that out to the output file.  If it is shorter (because it read only up to the null character), store that string in a variable and read the next line.  The sum of the lengths of the previous string plus the current string should be the length you expect or maybe the expected length minus one.  Concatenate the two strings together, adding a space character if necessary to get the total length, then write that line out. (This assumes at most one "null" character per line, if there are multiples, just read and concatenate until you reach the expected length.)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.