Jdbc array binding: character set encoding
character-encoding, java, jdbc, oracle
Solution
The solution, as well as the distinction between string inside objects/collections or not, is well documented, actually:
The basic Java Archive (JAR) files, ojdbc5.jar and ojdbc6.jar, contain all the necessary classes to provide complete globalization support for:
- Oracle character sets for CHAR, VARCHAR, LONGVARCHAR, or CLOB data that is not being retrieved or inserted as a data member of an Oracle object or collection type.
- CHAR or VARCHAR data members of object and collection for the character sets US7ASCII, WE8DEC, WE8ISO8859P1, WE8MSWIN1252, and UTF8.
To use any other character sets in CHAR or VARCHAR data members of objects or collections, you must include orai18n.jar in the CLASSPATH environment variable of your application.
After adding orai18n.jar in the CLASSPATH, it works like a charm
Problem
I'm trying to bind a SQL string array to a prepared statement, and for some database charset, the values of the array become null. If I bind simple strings (not in array), it works. If the charset (NLS_CHARACTERSET in v$nls_parameters) is AL32UTF8, it works fine. If it is WE8ISO8859P15, then I'm able to bind strings, but not arrays of strings. The difference seems to be that Oracle JDBC has a specific list of character set for which conversion is supported, and ISO-8859-15 is not part of them. That explains part of the problem, as when it finds that in the DB, it converts all the string to null. But the conversion does work when the string is not in an array... So I'm confused. My whole test is below. The table type I'm using is defined as `create type t_v4000_table as table of varchar2(4000);` ``` Connection connection; @Before public void setup() throws SQLException { OracleDataSource ds = new OracleDataSource(); ds.setUser("aaa"); ds.setPassword("a"); ds.setURL("jdbc:oracle:thin:@server:1521:orcl"); connection = ds.getConnection(); } @Test // works with both AL32UTF8 and WE8ISO8859P15 public void testScalar() throws SQLException { CallableStatement stmt = connection.prepareCall("declare a varchar2(4000) := ?; " + "begin if a is null then raise_application_error(-20000,'null'); end if; end;"); stmt.setString(1, "a"); stmt.execute(); } @Test // works only with AL32UTF8 public void testArray() throws SQLException { ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("T_V4000_TABLE", connection); String[] array = new String[] {"a"}; Array sqlArray = new ARRAY(descriptor, connection, array); CallableStatement stmt = connection.prepareCall("declare a t_v4000_table := ?; " + "begin if a(1) is null then raise_application_error(-20000,'null'); end if; end;"); stmt.setArray(1, sqlArray); stmt.execute(); } ``` I suspect that I'm doing something wrong in the way I declare and bind my array, but I can't find out what. Any idea?