Processing of abnormal information of Oracle

Introduction

many times, we call Oracle stored procedures to have a variety of exception information, such as ORA-12899 value is too large, ORA-01400 can not insert a null value and so on. Although this kind of anomaly is not in the front of the control in place, but the reality is difficult 100 % Full Control, so once this type of exception occurs, and the return of the front end of the display will result in a poor user experience. Perhaps, we can make this kind of abnormal processing, packaging better, let the user understand what happened, can handle themselves. Here's my little try.

We typically get exception information for exception handling in Oracle stored procedures, such as:

Exception      when  then     rollback ;     Dbms_output.put_line (SQLERRM);     Raise_application_error (-20000

However, we will get the following exception information, such as

ORA-12899)

Although 99% of our programmers can understand what this means, but also 99% of users can not see why. Our goal is to accidentally happen, users still understand. Next, I need to create a function that specifically handles this kind of exception and returns information that most users can read. The function code is as follows:

Create or Replace functionF_czl_geterror (Messageinch varchar2)return varchar2  isResultvarchar2( +); NUM1 Number:=0; Num2 Number:=0; NUM3 Number:=0; NUM4 Number:=0; NUM5 Number:=0; NUM6 Number:=0; Num7 Number:=0; NUM8 Number:=0; NUM9 Number:=0; NUM10 Number:=0; STR1varchar2( +); STR2varchar2( +); STR3varchar2( +); STR4varchar2( +); STR5varchar2( +); STR6varchar2( +); STR7varchar2( +); Str8varchar2( +); STR9varchar2( +); Str10varchar2( +);begin       ifInStr (Message,'ORA-12899')>0  ThenNUM1:=InStr (Message,'ORA-12899');--get the location of ORA-12899STR1:=SUBSTR (MESSAGE,NUM1);--get all the characters behind ORA-12899NUM2:=InStr (STR1,'"',1,3);--get the third "position behind the ORA-12899.NUM3:=InStr (STR1,'"',1,4);--get the position of the fourth "behind the ORA-12899.STR2:=SUBSTR (str1,num2+1, num3-Num2-1);--Get table nameNUM4:=InStr (STR1,'"',1,5);--get the position of the fifth "behind the ORA-12899.NUM5:=InStr (STR1,'"',1,6);--get the position of the sixth "behind the ORA-12899.STR3:=SUBSTR (str1,num4+1, NUM5-Num4-1);--Get field nameNUM6:=InStr (STR1,':',1,2);--get the second one behind ORA-12899: the positionNUM7:=InStr (STR1,',',1,1);--get the position of the 1th one behind ORA-12899.STR5:=SUBSTR (STR1,NUM6+1, NUM7-Num6-1);--get the length of the inputNUM8:=InStr (STR1,':',1,3);--get the second one behind ORA-12899: the positionNUM9:=InStr (STR1,')',1,1);--get the position of the 1th one behind ORA-12899.STR6:=SUBSTR (STR1,NUM8+1, NUM9-Num8-1);--get the maximum length            --get the corresponding field comment     SelectT.comments intoStr4 fromSYS. User_col_comments TwhereT.column_name=Str3 andT.table_name=str2; Result:=Str4||'the length exceeds the limit and the maximum number of characters is'||Str6||', the number of characters you enter is'||Str5||'.'; elsif InStr (Message,'ORA-01400')>0   ThenNUM1:=InStr (Message,'ORA-01400');--get the location of ORA-01400STR1:=SUBSTR (MESSAGE,NUM1);--get all the characters behind ORA-01400NUM2:=InStr (STR1,'"',1,3);--get the third "position behind the ORA-01400.NUM3:=InStr (STR1,'"',1,4);--get the position of the fourth "behind the ORA-01400.STR2:=SUBSTR (str1,num2+1, num3-Num2-1);--Get table nameNUM4:=InStr (STR1,'"',1,5);--get the position of the fifth "behind the ORA-01400.NUM5:=InStr (STR1,'"',1,6);--get the position of the sixth "behind the ORA-01400.STR3:=SUBSTR (str1,num4+1, NUM5-Num4-1);--Get field name           --get the corresponding field comment     SelectT.comments intoStr4 fromSYS. User_col_comments TwhereT.column_name=Str3 andT.table_name=str2; Result:=Str4||'cannot be empty, please enter the content.'; elsif InStr (Message,'ORA-01438')>0   ThenResult:=Str4||'the number length exceeds the limit, please check!'; ElseResult:=message; End if; return(Result);EndF_czl_geterror;

Then we use this function in the exception handling of the stored procedure, such as:

Exception      when  then     rollback ;     Dbms_output.put_line (F_czl_geterror (SQLERRM));     Raise_application_error (-20000, F_czl_geterror (SQLERRM));   

To test again, we got:

 - .

At least some users can understand it, we have achieved the goal.

Summary

The above describes how to convert the system exception information of Oracle to the information that the user understands. In fact, this is only a remedial measure, to some extent, is ' cheating ' the user, the program is normal operation, but it can not be denied that our program is still imperfect. However, Some remedies are better than none. Finally, if you have better suggestions, please feel free.

Processing of abnormal information of Oracle