1 2 Previous Next 17 Replies Latest reply on Feb 16, 2011 10:45 AM by 840197

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

    511346
      hi,
      I am having having problems with the getArray() of oracle.sql.ARRAY.
      Here are the details:
      Oracle Database - 9.2.0.6. The JDBC driver used -10.2.0.1 jar.(the latest available).

      I am calling a stored procedure from java, that has out param as a collection (VARRAY) of a user defined type.
      The stored procedure works fine, if the user/schema used to connect,contains actual type, and i am able to extract the user defined object from the oracle.sql.ARRAY, via getArray().
      But if i use a different user to connect which has public synonyms and execute rights to the actual type, i get the following error:

      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:370)

      The problem is happening because we have 2 different users to connect to the database. one is the dba user, which contains all the types, packages and other one the application user, which we are supposed to use, to connect via java. This application user does have access to types via public synonym, but it gives the above mentioned error.

      Please help :(.

      Regards,
      Gaurav.
        • 1. Re: java.sql.SQLException: Internal Error with oracle.sql.ARRAY getArray()
          Avi Abrami
          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.
          • 2. Re: java.sql.SQLException: Internal Error with oracle.sql.ARRAY getArray()
            511346
            Avi,
            Thanx for your reply.

            I am indeed preceeding the type name with the application user name, and thats why is the problem.
            Consider this - there are 2 users: dba_user & app_user. The types are owned by dba_user, but they have public synonym and execute rights for the app_user. Now, from the application when i connect, and try to map the types as "app_user.UserType", it gives the above mentioned error, but if i use "dba_user.UserType" it will work fine. Problem is i cannot use "dba_user.UserType" , because right now the application is in dev environment, but when it moves to UAT/Production, we would not be able to find the dba user name.

            I had this kind of problem i few days back too (oracle API unable to map a java class to a type available as synonym. the bug was in oracle.sql.ArrayDescriptor - Bug # 3320782) , The problem was fixed when we upgraded the driver to 10.2.0.1.
            I suspect that this is also a bug in oracle driver ???


            Regards,
            Gaurav.
            • 3. Re: java.sql.SQLException: Internal Error with oracle.sql.ARRAY getArray()
              507478
              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?
              • 4. Re: java.sql.SQLException: Internal Error with oracle.sql.ARRAY getArray()
                Avi Abrami
                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.
                • 5. Re: java.sql.SQLException: Internal Error with oracle.sql.ARRAY getArray()
                  514823
                  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
                  • 6. Re: java.sql.SQLException: Internal Error with oracle.sql.ARRAY getArray()
                    Avi Abrami
                    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.
                    • 7. Re: java.sql.SQLException: Internal Error with oracle.sql.ARRAY getArray()
                      514823
                      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
                      • 8. Re: java.sql.SQLException: Internal Error with oracle.sql.ARRAY getArray()
                        514823
                        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
                        • 9. Re: java.sql.SQLException: Internal Error with oracle.sql.ARRAY getArray()
                          514823
                          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
                          • 10. Re: java.sql.SQLException: Internal Error with oracle.sql.ARRAY getArray()
                            Avi Abrami
                            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.
                            • 11. Re: java.sql.SQLException: Internal Error with oracle.sql.ARRAY getArray()
                              514823
                              Hi Avi,
                              Thanks a lot..

                              Regards,
                              Archana
                              • 12. Re: java.sql.SQLException: Internal Error with oracle.sql.ARRAY getArray()
                                514823
                                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
                                • 13. Re: java.sql.SQLException: Internal Error with oracle.sql.ARRAY getArray()
                                  Avi Abrami
                                  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.
                                  • 14. Re: java.sql.SQLException: Internal Error with oracle.sql.ARRAY getArray()
                                    ora_et_labora
                                    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 \==--
                                    1 2 Previous Next