SQL & PL/SQL

Hi All,

We are trying to handle the exception ORA-8103 ( Object no longer exists)  - which causes here due to partition exchange - But the code ( Shown below ) is still giving error ORA-8103. Please help me to identify what is wrong in our code ( it is a part of a pckage and is trying to use recursion )

Version:  Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Code:

procedure merge_data is
  exc_part_missing exception;
  pragma exception_init(exc_part_missing,-8103);
  exc_12801 exception;
  pragma exception_init(exc_part_missing,-12801);
    v_err_msg varchar2(4000);
  begin
        MERGE INTO table_A
        USING(
              Select ....
          from table_b
where .. ---->  Filter includes partition key
group by ...
         ) src
        ON (....)
      WHEN MATCHED THEN
      UPDATE SET .... ;
    exception
    when exc_part_missing then
       v_msgtext := v_msgtext||'Rerun after -8103 exception :';
       merge_data;
    when exc_12801 then
       v_err_msg := substr(sqlerrm,1,4000);
       v_msgtext := v_msgtext||'Rerun after -12801 exception :';
       if instr(v_err_msg,'ORA-08103') <> 0 then
         merge_data;
       else
         raise;
       end if;
   end merge_data;

Whenever partition exchange happens in between MERGE, it fails. I will try to generate a test case and post it.

Thanks in Advance.

Message was edited by: zzzzzzz