I am trying to enter few records from a tape file to a table residing in Oracle database. I am trying to do this with the help of control file.
However, in the tape file there are few spaces which are targeted to a primary key attribute. While migrating the record, the loader is considering these spaces as NULL and hence throwing error.
Is there some way to insert the blank spaces to the primary key attribute?
Thanks in advance!
A primary key, by definition, can not be null. So no, you can not load a record with a null value. You need to find a way to put a unique value into that field at load time. So a constant is not an option as it would only work for one record due to the uniqueness factor of a primary key.
For this, there are options. You could create a staging table with the same structure as the target but without a primary key, load to that table first and then transfer the valid records into the table and figure out how to handle the bad rows later.
Or, if the key is numeric-based, then as long as you are not using the SQL*Loader direct option you could add an on insert trigger to that table which checks for a null in that field and sets it to a value if it is null. Sequences are often used for this, but in your instance you would need to test for collisions with existing key values (e.g. create a sequence that starts in a range far higher than any value from the data source). Still, this is a risk in that you could run into future collisions if the source system eventually duplicates a key you have created, and it breaks any ability to trace a record from the target back to the source based on the ID.