PL/SQL: Exceptions

I'm not really sure what I learned today, yet. Here's what I went through though.

I'm rebuilding/redesigning/refactoring a payment processing platform. It's complete with WHEN others...there is logging after the WHEN others, but no RAISE.

I was taught to use exceptions, which to me, meant using

raise_application_error( -20001, 'something went wrong' )

which meant that my calling PL/SQL had to use the PRAGMA EXCEPTION_INIT declaration. Not a big deal when it's 1 or 2 layers deep, but that's part of today's lesson (for me).

Exceptions were used in the code, they were slightly different though, just the

DECLARE
  some_exception EXCEPTION;
BEGIN
...

variety. It's way better than nothing and I believe they were headed in the right direction.

As I peel away the layers though, far too many errors are being caught with OTHERS. Bad. Bad. Bad.

Payment processing, being at the center of most everything, should, ney, must, blow up loudly if something unknown goes wrong. Before that ever goes live you should know about the vast majority of exceptions. Vast Majority to me means 99.9%.

By blowing up loudly, you don't have to rely on looking through error logs and you are far less likely to encounter strange behavior. If one pops up that you didn't account for, it's a quick code change to add that handling.

Of course much of this is predicated on having unit tests or other testing means available. Once of the first things I did was build about 80 test cases with SQLUnit. So I am fairly confident when I make changes that I haven't affected (much) the underlying code.

Finally, on to the exceptions.

There were 4 or 5 generic exceptions (other than OTHERS) defined. I wanted more though. So I began adding them in. Currently the code travels through about 5 levels of the candy cane forest, I mean, PL/SQL. In the lower most level, I used

raise_application_error( -20001, 'invalid card number (gateway)' );

Reran the tests and nothing showed up. I added an internal function to capture the error stack.

  FUNCTION error_stack RETURN VARCHAR2
  IS
    l_error_stack VARCHAR2(32767);
  BEGIN
    l_error_stack := dbms_utility.format_call_stack;
    l_error_stack := l_error_stack || dbms_utility.format_error_backtrace;
    l_error_stack := l_error_stack || dbms_utility.format_error_stack;
    
    RETURN l_error_stack;
  END error_stack;

so I wouldn't have to rewrite those 3 (long) lines over and over. I realize that you get an extra line in there, but I'll know to ignore it.

Rerun the tests and I can see the call stack with a reference to ORA-20001. I'm getting somewhere. That's when I realized that even if you throw an exception in that manner, if you have an exception block in the same block of code and a WHEN others, WHEN others will catch it. For some reason, I always thought it bypassed that current block of code, but then again, I've rarely used WHEN others.

One by one I began to remove the WHEN others from the calling layers. I created global exceptions:

invalid_card EXCEPTION;
PRAGMA EXCEPTION_INIT( invalid_card, -20001 );

, removed WHEN others and created a new exception block in the top-most procedure. Perfect!

