OrindaBuild is now called "JDBCWizard"...

For years we've been struggling with the fact that although we produce a very useful product describing what it does in one sentence can sometimes be hard. A part of the problem has been the name we chose - "OrindaBuild". It was deliberately intended to be slightly ambiguous as the advice we'd received on trade marks was that it's easier to trademark something if it doesn't literally describe the function of the product in question.

The problem is that nobody who hears "OrindaBuild" has the slightest idea what the product actually does.

So we're renaming it to "JDBCWizard" as of today. As the name suggests it's a wizard, for JDBC. It works with Oracle and creates a Java class to run your SQL statements and PL/SQL. You no longer need to be an expert in Oracle and PL/SQL to use a database.

Passing XMLType to JDBC

We're currently working on adding XMLType support to OrindaBuild. We've found that there are a couple of 'gotchas' anyone working in this area would want to watch for.

JDBC doesn't have a direct representation of XMLType. Instead we use the wonderfully obscure OPAQUE data type to represent it internally. Oracle doesn't provide a vast array of documentation. The most useful ones would be:

• 
  The "Java DOM for API for XMLType" chapter in the XMLDB Manual.


• 
  The "XMLTYPE" entry in the PL/SQL Packages and Types Reference.
• The "Oracle XMLType" Javadoc


• 
  Oracle's "How to Obtain an XMLType from JDBC" code snippet.
  

Passing XMLType in...

In order to pass an XMLType in you have to bind it as an Opaque. Now oracle.xdb.xmltype extends oracle.sql.OPAQUE so either setOPAQUE or setObject should work fine. In practice this is only true until you try passing a null XMLType. You then hit a problem. setOPAQUE doesn't work for a null value - you a message which says:

ORA-17004 java.sql.SQLException:Invalid column type: sqlType=2007

This is because OPAQUE has some very odd null semantics - setNull only works if you say what kind of null it is:

theOraclePreparedStatement.setNull(i

                         , OracleTypes.OPAQUE

                         , myOpaqueThing);

Getting XMLType out...

 
If dealing with a cursor you can use getObject:

XMLType myXml= (XMLType)myResultSet.getObject(1);

Otherwise you need to use getOPAQUE and specify what kind of OPAQUE you're expecting:

theCallableStatement.registerOutParameter

(i+1,parameterTypeArray[i],"SYS.XMLTYPE");

Converting PL/SQL Calls from Delphi to Java and JDBC

We recently had a support call from a customer who was migrating legacy code from Delphi and PL/SQL to Java and PL/SQL. Doing this isn't that easy - while you can search for a utility to help you do this, the best you are likely to find are utilities that convert Delphi to C#, and then C# to Java.

Apart from the inherent ugliness of this your converted code won't be able to speak to Oracle properly because the JDBC API is different from both Delphi and C#, so your database API calls will be nonsensical.

Even if you decide to convert your Delphi application to Java by re-coding you can still hit issues. While OrindaBuild will happily generate JDBC calls for PL/SQL and SQL you need to watch for unforeseen and sometimes unreasonable situations.

Our customer's problem was that in the Delphi Oracle API there is no support for PL/SQL parameters which are only "OUT", as opposed to "IN" or "IN OUT". As a result all their PL/SQL had been written to use "IN OUT" even in cases where the parameter in question was only an "OUT" parameter. Given that they had over 1200 PL/SQL procedures re-writing the PL/SQL wasn't an attractive option.

When they used OrindaBuild to generate a layer of Java to run their PL/SQL they hit a bug - generated code was breaking because it never occurred to us that anyone would want to pass a REF CURSOR as an "IN" parameter while working with JDBC. While this is supported if you are within PL/SQL the JDBC driver doesn't allow you to bind cursors as input parameters, so we had treated it as an error in the product.

At the request of the customer we tweaked OrindaBuild so that if a REF CURSOR parameter is defined as "IN OUT" the generated code works by passing in an empty string bound to null as a stub. If you are converting legacy Delphi or C++ to Java you should expect to encounter oddballs situations like this.

PL/SQL Anonymous Blocks and Java

An 'Anonymous Block' is a PL/SQL procedure that doesn't have a name, doesn't directly take parameters and isn't persisted in the database. In this post we'll explain why it's a useful construct if you are working with PL/SQL, Java and JDBC

This is an example of an Anonymous Block:

 DECLARE 

 -- 

 -- 

 -- First and Second input params are name and number 

 -- 

 l_name emp.ename%TYPE := ?; 

 l_number emp.empno%TYPE := ?; 

 -- 

 l_howmany NUMBER; 

 -- 

 BEGIN 

 -- 

 INSERT INTO emp 

 (empno, ename) 

 VALUES 

 (l_name, l_number); 

 -- 

 COMMIT; 

 -- 

 SELECT COUNT(*) 

 INTO l_howmany 

 FROM emp; 

 -- 

 -- Third param is an output one - number of records in table. 

 -- 

 ? := l_howany; 

 -- 

 END; 

Note how the above block has all the attributes of a stored procedure except a name and parameters. We can still use JDBC bind variables though, as long as they as used as values and targets for assignments,

To call an Anonymous Block of PL/SQL from Java you simply use a CallableStatement.

Note: On May 17th 2010 OrindaBuild was renamed to JDBCWizard