Home » RDBMS Server » Server Utilities » sqlldr disable truncate (10g)
sqlldr disable truncate [message #466157] Sat, 17 July 2010 06:13 Go to next message
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 #466183 is a reply to message #466157] Sat, 17 July 2010 09:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It truncates first (how could it truncate after?) and then load, so it sees there are errors during loading processing and so AFTER truncate.

You could use external table instead then you can check the data before truncating and loading into the target table.

Regards
Michel
Re: sqlldr disable truncate [message #466199 is a reply to message #466183] Sat, 17 July 2010 22:16 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #466207 is a reply to message #466206] Sat, 17 July 2010 23:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>My question is how to overcome this?
RENAME
Re: sqlldr disable truncate [message #466214 is a reply to message #466199] Sun, 18 July 2010 01:05 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
garuda wrote on Sun, 18 July 2010 05:16
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.

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
Previous Topic: data_pump name_expr
Next Topic: Snapshot error during export
Goto Forum:
  


Current Time: Fri Mar 29 09:33:12 CDT 2024