• Post Reply Bookmark Topic Watch Topic
  • New 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

passing 2D arrays to Oracle Stored Procedure with/without using JPublisher

 
Ranch Hand
Posts: 144
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Our application is J2EE based with Oracle 8i as back end. The database logic resides in the Oracle Stored Procedures & our Java database classes invoke these stored procedures for the datbase operations.

We have a requirement where we are supposed to pass a 2D array from Java to the Oracle procedure. We have no problems passing a 1D array through JDBC. But we are facing certain issues in case of passing 2D arrays. For passing 1D arrays, we have created a User defined SQL datatype in oracle using the below script.

create or replace type NUM_ARRAY as table of number;

In our database class, we used OracleCallableStatement to pass the 1D array. We thought the same concept could be extended to 2D arrays. But it is not possible to create nested data types in Oracle 8i using the below script

create or replace type NUM_ARRAY _2D as table of NUM_ARRAY;

This feature is there in Oracle 9i but not in Oracle 8i. So, we created a user defined Object type as below

create or replace type NUM_ARRAY AS object(data NUMBER(9,0));

and we created another user defined data type which will represent the array as below

create or replace type NUM_ARRAY_2D as table of NUM_ARRAY;


To pass user defined object types through JDBC, we found that JPublisher should be used so that the internal object representations of Oracle & Java could be matched. JPublisher will create a class that will represent the SQL object type and the class will be used in our JDBC logic.

I google for JPublisher & have found enough docs. Is it advisable to use JPublisher. It would be great if those who have used it can share their experiences.

Is there any alternative solution to my problem, one without using JPublisher ?

Thanks,
Arvind
reply
    Bookmark Topic Watch Topic
  • New Topic