Writing plsql exception errors to DMBS_OUTPUT and FND_FILE job log

Question

I have a MERGE/UPDATE statement. I want to add exception handling such that if the update fails for any reason, write to DBMS_output and job log. I've come up with something - it compiles OK, but doesn't seem to work.

I removed the MAX(date) and group by so that the program complies but the query itself is failing with 'unable to get a stable set of rows' - and yet no exception is being triggered.

Appreciate any recommendations.

MERGE INTO xxcb_RTL_inbnd_shipments_iface A
USING (select DISTINCT aa.shipment_line_id,aa.transaction_type,aa.last_update_date --max(aa.last_update_date) 
       from rcv_transactions aa
           right join xxcb_RTL_inbnd_shipments_iface bb on aa.shipment_line_id=bb.shipment_line_id
       where aa.transaction_type='DELIVER' 
           AND bb.interface_status='RELEASED'
       --group by aa.shipment_line_id,aa.transaction_type
) B ON (a.shipment_line_id=b.shipment_line_id)
when matched then update set a.interface_status='CLOSED'
                            ,a.interface_last_update=sysdate
                            ,a.interface_update_by='ORACLE'
where a.interface_status='RELEASED';
exception when others then FND_FILE.PUT_LINE(FND_FILE.LOG,DBMS_UTILITY.FORMAT_ERROR_STACK);           
                           FND_FILE.PUT_LINE(FND_FILE.LOG,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
                           DBMS_OUTPUT.PUT(DBMS_UTILITY.FORMAT_ERROR_STACK);
                           DBMS_OUTPUT.PUT(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

Answer 1

I got this to work after moving the DBMS_OUTPUT commands above the FND_FILE commands. Initially I was not testing through concurrent. I think the FND_FILE commands running before the DBMS_OUTPUTwas causing the exception to not run DBMS_OUTPUT

Removing the aggregation allowed the program to still compile but also generate a run time error.

The FND_FILE commands only work through concurrent.

By the way for those not in the know, FORMAT_ERROR_STACK give something like ORA-30926: unable to get a stable set of rows in the source tables' and FORMAT_ERROR_BACKTRACE gives ORA-06512: at "ORA-06512: at "APPS.procedureName", line 21

MERGE INTO xxcb_RTL_inbnd_shipments_iface A
USING (select DISTINCT aa.shipment_line_id,aa.transaction_type,aa.last_update_date--max(aa.last_update_date) 
       from rcv_transactions aa  --may need to examine PO or ISO/Req instead?
           right join xxcb_RTL_inbnd_shipments_iface bb on aa.shipment_line_id=bb.shipment_line_id
       where --aa.transaction_type='DELIVER' 
           bb.interface_status='RELEASED'
       --group by aa.shipment_line_id,aa.transaction_type
       ) B ON (a.shipment_line_id=b.shipment_line_id)
when matched then update set a.interface_status='CLOSED'
                            ,a.interface_last_update=sysdate
                            ,a.interface_update_by='ORACLE'
where a.interface_status='RELEASED';
exception when others 
    then dbms_output.put_line('Error!');
        DBMS_OUTPUT.PUT_line(DBMS_UTILITY.FORMAT_ERROR_STACK);
        DBMS_OUTPUT.PUT_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);  
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Error!');
        FND_FILE.PUT_LINE(FND_FILE.LOG,DBMS_UTILITY.FORMAT_ERROR_STACK);           
        FND_FILE.PUT_LINE(FND_FILE.LOG,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);