Page 1 of 1

Delete then insert records

Posted: Tue Jan 24, 2006 10:01 am
by babbu9
Hi
I am working with the Oracle Enterprise stage and would like to truncate a table and then insert records into it.
I tried the load option in the stage but it gives an error. I would like to do this by including custom code in the oracle stage, first truncate then insert records.How do I do this?


Please inform.

Bob

Re: Delete then insert records

Posted: Tue Jan 24, 2006 11:39 am
by kwwilliams
babbu9 wrote:Hi

I tried the load option in the stage but it gives an error. I would like to do this by including custom code in the oracle stage, first truncate then insert records.How do I do this?
Bob,

What errors does it throw? If it could run without custom code would you prefer that? Did you have disable contraints = True and have the indexes set to rebuild? Other issue could be permissions, to truncate the table you either have to log in as the schema owner or have alter privileges on the table and all of its indexes.

Keith

Re: Delete then insert records

Posted: Wed Jan 25, 2006 10:36 am
by klarson88
babbu9 wrote:Hi
I am working with the Oracle Enterprise stage and would like to truncate a table and then insert records into it.
I tried the load option in the stage but it gives an error. I would like to do this by including custom code in the oracle stage, first truncate then insert records.How do I do this?


Please inform.

Bob
We created a PL/SQL for similar item, except that we wanted to delete records from the table by source (one out of four) by day. We created a routine to execute before running the load job, but you could also call the procedure in the SQL/BEFORE window within the job.

Posted: Wed Jan 25, 2006 10:55 am
by babbu9
Thank you for the mail. SQL before window exists in server jobs and not in parallel. I have not looked into the drop constraints and rebuild indexes options in parallel.

Thanks again.

Posted: Thu Jan 26, 2006 3:36 am
by raj_konig
Hello Babbu,

Just create another instance of the same table for the delete action. Make sure that the inputs for insertion and deletion instances of the table comes from the same transformer. Also make sure that delete output from the transformer should be done first before the insert.

Thanks,
rajesh