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;

Date_var := to_date('234w', 'mmyy')

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.

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.