Using java 1.7.0_25 with Glassfish 3.1 and Oracle 11.2 database.
There are many examples online to pass an array from Java to an Oracle stored procedure. But throw Glassfish into the mix to use a JDBC connection pool, and those examples no longer work. The following describes the error I see:
Currently I have Glassfish admin console's settings set to:
Resource Type: javax.sql.DataSource
Datasource Classname: oracle.jdbc.pool.OracleDataSource
I define a type `TYPE_ARRAY_SINGLE` in Schema level of Oracle database (e.g. not inside a package):
create or replace TYPE TYPE_ARRAY_SINGLE AS TABLE OF BINARY_FLOAT; -- array of single precision (e.g. float)
To establish a baseline, I first have the following code that works (e.g. where no array is passed yet):
----ORACLE----
create or replace procedure SAVE_DATA (
in_1 IN varchar2,
out_1 OUT varchar2)
as
begin
out_1='valid';
end SAVE_DATA;
----JAVA----
public String SaveData(String in1, float[] myArray) throws Exception {
String out1="";
Connection conn = null;
CallableStatement cs=null;
try {
Context context = new InitialContext();
DataSource ds = (DataSource) context.lookup("jdbc/myOraclePool");
OracleDataSource ods = ds.unwrap(OracleDataSource.class);
conn = (OracleConnection) ods.getConnection();
ArrayDescriptor des_single=ArrayDescriptor.createDescriptor("TYPE_ARRAY_SINGLE", conn);
cs = conn.prepareCall("{call save_data(?,?)}");
cs.setString(1, in1);
ARRAY myArray_orcl = new ARRAY(des_single, conn, myArray);
cs.registerOutParameter(2, Types.VARCHAR);
cs.execute();
out1=cs.getString(2);
} catch (Exception e) {
} Finally {
conn.close();
}
return out1;
}
Then I change to above code to pass array `myArray` (see changes below in bold), restart Glassfish, and observe the runtime error: java.sql.SQLException: Internal Error: Array is in inconsistent status,
create or replace procedure SAVE_DATA (
in_1 IN varchar2,
my_array IN TYPE_ARRAY_SINGLE, -- add array here
out_1 OUT varchar2)
as
begin
out_1='valid';
end SAVE_DATA;
----JAVA----
public String SaveData(String in1, float[] myArray) throws Exception {
String out1="";
Connection conn = null;
CallableStatement cs=null;
try {
Context context = new InitialContext();
DataSource ds = (DataSource) context.lookup("jdbc/myOraclePool");
OracleDataSource ods = ds.unwrap(OracleDataSource.class);
conn = (OracleConnection) ods.getConnection();
ArrayDescriptor des_single=ArrayDescriptor.createDescriptor("TYPE_ARRAY_SINGLE", conn);
cs = conn.prepareCall("{call save_data(?,?,?)}");
cs.setString(1, in1);
ARRAY myArray_orcl = new ARRAY(des_single, conn, myArray);
cs.setArray(2, myArray_orcl); // add array here
cs.registerOutParameter(3, Types.VARCHAR);
cs.execute();
out1=cs.getString(3);
} catch (Exception e) {
} Finally {
conn.close();
}
return out1;
}
Any ideas why I get a runtime error, and how to resolve? I don't even understand what the error is telling me (and Googling the error doesn't turn up much).
Do I need to grant any special permission to the type TYPE_ARRAY_SINGLE to enable jdbc or java to work with it?
The stack trace error is:
full stack trace: java.sql.SQLException: Internal Error: Array is in inconsistent status
at com.example.AClass.SaveData(AClass.java:137)
at com.example.BClass.uSaveData(BClass.java:9932)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at flex.messaging.services.remoting.adapters.JavaAdapter.invoke(JavaAdapter.java:418)
at flex.messaging.services.RemotingService.serviceMessage(RemotingService.java:183)
at flex.messaging.MessageBroker.routeMessageToService(MessageBroker.java:1400)
at flex.messaging.endpoints.AbstractEndpoint.serviceMessage(AbstractEndpoint.java:1005)
at flex.messaging.endpoints.amf.MessageBrokerFilter.invoke(MessageBrokerFilter.java:103)
at flex.messaging.endpoints.amf.LegacyFilter.invoke(LegacyFilter.java:158)
at flex.messaging.endpoints.amf.SessionFilter.invoke(SessionFilter.java:44)
at flex.messaging.endpoints.amf.BatchProcessFilter.invoke(BatchProcessFilter.java:67)
at flex.messaging.endpoints.amf.SerializationFilter.invoke(SerializationFilter.java:166)
at flex.messaging.endpoints.BaseHTTPEndpoint.service(BaseHTTPEndpoint.java:291)
at flex.messaging.MessageBrokerServlet.service(MessageBrokerServlet.java:353)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:770)
at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1542)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:343)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:217)
at com.example.filters.CharsetFilter.doFilter(CharsetFilter.java:49)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:217)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:279)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:161)
at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:331)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:231)
at com.sun.enterprise.v3.services.impl.ContainerMapper$AdapterCallable.call(ContainerMapper.java:317)
at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:195)
at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:849)
at com.sun.grizzly.http.ajp.AjpProcessorTask.invokeAdapter(AjpProcessorTask.java:135)
at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:746)
at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1045)
at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:228)
at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)
at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)
at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)
at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)
at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)
at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)
at com.sun.grizzly.ContextTask.run(ContextTask.java:71)
at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)
at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)
at java.lang.Thread.run(Thread.java:724)
Furthermore, I queried the SQLException as follows:
getSQLState() returns 99999
getErrorCode() returns 17001
I'm not sure what that means though.