Symptom

Early versions of Oracle thin client drivers do not support Process Commander.  These thin drivers do not support the JDBC specification for Binary Large Objects (BLOB – or Stream).

Process Commander relies on the database driver to provide services related to passing data to and from the database.  Given the data-driven architecture of Process Commander,  the reliability of these services is critical if applications built in Process Commander are to function.

In their Metalink documentation, Oracle has stated that Type 4 (“thin client”) drivers do not work with Oracle 9i: See:

The JDBC specifications define types for various database columns, including the BLOB column.  In this Metalink article, Oracle maintains that the JDBC 2.0 specification was vague regarding support of BLOB columns.  Oracle states that it is possible to read and write to LOBs if you are using the Oracle JDBC OCI driver (the Type 2 “thick client” driver), and there will be no problem.  However, if you use the Oracle JDBC (Type 4) Thin Driver, there is a documented size limit of 4KB for writing LOBs to the database.  Since Process Commander frequently needs to write BLOB entries which are larger than 4KB, this limitation will cause problems in a Process Commander installation.

Note that although Oracle asserts in this article that the 10.1 driver has completely fixed this problem, there is an additional note in the Oracle README for this version:

 http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_readme101020.html

Excerpt from this file:

There is a limitation regarding the use of stream input for LOB types. Stream input for LOB types can only be used for 8.1.7 or later JDBC OCI driver connecting to an 8.1.7 or later Oracle server. The use of stream input for LOB types in all other configurations may result in data corruption. PreparedStatement stream input APIs include: setBinaryStream(), setAsciiStream(), setUnicodeStream(), setCharacterStream() and setObject().

Solution

Workaround:

Oracle 9i:

The Oracle thick client drivers are required.

Oracle 10g:

Update: As of July 2006, the thin client driver for the 10g version of Oracle connected to an Oracle 10g database has successfully passed Pegasystem's testing in Test Engineering and the Performance Lab.  Based on these tests, thin client drivers for 10g now are accepted to work successfully with Process Commander. 

Article originally written 7/13/2006.