passing 2D arrays to Oracle Stored Procedure with/without using JPublisher
posted 10 years ago
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 ?