Home » SQL & PL/SQL » SQL & PL/SQL » Bulk delete - save exceptions
Bulk delete - save exceptions [message #194324] Thu, 21 September 2006 09:05 Go to next message
rrr74
Messages: 2
Registered: September 2006
Location: BANGALORE, INDIA
Junior Member
I am using Bulk Collect-For All Delete and Save Exceptions.

I have 3 records to be deleted, where in first record has child records and can't be deleted (other two can be deleted). The expected behaviour in this case is that BULK_EXCEPTIONS should have entry for only one error (for first record) and rest two records should be deleted.

But, i am getting error for all records which same error message (ORA-02292: integrity constraint (.) violated - child record found)

Any thoughts on this. Following is the code snippet.

DECLARE
	 TYPE TYP_NUMBER 	IS TABLE OF NUMBER;  
	 V_COL_IFD_ID  TYP_NUMBER;
	 BULK_ERRORS   EXCEPTION;
	 PRAGMA EXCEPTION_INIT (BULK_ERRORS, -24381 );
	 V_ERS_CNT			NUMBER;
	 V_ERR_MSG			VARCHAR2(2000);
	 V_ERR_CODE			INTEGER;
	 V_ERR_REFID			NUMBER;
BEGIN
	 SELECT REF_ID
	 BULK	COLLECT INTO V_COL_IFD_ID
	 FROM	AOM@MAIN
	 WHERE	TABLE_CODE = 'IFD'
	 ORDER	BY REF_ID ASC;
	 IF V_COL_IFD_ID.COUNT > 0 THEN
	   FORALL IFDID IN V_COL_IFD_ID.FIRST..V_COL_IFD_ID.LAST 
                                                SAVE EXCEPTIONS
		  	  DELETE FROM IFD
			  WHERE  IFD_ID = V_COL_IFD_ID(IFDID);
	 END IF;
   EXCEPTION
 	 WHEN BULK_ERRORS THEN
	 	  V_ERS_CNT := SQL%BULK_EXCEPTIONS.COUNT;
	 	  FOR ERR_INDX IN 1..V_ERS_CNT
	 	  LOOP
	 	        V_ERR_CODE := SQL%BULK_EXCEPTIONS
                                     (ERR_INDX).ERROR_CODE * -1;
		 	V_ERR_MSG := SQLERRM(V_ERR_CODE);
			V_ERR_MSG := SUBSTR(V_ERR_MSG,1,2000);
		 	V_ERR_REFID := V_COL_IFD_ID(SQL%
                          BULK_EXCEPTIONS(ERR_INDX).ERROR_INDEX);
			DBMS_OUTPUT.PUT_LINE(V_ERR_REFID || '~' 
                          || V_ERR_CODE || '~' || V_ERR_MSG);													
	 	  END LOOP;
		  V_ERR_CODE := 0;
		  V_ERR_MSG := NULL;
		  V_ERR_REFID := 0;
END;

Re: Bulk delete - save exceptions [message #194853 is a reply to message #194324] Mon, 25 September 2006 13:54 Go to previous messageGo to next message
markmal
Messages: 113
Registered: April 2006
Location: Toronto, Canada
Senior Member
All worked well in my test. I've got just one error, one parent row has been left in table, because it had a child. Other rows deleted successfully.

make sure that other rows of the parent's table don't have children. Try to drop FK of the child table and PK of the parent, and recreate. If you have an index on FK, drop and recreate it too.

Re: Bulk delete - save exceptions [message #194887 is a reply to message #194853] Mon, 25 September 2006 23:16 Go to previous message
rrr74
Messages: 2
Registered: September 2006
Location: BANGALORE, INDIA
Junior Member
It works for me in test database. When i try the same on one of our production database it doesn't work.

Is there any configuration parameter/s that affects bulk exceptions?

I can't take a risk of disabling the constraint/dropping index as data is inserted 24/7 into database.
Previous Topic: help please
Next Topic: stddev
Goto Forum:
  


Current Time: Sat Dec 10 05:15:03 CST 2016

Total time taken to generate the page: 0.14187 seconds