Why I can't extract this value from an CLOB XML using XMLType into a JDBC query?

Question

I am going crazy trying to use xmltype to extract XML field value from a CLOB field of an Oracle DB table.

So I have the following query:

SELECT
  'ENELSERV'    AS codcliente,
  'SL'          AS sottosistema,
  'DD01'        AS account,
  substr(xmltype(d.documento)
           .EXTRACT('//cbc:IssueDate/text()').getStringVal()
        , 1, 4) AS anno
 FROM coda_tx c,
      documenti_tx d
WHERE c.fk_tipo_doc = 99
  AND c.fk_stato = 1
  AND c.pk_coda = d.pfk_coda
  AND c.canale = 'STA'
  AND c.fk_piva_mittente = '05779711000'

If I perform this query into Oracle SQL Developer I obtain the espected result, this table with these 2 rows:

CODCLIENTE  SOTTOSISTEMA  ACCOUNT  ANNO
----------------------------------------
MYCLIENT    SL            DD01     2014
MYCLIENT    SL            DD01     2014

So the query works fine and I obtain the espected result.

Now I have to put the previous query into a JDBC DAO class and I done in this way:

public void getListaFatturePDF(String partitaIva) {
    System.out.println("INTO ottieniListaFatturePDF()");

    Blob blobFibalPdf;
    String sql;

    StringBuffer sb = new StringBuffer();

    sb.append("SELECT 'ENELSERV' as CODCLIENTE, 'SL' as SOTTOSISTEMA, 'DD01' as ACCOUNT, ");
    sb.append("substr(xmltype(d.documento).EXTRACT('//cbc:IssueDate/text()').getStringVal (),1,4) as ANNO ");
    sb.append("from coda_tx c, documenti_tx d ");
    sb.append("WHERE   C.FK_TIPO_DOC = 99 ");
    sb.append("AND C.FK_STATO = 1 ");
    sb.append("AND C.PK_CODA = D.PFK_CODA ");
    sb.append("AND C.CANALE='STA' ");
    sb.append("AND C.FK_PIVA_MITTENTE = '05779711000'");

    sql = sb.toString();

    try {
        statment = connection.createStatement();
        ResultSet rs = statment.executeQuery(sql);
        System.out.println("ResultSet obtained");

        //STEP 5: Extract data from result set
        while(rs.next()){

            // Retrieve by column name:
            String codCliente = rs.getString("CODCLIENTE");
            String sottosistema = rs.getString("SOTTOSISTEMA");
            String account = rs.getString("ACCOUNT");
            String anno = rs.getString("ANNO");

            // Display values:
            System.out.println("codCliente: " + codCliente);
            System.out.println("sottosistema: " + sottosistema);
            System.out.println("account: " + account);
            System.out.println("anno: " + anno);
        }

    } catch (SQLException e) {
        e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
    }

The problem is that when the previous method is performed I obtain the correct valies of the CODCLIENTE, SOTTOSISTEMA, ACCOUNT fields but the ANNO field value is an empty String, infact this is what I obtain in the console (the println result):

INTO ottieniListaFatturePDF()
ResultSet obtained
codCliente: ENELSERV
sottosistema: SL
account: DD01
anno: 

Why the previous query works fine if it is performed directly into Oracle SQL Developer but have this problem when I try to use it into my JDBC DAO method?

Could be maybe something related to character that have to be escaped into:

sb.append("substr(xmltype(d.documento).EXTRACT('//cbc:IssueDate/text()').getStringVal (),1,4) as ANNO ");

What am I missing? How can I fix this issue?

Tnx

Answer 1

Solved by myself. I change the JDBC driver into my project using the ojdbc.jar file that is into the Oracle SQL Developer installation directory (I think that is an older version but it wors).

This is the MANIFEST.MF of the don't working drive version:

Manifest-Version: 1.0
Ant-Version: Apache Ant 1.7.1
Created-By: 20.75-b01 (Sun Microsystems Inc.)
Implementation-Vendor: Oracle Corporation
Implementation-Title: JDBC
Implementation-Version: 12.1.0.2.0
Repository-Id: JAVAVM_12.1.0.2.0_LINUX.X64_140630
Specification-Vendor: Sun Microsystems Inc.
Specification-Title: JDBC
Specification-Version: 4.0
Main-Class: oracle.jdbc.OracleDriver
sealed: true

Name: oracle/sql/converter/
Sealed: false

Name: oracle/sql/
Sealed: false

Name: oracle/sql/converter_xcharset/
Sealed: false

And this is the MANIFEST.MF of the working one:

Manifest-Version: 1.0
Ant-Version: Apache Ant 1.6.5
Created-By: 1.5.0_24-rev-b08 (Sun Microsystems Inc.)
Implementation-Vendor: Oracle Corporation
Implementation-Title: JDBC
Implementation-Version: 11.2.0.2.0
Repository-Id: JAVAVM_11.2.0.2.0_LINUX_100812.1
Specification-Vendor: Sun Microsystems Inc.
Specification-Title: JDBC
Specification-Version: 4.0
Main-Class: oracle.jdbc.OracleDriver
sealed: true

Name: oracle/sql/converter/
Sealed: false

Name: oracle/sql/
Sealed: false

Name: oracle/sql/converter_xcharset/
Sealed: false