Re: Helping developers write a pl/sql wrapper to translate a nested table to jdbc VARRAY

From: Charles Schultz <sacrophyte_at_gmail.com>
Date: Thu, 25 Jan 2007 13:13:23 -0600
Message-ID: <7b8774110701251113q68dcdcb4qa2c9ce2bd738b416@mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 25 2007 - 13:13:23 CST