HikariCP pass Oracle custom type

Question

I switched to HikariCP from Oracle default datasource. There is a piece of code where I pass custom Oracle type to a stored parameter, and cast java.sql.Connection to oracle.jdbc.OracleConnection.

try(OracleConnection connection = (OracleConnection) dbConnect.getConnection()) {
        try(CallableStatement callableStatement = connection.prepareCall("{? = call pkg_applications.add_application(?,?,?)}")) {
            callableStatement.registerOutParameter(1, Types.VARCHAR);
            callableStatement.setString(2, form.getPolicyNumber());
            callableStatement.setString(3, form.getPolicyStart());

            Object[][] uploads = new Object[wrappers.size()][];

            for(int i=0; i<wrappers.size(); i++) {
                uploads[i] = new Object[4];
                uploads[i][0] = wrappers.get(i).getName();
                uploads[i][1] = wrappers.get(i).getFile().getContentType();
                uploads[i][2] = wrappers.get(i).getFile().getSize();
                uploads[i][3] = wrappers.get(i).getLocation();
            }

            callableStatement.setArray(4, connection.createARRAY("T_UPLOAD_FILE_TABLE", uploads));

            callableStatement.execute();
            int applicationId = callableStatement.getInt(1);

            operationResponse.setData(applicationId);
            operationResponse.setCode(ResultCode.OK);
        }
    }
    catch(Exception e) {
        log.error(e.getMessage(), e);
    }

I get a java.lang.ClassCastException - com.zaxxer.hikari.pool.HikariProxyConnection cannot be cast to oracle.jdbc.OracleConnection.

How can I pass Oracle custom types to a stored procedure using HikariCP?

Nitin · Accepted Answer · 2016-10-17T15:38:11.947

24

What you get from pool is a proxy connection. To access the underlying Oracle connection, you should use unwrap() with isWrapperFor():

try (Connection hikariCon = dbConnect.getConnection()) {
   if (hikariCon.isWrapperFor(OracleConnection.class)) {
      OracleConnection connection = hikariCon.unwrap(OracleConnection.class);
      :
      :
   }

However, which method is OracleConnection specific in your example ? you may not need to cast at all !

edited Oct 17 '16 at 15:38

answered Oct 17 '16 at 15:21

Nitin

1,582
11
12

I need to pass a custom type T_UPLOAD_FILE_TABLE which is a table of another custom type. Can you send a link to HikariCP docs, where I can find it? How can I pass custom oracle type to stored procedures with HCP? – 0bj3ct Oct 17 '16 at 16:25
1

Have you tried removing cast ? If you do not cast, does connection.createARRAY("T_UPLOAD_FILE_TABLE", uploads)); throws exception? If it does, have you tried using unwrap ? Look for documentation of OracleConnection if you need something more specific. HTH – Nitin Oct 18 '16 at 04:42
1

It solved my problem. I could not use without cast, it throws "java.sql.SQLException: Unsupported feature". But unwrapping the HikariProxyConnection did the job! Thank you mate! – 0bj3ct Oct 18 '16 at 06:06

HikariCP pass Oracle custom type

1 Answers1

Linked