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:
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
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