This discussion is archived
7 Replies Latest reply: Sep 16, 2013 11:59 AM by ca476d4d-b1d8-4967-a6ee-9076476effc5 RSS

ARRAY passing from Java to Procedure

ca476d4d-b1d8-4967-a6ee-9076476effc5 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points