I rerun the tests and the error propogates all the way to the top (as it should, I just wasn't used to it). Tests begin to work again and I'm all set to go. Win!

For more on exception handling in PL/SQL, go here for 10gR2, here for 11gR1, and here for 11gR2.

SQL Objects, JDeveloper, JPublisher and Java

This is one for my application developer friends.

Say your application uses stored procedures. Not just any stored procedures, but ones that accept arrays or SQL Object types (User Defined Types - UDTs).

One thing you have to do is map a java object to that SQL Object before you can pass it back to the stored procedure. I can't remember the specifics, but I can show you how to "automatically" create the Java code that maps to the SQL Object.

First, create type UDT.

If will be a table of a record...is it called a multi-dimensional array? Whatever.

CREATE TYPE r_record IS OBJECT
(
  id NUMBER(10),
  col1 VARCHAR2(30),
  col2 VARCHAR2(30),
  col3 VARCHAR2(30)
)
/

CREATE TYPE t_table AS TABLE OF R_RECORD
/

I'll create a procedure that accepts T_TABLE as an input parameter. It won't do anything, but just to give you an idea.

CREATE OR REPLACE
PROCEDURE test( p_t_table IN T_TABLE )
IS
BEGIN
  NULL;
END test;
/

Now go into JDeveloper and through the Database Navigator, select the schema you placed these objects.



Right click on the object and select Generate Java (as shown above).

You'll then be presented with an options menu:



It's all foreign to me so I accept the defaults. I do notice however that it's pretty customizable, for instance, you can select how you want to Number Types to be generated, either objectjdbc, oracle, jdbc or bigdecimal. You'll know better about what this means than I will.



From that, you get 3 files, TTable.java which maps to the TABLE OF R_RECORD, RRecord.java which maps to the SQL Type R_RECORD, and finally RRecordRef.java...which I don't really know what it does. I'm sure you will though.

Here's the code generated, in order.

TTable.java

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.jpub.runtime.MutableArray;

public class TTable implements ORAData, ORADataFactory
{
  public static final String _SQL_NAME = "CJUSTICE.T_TABLE";
  public static final int _SQL_TYPECODE = OracleTypes.ARRAY;

  MutableArray _array;

private static final TTable _TTableFactory = new TTable();

  public static ORADataFactory getORADataFactory()
  { return _TTableFactory; }
  /* constructors */
  public TTable()
  {
    this((RRecord[])null);
  }

  public TTable(RRecord[] a)
  {
    _array = new MutableArray(2002, a, RRecord.getORADataFactory());
  }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    return _array.toDatum(c, _SQL_NAME);
  }

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  {
    if (d == null) return null;
    TTable a = new TTable();
    a._array = new MutableArray(2002, (ARRAY) d, RRecord.getORADataFactory());
    return a;
  }

  public int length() throws SQLException
  {
    return _array.length();
  }

  public int getBaseType() throws SQLException
  {
    return _array.getBaseType();
  }

  public String getBaseTypeName() throws SQLException
  {
    return _array.getBaseTypeName();
  }

  public ArrayDescriptor getDescriptor() throws SQLException
  {
    return _array.getDescriptor();
  }

  /* array accessor methods */
  public RRecord[] getArray() throws SQLException
  {
    return (RRecord[]) _array.getObjectArray(
      new RRecord[_array.length()]);
  }

  public RRecord[] getArray(long index, int count) throws SQLException
  {
    return (RRecord[]) _array.getObjectArray(index,
      new RRecord[_array.sliceLength(index, count)]);
  }

  public void setArray(RRecord[] a) throws SQLException
  {
    _array.setObjectArray(a);
  }

  public void setArray(RRecord[] a, long index) throws SQLException
  {
    _array.setObjectArray(a, index);
  }

  public RRecord getElement(long index) throws SQLException
  {
    return (RRecord) _array.getObjectElement(index);
  }

  public void setElement(RRecord a, long index) throws SQLException
  {
    _array.setObjectElement(a, index);
  }
}

RRecord.java

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import oracle.jpub.runtime.MutableStruct;

public class RRecord implements ORAData, ORADataFactory
{
  public static final String _SQL_NAME = "CJUSTICE.R_RECORD";
  public static final int _SQL_TYPECODE = OracleTypes.STRUCT;

  protected MutableStruct _struct;

  protected static int[] _sqlType =  { 2,12,12,12 };
  protected static ORADataFactory[] _factory = new ORADataFactory[4];
  protected static final RRecord _RRecordFactory = new RRecord();

