Win a copy of The Journey To Enterprise Agility this week in the Agile and Other Processes forum! And see the welcome thread for 20% off.
    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 ...
  • Jeanne Boyarsky
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
  • Paul Clapham
  • Junilu Lacar
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Ganesh Patekar
  • Tim Moores
  • Pete Letkeman
  • Stephan van Hulst
  • Carey Brown
  • Tim Holloway
  • Joe Ess
  • 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.

      Bookmark Topic Watch Topic
    • New Topic
    Boost this thread!