Catching error code that arises in PL/SQL
In my PL/SQL, I often use this construction:
Begin
....
Exception
When Dup_val_on_index Then ...
When Others Then
XYZ := To_Char(SQLCODE);
...
End;
Is there any way to "catch" error code that has arisen in PL/SQL? For example, in the statement:
Date_var := to_date('234w', 'mmyy')
The only thing I can do is to declare all possible exceptions, associate them to ORA errors, and check for every one of them. It would be enough for me to check for others and display real error code.
You're doing it the way you need to. For every error condition your program may generate, you want to make sure to create an appropriate error handler. The "catch all" is WHEN OTHERS. If you simply want to display the actual error message, then using only a WHEN OTHERS exception handler will suffice. If you want to trap an error and have the code "fix" itself in the case of certain errors, you'd need to identify the errors individually.
The specific example you give is an error generated by passing an invalid value to the to_date function. If a developer hard-coded such a thing it should be caught immediately in testing and corrected to not occur. But, if you're using this as an example of a value a user might type in and then get passed into the call to to_date, I'd suggest that you'd need code some data validation checks on the values the user is entering before passing to the function. For example:
SQL> declare
2 date_var varchar2(10);
3 user_entry varchar2(4) ;
4 date_err exception ;
5 begin
6 user_entry := '&user_input_value' ;
7 if (substr(user_entry,1,2) not between 1 and 12) OR
8 (substr(user_entry,3,2) not between 0 and 99) then
9 raise date_err ;
10 end if ;
11 date_var := to_date(user_entry, 'mmyy') ;
12 dbms_output.put_line(date_var) ;
13 exception
14 when date_err then
15 dbms_output.put_line('You must enter a valid date in MMYY format.') ;
16* end ;
SQL> /
Enter value for user_input_value: 1299
01-DEC-99
PL/SQL procedure successfully completed.
SQL> /
Enter value for user_input_value: 234w
You must enter a valid date in MMYY format.
PL/SQL procedure successfully completed.
See how the 234w is caught by the validation? Now, this code is not meant to be the only way or the "correct" way to validate an entry. It was just a quick way for me to show you what I mean.
Hope that helps.
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