  public static ORADataFactory getORADataFactory()
  { return _RRecordFactory; }
  /* constructors */
  protected void _init_struct(boolean init)
  { if (init) _struct = new MutableStruct(new Object[4], _sqlType, _factory); }
  public RRecord()
  { _init_struct(true); }
  public RRecord(java.math.BigDecimal id, String col1, String col2, String col3) throws SQLException
  { _init_struct(true);
    setId(id);
    setCol1(col1);
    setCol2(col2);
    setCol3(col3);
  }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    return _struct.toDatum(c, _SQL_NAME);
  }


  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  { return create(null, d, sqlType); }
  protected ORAData create(RRecord o, Datum d, int sqlType) throws SQLException
  {
    if (d == null) return null;
    if (o == null) o = new RRecord();
    o._struct = new MutableStruct((STRUCT) d, _sqlType, _factory);
    return o;
  }
  /* accessor methods */
  public java.math.BigDecimal getId() throws SQLException
  { return (java.math.BigDecimal) _struct.getAttribute(0); }

  public void setId(java.math.BigDecimal id) throws SQLException
  { _struct.setAttribute(0, id); }


  public String getCol1() throws SQLException
  { return (String) _struct.getAttribute(1); }

  public void setCol1(String col1) throws SQLException
  { _struct.setAttribute(1, col1); }


  public String getCol2() throws SQLException
  { return (String) _struct.getAttribute(2); }

  public void setCol2(String col2) throws SQLException
  { _struct.setAttribute(2, col2); }


  public String getCol3() throws SQLException
  { return (String) _struct.getAttribute(3); }

  public void setCol3(String col3) throws SQLException
  { _struct.setAttribute(3, col3); }
}

RRecordRef.java

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.REF;
import oracle.sql.STRUCT;

public class RRecordRef implements ORAData, ORADataFactory
{
  public static final String _SQL_BASETYPE = "CJUSTICE.R_RECORD";
  public static final int _SQL_TYPECODE = OracleTypes.REF;

  REF _ref;

private static final RRecordRef _RRecordRefFactory = new RRecordRef();

  public static ORADataFactory getORADataFactory()
  { return _RRecordRefFactory; }
  /* constructor */
  public RRecordRef()
  {
  }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    return _ref;
  }

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  {
    if (d == null) return null;
    RRecordRef r = new RRecordRef();
    r._ref = (REF) d;
    return r;
  }

  public static RRecordRef cast(ORAData o) throws SQLException
  {
     if (o == null) return null;
     try { return (RRecordRef) getORADataFactory().create(o.toDatum(null), OracleTypes.REF); }
     catch (Exception exn)
     { throw new SQLException("Unable to convert "+o.getClass().getName()+" to RRecordRef: "+exn.toString()); }
  }

  public RRecord getValue() throws SQLException
  {
     return (RRecord) RRecord.getORADataFactory().create(
       _ref.getSTRUCT(), OracleTypes.REF);
  }

  public void setValue(RRecord c) throws SQLException
  {
    _ref.setValue((STRUCT) c.toDatum(_ref.getJavaSqlConnection()));
  }
}

How do you integrate all that? I'm not really sure, but Marc recently wrote up a piece on using this method to tie into SQLUnit, pretty nice one too.

SQLUnit: Database Unit Testing

What feels like a thousand years ago I used SQLUnit to do create and perform unit tests on database stored procedures. Lots of them. To the point where that's almost all I was doing. I both hated it and loved it. Hated it because it was boring, tedious, mind-numbing work. Loved it because it gave me confidence in the code I was writing and, once all the tedium was done, could be done quickly. A small change could be immediately tested to see it's effect.

Something else happened too, I began to write code thinking about the tests. Every single line of code was a potential test. If I do this, how will I test it? If I do that, how will I test it? Where do the tests end? You could theoretically test forever...and then I would inevitably fall down that rabbit hole.

Like I was saying, it changed the way I wrote code. Test Driven Development without writing the tests first (which I can hardly stand). That was a good thing because I began to anticipate certain errors. On an INSERT statement with a sequence generated Primary Key, should I trap the exception in code and give it my own error number? Or just let the ORA-00001 propagate up? I just let the Oracle error come up, no need to create my own for that. There's something seriously wrong if you get an error in that case and the whole world should know about it.

Speaking of exceptions, use them, throw them, don't trap them (mostly) and do something else. Log it and then RAISE it. I think it's good for every single developer to know when something is messed up. I've spent far too much time trying to debug something because all the errors are being trapped and processing just continues. I've been lucky on some occasions to have error logging, at least...

