I'm using JDev 11.1.1.7.0 and oracle Exp 11g DB
In my ADF application i'm trying to call a stored procedure, but i keep getting errors, the last one was "Invalid column type: 1111".
I think the problem is around this line
st.registerOutParameter(8,Types.OTHER);
My AppModuleImpl complete method:
public Row callProcWithRowOut(Object[] bindVars) {
CallableStatement st = null;
try {
st = getDBTransaction().createCallableStatement("begin ADF_ITEM_SOLD(?,?,?,?,?,?,?,?);end;",getDBTransaction().DEFAULT);
st.registerOutParameter(8,Types.OTHER);
if (bindVars != null) {
for (int z = 0; z < bindVars.length; z++) {
st.setObject(z + 1, bindVars[z]);
}
}
st.executeUpdate();
return (Row)st.getObject(8);
} catch (SQLException e) {
throw new JboException(e);
} finally {
if (st != null) {
try {
st.close();
}
catch (SQLException e) {}
}
}
}
The trace:
java.sql.SQLException: Invalid column type: 1111
at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:5344)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:153)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:399)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:581)
at oracle.jdbc.driver.OracleCallableStatementWrapper.registerOutParameter(OracleCallableStatementWrapper.java:1765)
at weblogic.jdbc.wrapper.CallableStatement_oracle_jdbc_driver_OracleCallableStatementWrapper.registerOutParameter(Unknown Source)
at model.BC.AppModule.AppModule_AMImpl.callProcWithRowOut(AppModule_AMImpl.java:1793)
at model.BC.Views.SalesInvoiceItems_VOImpl.insertItems(SalesInvoiceItems_VOImpl.java:54)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at oracle.adf.model.binding.DCInvokeMethod.invokeMethod(DCInvokeMethod.java:657)
at oracle.adf.model.binding.DCDataControl.invokeMethod(DCDataControl.java:2143)
at oracle.adf.model.bc4j.DCJboDataControl.invokeMethod(DCJboDataControl.java:3118)
at oracle.adf.model.binding.DCInvokeMethod.callMethod(DCInvokeMethod.java:261)
at oracle.jbo.uicli.binding.JUCtrlActionBinding.doIt(JUCtrlActionBinding.java:1635)
at oracle.adf.model.binding.DCDataControl.invokeOperation(DCDataControl.java:2150)
at oracle.jbo.uicli.binding.JUCtrlActionBinding.invoke(JUCtrlActionBinding.java:740)
at oracle.adf.controller.v2.lifecycle.PageLifecycleImpl.executeEvent(PageLifecycleImpl.java:407)
at oracle.adfinternal.view.faces.model.binding.FacesCtrlActionBinding._execute(FacesCtrlActionBinding.java:252)
at oracle.adfinternal.view.faces.model.binding.FacesCtrlActionBinding.execute(FacesCtrlActionBinding.java:210)
at view.backing.SalesInvoicesUpdate.insertItems_action(SalesInvoicesUpdate.java:281)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.sun.el.parser.AstValue.invoke(Unknown Source)
at com.sun.el.MethodExpressionImpl.invoke(Unknown Source)
at org.apache.myfaces.trinidad.component.MethodExpressionMethodBinding.invoke(MethodExpressionMethodBinding.java:46)
at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102)
at org.apache.myfaces.trinidad.component.UIXCommand.broadcast(UIXCommand.java:190)
What might be the problem?
Thank you for your time.
As I already said in a comment, it's not normally possible to call stored procedures that return non-sql types (there're some exceptions to this using database specific APIs). Since %ROWTYPE is a Pl/Sql type, you will have to modify your procedure to return a supported (i.e. SQL) type. The simplest way to do that is to return a number of OUT parameters corresponding to your %ROWTYPE or you can even return a simple string/varchar (this string may contain a comma separated list of all your attributes) and then parse this string on the client (Java ) side.
I've also written a simple example avalable here that shows one way how to deal with this situation ( http://easyorm.info/SProc ). This example uses EasyORM library (simple JDBC wrapper), and not plain JDBC, but it should give you an idea how to modify your stored procedure.
You can also encode your %ROWTYPE as an Oracle object (sql type) and have your stored procedure return that object but this approach is more complex (see Mapping an Oracle stored procedure result to a custom Java type (class) for an example)