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

From: Job Miller <jobmiller_at_yahoo.com>
Date: Thu, 25 Jan 2007 13:12:10 -0800 (PST)
Message-ID: <20070125211210.41810.qmail@web53913.mail.yahoo.com>

while the process would have been the same... this probably could have saved you some time.. :)

 4.1.3 Using PL/SQL Types Through JPublisher JDBC does not support PL/SQL-specific types, such as the BOOLEAN type and PL/SQL RECORD types that are used in stored procedures or functions. JPublisher provides the following workarounds for PL/SQL types:  

    JPublisher has a type map that you can use to specify the mapping for a PL/SQL type unsupported by JDBC.  

    For PL/SQL RECORD types or indexed-by tables types, you have the choice of JPublisher automatically creating a SQL object type or SQL collection type, respectively, as an intermediate step in the mapping.

 With either workaround, JPublisher creates PL/SQL conversion functions or uses predefined conversion functions that are typically found in the SYS.SQLJUTL package to convert between a PL/SQL type and a corresponding SQL type. The conversion functions can be used in generated Java code that calls a stored procedure directly, or JPublisher can create a wrapper function around the PL/SQL stored procedure, where the generated Java code calls the wrapper function, which calls the conversion functions. Either way, only SQL types are exposed to JDBC.

Charles Schultz <sacrophyte_at_gmail.com> wrote: 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.ARRAY should 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 

 
---------------------------------
8:00? 8:25? 8:40?  Find a flick in no time
 with theYahoo! Search movie showtime shortcut.
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 25 2007 - 15:12:10 CST