Win a copy of Spring in Action (5th edition) this week in the Spring forum!
    Bookmark Topic Watch Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Bear Bibeault
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Knute Snortum
  • Junilu Lacar
  • paul wheaton
Saloon Keepers:
  • Ganesh Patekar
  • Frits Walraven
  • Tim Moores
  • Ron McLeod
  • Carey Brown
  • Stephan van Hulst
  • salvin francis
  • Tim Holloway
  • Mark post as helpful
  • send pies
  • Report post to moderator
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.

    Consider Paul's rocket mass heater.
      Bookmark Topic Watch Topic
    • New Topic
    Boost this thread!