RE: Not all rows deleted

From: Ruiz, Mary A (CAP, CDI) <Mary.Ruiz_at_gecapital.com>
Date: Fri, 26 Jan 2001 11:23:42 -0500
Message-Id: <10753.127596@fatcity.com>

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-----
From: bonnergj_at_songs.sce.com
Sent: Friday, January 26, 2001 10:36 AM
To: Multiple recipients of list ORACLE-L Subject: Not all rows deleted

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). Received on Fri Jan 26 2001 - 10:23:42 CST