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.
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.
Joined: Dec 09, 2004
Thanks a lot Martin. Will look into this. Does anyone has any working example.