5 Replies Latest reply: Jun 7, 2013 5:55 PM by dsurber RSS

    Bug in Oracle Driver 11.2.0.3.0 when handling CLOB?

    1011420

      Hello,

      I got a table which is defined like this:

      CREATE TABLE FOO
      (
      FOO_id NUMBER(18) NOT NULL ,
      xml_content sys.XMLTYPE NOT NULL
      )

      According to the documentation Oracle is using a CLOB as internal type.

      For reading the CLOB from the database we are using Spring's org.springframework.jdbc.support.lob.OracleLobHandler (Spring 3.0.6.RELEASE) class which is implemented like this:

      public String getClobAsString( ResultSet rs, int columnIndex ) throws SQLException
      {
      logger.debug( "Returning Oracle CLOB as string" );
      Clob clob = rs.getClob( columnIndex );
      initializeResourcesBeforeRead( rs.getStatement().getConnection(), clob );
      String retVal = ( clob != null ? clob.getSubString( 1, (int) clob.length() ) : null );
      releaseResourcesAfterRead( rs.getStatement().getConnection(), clob );
      return retVal;
      }

      For me, this looks like the valid solution. But when reading CLOB greater than 4109 bytes, the resulting String contains a 0x0 (NULL) byte. Because the table is defined to contain XML, the XML parser is unable to handle this.

      As workaround I got the following solution:

      public String getClobAsString( ResultSet rs, int columnIndex ) throws SQLException
      {
      logger.debug( "Returning Oracle CLOB as string" );
      Clob clob = rs.getClob( columnIndex );
      initializeResourcesBeforeRead( rs.getStatement().getConnection(), clob );
      readAllCharacter( clob );
      String retVal = ( clob != null ? clob.getSubString( 1, (int) clob.length() ) : null );
      releaseResourcesAfterRead( rs.getStatement().getConnection(), clob );
      return retVal;
      }

      /**
      * Dummy read of all characters of the given lob. This fixes an issue that the resulting string
      * contains 0x0 bytes.
      *
      * @param clob the clob
      * @throws SQLException
      */
      private void readAllCharacter( Clob clob ) throws SQLException
      {
      if( clob != null )
      {
      Reader characterStream = clob.getCharacterStream();
      char[] buffer = new char[4 * 1000];
      try
      {
      while( characterStream.read( buffer ) != -1 )
      {
      }
      }
      catch( IOException e )
      {
      logger.error( "Exception while reading from the clob", e );
      }
      finally
      {
      try
      {
      characterStream.close();
      }
      catch( IOException e )
      {
      // nothing to do;
      }
      }
      }
      }

      With this dummy read, the string does not contain the 0x0 token.

      I think it's a Bug in the oracle.sql.CLOB class or did I miss something?

      Kind regards
      Michael

        • 1. Re: Bug in Oracle Driver 11.2.0.3.0 when handling CLOB?
          rp0428
          >
          According to the documentation Oracle is using a CLOB as internal type.
          >
          What documentation?

          See 'Working with BLOBs, CLOBs and NCLOBs' in the JDBC Dev Guide
          http://docs.oracle.com/cd/B28359_01/java.111/b31224/oralob.htm#i1058035

          Also see this section in the doc
          >
          The oracle.jdbc.getObjectReturnsXMLType Property

          In Oracle Database 10g and earlier versions of Oracle Database 11g, Oracle JDBC drivers supported the Oracle SQL XML type (XMLType) through an Oracle proprietary extension. XML values were represented by instances of the oracle.xdb.XMLType class and the SQL XMLType values were read and set through the JDBC standard getObject, setObject, and updateObject methods.

          The JDBC standard requires the getObject method to return an instance of java.sql.SQLXML type when called on a SQL XML type column. But, the earlier versions of Oracle JDBC drivers return an instance of oracle.xdb.XMLType. This does not conform to the JDBC standard.

          The current release of Oracle JDBC drivers conform to the JDBC standard with the introduction of a new connection property, oracle.jdbc.getObjectReturnsXMLType. If you set this property to false, then the getObject method returns an instance of java.sql.SQLXML type. You can achieve this by using the following command line option while compiling your program with javac:

          -Doracle.jdbc.getObjectReturnsXMLType="false"
          If you depend on the existing Oracle proprietary support for SQL XMLType using oracle.xdb.XMLType, then you can change the value of this property back to true by using the following command line option:

          -Doracle.jdbc.getObjectReturnsXMLType="true"
          The value of the oracle.jdbc.getObjectReturnsXMLType property is a String representing a boolean value of either true or false. If the value of this property is true, then the getObject method returns oracle.xdb.XMLType instances, when called for a SQL XMLType column. This is the deafault value of the oracle.jdbc.getObjectReturnsXMLType property. If the value of this property is false, then the getObject method returns java.sql.SQLXML instances. This is the standard JDBC-compliant mode.

          Note:

          The oracle.jdbc.getObjectReturnsXMLType property affects only the result of the getObject method. All other methods conform to the JDBC 4.0 standard regardless of the value of the property.
          >
          The method you use will depend on the JDBC driver version you are using.
          • 2. Re: Bug in Oracle Driver 11.2.0.3.0 when handling CLOB?
            1011420
            Hi,
            thanks for you reply. I'm unsure what you mean with your citation and how this affects my code? I'm not using the getObject method.

            According to the linked documentation:
            >
            To read from a CLOB, use the getAsciiStream or getCharacterStream method of an java.sql.CLOB object to retrieve the entire CLOB as an input stream. The getAsciiStream method returns an ASCII input stream in a java.io.InputStream object. The getCharacterStream method returns a Unicode input stream in a java.io.Reader object.

            As with any InputStream or Reader object, use one of the overloaded read methods to read the LOB data and use the close method when you finish.

            You can also use the getSubString method of java.sql.CLOB object to retrieve a subset of the CLOB as a character string of type java.lang.String.>
            A call to getSubString should give me the content of the clob. But why does the content contain some bytes (0x0) which should not be there.
            When calling the dummy read method twice and storing the result in an byte array, the first read results also in an String with the 0x0 byte. The second call to the same method results to the expected string. For me this does not make any sense and looks like a bug.
            • 3. Re: Bug in Oracle Driver 11.2.0.3.0 when handling CLOB?
              rp0428
              >
              I'm unsure what you mean with your citation and how this affects my code?
              >
              That citation specifically tells you how the drivers (based on version) support 'the Oracle SQL XML type (XMLType)'.
              >
              A call to getSubString should give me the content of the clob. But why does the content contain some bytes (0x0) which should not be there.
              >
              A CLOB instance is just a LOB locator. A locator contains the information necessary for Oracle to 'locate' the full lob contents. It usually also contains all of the lob data that is stored 'inline' in the table row. There is a limit of around 4k for the 'inline' part of the lob data.

              A lob locator does NOT contain the part of the lob that is stored in the lob segment. The 'out-of-line' lob is generally retrieved using streams as shown in that doc.

              So the 'getSubString' call is only using the contents of the locator.

              See Inline and Out-of_Line LOB Storage in the 'Oracle® Database SecureFiles and Large Objects Developer's Guide'
              http://docs.oracle.com/cd/B28359_01/appdev.111/b28393/adlob_tables.htm
              >
              Inline and Out-of-Line LOB Storage
              LOB columns store locators that reference the location of the actual LOB value. Depending on the column properties you specify when you create the table, and depending the size of the LOB, actual LOB values are stored either in the table row (inline) or outside of the table row (out-of-line).

              LOB values are stored out-of-line when any of the following situations apply:

              If you explicitly specify DISABLE STORAGE IN ROW for the LOB storage clause when you create the table.

              If the size of the LOB is greater than approximately 4000 bytes (4000 minus system control information), regardless of the LOB storage properties for the column.

              If you update a LOB that is stored out-of-line and the resulting LOB is less than approximately 4000 bytes, it is still stored out-of-line.

              LOB values are stored inline when any of the following conditions apply:

              When the size of the LOB stored in the given row is small, approximately 4000 bytes or less, and you either explicitly specify ENABLE STORAGE IN ROW or the LOB storage clause when you create the table, or when you do not specify this parameter (which is the default).

              When the LOB value is NULL (regardless of the LOB storage properties for the column).

              Using the default LOB storage properties (inline storage) can allow for better database performance; it avoids the overhead of creating and managing out-of-line storage for smaller LOB values. If LOB values stored in your database are frequently small in size, then using inline storage is recommended.

              Note:

              LOB locators are always stored in the row.

              A LOB locator always exists for any LOB instance regardless of the LOB storage properties or LOB value - NULL, empty, or otherwise.

              If the LOB is created with DISABLE STORAGE IN ROW properties and the BASICFILE LOB holds any data, then a minimum of one CHUNK of out-of-line storage space is used; even when the size of the LOB is less than the CHUNK size.

              If a LOB column is initialized with EMPTY_CLOB() or EMPTY_BLOB(), then no LOB value exists, not even NULL. The row holds a LOB locator only. No additional LOB storage is used.

              LOB storage properties do not affect BFILE columns. BFILE data is always stored in operating system files outside the database.
              >
              You should generally use streams to read/write LOBs as they are the most efficient way to access them. If you commonly only need a subset of the data I suggest you use a PL/SQL package/function/procedure to perform the substringing and return the results.
              >
              the first read results also in an String with the 0x0 byte
              >
              And those would be part of the lob locator.
              • 4. Re: Bug in Oracle Driver 11.2.0.3.0 when handling CLOB?
                1011420
                Hi,
                thanks for your reply.
                So the 'getSubString' call is only using the contents of the locator.
                But this is not the behaviour as described in the JavaDoc of the JDK:
                http://docs.oracle.com/javase/6/docs/api/java/sql/Clob.html
                Here the getSubString method is described as
                >
                Retrieves a copy of the specified substring in the CLOB value designated by this Clob object. The substring begins at position pos and has up to length consecutive characters.>
                Where CLOB is used like this:
                By default drivers implement a Clob object using an SQL locator(CLOB), which means that a Clob object contains a logical pointer to the SQL CLOB data rather than the data itself.
                So according to the documentation the call "clob.getSubString(1, (int) clob.length())" should result in getting the whole content
                You should generally use streams to read/write LOBs as they are the most efficient way to access them. If you commonly only need a subset of the data I suggest you use a PL/SQL package/function/procedure to perform the substringing and return the results.
                But I'm doing so. I'm simply using the getSubString method because it is the default implementation by the Spring-Java-Framework. It's not my code and I always need the whole content and not only a subset.
                Lets say we replace my code for reading the value of the CLOB from the stream and remove the getSubString call. Then the code would look like the following example to get a valid result:

                public String getClobAsString( ResultSet rs, int columnIndex ) throws SQLException
                {
                logger.debug( "Returning Oracle CLOB as string" );
                Clob clob = rs.getClob( columnIndex );
                initializeResourcesBeforeRead( rs.getStatement().getConnection(), clob );
                String retValue = readAllCharacterFromTheCharacterStream( clob ); // first call result may contain 0x0 bytes
                retValue = readAllCharacterFromTheCharacterStream( clob ); // second call does not contain 0x0 bytes
                releaseResourcesAfterRead( rs.getStatement().getConnection(), clob );
                return retVal;
                }

                As you can see I have to call the method "readAllCharacterFromTheCharacterStream" twice to get a valid result. This does not make any sense to me.
                • 5. Re: Bug in Oracle Driver 11.2.0.3.0 when handling CLOB?
                  dsurber
                  XMLTYPE is an OPAQUE not a CLOB. I'm surprised this code works at all (not that I have any idea what most of it is trying to do). Just call rs.getSQLXML(columnIndex).getString().

                  An OPAQUE is a named type that is a bucket of bits that neither you nor I know how to interpret. XDB, the Oracle XML support code, does know how to interpret OPAQUEs that are SYS.XMLTYPE. It is the case that some of these SYS.XMLTYPE OPAQUEs contain CLOB locators, but not all. But that doesn't mean in any way that a SYS.XMLTYPE value is a CLOB. It is an OPAQUE. The only reasonable thing you can do with it is hand it over to XDB.

                  Douglas