Custom type handler for a blob inside an Oracle STRUCT

[Ron van Weverwijk]

For my current project I need to call a PL/SQL function with the following
input type:
 
CREATE OR REPLACE
type prodvar_tabtype
as table of prodvar_rectype
/
 
CREATE OR REPLACE
type prodvar_rectype as object
  (
      loginnaam           varchar2(8)
  ,   reden_aanlevering   varchar2(1)
  ,   product_naam        varchar2(200)
  ,   msds_file_naam      varchar2(200)
  ,   msds_file           blob
  ,   comp_file_naam      varchar2(200)
  ,   comp_file           blob
  )
/
 
So this is a table type with record types.
 
I have written a CustomTypeHandler to convert an ArrayList to this database
types like this:
 
public class ProductAanvraagTypeHandler implements TypeHandlerCallback {
 
    public void setParameter(ParameterSetter setter, Object parameter) {
        if (parameter instanceof List) {
            try {
                Connection conn =
((PoolableConnection)setter.getPreparedStatement().getConnection()).getDelegate();
                
                final Object[] attributes = 
                    new
Object[((List<ProductAanlevering>)parameter).size()];
                int i = 0;
                for (ProductAanlevering productAanlevering :
(ArrayList<ProductAanlevering>)parameter) {
                    attributes[i++] =
mapProductAanlevering(productAanlevering, conn);
                }
                ARRAY table = CustomSqlMapClient.getArray("PRODVAR_TABTYPE",
conn, attributes);
                    int paramIndex = setter.getParameterIndex();
                setter.setObject(table);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
 
    private STRUCT mapProductAanlevering(ProductAanlevering
productAanlevering, Connection conn) throws SQLException {
        
        final Object[] attributes = new Object[7];
        attributes[0] = productAanlevering.getLoginnaam();
        attributes[1] = productAanlevering.getRedenAanlevering();
        attributes[2] = productAanlevering.getProductNaam();
        attributes[3] = productAanlevering.getMsdsFileNaam();
        attributes[4] = productAanlevering.getMsdsFile();
        attributes[5] = productAanlevering.getCompFileNaam();
        attributes[6] = productAanlevering.getCompFile();
        STRUCT struct = CustomSqlMapClient.getStruct("PRODVAR_RECTYPE",
conn, attributes);
        return struct;
    }
 
…
 
The CustomSqlMapClient looks like this:
 
    public static synchronized StructDescriptor getStructDescriptor(
        final String oracleTypeName, Connection connection) throws
SQLException {
        return StructDescriptor.createDescriptor(oracleTypeName,
                    connection);
            }
 
    public static synchronized ArrayDescriptor getArrayDescriptor(
        final String oracleTypeName, Connection connection) throws
SQLException {
return ArrayDescriptor.createDescriptor(oracleTypeName,
                    connection);
    }
 
    public static ARRAY getArray(String string, Connection conn, Object[]
attributes) throws SQLException{
        return new ARRAY(CustomSqlMapClient.getArrayDescriptor(string,
conn), conn, attributes);
    }
 
    public static STRUCT getStruct(String string, Connection conn, Object[]
attributes) throws SQLException{
        return new STRUCT(CustomSqlMapClient.getStructDescriptor(string,
conn), conn, attributes);
    }
 
 
This is working fine without the blob type inside prodvar_rectype. 
With the blob type I am getting the following exception:
 
java.sql.SQLException: Converteren naar interne representatie is mislukt.:
[EMAIL PROTECTED]
08/11/21 17:09:48         at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:138)
08/11/21 17:09:48         at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:175)
08/11/21 17:09:48         at
oracle.jdbc.driver.DatabaseError.check_error(DatabaseError.java:874)
08/11/21 17:09:48         at
oracle.jdbc.oracore.OracleTypeBLOB.toDatum(OracleTypeBLOB.java:62)
08/11/21 17:09:48         at
oracle.sql.StructDescriptor.toOracleArray(StructDescriptor.java:536)
08/11/21 17:09:48         at
oracle.sql.StructDescriptor.toArray(StructDescriptor.java:1112)
08/11/21 17:09:48         at oracle.sql.STRUCT.<init>(STRUCT.java:140)
 
 
I know that there is a blob type handler for iBatis but how to I use this
when I am already inside a custom type handler? Or is there another way to
do this?
 
Thanks in advance! 
Ron van Weverwijk

-- 
View this message in context: 
http://www.nabble.com/Custom-type-handler-for-a-blob-inside-an-Oracle-STRUCT-tp20656071p20656071.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.