Inconsistent java and sql object types

Question

Need a little assistance with a jdbc issue.

I've really struggled finding a solution to this. I don't have much experience with JDBC, and it doesn't look too newbie-friendly. If anyone can help it would be appreciated. By help I mean pointing me to other resources or even better if you can offer a direct solution.

The exception is created in oracle.sql.STRUCT.java:51

throw new SQLException("Cannot construct STRUCT instance, invalid connection");

and is thrown in the final line of the following code:

SimpleJdbcCall simpleJdbcCall = getSimpleJDBCCall();
con = simpleJdbcCall.getJdbcTemplate().getDataSource().getConnection().getMetaData().getConnection();

WLConnection wc = (WLConnection) con;            
Connection vendorConn = wc.getVendorConnection();

con.setAutoCommit(false);

Object[] custIdentifier = { null, new Long(125435345L)};

StructDescriptor structDesc =         StructDescriptor.createDescriptor("O_CUSTOMER_TYPE_CID_PID", vendorConn);

STRUCT struct = new STRUCT(structDesc, vendorConn, custIdentifier);
STRUCT[] structArray = new STRUCT[] { struct };

ArrayDescriptor des = ArrayDescriptor.createDescriptor("T_CUSTOMER_TYPE_CID_PID", vendorConn);
ARRAY array = new ARRAY(des, vendorConn, structArray);

ArrayDescriptor desEvnt = ArrayDescriptor.createDescriptor("T_EVENT_TYPE_ID", vendorConn);
ARRAY arrayEvnt = new ARRAY(desEvnt, vendorConn, events);

StructDescriptor fltDetailsStructDesc =
                StructDescriptor.createDescriptor("O_FLIGHT_DETAILS", vendorConn);

DATE flightDateUTC = new DATE(flightDt);
NUMBER flightNum = new NUMBER(fltNo);
Object[] fltDetailFields = new Object[] { flightNum, flightDateUTC };

// Exception thrown in constructor below
STRUCT fltDetailsStruct = new STRUCT(fltDetailsStructDesc, vendorConn,     fltDetailFields);

I'm using ojdbc6 version 11.2.0.4

The SQl object expected is:

TYPE O_FLIGHT_DETAILS AS OBJECT 
( 
FLIGHT_NO NUMBER(5), -- Marketing Flight Number 
UTC_DEP_DATE Date, -- Scheduled Flight departure Date in UTC 
BKG_DT DATE -- Flight Booking date in UTC 
);

Any help, much appreciated.

score 4 · Answer 1 · answered Jul 21 '15 at 09:36

I think the problem was as follows:

the StructDescriptor for the 'O_FLIGHT_DETAILS' object was expecting an array containing 3 values, as per the SQL object. Since the array supplied contained only 2 values, a mismatch occurred and exception was thrown. The error message 'inconsistent java and sql object types' was accurate.

I changed the fltDetailFields instantiation line to:

Object[] fltDetailFields = new Object[] { flightNum, flightDateUTC, null };

and it worked as expected.

can u please look at :http://stackoverflow.com/questions/36808408/jdbc-call-to-stored-procedure-which-take-array-as-parameter — fiddle, Apr 25 '16 at 06:40

Inconsistent java and sql object types

1 Answers1