EXPERT RESPONSE
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
|
