musicar musicar - 24 days ago 8
Java Question

Can we integrate Oracle RECORD TYPE with java Callable statement?

Is it true that RECORD type is not compatible with JDBC? the driver am using is OJDBC6.jar

Java Code:

dbStrategy.openConnection();
WrappedConnectionJDK6 wrapped = (WrappedConnectionJDK6) dbStrategy.getConnection();
OracleConnection oracleConnection = (OracleConnection) wrapped.getUnderlyingConnection();
CallableStatement callableStatement = oracleConnection.prepareCall("{call XXX_Info_Utl_Pkg.get_order_admin(?,?,?,?,?)}");
callableStatement.setInt(1, Integer.parseInt(orderNumber));
callableStatement.registerOutParameter(2, OracleTypes.ARRAY, "XXX_INFO_UTL_PKG.SEAGRS_ORDER_REC");
callableStatement.registerOutParameter(3, OracleTypes.NUMBER);
callableStatement.registerOutParameter(4, OracleTypes.VARCHAR);
callableStatement.registerOutParameter(5, OracleTypes.NUMBER);


And Oracle Code:

PROCEDURE get_order_admin(
p_order_num_in IN NUMBER,
p_order_admin_out OUT XXX_info_utl_pkg.seagrs_order_rec,
p_error_code_out OUT PLS_INTEGER,
p_error_msg_out OUT VARCHAR2,
p_ret_status_out OUT PLS_INTEGER);


TYPE seagrs_order_rec
IS
record
(
order_admin VARCHAR2(10),
contact_name VARCHAR2(50),
contact_email_addr VARCHAR2(100),
contact_phone_num VARCHAR2(30));


The error am facing here is -


Exception : invalid name pattern: XXX_INFO_UTL_PKG.SEAGRS_ORDER_REC

Answer

Record Type is not compatible with JDBC drivers:

It is not feasible for Oracle JDBC drivers to support calling arguments or return values of the PL/SQL RECORD, BOOLEAN, or table with non-scalar element types. However, Oracle JDBC drivers support PL/SQL index-by table of scalar element types.

READ THIS