| sqlldr disable truncate [message #466157] |
Sat, 17 July 2010 06:13  |
garuda
Messages: 17
Registered: July 2010
|
Junior Member |
|
|
Hi Experts
I need your help in sqlldr. I am using it for the first time which I managed it successfully. However, I have an issue.
My requirement is to to truncate the table and load it with the data present in file. In the control file, I used the "TRUNCATE" command as well.
In case, if the file has some invalid data and sqlldr fails, my existing data will be lost. Is there any option in which the sqlldr does not TRUNCATE the table in case of a failure.
Please advice.
|
|
|
|
|
|
| Re: sqlldr disable truncate [message #466199 is a reply to message #466183] |
Sat, 17 July 2010 22:16  |
garuda
Messages: 17
Registered: July 2010
|
Junior Member |
|
|
Hi Michel
Thanks for your answer. I checked the documentation of external tables, and realized you cannot create any primary key in it. So, in this scenario, the other option I can think of is, before using sqlldr, take a copy of the table, if sqlldr does not succeed, restore the old table.
Any other options other than this?
Please advice.
|
|
|
|
| Re: sqlldr disable truncate [message #466200 is a reply to message #466199] |
Sat, 17 July 2010 22:44 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
> I checked the documentation of external tables, and realized you cannot create any primary key in it.
Prior to message #466199 no mention about Primary Key existed.
Please tell us ALL requirements so any proposed solution might actually work for you!
From external table load WORK_TBL.
If WORK_TBL is acceptable ( & you have not told us what exactly is acceptable), then rename or TRUNCATE then DELETE old table & rename WORK_TBL to OLD_NAME.
You did not even tell us the NAME of the table involved with any solution!
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
[Updated on: Sat, 17 July 2010 22:44] Report message to a moderator |
|
|
|
| Re: sqlldr disable truncate [message #466206 is a reply to message #466200] |
Sat, 17 July 2010 23:26 |
garuda
Messages: 17
Registered: July 2010
|
Junior Member |
|
|
Hi Blackswann
Thanks for your response. I will give my exact requirement. I have a table EMPLOYEE with fields ID, NAME and SAL with ID being the primary key. I get a file once in 6 months during which the old data has to be removed and the table be re-loaded with the data in the new file. I need the data to physically exist in this table.
As said earlier, I got this to work, however with one concern. In case of any failure say because of primary key violation, the existing data in the table is lost due to TRUNCATE in the control file.
My question is how to overcome this?
|
|
|
|
|
|
| Re: sqlldr disable truncate [message #466214 is a reply to message #466199] |
Sun, 18 July 2010 01:05 |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
garuda wrote on Sun, 18 July 2010 05:16Hi Michel
Thanks for your answer. I checked the documentation of external tables, and realized you cannot create any primary key in it. So, in this scenario, the other option I can think of is, before using sqlldr, take a copy of the table, if sqlldr does not succeed, restore the old table.
Any other options other than this?
Please advice.
You don't understand what is an external table.
An external table allows you to query your file as if it was a table. So you can query anything on it to verify the data.
If your concern is about duplicates just query with a select group by and a count and reject/stop if any count is greater than one or if any data is wrong.
You can also load in an intermediate table and verify the data there.
Or you can load in the target table after saving the previous data as you mentioned.
You can do as you want, it depends on which checking you have to do.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
