Re: Helping developers write a pl/sql wrapper to translate a nested table to jdbc VARRAY
- From: Job Miller <jobmiller@xxxxxxxxx>
- To: Charles Schultz <sacrophyte@xxxxxxxxx>, Oracle-L <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 25 Jan 2007 13:12:10 -0800 (PST)
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@xxxxxxxxx> 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.
Other related posts:
