upd-PLSQL Exception

Submitted by code_admin on Mon, 07/23/2018 - 09:36

Example plsql exception handlers:

  1.     EXCEPTION
  2.     WHEN OTHERS
  3.     THEN
  4.         Record_Message('LOG',DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
  5.         g_sqlerrm := SUBSTR(SQLERRM,1,240);
  6.         Record_Message('LOG','ERROR - '||g_sqlerrm);
  7.         Record_Message('LOG','Main Exception Handler msg: ' || g_sqlerrm || ' Sending email');
  8.         XXGC_EMAIL_FNS_PKG.Email_SST_Message(
  9.             'The new IVR Overnight Process has failed. This effects element changes. The error message was:' || g_sqlerrm,
  10.             fnd_global.conc_request_id,
  11.             'New IVR Overnight Process'
  12.         );
  13.         retcode := 2;

Alternative

  1.     x_output_msg := x_output_msg || 'OtherExcp:=-' || SUBSTR(SQLERRM,1,1024) || '-' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;

Note: Record_Message code can be foud at https://code.metcarob.com/node/52

Standard Erroring procedure

  1. function Msg (
  2.   P_MSGID IN VARCHAR2,
  3.     X_ERROR OUT VARCHAR2  
  4. ) return number
  5. is
  6.   l_error varchar2(4096) := '';
  7. begin
  8.     x_error := '';
  9.  
  10.     //STUFF
  11.  
  12.   return 0;
  13.  
  14.   exception when others then
  15.     l_error := to_char(SQLCODE) || ' ' || nvl(substr(sqlerrm,0,990),'NULL');
  16.     X_error := l_error || ':-' || nvl(substr(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,0,3000),'NULL');
  17.     return 99;  
  18. end;

Alternate

  1. raise_application_error(-20000, 'pHub Transmit msg but target is null!');      

Search terms

Stack Trace Dump stacktrace backtrace back

Tags

RJM Article Type
Quick Reference