This discussion is archived
11 Replies Latest reply: Feb 7, 2013 10:53 AM by odie_63 RSS

XMLType store as Blob and read as Clob

975826 Newbie
Currently Being Moderated
HI,
We are using oracle 11.2.0.2 release. We are facing one issue on this. In this version oracle have changed default storage for XML type to Blob. When we are reading any stored xml with more than 4000 characters as CLOB we are only getting 2000 characters and remainging is getting truncated. For workaround we changed storage to CLOB and when we are reading it, it is working fine. We are using XMLType for unstructured data.

Edited by: 972823 on Nov 22, 2012 9:51 PM
  • 1. Re: XMLType store as Blob and read as Clob
    odie_63 Guru
    Currently Being Moderated
    Hi,

    You're gonna have to give us a clear test case that shows the issue.

    How and where are you fetching the data?

    Is it not just a display issue, client-side?


    The following works for me, please tell us what you're doing differently :
    SQL> create table tmp_xml of xmltype;
     
    Table created
     
    SQL> 
    SQL> insert into tmp_xml values (
      2   dbms_xmlgen.getXMLType('select * from hr.employees')
      3  );
     
    1 row inserted
     
    SQL> commit;
     
    Commit complete
     
    SQL> 
    SQL> select dbms_lob.getlength(
      2           xmlserialize(document object_value as clob)
      3         ) as xml_length
      4  from tmp_xml;
     
    XML_LENGTH
    ----------
         39760
     
    SQL> set long 50000
    SQL> 
    SQL> select xmlserialize(document object_value as clob)
      2  from tmp_xml;
     
    XMLSERIALIZE(DOCUMENTOBJECT_VA
    --------------------------------------------------------------------------------
    <ROWSET>
      <ROW>
        <EMPLOYEE_ID>100</EMPLOYEE_ID>
        <FIRST_NAME>Steven</FIRST_NAME>
        <LAST_NAME>King</LAST_NAME>
        <EMAIL>SKING</EMAIL>
        <PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
        <HIRE_DATE>17/06/03</HIRE_DATE>
        <JOB_ID>AD_PRES</JOB_ID>
        <SALARY>24000</SALARY>
        <DEPARTMENT_ID>90</DEPARTMENT_ID>
      </ROW>
      <ROW>
        <EMPLOYEE_ID>101</EMPLOYEE_ID>
        <FIRST_NAME>Neena</FIRST_NAME>
        <LAST_NAME>Kochhar</LAST_NAME>
        <EMAIL>NKOCHHAR</EMAIL>
        <PHONE_NUMBER>515.123.4568</PHONE_NUMBER>
        <HIRE_DATE>21/09/05</HIRE_DATE>
        <JOB_ID>AD_VP</JOB_ID>
        <SALARY>17000</SALARY>
        <MANAGER_ID>100</MANAGER_ID>
        <DEPARTMENT_ID>90</DEPARTMENT_ID>
      </ROW>
      <ROW>
        <EMPLOYEE_ID>102</EMPLOYEE_ID>
        <FIRST_NAME>Lex</FIRST_NAME>
        <LAST_NAME>De Haan</LAST_NAME>
        <EMAIL>LDEHAAN</EMAIL>
        <PHONE_NUMBER>515.123.4569</PHONE_NUMBER>
        <HIRE_DATE>13/01/01</HIRE_DATE>
        <JOB_ID>AD_VP</JOB_ID>
        <SALARY>17000</SALARY>
        <MANAGER_ID>100</MANAGER_ID>
        <DEPARTMENT_ID>90</DEPARTMENT_ID>
      </ROW>
    
      <!-- snip -->
    
      <ROW>
        <EMPLOYEE_ID>205</EMPLOYEE_ID>
        <FIRST_NAME>Shelley</FIRST_NAME>
        <LAST_NAME>Higgins</LAST_NAME>
        <EMAIL>SHIGGINS</EMAIL>
        <PHONE_NUMBER>515.123.8080</PHONE_NUMBER>
        <HIRE_DATE>07/06/02</HIRE_DATE>
        <JOB_ID>AC_MGR</JOB_ID>
        <SALARY>12008</SALARY>
        <MANAGER_ID>101</MANAGER_ID>
        <DEPARTMENT_ID>110</DEPARTMENT_ID>
      </ROW>
      <ROW>
        <EMPLOYEE_ID>206</EMPLOYEE_ID>
        <FIRST_NAME>William</FIRST_NAME>
        <LAST_NAME>Gietz</LAST_NAME>
        <EMAIL>WGIETZ</EMAIL>
        <PHONE_NUMBER>515.123.8181</PHONE_NUMBER>
        <HIRE_DATE>07/06/02</HIRE_DATE>
        <JOB_ID>AC_ACCOUNT</JOB_ID>
        <SALARY>8300</SALARY>
        <MANAGER_ID>205</MANAGER_ID>
        <DEPARTMENT_ID>110</DEPARTMENT_ID>
      </ROW>
    </ROWSET>
     
  • 2. Re: XMLType store as Blob and read as Clob
    user485536 Newbie
    Currently Being Moderated
    I faced this issue (using JDBC driver) too and blogged about it http://lindseyinit.blogspot.com/2012/09/xmlserialize-returns-value-truncated-to.html

    XMLSERIALIZE returns value truncated to 2000/4000 char
    Seems like using XMLSERIALIZE or getClobVal on a XMLTYPE column stored as BINARY XML returns value truncated to 2000/4000 char without any error.
    Seems like the only way to retrieve the data is to retrieve XMLTYPE using JDBC getObject which returns oracle.xdb.XMLType.
    It is good in its way. It forces people to use more effective way of XML retrieval as using oracle.xdb.XMLType will get data from Oracle DB in its binary representation.

    It is probably a bug in Oracle JDBC thin driver versions 11.2.0.1, 11.2.0.2, 11.2.0.3 but could be TNS protocol bug too.
  • 3. Re: XMLType store as Blob and read as Clob
    mdrake Expert
    Currently Being Moderated
    We need a testcase.
    SQL> truncate table play_table;
    
    Table truncated.
    
    SQL> desc PLAY_TABLE;
     Name                                                                                                      Null?    Type
     ----------------------------------------------------------------------------------------------------------------- -------- -----------------------------------
    TABLE of PUBLIC.XMLTYPE STORAGE BINARY
    and given
    package com.oracle.st.xmldb.pm.examples;
    
    import com.oracle.st.xmldb.pm.common.baseApp.BaseApplication;
    
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.IOException;
    
    import java.io.Reader;
    
    import java.sql.SQLException;
    
    import oracle.jdbc.OracleCallableStatement;
    
    import oracle.jdbc.OracleResultSet;
    
    import oracle.sql.CLOB;
    
    import oracle.xdb.XMLType;
    
    import oracle.xml.binxml.BinXMLException;
    import oracle.xml.binxml.BinXMLMetadataProviderFactory;
    import oracle.xml.binxml.DBBinXMLMetadataProvider;
    
    import org.xml.sax.SAXException;
    
    public class StoreAndSerialize extends BaseApplication {
        public StoreAndSerialize() throws SAXException, IOException, SQLException {
            super();
        }
        public void loadDocument() 
        throws SQLException, BinXMLException, FileNotFoundException {
     
        String statementText;
        statementText = "insert into PLAY_TABLE values(:1)";
        OracleCallableStatement statement = (OracleCallableStatement) getConnection().prepareCall(statementText);
            
        DBBinXMLMetadataProvider repos = BinXMLMetadataProviderFactory.createDBMetadataProvider();
        repos.setConnection(getConnection());
        repos.associateDataConnection(getConnection());
            
        FileInputStream is = new FileInputStream("C:\\xdb\\Demo\\11.2.0.1.0\\LATEST\\XFILES.5\\sampleData\\XML\\Plays\\Tragedies\\The Tragedy of Hamlet, Prince of Denmark.xml");
        XMLType xml = new XMLType(getConnection(),is);
        xml.setPicklePreference(XMLType.XMLTYPE_PICKLE_AS_BINXML);            
        xml.setMetadataConn(getConnection());    
        statement.setObject(1,xml);
        boolean result = statement.execute();
        statement.close();
        getConnection().commit();      
        }
        
        public void fetchDocument() throws SQLException, IOException {
            String statementText;
            statementText = "select XMLSERIALIZE(DOCUMENT OBJECT_VALUE as CLOB) from PLAY_TABLE";
            OracleCallableStatement statement = (OracleCallableStatement) getConnection().prepareCall(statementText);
            
            OracleResultSet resultSet =(OracleResultSet) statement.executeQuery();
            
            int docCount = 0;
            while (resultSet.next()) {
                docCount++;
                CLOB clob = resultSet.getCLOB(1);
                char[] buffer = new char[CLOB.MAX_CHUNK_SIZE];
                Reader reader = clob.getCharacterStream();
                int n;
                int m = 0;
                while (-1 != (n = reader.read(buffer)))  {
                    m = m + n;
                }
                System.out.println("Document (" + docCount + "). Bytes read = " + m);
                   
            }
            resultSet.close();
            statement.close();
        }
         
        public void doSomething(String[] args) throws SQLException, BinXMLException, FileNotFoundException, IOException {
            loadDocument();
            fetchDocument();
            getConnection().close();
        }
            
        public static void main( String [] args )
        {
          try  {
            StoreAndSerialize app = new StoreAndSerialize();
            app.doSomething(args);
          }
          catch( Exception e )
          {
             e.printStackTrace();
          }
        }  
    
    }
    I get
    C:\Temp>
    "C:\Program Files\Java\jdk1.6.0_33\bin\javaw.exe" -server -classpath C:\xdb\JDeveloper\SimpleExamples\.adf;C:\xdb\JDeveloper\Classes;C:\app\oracle\product\11.2.0\dbhome_1\RDBMS\jlib\servlet.jar;C:\app\oracle\product\11.2.0\dbhome_1\jdbc\lib\ojdbc6.jar;C:\app\oracle\product\11.2.0\dbhome_1\ucp\lib\ucp.jar;C:\app\oracle\product\11.2.0\dbhome_1\RDBMS\jlib\xdb.jar;C:\app\oracle\product\11.2.0\dbhome_1\LIB\xmlparserv2.jar -Dcom.oracle.st.xmldb.pm.ConnectionParameters=C:\\xdb\\jdeveloper\\SimpleExamples\\Connection.xml com.oracle.st.xmldb.pm.examples.StoreAndSerialize -mx2048M
    2012-11-26T04:11:53.732 : ConnectionManager.createConnection(): Connecting as SCOTT/tiger@jdbc:oracle:thin:@//localhost:1521/ORA11203.win_x64.mark.drake.oracle.com
    2012-11-26T04:11:53.935 : ConnectionManager.createConnection(): Database Connection Established
    Document (1). Bytes read = 340506
    Process exited with exit code 0.
    So I have read 340K from an XMLType store as SECUREFILE binary XML using XMLSerialize.

    That said, it is still better practice to use the XMLType directly in the JDBC program.
  • 4. Re: XMLType store as Blob and read as Clob
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Could you do me a favor tomorrow Mark and give a code example of the correct way of doing it, here, as well.
    In that case I can create a book mark or URL reference on my site regarding "the way of doing it" referencing this thread for future reference.
  • 5. Re: XMLType store as Blob and read as Clob
    mdrake Expert
    Currently Being Moderated
        public void fetchDocumentAsXML() throws SQLException, IOException {
            String statementText;
            statementText = "select OBJECT_VALUE from PLAY_TABLE";
            OracleCallableStatement statement = (OracleCallableStatement) getConnection().prepareCall(statementText);
            
            OracleResultSet resultSet =(OracleResultSet) statement.executeQuery();
            
            int docCount = 0;
            while (resultSet.next()) {
                docCount++;
                XMLType xml = (XMLType) resultSet.getObject(1);
                System.out.println("Document (" + docCount + "). Bytes read = " + xml.getStringVal().length());
                xml.close();
            }
            resultSet.close();
            statement.close();
        }
  • 6. Re: XMLType store as Blob and read as Clob
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Thanks

    ;-)
  • 7. Re: XMLType store as Blob and read as Clob
    917188 Newbie
    Currently Being Moderated
    Hi folks,

    I wonder if you could help me with this - I've changed my XMLType creation to the following:
            XMLType xml = new XMLType(myConnection,myInputStream);
            xml.setPicklePreference(XMLType.XMLTYPE_PICKLE_AS_BINXML);
            xml.setMetadataConn(myConnection);
            return xml;
    but "setPicklePreference" throws the following error:

    java.sql.SQLException: Attempt to write a SQLXML that is not writeable.
         at oracle.xdb.XMLType.setPicklePreference(XMLType.java:794)

    Could it be a jar versioning issue?

    I'm using:

    ojdbc6.jar (2,651kb, MANIFEST.MF has "Implementation-Version: 11.2.0.3.0")
    xdb6.jar (257kb)
    xmlparserv2.jar (1,429kb, taken from SQLDeveloper 3.2.2.0)

    Any help appreciated.

    Regards
    Larry
  • 8. Re: XMLType store as Blob and read as Clob
    odie_63 Guru
    Currently Being Moderated
    914185 wrote:
    Could it be a jar versioning issue?
    It might.
    There are a lot of discrepancies among the different distributions of xmlparserv2.

    Could you try with the jar bundle with the database?
  • 9. Re: XMLType store as Blob and read as Clob
    mdrake Expert
    Currently Being Moderated
    Please try using ojdbc6.jar and xdb6.jar from the oracle_home
  • 10. Re: XMLType store as Blob and read as Clob
    917188 Newbie
    Currently Being Moderated
    Thanks for the replies.

    I got the same message when I tried that test with the following classpath:

    java -cp $ORACLE_HOME/rdbms/jlib/xdb6.jar:$ORACLE_HOME/lib/xmlparserv2.jar:$ORACLE_HOME/jdbc/lib/ojdbc6.jar:. Standalone

    java.sql.SQLException: Attempt to write a SQLXML that is not writeable.
    at oracle.xdb.XMLType.setPicklePreference(XMLType.java:794)
    at Standalone.runTest(Standalone.java:68)
    at Standalone.main(Standalone.java:82)

    Standalone.java basically does the following - it tries to insert an XML file into an XMLType table:
        public void runTest()
        throws Exception{
    
           connection = DriverManager.getConnection("jdbc:oracle:thin:@mydb:1521:orcl", "usr", "pass");
           OracleCallableStatement  statement = null;
           statement = (OracleCallableStatement) connection.prepareCall("insert into my_test values(:1)");
    
           FileInputStream is = new FileInputStream("/home/oracle/file.xml");
           XMLType xml = new XMLType(connection,is);
           xml.setPicklePreference(XMLType.XMLTYPE_PICKLE_AS_BINXML);
           xml.setMetadataConn(connection);
           statement.setObject(1,xml);
           boolean result = statement.execute();
    
           statement.close();
           connection.commit();
           connection.close();
        }
    The database has patch 13493330 applied, and v$version contains:

    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
    "CORE11.2.0.3.0     Production"
    TNS for Linux: Version 11.2.0.3.0 - Production
    NLSRTL Version 11.2.0.3.0 - Production

    Any other insights appreciated.

    Regards
    Larry
  • 11. Re: XMLType store as Blob and read as Clob
    odie_63 Guru
    Currently Being Moderated
    It works with xdb_g.jar instead of xdb6.jar.

    The XMLType class implemented in xdb6 jar has an additional field that controls if an instance is writeable or not, and it seems it's almost always set to false. I don't know the reason.
    Surely the XDK team may answer that...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points