Re: Helping developers write a pl/sql wrapper to translate a nested table to jdbc VARRAY
- From: "Charles Schultz" <sacrophyte@xxxxxxxxx>
- To: Oracle-L <oracle-l@xxxxxxxxxxxxx>, "Job Miller" <jobmiller@xxxxxxxxx>
- Date: Thu, 25 Jan 2007 13:13:23 -0600
Thanks Job and Maxim - these have helped a LOT! We have a working example
using arrays on the Java side and VARRAY on the Oracle side. Works great,
but rather tedious as Maxim pointed out.
Now, my next goal is to convert the Oracle VARRAY to nested table. Creating
the Oracle objects is trivial, but when we attempt to initialize the array
in Java, it throws an error:
final String DATA_TYPE = "UILLINOIS.JOBLABORNESTEDTABLETYPE";
ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor(
DATA_TYPE,
db_connection);
String[] content = {
"88659", // NBRJLBD_PIDM
...
...
"1"}; // r_internal_record_id
Array array = new ARRAY(
arrayDescriptor,
db_connection,
content);
[java] 2007-01-25 10:58:38 ERROR - java.sql.SQLException: Fail to
convert to internal representation: 88659
[java] java.sql.SQLException: Fail to convert to internal
representation: 88659
[java] at oracle.jdbc.driver.DatabaseError.throwSqlException(
DatabaseError.java:111)
[java] at oracle.jdbc.driver.DatabaseError.throwSqlException(
DatabaseError.java:145)
[java] at oracle.jdbc.oracore.OracleTypeADT.toDatum(
OracleTypeADT.java:238)
[java] at oracle.jdbc.oracore.OracleTypeADT.toDatumArray(
OracleTypeADT.java:273)
[java] at oracle.jdbc.oracore.OracleTypeUPT.toDatumArray(
OracleTypeUPT.java:114)
[java] at oracle.sql.ArrayDescriptor.toOracleArray(
ArrayDescriptor.java:1313)
[java] at oracle.sql.ARRAY.<init>(ARRAY.java:151)
[java] at DBConnectionTester.testSQL1(DBConnectionTester.java:241)
[java] at DBConnectionTester.execute(DBConnectionTester.java:77)
[java] at
org.openeai.afa.ScheduledApp$ScheduledAppThread.executeSchedule(
ScheduledApp.java:306)
[java] at org.openeai.afa.ScheduledApp$ScheduledAppThread.run(
ScheduledApp.java:388)
[java] at java.lang.Thread.run(Thread.java:595)
My guess is that the array on the java side is made up of homogeneos
strings, whereas the oracle nested table is a mixture of numbers, dates and
character strings. Unfortunately, the java error does not point out
specifically where the problem is - could be a problem with the date format,
but we played around with several formats and could not find an reason that
is the problem. According to the JDBC Oracle documentation,
oracle.sql.ARRAYshould be smart enough to convert the java array to
the oracle collection,
so any help in figuring out this minor problem would be greatly appreciated!
I am sure we will another stumbling block later one, but this one has me
stumped.
PS - Maxim, do you know that your return address is invalid?
--
Charles Schultz
Other related posts:
