Wednesday, March 10, 2010

Trying to use Oracle Object types, Record types and PL/SQL tables in Apache iBatis

We recently came across a thread on the Sun JDBC forum An iBatis user was trying to call a stored procedure that took a %ROWTYPE parameter:

Does iBatis support Oracle tableType or Oracle Record type as input parameter to stored procedure?


Our advice was simple but probably not helpful:

Oracle's JDBC driver has no concept or understanding of this (%ROWTYPE), so it's not a question of picking the right data type to bind. It's simply not possible to pass in a %ROWTYPE record as a parameter directly, so any technology (such as iBatis) that relies on the JDBC driver won't be able to do this.



This is a problem which will afflict any open source technology that isn't willing to optimize its implementation. The driver has no concept of %ROWTYPE, so you can either rewrite the procedure to take scalar parameters or do what OrindaBuild does which is this:

public String 
getProcCallStatement()

   {                    

   if (procCall == null)

     {                        

     procCall = new StringBuffer("DECLARE \n"); // 1

     procCall.append("/* Created  By OrindaBuild 6.0.2699 */ \n"); // 2

     procCall.append("/* Which can be obtained at www.orindasoft.com */ \n"); // 3

     procCall.append("p_row FOO%ROWTYPE; \n"); // 4

     procCall.append("BEGIN  \n"); // 5

     procCall.append("p_row.text_col := ?; \n"); // 6

     procCall.append("p_row.date_col := ?; \n"); // 7

     procCall.append("p_row.num_col := ?; \n"); // 8

     procCall.append(" \n"); // 9

     procCall.append("FOOPROC(p_row); \n"); // 10

     procCall.append("END; "); // 11 192 characters

    

    }    

   return(procCall.toString());



  } 


The above code takes 3 scalar parameters, creates a record, and then passes it to PL/SQL, thus removing the need to modify the procedure in question. We plan on doing a much more detailed blog post on this in the future.

We also took a look at iBatis itself. As platform-neutral solutions go it's ok, but we think there's an awful lot of typing XML involved. To be honest we're pretty happy as OrindaBuild does pretty much everything iBatis does without any XML whatsoever, let alone the 'war and peace' descriptions of SQL statements and their parameters that iBatis runs on.


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


2 comments:

Andreas Boos said...

Hi.

This implementation of using %ROWTYPE lets you use parameters of type OUT / INOUT?

In our tests, I could use to send data, but in some PROCEDURES our parameter %ROWTYPE is an IN/OUT and are not returning the modified values in PL / SQL.

Is there anything else that should be specified to work?

David Rolfe said...

Did you read the modified values back afterwords? If you only bound them as input variables you wouldn't see any changes.

In the example above you'd see extra lines like:

? := p_row.text_col ;
? := p_row.date_col ;
? := p_row.num_col ;