21st Century PL/SQL

January 27, 2005

Exception a datatype like any other

Let us manipulate an exception as we would any other variable or perhaps object type: pass it as an argument, add other attributes to the variable, etc.

Posted by sf at 11:15 PM | Add your thoughts (0)

January 30, 2005

A 'finally' section like in Java

It would be very useful to have a section like the "finally" construction in Java. So you'd have a block of the form

BEGIN
...
EXCEPTION
...
FINALLY
...
END

where the code in the final section is always executed, regardless of how the block is exited. Often the EXCEPTION section would not be needed, as currently it is necessary to handle an exception just to do some clean up and then re-raise it.

Posted by Chris Rimmer at 03:02 PM | Add your thoughts (6)

June 30, 2005

Unit Execution Metadata

Since my first days with PL/SQL in 1995, I have yearned for four missing built-in features. The first was finally satisfied with 9iR2 DML with records. The remaining three have yet to be resolved properly and would improve the error handling component of the PL/SQL frameworks every good shop builds: current_line_number(), line_number_at_error() and what_is_my_name().

My Gripe:
Until 10g, adding an exception handler practically defeated the purpose because it would hide or obliterate the actual line where the error occurred. If the error were particularly difficult, one had to comment out the EXCEPTION section in order to finally have Oracle report which line it died on. In order to keep exception handlers in production code, workarounds generally included a local variable in every routine that gets a new value just before each major statement; a breadcrumb, if you will, that was passed to the error handling component of our PL/SQL framework. That is just uncalled for and should have been fixed back in 7.3.

With 10g, the line number of the culprit can finally be had through the new DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, but that still means that every shop has to write their own component that parses the backtrace and gets the line number. It just should not be this hard.

It is still not possible for packaged routines to reliably determine their own names. For this reason, every shop I have been in has the standard of each package and each routine having a variable that contains its own name, so that it can report on itself to the error handling/logging component of the framework. This is kludgy and error prone since module names can and do frequently change. Again, it just should not be this hard.

In C, PERL and other languages, it is very easy to get the current line number during execution, the last line executed (where the error occurred) and the name of a given module.

Suggestion:
Much like one can get metadata (%NOTFOUND, %ROWCOUNT) about cursors given a handle to the cursor, PL/SQL should provide a handle to the containing package and the current routine which can report on themselves. Perhaps provide a SELF and PKG built-in function or handle that can then be read for things like line number and name. Just imagine:

EXCEPTION
  WHEN lx_time_travel THEN
     arch_excp.log_cont(SELF%METADATA,
       TO_CHAR(i_end_dt,dates.UI_DTMMASK)||' is in the future. '||
       'We will assume you meant COB today, and continue.'
     );

-- then in arch_excp.log_cont, you'd see something like this
  arch_log.ins_msg(i_caller.pkg_name, i_caller.name, i_caller.line_at_error(),
     i_msg, SYSTIMESTAMP, arch_sec.get_user_id
  );
END myproc;

No name literals! Self-maintaining! Accurate! No commenting out the exception section to debug! Ah, that would be the life!

Posted by Bill Coulam at 06:57 PM | Add your thoughts (4)

October 06, 2005

Complete error information, for example in procedure parameter, error

There are several errors requiring you to go to get further information for example doing dbms_output the idea is to include all the information in the error message. Give a check to all errors and those that requires you to go sqlplus to do a query, must be improved :) You send a parameter to a function to big or incorrect The error should be, You sent a value too large (or incorrect), the parameter is xxx. It takes a lot of work to find thta incorrect parameter when you have more than 20.

Posted by Juan Carlos Reyes at 01:48 PM | Add your thoughts (1)

November 18, 2005

print procdure name for call stack

When you do dbms_utility.format_call_Stack or in 10g with the extend format . it would be nice the have to procedure/function name for auditing and debuging support.

Posted by amihay gonen at 03:27 AM | Add your thoughts (0)