7 Replies Latest reply: Sep 16, 2013 1:59 PM by ca476d4d-b1d8-4967-a6ee-9076476effc5 RSS

    ARRAY passing from Java to Procedure

    ca476d4d-b1d8-4967-a6ee-9076476effc5

      I have following problem running application in Jetty embedded server (application is using Spring). I'm trying to pass an array of strings to the remote oracle procedure but it only receives array with correct ammount of null values. Trying to determine the problem, I used this debug info:

      ret = new AbstractSqlTypeValue() {
           @Override
           public Object createTypeValue(Connection conn, int type, String typeName) throws SQLException {
      
                String[] strArr = {"abc", "bcd", "cde", "123", "234"};
                java.sql.Array array = ((OracleConnection)conn).createOracleArray(typeName, strArr);
      
      
                String[] objArr = (String[]) strArr;
                for (int i = 0; i < objArr.length; i++) {
                     //log original values
                     LOG.warn(typeName + ":" + String.valueOf(objArr[i]));
                }
                objArr = (String[]) array.getArray();
                for (int i = 0; i < objArr.length; i++) {
                     //log values in new array that will be passed to oracle procedure
                     LOG.warn(typeName + ":" + String.valueOf(objArr[i]));
                }
      
                return array;
           }
      };
      

      result looks like this:

      2013-09-12 16:14:10,873 DBConnectorJob_1                    DBHelper                            WARN : STRING_ARRAY:abc
      2013-09-12 16:14:10,873 DBConnectorJob_1                    DBHelper                            WARN : STRING_ARRAY:bcd
      2013-09-12 16:14:10,873 DBConnectorJob_1                    DBHelper                            WARN : STRING_ARRAY:cde
      2013-09-12 16:14:10,874 DBConnectorJob_1                    DBHelper                            WARN : STRING_ARRAY:123
      2013-09-12 16:14:10,874 DBConnectorJob_1                    DBHelper                            WARN : STRING_ARRAY:234
      2013-09-12 16:14:10,874 DBConnectorJob_1                    DBHelper                            WARN : STRING_ARRAY:???
      2013-09-12 16:14:10,874 DBConnectorJob_1                    DBHelper                            WARN : STRING_ARRAY:???
      2013-09-12 16:14:10,874 DBConnectorJob_1                    DBHelper                            WARN : STRING_ARRAY:???
      2013-09-12 16:14:10,874 DBConnectorJob_1                    DBHelper                            WARN : STRING_ARRAY:???
      2013-09-12 16:14:10,874 DBConnectorJob_1                    DBHelper                            WARN : STRING_ARRAY:???
      

      I'm not really sure, what's happened here - sql.Array contains 5 items, but I'm not able to read it. Oracle procedure (using INSERT as a logger) will insert 5 empty records... Do you have any idea, what could cause the problem and how to solve it?

        • 1. Re: ARRAY passing from Java to Procedure
          rp0428

          Back up a step and instead of this:

          java.sql.Array array = ((OracleConnection)conn).createOracleArray(typeName, strArr);

          Use:

          oracle.sql.ARRAY array = ((OracleConnection)conn).createArray(typeName, strArr);

          Then use the 'dump' method to see the contents.

           

          Post the results. Also, when you are troubleshooting I'd suggest that you use DIFFERENT messages for your different use cases. You are using this:

          LOG.warn(typeName + ":" + String.valueOf(objArr[i]));

          for BOTH loops and that makes it difficult to know which lines came from which loop. Add an identifier to the message just like you did for the comment in the loop. Your first comment is: //log original values so add something similar to the message itself.

          • 2. Re: ARRAY passing from Java to Procedure
            ca476d4d-b1d8-4967-a6ee-9076476effc5

            I have made modifications as you have suggested, but result is the same.Oracle ARRAY contains 5 elements with content "???", which is represented as null (or empty string, not sure) in plsql procedure...

            I'm not really sure what could be the next step

             

            LOG.warn(conn.getClass().getName());
            ...
            oracle.sql.ARRAY array = ((OracleConnection)conn).createARRAY(typeName, strArr);
            ...
            LOG.warn("(sql array) " + typeName + ":" + array.dump());
            ...
            ------------------- LOG --------------------
            2013-09-13 08:30:35,758 DBConnectorJob_1                    DBHelper                            WARN : oracle.jdbc.driver.T4CConnection
            2013-09-13 08:30:35,758 DBConnectorJob_1                    DBHelper                            WARN : (java array) STRING_ARRAY:abc
            2013-09-13 08:30:35,758 DBConnectorJob_1                    DBHelper                            WARN : (java array) STRING_ARRAY:bcd
            2013-09-13 08:30:35,758 DBConnectorJob_1                    DBHelper                            WARN : (java array) STRING_ARRAY:cde
            2013-09-13 08:30:35,758 DBConnectorJob_1                    DBHelper                            WARN : (java array) STRING_ARRAY:123
            2013-09-13 08:30:35,758 DBConnectorJob_1                    DBHelper                            WARN : (java array) STRING_ARRAY:234
            2013-09-13 08:30:35,758 DBConnectorJob_1                    DBHelper                            WARN : (sql array) STRING_ARRAY:name = STRING_ARRAY
            max length = 0
            length = 5
            element[0] = ???
            element[1] = ???
            element[2] = ???
            element[3] = ???
            element[4] = ???
            
            2013-09-13 08:30:35,758 DBConnectorJob_1                    DBHelper                            WARN : (sql array) STRING_ARRAY:???
            2013-09-13 08:30:35,758 DBConnectorJob_1                    DBHelper                            WARN : (sql array) STRING_ARRAY:???
            2013-09-13 08:30:35,758 DBConnectorJob_1                    DBHelper                            WARN : (sql array) STRING_ARRAY:???
            2013-09-13 08:30:35,758 DBConnectorJob_1                    DBHelper                            WARN : (sql array) STRING_ARRAY:???
            2013-09-13 08:30:35,758 DBConnectorJob_1                    DBHelper                            WARN : (sql array) STRING_ARRAY:???
            
            • 3. Re: ARRAY passing from Java to Procedure
              ca476d4d-b1d8-4967-a6ee-9076476effc5

              I have reduced problem domain - it has nothing to do with spring/jetty... here's the example code, where it also fails:

               

              public static void passArray()

               

                  {

                      try{

                      

                          Class.forName("oracle.jdbc.OracleDriver");        

                          Connection con = DriverManager.getConnection("jdbc:oracle:thin:@IP:PORT:SYS","USER","PASS");

                          

                          String array1[] = {"one", "two", "three","four"};

                          String array2[] = {"four", "three", "two","one"};

               

                          ArrayDescriptor des = ArrayDescriptor.createDescriptor("MY_SCHEME.STRING_ARRAY", con);

                          ARRAY array1_to_pass = new ARRAY(des,con,array1);

                          ARRAY array2_to_pass = new ARRAY(des,con,array2);

                         

                          System.out.println(array1_to_pass.dump());

                          System.out.println(array2_to_pass.dump());

               

                          CallableStatement st = con.prepareCall("call MY_SCHEME.FORMCREATE(?, ?, ?, ?, ?, ?, ?, ?, ?)");

                          String p1 = "1";

                          String p3 = "2";

                          String p4 = "3";           

                         

                          st.setObject(1, p1);

                          st.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);

                          st.setObject(3, p3);

                          st.setObject(4, p4);

                          st.registerOutParameter(4, oracle.jdbc.OracleTypes.VARCHAR);           

                          st.setObject(5, array1_to_pass);

                          st.setObject(6, array2_to_pass);

                          st.registerOutParameter(7, oracle.jdbc.OracleTypes.CURSOR);

                          st.registerOutParameter(8, oracle.jdbc.OracleTypes.NUMBER);

                          st.registerOutParameter(9, oracle.jdbc.OracleTypes.NUMBER);

                          st.execute();

               

                      } catch(Exception e) {

                          System.out.println(e);

                      }

                  }

               

              associated structures in Oracle DB looks like this:

               

              create or replace type MY_SCHEME.STRING_ARRAY is TABLE of VARCHAR2(200);
              

               

              create or replace
              PROCEDURE       formcreate(
                id             IN VARCHAR2,
                pid           OUT VARCHAR2,
                lid            IN VARCHAR2,
                fid            IN OUT VARCHAR2,
                iid            IN MY_SCHEME.STRING_ARRAY,
                vvalues        IN MY_SCHEME.STRING_ARRAY,
                items         OUT SYS_REFCURSOR,
                scode         OUT NUMBER,
                ecode         OUT NUMBER
                )
              IS
              --...
              BEGIN
                ecode := 100;
                scode:= 1;
                pid:=10;
              
                FOR i IN 1 .. iid.COUNT
                 LOOP
                    log.p_error('A' || iid(i));   --package "log", function INSERTs into log table...
                 END LOOP;
              
                 FOR i IN 1 .. vvalues.COUNT
                 LOOP
                    log.p_error('B'|| vvalues(i)); --package "log", function INSERTs into log table...
                 END LOOP;
              
                COMMIT;
              END;
              

               

              my problem:

              application output looks like this:

               

              name = MY_SCHEME.STRING_ARRAY

               

              max length = 0

              length = 4

              element[0] = ???

              element[1] = ???

              element[2] = ???

              element[3] = ???

               

              name = MY_SCHEME.STRING_ARRAY

              max length = 0

              length = 4

              element[0] = ???

              element[1] = ???

              element[2] = ???

              element[3] = ???

               

              database log table contains 8 new rows:

              A

               

              A

              A

              A

              B

              B

              B

              B

               

              I really don’t understand – when I change String array to int[], (and database type to “table of number”), everything works just fine (log sql table contains A1, A2, A3, A4, B1, B2, B3, B4)… but it is impossible to for me to make it work with string :(

              any ideas why is it so?

              my oracle driver is ojdbc6-11.2.0.4.0, database version Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production… every example I have found on the internet claims it works just fine – I really don’t understand

              • 4. Re: ARRAY passing from Java to Procedure
                rp0428

                Works for me using ojdbc6 and vanilla 11.2.0.1.0 DB

                 

                I created everything in the SCOTT schema and am connecting as SCOTT to eliminate any 'privilege' issue with the Oracle objects.


                • 5. Re: ARRAY passing from Java to Procedure
                  ca476d4d-b1d8-4967-a6ee-9076476effc5

                  It was caused by encoding problem, however - without any exception or debug information Including orai18n.jar to the project libraries solved this... it is really sad, there is noexception or something that would indicate how to solve the problem

                  • 6. Re: ARRAY passing from Java to Procedure
                    rp0428

                    ca476d4d-b1d8-4967-a6ee-9076476effc5 wrote:

                     

                    It was caused by encoding problem, however - without any exception or debug information Including orai18n.jar to the project libraries solved this... it is really sad, there is noexception or something that would indicate how to solve the problem

                    What 'encoding problem' are you referring to?

                     

                    Please post the solution you found so that others can understand it and may be helped by it.

                    • 7. Re: ARRAY passing from Java to Procedure
                      ca476d4d-b1d8-4967-a6ee-9076476effc5

                      I'm not really sure - as a desperate solution, I've tried to include orai18n.jar (available at ojdbcX.jar download site) to the project. After that, arrays started to work...