RE: Not all rows deleted

well he did say that they wanted to be able to rollback the delete if the insert failed -- "old data is better than no data"

if he commits after the delete, there is no data

>From: "Ruiz, Mary A (CAP, CDI)" <Mary.Ruiz_at_gecapital.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: Not all rows deleted
>Date: Fri, 26 Jan 2001 08:30:19 -0800
>
>Hi,
> I am not sure what the problem is, but when I saw this I wondered why
>you do not go ahead and commit after the first delete statement.
>
>Mary Ruiz / Atlanta GA
>
>-----Original Message-----
>Sent: Friday, January 26, 2001 10:36 AM
>To: Multiple recipients of list ORACLE-L
>
>
>Hi everyone,
>Sent this to the list two days ago but got no response. Come on, someone
>has to have some kind of unreasonable, uncomprehensible explanation
>for this.
>I setup a job in cron that runs an oracle procedure every night at 3am. The
>procedure deletes all the
>rows of a table(less than 500 rows) then does an "insert select from a
>remote database" to reload the
>table. We delete rather than truncate because if the job fails it does a
>rollback on the table and although
>the data is 24 hours old it's preferred over "no data" until the problem is
>corrected and reloaded.
>The job has started failing 2 out of 5 times for the past several weeks.
>When the "insert select" executes
>it's failing with a unique constraint violation. The table has a unique
>index on one column, same as the
>remote table it's selecting from. The table being loaded only has 3
>columns, same as the table it's
>selecting from.
>Just on a chance, I inserted some code between the delete and insert to
>count how many records were
>on the table after the delete. I was sure the count would be 0.
> DELETE FROM bcc.func_desc_table;
> SELECT COUNT(*) INTO v_count FROM bcc.func_desc_table;
> IF v_count > 0 THEN
> RAISE DELETE_ERROR;
> END IF;
> INSERT INTO BCC.FUNC_DESC_TABLE
> (SELECT FUNWO , FUNCDESC , ACTIVITY
> FROM BCC.TABLE20_at_BPCC.SONGS.SCE.COM);
>I'm hitting the Raise Delete_Error because the count comes back with either
>3 or 4 records still in the
>table. This explains the unique constraint violation when the table is
>reloaded but why are there records
>remaining on the table after the Delete?
>Next step, I added "LOCK TABLE bcc.func_desc_table IN EXCLUSIVE MODE
>NOWAIT" before the delete.
>If the table couldn't be locked I would have gone to my Exception handler
>with a "-0054 resource busy" .
>Even with the table locked it's still hitting the Raise Delete_Error with 3
>records remaining on the table.
>There are no synonyms and neither table has primary or foreign keys.
>When I come in at 7 or 8am and manually run the job there is never a
>problem.
>Any ideas, suggestions, theories??
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author:
> INET: bonnergj_at_songs.sce.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Ruiz, Mary A (CAP, CDI)
> INET: Mary.Ruiz_at_gecapital.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).