OpenJPA
  1. OpenJPA
  2. OPENJPA-1691

Oracle XMLType column failed to insert/update when xml contains more than 4000 characters

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.2.2, 2.0.0, 2.0.1
    • Fix Version/s: 1.2.3, 1.3.0, 2.0.2, 2.1.0
    • Component/s: sql
    • Environment:
      Oracle 10g, JDBC driver (ojdbc14.jar) 10.2.0.4.0, OpenJPA 1.2.2

      Description

      Oracle throws the following exception when XML content is greater than 4000 characters:
      ORA-01461: can bind a LONG value only for insert into a LONG column

      1. OPENJPA-1691_2.0.x.patch
        12 kB
        Jeremy Bauer
      2. OPENJPA-1691.1.2.x.patch
        11 kB
        Jeremy Bauer

        Issue Links

          Activity

          Hide
          Milosz Tylenda added a comment -

          Some work notes:

          Neither PreparedStatement.setCharacterStream(int, Reader, int) nor OraclePreparedStatement.setStringForClob(int, String) helps here.
          So far I have found two methods that seem to work with data over 4000 chars:

          a)
          1. Prepare a statement using XMLType constructor "insert into TORDERXML values(?, ?, ?, XMLType, ?, ?, ?)"
          2. Set the value by using PreparedStatement.setClob(int, Reader). This requires JDBC 4 (JDK 6). Tried with 11.2.0.1 driver.

          b)
          1. Prepare a statement using XMLType constructor "insert into TORDERXML values(?, ?, ?, XMLType, ?, ?, ?)"
          2. Create a temporary CLOB, fill it with XML data (similarly to [1]) and use PreparedStatement.setClob(int, Clob) or PreparedStatement.setObject(int, Object). This requires using Oracle-specific API and the temporary CLOB should be freed after statement execution. This works with 10.2.0.4 driver but 11.2.0.1 throws a bunch of ORAs:
          ORA-31011: XML parsing failed
          ORA-19202: Error occurred in XML processing
          LPX-00229: input source is empty
          Error at line 0
          ORA-06512: at "SYS.XMLTYPE", line 254
          ORA-06512: at line 1

          Neither method is a workaround for users to be applied solely in DBDictionary but requires a patch.

          Kelvin, which JDK are you using?

          [1] http://www.oracle.com/technology/sample_code/tech/java/codesnippet/xmldb/Example_Code.html#createclob

          Show
          Milosz Tylenda added a comment - Some work notes: Neither PreparedStatement.setCharacterStream(int, Reader, int) nor OraclePreparedStatement.setStringForClob(int, String) helps here. So far I have found two methods that seem to work with data over 4000 chars: a) 1. Prepare a statement using XMLType constructor "insert into TORDERXML values(?, ?, ?, XMLType , ?, ?, ?)" 2. Set the value by using PreparedStatement.setClob(int, Reader). This requires JDBC 4 (JDK 6). Tried with 11.2.0.1 driver. b) 1. Prepare a statement using XMLType constructor "insert into TORDERXML values(?, ?, ?, XMLType , ?, ?, ?)" 2. Create a temporary CLOB, fill it with XML data (similarly to [1] ) and use PreparedStatement.setClob(int, Clob) or PreparedStatement.setObject(int, Object). This requires using Oracle-specific API and the temporary CLOB should be freed after statement execution. This works with 10.2.0.4 driver but 11.2.0.1 throws a bunch of ORAs: ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00229: input source is empty Error at line 0 ORA-06512: at "SYS.XMLTYPE", line 254 ORA-06512: at line 1 Neither method is a workaround for users to be applied solely in DBDictionary but requires a patch. Kelvin, which JDK are you using? [1] http://www.oracle.com/technology/sample_code/tech/java/codesnippet/xmldb/Example_Code.html#createclob
          Hide
          Kelvin Law added a comment -

          Hi Milosz,

          I am using Sun JDK 1.5.0_22

          Show
          Kelvin Law added a comment - Hi Milosz, I am using Sun JDK 1.5.0_22
          Hide
          Milosz Tylenda added a comment - - edited

          The fix requires a JDBC 4 driver. There are still issues when querying over long individual XML nodes coming from obscure limits in Oracle's extractValue() function.

          Note that reading XML values longer than 4000 bytes was also broken - replaced getStringVal() calls with getClobVal().

          As a side effect users gained a bit of control on INSERT/UPDATE statements in DBDictionary.getMarkerForInsertUpdate method. One can do things like INSERT INTO tab(id, uppercased_name) VALUES (?, UPPER).

          Show
          Milosz Tylenda added a comment - - edited The fix requires a JDBC 4 driver. There are still issues when querying over long individual XML nodes coming from obscure limits in Oracle's extractValue() function. Note that reading XML values longer than 4000 bytes was also broken - replaced getStringVal() calls with getClobVal(). As a side effect users gained a bit of control on INSERT/UPDATE statements in DBDictionary.getMarkerForInsertUpdate method. One can do things like INSERT INTO tab(id, uppercased_name) VALUES (?, UPPER ).
          Hide
          Michael Dick added a comment -

          Closing issue which has been resolved for some time. If you believe the issue is not resolved please reopen or open a new issue.

          Show
          Michael Dick added a comment - Closing issue which has been resolved for some time. If you believe the issue is not resolved please reopen or open a new issue.
          Hide
          Mitchell Arends added a comment -

          This issue also affects the 2.0.2 builds as well. Is there a known workaround for this issue for the 2.0.2 release?

          Show
          Mitchell Arends added a comment - This issue also affects the 2.0.2 builds as well. Is there a known workaround for this issue for the 2.0.2 release?
          Hide
          Milosz Tylenda added a comment -

          Mitchell, you can fix reading of XML values by extending the OracleDictionary and setting the getStringVal property to ".getClobVal()".

          For insert/update, I am afraid there might be no workaround or one is not easy. First, in your extended OracleDictionary you overrride setClobString method to use the JDBC 4 variant of setClob mentioned above. Second, you provide your own UpdateManager via openjpa.jdbc.UpdateManager property and somehow make it use your own implementation of Row interface to emit "XMLType" in INSERT and UPDATEs.

          See also a thread on users list [1] and let us know if you succeed.

          [1] http://openjpa.208410.n2.nabble.com/Oracle-XMLType-fetch-problems-td6208344.html#a6252836

          Show
          Milosz Tylenda added a comment - Mitchell, you can fix reading of XML values by extending the OracleDictionary and setting the getStringVal property to ".getClobVal()". For insert/update, I am afraid there might be no workaround or one is not easy. First, in your extended OracleDictionary you overrride setClobString method to use the JDBC 4 variant of setClob mentioned above. Second, you provide your own UpdateManager via openjpa.jdbc.UpdateManager property and somehow make it use your own implementation of Row interface to emit "XMLType " in INSERT and UPDATEs. See also a thread on users list [1] and let us know if you succeed. [1] http://openjpa.208410.n2.nabble.com/Oracle-XMLType-fetch-problems-td6208344.html#a6252836
          Hide
          Jeremy Bauer added a comment -

          Re-opening to post a patch for pre-2.1.x releases.

          Show
          Jeremy Bauer added a comment - Re-opening to post a patch for pre-2.1.x releases.
          Hide
          Jeremy Bauer added a comment -

          The fix for this issue in 2.1.0 involved full JDBC 4.0-level support within OpenJPA. This is not possible on releases prior to 2.1.0 since they support Java 1.5. Thus, the same fix for this issue is not possible on pre-2.1.x releases. Using reflection, I was able to produce a patch that works with prior releases and still compiles and works with Java 1.5. The code reflectively calls JDBC 4.0 methods to set the XML value as a clob input reader. JRE 1.6 and an Oracle 1.6 (ojdbc6.jar) are required. In addition, the OracleDictionary supportsSetClob value must be set to true in order to enable the support. If DBCP is used, at least DBCP 1.4 is required since it supports call-thru of JDBC 4.0 connection and prepared statement methods.

          Show
          Jeremy Bauer added a comment - The fix for this issue in 2.1.0 involved full JDBC 4.0-level support within OpenJPA. This is not possible on releases prior to 2.1.0 since they support Java 1.5. Thus, the same fix for this issue is not possible on pre-2.1.x releases. Using reflection, I was able to produce a patch that works with prior releases and still compiles and works with Java 1.5. The code reflectively calls JDBC 4.0 methods to set the XML value as a clob input reader. JRE 1.6 and an Oracle 1.6 (ojdbc6.jar) are required. In addition, the OracleDictionary supportsSetClob value must be set to true in order to enable the support. If DBCP is used, at least DBCP 1.4 is required since it supports call-thru of JDBC 4.0 connection and prepared statement methods.
          Hide
          Jeremy Bauer added a comment -

          Attached patch for 1.2.x stream

          Show
          Jeremy Bauer added a comment - Attached patch for 1.2.x stream
          Hide
          Jeremy Bauer added a comment -

          Attached patch for 2.0.x.

          Show
          Jeremy Bauer added a comment - Attached patch for 2.0.x.
          Hide
          Heath Thomann added a comment -

          I'd like to add a few more details about the code changes made to 1.2.x and 2.0.x. These details are in addition to the previous (and vitally important) details added by Jeremy. In addition to his requirements, it should be noted that the code changes made in this JIRA are gated (enabled/disabled) via a system property. The system property is named 'supportsSetClob' and is defined on the Oracle dictionary class. As an example, the property can be defined as follows:

          <property name="openjpa.jdbc.DBDictionary" value="oracle(supportsSetClob=true)"/>           

          Next, it should be noted that we expect the XML field(s) to be annotated with:                 
                                                                                  
          @Strategy("org.apache.openjpa.jdbc.meta.strats.XMLValueHandler")        
                                                                                  
          This allows a user to indicate that OpenJPA should use JAXB classmetadata to perform the to/from database value retrieval and storage. In addition, using this strategy indicates there is an XMLType used to store the value in the database.  Instead, we've seen customers attempt to managing their own XML string, mapped to a string field, and expect the code changes of this JIRA to apply. While a user has an XMLType specified in the column definition, OpenJPA cannot count on this data to detect whether the column is really an XML column in the database (the table could have been created manually with separate DDL instead of using OpenJPA's mapping tool).  
          In this case, where the user is doing their own String mapping rather than using the XMLValueHandler, there are some additional settings a user may need to set/enable in order to resolve the issue addressed in this JIRA. The necessary settings/actions are as follows:

          First, it may be necessary to annotate an XML field with @Lob, as follows:                                                        
                                                                                  
          @Lob                                                                    
          @Column(name="CONTENTS_XML", columnDefinition ="XMLCOLUMN XMLType") 
          private String contentsXml;                                            
                                                                                  
          Second, in addition to annotating the XML field with @Lob, a user may also need to set this dictionary property:                                                               
                                                                                  
          maxEmbeddedClobSize=-1                                                  
                                                                                  
          Therefore, the final DBDictionary property would be:
                                                                                  
          <property name="openjpa.jdbc.DBDictionary" value="oracle(supportsSetClob=true,maxEmbeddedClobSize=-1)"/>           
                                                                                 
                                                                                   
          Finally, a user may need to use the native schema factory to pick up the XMLType from the database, that property is as follows:                
                                                                                  
          <property name="openjpa.jdbc.SchemaFactory" value="native"/>            

          By enabling OpenJPA's native schema factory, the database column type can be detected as XMLType.

          Show
          Heath Thomann added a comment - I'd like to add a few more details about the code changes made to 1.2.x and 2.0.x. These details are in addition to the previous (and vitally important) details added by Jeremy. In addition to his requirements, it should be noted that the code changes made in this JIRA are gated (enabled/disabled) via a system property. The system property is named 'supportsSetClob' and is defined on the Oracle dictionary class. As an example, the property can be defined as follows: <property name="openjpa.jdbc.DBDictionary" value="oracle(supportsSetClob=true)"/>            Next, it should be noted that we expect the XML field(s) to be annotated with:                                                                                           @Strategy("org.apache.openjpa.jdbc.meta.strats.XMLValueHandler")                                                                                  This allows a user to indicate that OpenJPA should use JAXB classmetadata to perform the to/from database value retrieval and storage. In addition, using this strategy indicates there is an XMLType used to store the value in the database.  Instead, we've seen customers attempt to managing their own XML string, mapped to a string field, and expect the code changes of this JIRA to apply. While a user has an XMLType specified in the column definition, OpenJPA cannot count on this data to detect whether the column is really an XML column in the database (the table could have been created manually with separate DDL instead of using OpenJPA's mapping tool).   In this case, where the user is doing their own String mapping rather than using the XMLValueHandler, there are some additional settings a user may need to set/enable in order to resolve the issue addressed in this JIRA. The necessary settings/actions are as follows: First, it may be necessary to annotate an XML field with @Lob, as follows:                                                                                                                                  @Lob                                                                     @Column(name="CONTENTS_XML", columnDefinition ="XMLCOLUMN XMLType")  private String contentsXml;                                                                                                                      Second, in addition to annotating the XML field with @Lob, a user may also need to set this dictionary property:                                                                                                                                         maxEmbeddedClobSize=-1                                                                                                                            Therefore, the final DBDictionary property would be:                                                                          <property name="openjpa.jdbc.DBDictionary" value="oracle(supportsSetClob=true,maxEmbeddedClobSize=-1)"/>                                                                                                                                                              Finally, a user may need to use the native schema factory to pick up the XMLType from the database, that property is as follows:                                                                                          <property name="openjpa.jdbc.SchemaFactory" value="native"/>             By enabling OpenJPA's native schema factory, the database column type can be detected as XMLType.

            People

            • Assignee:
              Milosz Tylenda
              Reporter:
              Kelvin Law
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development