How to pass an array of objects from Java to PL/SQL?
Several not-so-obvious requirements must be followed to be able to pass arrays of objects between Java and PL/SQL:
- The corresponding Oracle type must be declared in SQL, not PL/SQL. That is, you need to use the CREATE TYPE SQL statement. Types declared in PL/SQL (ie. as part of a package) cannot be used with SQL and consequently neither within Java.
- The array descriptor should be registered using fully qualified type name (that is SCHEMA_NAME.ARRAY_TYPE_NAME) in the ArrayDescriptor.createDescriptor call. You might be able to do with the name of the type only, but if your data does not reside in the same schema you use to connect to the database, it might not work even if you use ALTER SESSION SET CURRENT_SCHEMA to set the schema.
- If you're using any other character set than UTF8, WE8DEC, US7ASCII or ISO-Latin-1, you need to include an Oracle library to convert the character set on the client side. For Oracle 10g drivers and later, use the orai18n.jar library, for prior versions, use nls_charset12.jar (or perhaps nls_charset11.jar in the unlikely case you're still on JDK 1.1). The JDBC Oracle FAQ page contains links to download pages of Oracle drivers, where these files can be obtained.
For better understanding, you might want to read the Oracle documentation: specifically Oracle Object Types and Oracle Collections. These links lead to 11g Release 2 version of the documentation; earlier versions of this documentation are available on the JDBC Oracle FAQ page.
An example of passing arrays of objects from Java to Oracle can be found in this valuable thread.