How Oracle handles custom exceptions in stored procedures

How Oracle handles custom exceptions in stored procedures:

In Oracle-more than 20000 is the code for user-defined exceptions, and you can customize your own exception hints based on this principle.

You can also invoke additional stored procedures in the stored procedure.

Here is an example for reference only.

Create or Replace procedure Cover28doc (P_docno varchar2,
P_reddocno VARCHAR2,
P_newdocno VARCHAR2,
P_VLDPSN VARCHAR2,
P_return in Out integer) as
-When the library Changhong, it may violate the rules of the position, that is, ' shelf '. When the red bill is audited, an error is encountered.

 --record voucher name
  v_docname varchar2;
  v_rtu     number (1);
  v_valno   VARCHAR2 (60);
  v_num     int;
Begin
  V_docname: = GETEBKDSC (2800 * 10000);
  p_return : = 0;
 --non-audited documents not red flushes
  SELECT COUNT (1)
    into V_num
    from DOC28BF
   where valid = 0
     docno = p_docno;
  If v_num > 0 Then
    raise_application_error ( -20000,
                              ' The documents you want to red are not red, and the documents are not audited. ');
    return;
  End If;

 -Red stamping documents not red
  SELECT COUNT (1)
    into V_num
    from DOC28BF
   where iscover = 1
     docno = p_docno;
  If v_num > 0 Then
    raise_application_error ( -20000,
                              ' The documents you want to red are not red, red-washed documents should not be red-washed. ');
    return;
  End If;

--The not red flush that has been closed
Select COUNT (1)
Into V_num
From DOC28BF
where CLSVLD = 1
and docno = P_docno;
If V_num > 0 Then
Raise_application_error (-20000,
' You have to red the document is not red, the closed accounts can not red Chong. ');
Return
End If;

Begin
--Update the document, mark not red Chong
Update DOC28BF Set iscover = 1 where docno = P_docno;

Update Doc28ext
Set DSC = ' Red single for ' | | P_reddocno | | '; New Lan single for ' | | P_newdocno | | ';'
where docno = P_docno;
exception
When others then
Rollback
Raise_application_error (-20000,
' In the Will ' | | V_docname | |
' There was an error in the red flushes of the data. The original error number is [' | |
Sqlcode | | '], the original error message is [' | | | sqlerrm | | '].');
Return
End

--Generate red stamping documents and review

--Generate primary table
Begin
INSERT INTO DOC28BF
(Docno,
Optid,
Ctyp,
Shpid,
Comid,
Docdat,
Invno,
Isred,
Iscover,
VALID)
Select P_reddocno, Optid, Ctyp, Shpid, Comid, Docdat, Invno, 1, 1, 0
From DOC28BF
where docno = P_docno;
exception
When others then
Rollback
Raise_application_error (-20000,
' In the Will ' | | V_docname | |
' There was an error in the red flushes of the data. The original error number is [' | |
Sqlcode | | '], the original error message is [' | | | sqlerrm | | '].');
Return
End

--Audit Red bill (internal call stored procedures again)
  begin
    Valid28doc (P_reddocno, P_VLDPSN, V_valno, V_rtu);
  Exception
    when others then
      rollback;
      raise_application_error ( -20000,
                                ' in Will ' | | V_docname | |
                               ' Data red-flush error. The original error number is [' | |
                               Sqlcode | | '], the original error message is [' | | | sqlerrm | | '].');
      return;
  End;

--Generate new documents

Commit

P_return: = 1;

End Cover28doc;