GeeCON Prague 2014*
The moose likes JDBC and the fly likes passing 2D arrays to Oracle Stored Procedure  with/without using JPublisher Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "passing 2D arrays to Oracle Stored Procedure  with/without using JPublisher" Watch "passing 2D arrays to Oracle Stored Procedure  with/without using JPublisher" New topic
Author

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

Arvind Sampath
Ranch Hand

Joined: May 11, 2005
Posts: 144
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
 
Don't get me started about those stupid light bulbs.
 
subject: passing 2D arrays to Oracle Stored Procedure with/without using JPublisher