0

I use the following code to create Oracle ARRAY of varchar2:

CREATE TYPE T_VARCHAR_LIST AS TABLE OF varchar2(32);

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(
      "jdbc:oracle:thin:@localhost:1521:TEST", user, password);
ArrayDescriptor arrayDescriptor = ArrayDescriptor.
      createDescriptor("T_VARCHAR_LIST", connection);
ARRAY array = new ARRAY(arrayDescriptor, conection, new String[]{"1", "2", "3"});

But array.getArray() return ["???", "???", "???"].

Any ideas why ARRAY of varchar2 is not created properly?

BTW, ARRAY of numbers (CREATE TYPE T_NUM_LIST AS TABLE OF NUMBER) gets created successfully.

koloale
  • 1
  • 1
  • 2
  • check to see if you've included `orai18n.jar` in your classpath, if not try adding it (its under the oracle home\jlib folder). – DazzaL Feb 25 '13 at 23:43

1 Answers1

1

I took your code and got it to run with a couple of minor changes:

My Array TYPE object is this:

create or replace
TYPE TBL_ARRAY_VARCHAR2  AS TABLE OF VARCHAR2(80);

My code looks like this:

try {
        Connection con;
        Class.forName("oracle.jdbc.driver.OracleDriver");

        con = DriverManager.getConnection(
                "jdbc:oracle:thin:@myhost:1521:LX1120", user,
                password);
        ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor(
                "TBL_ARRAY_VARCHAR2", con);
        ARRAY array = new ARRAY(arrayDescriptor, con, new String[] { "1",
                "2", "3" });

        Object oArray = array.getArray();
        System.out.println(oArray.getClass().getName());

        if (oArray instanceof String[]) {
            String[] oArrayString = (String[]) oArray;
            for (int i = 0; i < oArrayString.length; i++) {
                System.out.println(i + ":" + oArrayString[i]);
            }
        }

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

What I see when I run it is this:

[Ljava.lang.String;
0:1
1:2
2:3

So there's nothing wrong what what you are trying to do...

My guess - based on working with lots of different versions of Oracle and different Oracle JDBC drivers - is that you have a mismatch between your JDBC driver version and your database version, but that's just a guess. If I were you I'd copy the JDBC driver from the server and try again. Note that the contents of the driver can change even though the name doesn't, so comparing names is not all you need to do.

David Rolfe

David Rolfe
  • 21
  • 1
  • Thank you, David, for your answer. What version of Oracle and jdbc you are using? I use Oracle 11.2.0.2.0 and jdbc 11.2.0.3. – koloale Feb 26 '13 at 01:47
  • We use every version of Oracle from 8.15 to 11.2 because we are in the Oracle code generation business (See: www.orindasoft.com), but they key thing is that your driver version should be the same as the database version. You will probably get away with it if it's ahead, bad things will happen if it is behind... – David Rolfe Feb 26 '13 at 07:44