Problem solve Get help with specific problems with your technologies, process and projects.

Catching error code that arises in PL/SQL

In my PL/SQL, I often use this construction:

  When Dup_val_on_index Then ...
  When Others Then
    XYZ := To_Char(SQLCODE);
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

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

Dig Deeper on Using Oracle PL-SQL