java.sql.SQLException: Internal Error with oracle.sql.ARRAY getArray()

Gaurav,
I'm only guessing, but maybe you need to use the fully qualified name -- and not a synonym -- when trying to access the user-defined type via JDBC.

Or why don't you simply make the application user the owner of the user-defined type? If the DBA user has access to all types, then [s]he doesn't need to be the owner of the user-defined type. Or am I missing something?

Good Luck,
Avi.

Umm, why don't you simply put the schema owner into a configuration properties file and have the app use the value from configuration rather than hardcoding it in the Java code?

Gaurav,
You said:

I am indeed preceeding the type name with the application user name

You need to precede the type name with the schema owner name, not with the application user name!

The idea of defining a public synonym is so that you don't have to precede the type name with the schema owner name. So since you have set up a public synonym for the type name, then use the synonym only -- without preceding it with any name (dba nor user).

Good Luck,
Avi.

Hi avi,
Even I am getting the same error inspite of preceding the type name with the same user who has created the type.

It has displayed the array length.When I am trying to fetch each array, I am getting the error.

ARRAY simpleArray = (ARRAY) cs.getObject(3);          
                         
System.out.println("Array is of length " + simpleArray.length());          ********************** The above stmt prints 2 records ******************
But it goes to exception in the foll stmt..
     
Object [] objArrStructArray = null;
try{
     objArrStructArray = (Object[]) simpleArray.getArray();
}catch(SQLException e){
     e.printStackTrace();
}


Do u know abt this?

The foll is the error which I am getting.
java.sql.SQLException: Internal Error
     at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
     at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
     at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
     at oracle.jdbc.oracore.OracleTypeCOLLECTION.initCollElemTypeName(OracleTypeCOLLECTION.java:1026)
     at oracle.jdbc.oracore.OracleTypeCOLLECTION.getAttributeType(OracleTypeCOLLECTION.java:1056)
     at oracle.jdbc.oracore.OracleNamedType.getFullName(OracleNamedType.java:110)
     at oracle.jdbc.oracore.OracleTypeADT.createStructDescriptor(OracleTypeADT.java:2262)
     at oracle.jdbc.oracore.OracleTypeADT.unpickle81(OracleTypeADT.java:1656)
     at oracle.jdbc.oracore.OracleTypeUPT.unpickle81UPT(OracleTypeUPT.java:466)
     at oracle.jdbc.oracore.OracleTypeUPT.unpickle81rec(OracleTypeUPT.java:416)
     at oracle.jdbc.oracore.OracleTypeCOLLECTION.unpickle81_imgBody_elems(OracleTypeCOLLECTION.java:979)
     at oracle.jdbc.oracore.OracleTypeCOLLECTION.unpickle81_imgBody(OracleTypeCOLLECTION.java:923)
     at oracle.jdbc.oracore.OracleTypeCOLLECTION.unpickle81(OracleTypeCOLLECTION.java:743)
     at oracle.jdbc.oracore.OracleTypeCOLLECTION._unlinearize(OracleTypeCOLLECTION.java:242)
     at oracle.jdbc.oracore.OracleTypeCOLLECTION.unlinearize(OracleTypeCOLLECTION.java:208)
     at oracle.sql.ArrayDescriptor.toJavaArray(ArrayDescriptor.java:963)
     at oracle.sql.ARRAY.getArray(ARRAY.java:353)
     at com.telstra.plo.data.NetworkDAO.findCablesInBuffer(NetworkDAO.java:730)
     at com.telstra.plo.data.NetworkDAOTest.testFindCablesInBuffer(NetworkDAOTest.java:45)
     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:324)
     at junit.framework.TestCase.runTest(TestCase.java:154)
     at junit.framework.TestCase.runBare(TestCase.java:127)
     at junit.framework.TestResult$1.protect(TestResult.java:106)
     at junit.framework.TestResult.runProtected(TestResult.java:124)
     at junit.framework.TestResult.run(TestResult.java:109)
     at junit.framework.TestCase.run(TestCase.java:118)
     at junit.framework.TestSuite.runTest(TestSuite.java:208)
     at junit.framework.TestSuite.run(TestSuite.java:203)
     at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
     at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
     at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)

Thanks
Archana

Archana,
I'm only guessing, but it could be that your JDBC driver version is incompatible with your Oracle database version. I'm sorry but I couldn't find those version details in your post. Compatibility information is available from this Web page:

http://www.oracle.com/technology/tech/java/sqlj_jdbc/index.html

As a suggestion, perhaps use the "isConvertibleTo()" method -- of class "oracle.sql.ARRAY" -- to see if the ARRAY object can be converted to a "Object[]" array.

Good Luck,
Avi.

Hi avi,
I am using the driver, ojdbc14.zip . My database is oracle 10G.

I have done this :
System.out.println("convertible: " + simpleArray.isConvertibleTo(Arrays.class));
This gives false.

Any more inputs plz??

Thanks a lot..
archana

Hi avi,
sorry for wrong usage of isconvertible in the last reply.
I checked both the following.
System.out.println("convertible: " + simpleArray.isConvertibleTo(Object.class));
System.out.println("convertible: " + simpleArray.isConvertibleTo(Object[].class));
Both return false.

Is the driver ok ?
When I did debug in eclipse, I can see the values of objects as some byte values..

Please let me know if u hv some suggestions.

Thanks a lot..
archana

Hi Avi,
That problem was solved. I was using the synonym name in java. I changed to the exact collection name.

One more problem i have.
I have declared a type in pkg.
TYPE arrayCableGeometry IS TABLE OF MDSYS.SDO_GEOMETRY INDEX BY BINARY_INTEGER;

I am returning this array from pl/sql and trying get it in java.
cs.registerOutParameter(2,OracleTypes.ARRAY,"arrayCableGeometry");

This gives the error,
java.sql.SQLException: invalid name pattern: UDT1CPR2.arrayCableGeometry

thanks
archana

Archana,
You must define your type as a database type, using the DDL statement:

create or replace type ...

Types defined in PL/SQL packages are not supported. Sorry :-(

Good Luck,
Avi.

Hi Avi,
Thanks a lot..

Regards,
Archana

Hi Avi,
I wanted a help.
I have an out parameter in pl/sql which is a MDSYS.SDO_GEOMETRY.
In my java code I am doing the following,
cs.registerOutParameter(4, OracleTypes.STRUCT);

When I do cs.execute(), I am getting the following error:
Exception in testing NetworkDAO.testFindCablesInBuffer(...): java.sql.SQLException: ORA-06550: line 1, column 8:
PLS-00306: wrong number or types of arguments in call to 'FIND_CABLES_IN_BUFFER'
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored

Can you plz help me on this??

Thanks a lot..
Regards,
Archana

Archana,
You need a "StructDescriptor". Search the OTN Web site for "StructDescriptor".

The following Web page may also be helpful:

http://www.oracle.com/technology/sample_code/tech/java/codesnippet/jdbc/objects/ObjectTypes.html

Good Luck,
Avi.

I suspect that this is also a bug in oracle driver
???

Gaurav,

although your problem has already been solved, I'd like to point out to you and the community that there is indeed a bug in the JDBC drivers from 9.2.0.4 to 10.2.0.2. The only workaround so far is not to access object types via synonyms but instead to specify the object owner (like "myowner.mytype").
Like ck suggested, it would be good practice to put the owner in a .properties file as such things do change over time.

For those with access to Metalink: You might want to look up Bug #4439777 or DocID 387044.1.

Regards,
--==/ Uwe \==--