WebLogic. Wrapping data types.

Some time ago I faced the problem with WebLogic feature of wrapping complex Oracle data types. Let's say I have a stored procedure in the database:

create or replace procedure testArrayProc
(aID Number,
 aArray out TNumberTable
 )
and TNumberTable is a collection type with the following definition:

create or replace type TNUMBERTABLE as table of Number;

Please note, aArray is an out parameter. In my model I  have the following code to call the procedure:

        DBTransactionImpl dbti = (DBTransactionImpl)getDBTransaction();
        CallableStatement statement =
            dbti.createCallableStatement(("BEGIN " + "testArrayProc(?,?);" +
                                          "END;"), 0);
        try {
            statement.setLong(1, new Long(1));
            statement.registerOutParameter(2, Types.ARRAY, "TNUMBERTABLE");
            statement.execute();
            ARRAY ar = (ARRAY)statement.getArray(2);
            for (long i : ar.getLongArray()) {
                //do some work
            }

        } catch (SQLException sqlerr) {
            throw new JboException(sqlerr);
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException closeerr) {
                throw new JboException(closeerr);
            }
        }

The code used to work fine since I used JDBC URL connection type for my Application module. In such case I worked directly with Oracle JDBC drivers and everything was perfect. But when I created JDBC datasource on the WebLogic server and changed the connection type, I got the following exception in the same piece of code:
  weblogic.jdbc.wrapper.Array_oracle_sql_ARRAY cannot be cast to oracle.sql.ARRAY

Investigating the problem, I found out that WebLogic wraps complex data type parameters Arrays, Blobs, Clobs, NClobs, etc. with its own classes like weblogic.jdbc.wrapper.Array and for sure it can not be converted to oracle.sql.ARRAY. But my application as well as the code above are supposed to work not only on WebLogic. Other application servers (I've worked with) don't have any wrappers and allow the applications to work directly with drivers.
In WebLogic 10.3.4.0 (it comes as integrated server for JDeveloper 11.1.1.4.0) I found very useful checkbox Wrap Data Types. It is located at the Connection Pool tab of the datasources' settings in Advanced section. And it's possible to turn it off! According to the documentation this option allows features like debugging and connection usage to be done by the server, but turning it off can improve performance and in some cases significantly. Great! After turning the option off, my code started to work again.
Earlier versions of WebLogic don't have this wonderful checkbox, so you cannot switch the wrapping off (or may be you can, but I don't know how). But never mind, you always can modify the code to look like this:

        DBTransactionImpl dbti = (DBTransactionImpl)getDBTransaction();
        CallableStatement statement =
            dbti.createCallableStatement(("BEGIN " + "testArrayProc(?,?);" +
                                          "END;"), 0);
        try {
            statement.setLong(1, new Long(1));
            statement.registerOutParameter(2, Types.ARRAY, "TNUMBERTABLE");
            statement.execute();
            ARRAY ar = null;
            Object someArray = statement.getArray(2);
            if (someArray instanceof weblogic.jdbc.wrapper.Array)
                ar =
 (oracle.sql.ARRAY)(((weblogic.jdbc.wrapper.Array)someArray).unwrap(Class.forName("oracle.sql.ARRAY")));
            else
                ar = (oracle.sql.ARRAY)someArray;

            for (long i : ar.getLongArray()) {
                //do some work
            }

        } catch (SQLException sqlerr) {
            throw new JboException(sqlerr);
        } catch (ClassNotFoundException e) {
            throw new JboException(e);
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException closeerr) {
                throw new JboException(closeerr);
            }
        }

That's all. Thanks.

ADF BC. Working with Oracle collection data types

Introduction
ADF BC allows us to use Oracle object types in quite easy way. If your table contains attribute of Oracle object type, JDeveloper creates corresponding domain class automatically. Or you can create it manually using Create Domain Wizard. After that you can work with the attribute as if it had regular type. But!  If you need to work with collection type (like ".. as table of ..."), it works in a little bit different way.


Let's do it

I've got in my database the following sql type definition:

create or replace type varchar2_array_type as table of varchar2(200)

And the following table:
create table testarray (
 SomeField Number,
 ArrValue VARCHAR2_ARRAY_TYPE)
nested table ArrValue store as arrvalue_tab return as value;

In JDeveloper I created entity object on this table and payed some attention to the definition of ArrValue attribute.  In JDev 11.1.1.2.0 you will get this:

Have a look at the type of ArrValue attribute. It is String!!! I don't know why.
In JDev 11.1.1.4.0 you will get this:

JDev 11.1.1.4.0 seems to be a bit smarter as for working with collection types. But never mind if you're using JDev 11.1.1.2.0 or earlier.  Magic button "Browse" will help. You can manually choose needed type - oracle.jbo.domain.Array.

It works. Trust me.
After creating default view object and dropping it from the data control palette to page as an ADF Form, you will get this:

The default DomainArrayConverter represents our array as a comma separated string. If you don't like the default behavior, it is possible to show array values in a table. Have a look at the following jspx source code:

And you will get this:

You can also implement your custom JSF converter for Array type. For example the following converter represents  Array as multi-row string:

public class StringArrayConverter implements Converter{
 public StringArrayConverter() {
     super();
 }
    
 private Array getArray(String value) {
     String[] splittArray = value.split("\n");
     Array arr = new Array(splittArray);
     return arr;
 }

 public Object getAsObject(FacesContext context, UIComponent component,
                           String newValue) throws ConverterException {
    if ((newValue == null) || "".equals(newValue))
         return null;
     return getArray(newValue);
  }


 public String getAsString(FacesContext context, UIComponent component,
                           Object value) throws ConverterException {
     if (value == null)  return null;
 
     Array array = (Array)value;
     if (array.getSize() <= 0) return null;
     Object[] data = array.getArray();        
     StringBuffer result = new StringBuffer();
        if (data.length > 0) {
            result.append(data[0]);
            for (int i=1; i < data.length; i++) {
                result.append("\n");
                result.append(data[i]);
            }
        }
    return result.toString();
 }

Create inputText using this converter

And you will get this:

For sure using oracle.jbo.domain.Array is not limited by arrays of varchar2 or number. It allows us to work with really complex data type structures.