What is SQLUnit?

SQLUnit is a regression and unit testing harness for testing database stored procedures. An SQLUnit test suite would be written as an XML file. The SQLUnit harness, which is written in Java, uses the JUnit unit testing framework to convert the XML test specifications to JDBC calls and compare the results generated from the calls with the specified results.

Unfortunately it has not been worked on in almost 3 years, but I'd say it's in pretty good shape. There's support for Procedures, Functions, Ref Cursors and User Defined Types (though I haven't tried this out yet). The current version is 5.0. The last version I worked on consistently was 1.3 I believe. A colleague (who now works for Oracle) from my early days of SQLUnit testing even contributed some Oracle specific items.

I used it to build the database for CABEZE, my first, unsuccessful, try at my own business. It was nice because I was building it from scratch, so I could build all the test data along with it (no testing with production data...though there wasn't really much of that anyway), set up the database (create test data), run the tests and then tear it down back to it's original state (empty). Unfortunately the systems I've worked on since weren't empty and testing with production, or semi-production (cleansed) data was the only viable alternative.

Which brings me to now. I'm trying to reacquaint myself with the tool and writing test cases for various procedures. I ran into an issue create test cases because all of our credit card numbers were scrambled. Everything failed with an Invalid Card Number. Barnacles.

Why not create a routine that would generate "real" credit card numbers, more specifically, numbers of the appropriate length that had a check digit? So credit cards use the Luhn formula which is supposed to prevent certain transposition errors.

The Luhn algorithm will detect any single-digit error, as well as almost all transpositions of adjacent digits. It will not, however, detect transposition of the two-digit sequence 09 to 90 (or vice versa). It will detect 7 of the 10 possible twin errors (it will not detect 22 ↔ 55, 33 ↔ 66 or 44 ↔ 77).

With CABEZE, I had written my own PL/SQL card number generator (and validator), but I didn't blog at the time and seem to have lost that code. So I tried writing it again.

Since I'm using SQLUnit, what a great way to demonstrate the power of the tool. It's a somewhat complex (to me anyway) formula, so writing up tests for it should help me to figure it out. Here's the code to create a check digit based on the Luhn formula.

CREATE OR REPLACE
FUNCTION create_check_digit( p_card_number IN NUMBER ) RETURN NUMBER
IS
  TYPE t_digits IS TABLE OF INTEGER;
  l_table T_DIGITS := T_DIGITS();
  l_count INTEGER := 0;
  l_num INTEGER;
  l_digit INTEGER;
  l_odd INTEGER := 0;
  l_even INTEGER := 0;
  l_sum INTEGER := 0;
  l_check_digit INTEGER;
BEGIN
  IF p_card_number IS NULL THEN
    raise_application_error( -20001, 'you must provide a card number' );
  END IF;

  FOR i IN REVERSE 1..LENGTH( p_card_number ) LOOP
    l_count := l_count + 1;
    l_table.EXTEND(1);
    l_table( l_count ) := SUBSTR( p_card_number, i, 1 );
  END LOOP;

  FOR i IN 1..l_table.COUNT LOOP
    l_digit := l_table(i);
    IF MOD( i, 2 ) > 0 THEN
      l_num := l_digit * 2;
      IF l_num > 9 THEN
        FOR i IN 1..LENGTH( l_num ) LOOP
          l_odd := l_odd + SUBSTR( l_num, i, 1 );
        END LOOP;
      ELSE
        l_odd := l_num;
      END IF;
      p( 'odd:  ' || l_odd );
    ELSE
      l_even := l_digit;
    END IF;
    l_sum := l_sum + l_odd + l_even;
    p( 'l_sum:  ' || l_sum );
    l_odd := 0;
    l_even := 0;
  END LOOP;
  l_check_digit := ABS( ( CEIL( MOD( l_sum / 10, 10 ) ) * 10 ) - l_sum );
  p( 'check digit:  ' || l_check_digit );
  p( 'l_sum:  ' || l_sum );
  p( p_card_number || l_check_digit );
  RETURN l_check_digit;
END create_check_digit;
/

I have no doubt it could be done easier, specifically using regular expressions. This is just my first go at it...so don't give me a hard time...any other solutions are welcome in the comments. ;)

Here's the output of my final test run:

[sqlunit] *** Running SQLUnit file: p_cc.xml
[sqlunit] Getting connection(DEFAULT)
[sqlunit] Setting up test...
[sqlunit] Running test[1]: PASSING NULL (125ms)
[sqlunit] Running test[2]: VALID CARD NUMBER (4992739871) (15ms)
[sqlunit] Running test[3]: VALID CARD NUMBER (4012888888881881) (16ms)
[sqlunit] Running test[4]: VALID CARD NUMBER (4111111111111111) (0ms)
[sqlunit] Running test[5]: VALID CARD NUMBER (4222222222222) (15ms)
[sqlunit] Running test[6]: RANDOM (1) NUMBER (5) (0ms)
[sqlunit] Running test[7]: RANDOM (2) NUMBER (55) (0ms)
[sqlunit] Running test[8]: RANDOM (3) NUMBER (557) (16ms)
[sqlunit] Running test[9]: RANDOM (4) NUMBER (5579) (0ms)
[sqlunit] Running test[10]: RANDOM (5) NUMBER (65579) (0ms)
[sqlunit] Running test[11]: RANDOM (14) NUMBER (12345678965579) (16ms)
[sqlunit] Running test[12]: RANDOM NUMBER (5498975) (0ms)
[sqlunit] Tearing down test...

I was able to run a variety of different tests in seconds. A previous test run looked like this:

[sqlunit] *** Running SQLUnit file: p_cc.xml
[sqlunit] Getting connection(DEFAULT)
[sqlunit] Setting up test...
[sqlunit] Running test[1]: PASSING NULL (109ms)
[sqlunit] Running test[2]: VALID CARD NUMBER (4992739871) (109ms)
[sqlunit] Assertion "outparams-equal" failed (6(NUMERIC) != 1(NUMERIC) at outparams[0])
[sqlunit] *** expected:
[sqlunit] 
[sqlunit]   6
[sqlunit] 
[sqlunit] *** but got:
[sqlunit] 
[sqlunit]   1
[sqlunit] 
[sqlunit]
[sqlunit] Running test[3]: VALID CARD NUMBER (4012888888881881) (0ms)
[sqlunit] Running test[4]: VALID CARD NUMBER (4111111111111111) (0ms)
[sqlunit] Running test[5]: VALID CARD NUMBER (4222222222222) (0ms)
[sqlunit] Assertion "outparams-equal" failed (2(NUMERIC) != 0(NUMERIC) at outparams[0])
[sqlunit] *** expected:
[sqlunit] 
[sqlunit]   2
[sqlunit] 
[sqlunit] *** but got:
[sqlunit] 
[sqlunit]   0
[sqlunit] 
[sqlunit]
[sqlunit] Running test[6]: RANDOM NUMBER (5498975) (0ms)
[sqlunit] Tearing down test...
[sqlunit] sqlunit-ant: SQLUnit Tests Failed: In file: p_cc.xml, tests: 6, failures: 2, errors = 0
[sqlunit] SQLUnit Tests Failed: In file: p_cc.xml, tests: 6, failures: 2, errors = 0

I could then identify the problem, fix it, and run the tests within seconds. How cool is that?

That particular run helped me realize that if I passed in a string of numbers that was even, it would give me the wrong result. If you look at Visa for instance, it's typically 16 digits long with the final digit being the check digit. You start at the rightmost digit before the check digit and go backwards. That was the impetus behind putting the digits into the PL/SQL TABLE OF INTEGERS up above.

I'll post more on creating validly formatted credit card numbers later, I just wanted to use it as an example for SQLUnit.

Try it. Use it. Let me know how it goes. Maybe we can get some Java guy to get it started up again.