my dog learned polymorphism*
The moose likes JDBC and the fly likes Nested table of Nested tables in JDBC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Nested table of Nested tables in JDBC" Watch "Nested table of Nested tables in JDBC" New topic

Nested table of Nested tables in JDBC

Chaitanya Varanasi

Joined: Dec 09, 2004
Posts: 26
Hi All,
In my Oracle, i have three types as described below.

I have a stored procedure which has "PARAM_VALUE_COLL" as the Input parameter. I am confused on how to send this data from Java to the stored procedure. I have tried making an Object[][] for the data in the Array (SQL) ... but I get the following error java.sql.SQLException: Inconsistent java and sql object types

Need help on how to approach the issue.

Please do let me know if you need any more information from my side.

Thanks and Regards,
Chaitanya V
Martin Vajsar

Joined: Aug 22, 2010
Posts: 3606

I haven't ever used such a complicated type with Oracle, so I'm just guessing. However, your PARAM_VALUE_OBJ type is an object, not a table, and though it contains only one field of the STR_ARRAY type, I doubt you'll be ever able to map it into an array, which is what are you actually trying to do. You'll have to create a class on the Java side to represent the PARAM_VALUE_OBJ type and let JDBC know how to map it (I don't know how this is actually being done, all I've ever used were arrays of database types, such as NUMBERs).

Easier way could be to drop the PARAM_VALUE_OBJ type entirely and create the type PARAM_VALUE_COLL just as a table of STR_ARRAY. I guess this could be mapped onto a two dimensional array, though I've never used even two-dimensional arrays. However, if you plan to include other fields in the PARAM_VALUE_OBJ in the future, you clearly have to go the former way.

Also, use Oracle's latest JDBC Driver (that is the one corresponding to 11g). It should be backward compatible with older databases, at least up to 9i. In any case, you should probably delve into Oracle's JDBC documentation, especially chapter 13. Google up your version if you must use JDBC driver older than 11g.

Hope this helps at least a little bit. In either way, you may save yourself a lot of time if you read the documentation I've linked up for you.
Chaitanya Varanasi

Joined: Dec 09, 2004
Posts: 26
Thanks a lot Martin. Will look into this. Does anyone has any working example.

Thanks and Regards,
Chaitanya V
It is sorta covered in the JavaRanch Style Guide.
subject: Nested table of Nested tables in JDBC
Similar Threads
java.sql.SQLException: invalid name pattern
How to pass BLOB to a PL/SQL Stored Procedure
passing 2D arrays to Oracle Stored Procedure with/without using JPublisher
Passing String array as out parameter to stored procedure
Passing PL/SQL table from Stored Proc to Java