Home » RDBMS Server » Server Utilities » SQL*Loader and NULL value (Oracle 10g on Windows 7)
icon5.gif  SQL*Loader and NULL value [message #431515] Tue, 17 November 2009 20:54 Go to next message
Messages: 1
Registered: November 2009
Location: North Carolina
Junior Member
EDIT: I have fixed my own problem, and request this thread be deleted or closed. The extra pipe at the end DID indeed work, I was just looking at an old badfile.

Thank you.

Okay, I have a control file, a .dat file with 58 records, and need to load these records into the database.

The last attribute in the table is DEPARTMENT_ID, and every record except ONE has a value for this attribute. That's okay, right? It's a NULLable field. On we go.

When I wrote the control file, I included a "badfile" value, purely for academic purposes.

When I run the SQL*Loader, I get the 'thumbs-up' message from the system, saying all 58 records were loaded. However, when I query the table, one record is missing - yep, you guessed it - the one with the empty value for the last attribute. That record does get inserted into the bad file, also.

Now, I have tried adding an extra pipe to the end, to show that the attribute contains nothing, and I have tried inserting NULL into the .dat file at the appropriate place, but neither are allowing the record to be inserted.

I can see how empty values are dealt with for attributes NOT occurring at the end of the tuples, they simply insert another pipe and list the next value. This doesn't seem to work at the end, however.

My question is this: what special concession must I make to allow a record with the last attribute empty to be inserted into the database?

Thank you in advance,


P.S. this exercise is in a vacuum (school), and not a real-world problem, so there is really no work- or heart-stoppage going on, I am just looking for some insight Smile

[Updated on: Tue, 17 November 2009 21:22]

Report message to a moderator

Re: SQL*Loader and NULL value [message #431540 is a reply to message #431515] Wed, 18 November 2009 00:20 Go to previous message
Messages: 21146
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is great that you managed to solve the problem! Thank you for the information; also, I hope you'll participate further discussions on OraFAQ Forum!
Previous Topic: SQL*Loader-485
Next Topic: Problem in import
Goto Forum:

Current Time: Fri Aug 18 05:53:39 CDT 2017

Total time taken to generate the page: 0.14921 seconds