In 12.0 we upgraded the Oracle Drivers from 10g to 11g.  First thing to test is to downgrade 12.0 to 10g drivers and see if the problem still exists.

We also optimized the way to extract results from a resultset.  AFScriptContext:Line-3888 :

connSettings.getTransformer().getTransformedValue()

TypeTransOraXMLType.java

I executed this ...

CREATE TABLE warehouses(
  warehouse_id NUMBER(4),
  warehouse_spec XMLTYPE,
  warehouse_name VARCHAR2(35),
  location_id NUMBER(4))
go
INSERT INTO warehouses VALUES
   (       100, XMLType(
              '<Warehouse whNo="100">
               <Building>Owned</Building>
               </Warehouse>'), 'Tower Records', 1003)
go
select warehouse_spec from warehouses
go

And I get the warning ...

Warning: Class Return type not known:2009
Error: .getObject(): null Column Name: WAREHOUSE_SPEC DataTypeName: SYS.XMLTYPE DataType: 2009

The Oracle 11g driver seems to be throwing an exception when extracting.

When I downgrade the Oracle driver to 10g in ADS 12.0, it works.

The Oracle 11g driver throws a null pointer exception when we call rs.getObject(index).  Here is the stack trace of the exception:

java.lang.NullPointerException
    at oracle.jdbc.driver.NamedTypeAccessor.getOracleObject(NamedTypeAccessor.java:302)
    at oracle.jdbc.driver.NamedTypeAccessor.getObject(NamedTypeAccessor.java:164)
    at oracle.jdbc.driver.NamedTypeAccessor.getObject(NamedTypeAccessor.java:121)
    at oracle.jdbc.driver.OracleResultSetImpl.getObject(OracleResultSetImpl.java:1108)
Error: .getObject(): null Column Name: DBMS_XMLGEN.GETXMLTYPE('SELECT''SAUSAGE''ASPIZZAFROMDUAL') DataTypeName: SYS.XMLTYPE DataType: 2009
 

Hi, so this problem is definitely with the Oracle 11g driver. I found several threads about people running into this issue. Here's one that also offers some suggested workarounds:

https://forums.oracle.com/forums/message.jspa?messageID=4094190

As suggested in this thread, you could do:

select to_clob(DBMS_XMLGEN.getxmltype('select ''sausage'' as pizza from dual')) from dual

The other option you have is to downgrade the Oracle JDBC driver in ADS. You can take the Oracle drivers from ADS 11 and:

"To downgrade the ADS JDBC drivers you need to delete the ADS ojdbc6.jar, orai18n.jar and xdb6.jar and copy the ojdbc14.jar or classes12.zip/.jar into the [ADS_HOME]/lib/drivers directory, making sure to rename them so their file extensions are all .jar" More detailed instructions are here:

https://www.aquaclusters.com/app/home/project/public/aquadatastudio/wikibook/Documentation12/page/193/Oracle-10g-JDBC-Drivers

ADS 12.0 is bundled with the Oracle JDBC 11.2.0.3, which is the latest 11g driver version.  Short term solution would be to backdrop to 10g drivers.  I'll move this issue to 13.0 for now so I can track it.

Any updates?

We suggest backdropping to the 10g drivers as this is a bug in the Oracle 11g driver.

Instructions on how to downgrade:

"To downgrade the ADS JDBC drivers you need to delete the ADS ojdbc6.jar, orai18n.jar and xdb6.jar and copy the ojdbc14.jar or classes12.zip/.jar into the [ADS_HOME]/lib/drivers directory, making sure to rename them so their file extensions are all .jar" More detailed instructions are here:

https://www.aquaclusters.com/app/home/project/public/aquadatastudio/wikibook/Documentation12/page/193/Oracle-10g-JDBC-Drivers