How did I do that?

One of my goals in this blog is documenting things I do frequently enough that they're easy to do, yet not so frequently that I can do it without a quick reference check.

So, here's the start of a growing list.

My last post discussed the REXX programming language.  This time I'll discuss REXX's exception handling as a segue to two Oracle PL/SQL functions.

REXX's exception handling includes the signal on statement to trap specific exceptions, and the SIGL variable stores the program's line number where the exception occured.  Here's an example:

/* Demonstrate Rexx error handling*/
signal on syntax

say ' 50 / 0  = ' 50 / 0

exit 0

/* Error handler */
syntax:
  Say 'Error on line' SIGL':' sourceline(SIGL) 
  exit 1

Execute this code, and we get the line number where the error occurred and the sourceline function returns the program source, too:

tom@Pavilion-dv8000:~$ rexx error.rx
Error on line 4: say ' 50 / 0  = ' 50 / 0

This is really useful, and not all runtime environments are so friendly when you do something foolish. The COBOL runtime library would print the address where the machine code failed. Not very friendly, but at least you could use the offset to search thru the program listing to find where the error occured.

Some runtime environments do even less. Oracle's PL/SQL language supports exception handling, but the pre-11g database did not include a way to determine the line number of the exception.  If you found yourself maintaining code that was hundreds to thousands of lines long, tracking down the exception could be quite challenging.  Thankfully, Oracle 11g includes a new function, dbms_utility.format_error_backtrace,that returns the line number where the exception occurred.  Here's a quick example using the divide by zero problem:

declare
    x integer := 10;
    y integer := 0;
    z integer := null;
begin
    z := x / y;
   
    dbms_output.put_line('Program never gets here.');
exception 
    when others then 
    dbms_output.put_line(sqlerrm);
    dbms_output.put_line(dbms_utility.format_error_backtrace());
    
end;

Execute this code in SQLPLUS or or as an APEX script, and you get the following message:

ORA-01476: divisor is equal to zero
ORA-06512: at line 6

Statement processed. 0.01 seconds

The example is trivial, but knowing exactly where things went wrong is really useful when there are lots of places for code to go wrong.

Another useful Oracle function is dump. Have you ever wondered what was stored internally for an international character? Or have you ever wondered why two strings that looked the same, weren't the same? The dump function displays the internal coding of a character or a string. If you need to figure out why 'string' is not equal to 'string ', dump can help. Here's an example; notice the second parameter. The '16' indicates that we want the data displayed as hex numbers. The '1016' indicates that we want hex numbers and character set.

select dump('string',16), dump('string ',1016) 
from dual;

Execute this example, and you'll see the following:

DUMP('STRING',16)               DUMP('STRING',1016)
Typ=96 Len=6: 73,74,72,69,6e,67 Typ=96 Len=7 CharacterSet=AL32UTF8: 73,74,72,69,6e,67,20

Now it's easy to see that the second 'string' has an extra character space. Also, we can see that this database (hosted at apex.oracle.com) is using UTF8 character encoding.