There is a discussion about direct binding (fast) and stream binding (slow) at [1]. I think it answers Scott's questions. Rather than repeating the info here, please take a look at the Oracle documentation ( http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/datacc.htm#a... ). After thinking about this more, I think we should keep the Oracle dialects as is. Here are my reasons: - Hibernate defines the text/image types consistent with how Oracle maps LONGVARCHAR/LONGVARBINARY; - the bug lies with Oracle; - Oracle has effectively deprecated valid SQL data types (LONGVARCHAR and LONGVARBINARY); - the migration from "text" to "materialized_clob" has already been documented since 3.5; "image" is a new type as of 3.6; - anyone actually using text/image with LONG/LONGRAW columns may run into new migration issues in Hibernate 4.0.0; more testing would need to be done to determine the true impact; - users can easily create a new dialect making the changes I mentioned earlier; - this avoids having to make a confusing recommendation (i.e., using Oracle11gDialect for accessing an Oracle 10g DB). Regarding Oracle tests, IMO, the tests using "text" and "image" should be disabled. If there is no similar test using clob/blob, it would be a good idea to add one. ----- Original Message ----- > From: "Łukasz Antoniak" <lukasz.antoniak(a)gmail.com&gt; > To: "Strong Liu" <stliu(a)hibernate.org&gt; > Cc: "Scott Marlow" <smarlow(a)redhat.com&gt;, "Gail Badner" < gbadner(a)redhat.com&gt;, hibernate-dev(a)lists.jboss.org > Sent: Sunday, October 16, 2011 11:36:49 AM > Subject: Re: [hibernate-dev] HHH-6726 LONG and LONG RAW column types in Oracle > > Currently Oracle supports database versions from 10.1 to 11.2 [1]. > LONG > and LONG RAW data types are deprecated since version 8 and 8i > (released > before September 2000) [2]. Oracle keeps those column types only for > backward compatibility [3]. > > I tried the following scenario (Oracle 10gR2): > 1. Create schema with "hibernate.hbm2ddl.auto" set to "create". The > LONG > column is created. > 2. Insert some data. > 3. Modify Oracle dialect as Gail suggested. Avoid setting > "hibernate.hbm2ddl.auto". > 4. Insert some data. > To my surprise the test actually passed :). However, I think that we > cannot guaranty the proper behavior in every situation. > > As for performance, ImageType is extracted by calling > ResultSet.getBytes() method, which fetches all data in one call [4]. > I > don't suppose a major performance difference when data is streamed in > another call. oracle.jdbc.driver.LongRawAccessor.getBytes also > fetches > data by reading the stream. > > The bug reading LONG column affects JDBC drivers since version > 10.2.0.4. > I think that we have to choose between: > - changing Oracle10gDialect. Make a not about it in migration guide > to > 4.0 and update "5.2.2. Basic value types" chapter in Hibernate > documentation. > - introducing Oracle11gDialect. It can sound weird to access Oracle > 10g > database with Oracle 11g dialect. > - disabling execution of Hibernate tests that fail because of this > issue > with @SkipForDialect (and maybe develop another version of them with > CLOBs and BLOBs, @RequiresDialect). Hibernate is written correctly > according to "Default Mappings Between SQL Types and Java Types" > (referenced earlier by Gail) and this is more Oracle's JDBC > implementation issue. This option came to my mind, but it's weird :P. > > I would vote for the first option. > > Regards, > Lukasz Antoniak > > [1] > http://www.oracle.com/us/support/library/lifetime-support-technology-0691... > (page 4) > [2] > http://download.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90120/ch... > [3] > http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm > [4] "Getting a LONG RAW Data Column with getBytes" > http://download.oracle.com/docs/cd/B19306_01/java.102/b14355/jstreams.htm > > Strong Liu pisze: > > I think oracle 11g is the only one supported DB version by oracle, > > can we just introduce a new oracle dialect with suggested changes, > > and deprecate all other existed oracle dialects? this won't > > affects users app > > > > ----------- > > Strong Liu <stliu(a)hibernate.org&gt; > > http://hibernate.org > > http://github.com/stliu > > > > On Oct 15, 2011, at 11:14 AM, Scott Marlow wrote: > > > >> How does this impact existing applications? Would they have to > >> convert > >> LONGs to CLOBs (and LONGRAWs to BLOBs) to keep the application > >> working? > >> > >> As far as the advantage of CLOB over TEXT, if you read every > >> character, > >> which one is really faster? I would expect TEXT to be a little > >> faster, > >> since the server side will send the characters before they are > >> asked > >> for. By faster, I mean from the application performance point of > >> view. :) > >> > >> Could this be changed in a custom Oracle dialect? So new > >> applications/databases could perhaps use that and existing > >> applications > >> might use LONGs a bit longer via the existing Oracle dialect. > >> > >> On 10/14/2011 09:22 PM, Gail Badner wrote: > >>> In [1], I am seeing the following type mappings: > >>> > >>> Column type: LONG -> java.sql.Types.LONGVARCHAR -> > >>> java.lang.String > >>> Column type: LONGRAW -> java.sql.Types.LONGVARBINARY -> byte[] > >>> > >>> org.hibernate.type.TextType is consistent with the mapping for > >>> LONG. > >>> > >>> org.hibernate.type.ImageType is consistent with the mapping for > >>> LONGRAW. > >>> > >>> From this standpoint, the current settings are appropriate. > >>> > >>> I understand there are restrictions when LONG and LONGRAW are > >>> used and I see from your other message that there is Oracle > >>> documentation for migrating to CLOB and BLOB. > >>> > >>> I agree that changing column type registration as follows (for > >>> Oracle only) should fix this: > >>> registerColumnType( Types.VARBINARY, 2000, "raw($l)" ); > >>> registerColumnType( Types.VARBINARY, "blob" ); > >>> > >>> registerColumnType( Types.LONGVARCHAR, "clob" ); > >>> registerColumnType( Types.LONGVARBINARY, "blob" ); > >>> > >>> registerColumnType( Types.VARCHAR, 4000, "varchar2($l char)" ); > >>> registerColumnType( Types.VARCHAR, "clob" ); > >>> > >>> Steve, what do you think? Is it too late to make this change for > >>> 4.0.0? > >>> > >>> [1] Table 11-1 of Oracle® Database JDBC Developer's Guide and > >>> Reference, 11g Release 1 (11.1) > >>> ( http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/datacc.htm#g... ) > >>> [2] Hibernate Core Migration Guide for 3.5 > >>> (http://community.jboss.org/wiki/HibernateCoreMigrationGuide35) > >>> [3] Table 2-10 of Oracle® Database SQL Language Reference > >>> 11g Release 1 (11.1) > >>> ( http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elemen... ) > >>> > >>> ----- Original Message ----- > >>>> From: "Łukasz Antoniak&quot;&lt;lukasz.antoniak(a)gmail.com&gt; > >>>> To: hibernate-dev(a)lists.jboss.org > >>>> Sent: Thursday, October 13, 2011 12:50:13 PM > >>>> Subject: [hibernate-dev] HHH-6726 LONG and LONG RAW column types > >>>> in Oracle > >>>> > >>>> Welcome Community! > >>>> > >>>> I have just subscribed to the list and wanted to discuss > >>>> HHH-6726 > >>>> JIRA > >>>> issue. > >>>> > >>>> Gail Badner wrote > >>>> ( http://lists.jboss.org/pipermail/hibernate-dev/2011-October/007208.html): > >>>> HHH-6726 (Oracle : map TextType to clob and ImageType to blob) > >>>> https://hibernate.onjira.com/browse/HHH-6726 > >>>> There have been a number of issues opened since the change was > >>>> made > >>>> to > >>>> map TextType (LONGVARCHAR) 'long' and ImageType (LONGVARBINARY) > >>>> to > >>>> 'long > >>>> raw'. This change was already documented in the migration notes. > >>>> Should > >>>> the mapping for Oracle (only) be changed back to clob and blob? > >>>> > >>>> HHH-6726 is caused by an issue in Oracle JDBC driver (version > >>>> 10.2.0.4 > >>>> and later). This bug appears when LONG or LONG RAW columns are > >>>> accessed > >>>> not as first or last while processing SQL statement. > >>>> > >>>> I have discussed the topic of mapping TextType to CLOB and > >>>> ImageType > >>>> to > >>>> BLOB (only in Oracle dialect) with Strong Liu. Reasons for doing > >>>> so: > >>>> - Oracle allows only one LONG / LONG RAW column per table. This > >>>> might > >>>> be > >>>> the most important from Hibernate's perspective. > >>>> - LONG / LONG RAW - up to 2 GB, BLOB / CLOB - up to 4 GB. > >>>> - In PL/SQL using LOBs is more efficient (random access to > >>>> data). > >>>> LONG > >>>> only sequential. > >>>> - LONG and LONG RAW are deprecated. > >>>> > >>>> What is your opinion? > >>>> > >>>> Regards, > >>>> Lukasz Antoniak > >>>> _______________________________________________ > >>>> hibernate-dev mailing list > >>>> hibernate-dev(a)lists.jboss.org > >>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev > >>>> > >>> _______________________________________________ > >>> hibernate-dev mailing list > >>> hibernate-dev(a)lists.jboss.org > >>> https://lists.jboss.org/mailman/listinfo/hibernate-dev > >> _______________________________________________ > >> hibernate-dev mailing list > >> hibernate-dev(a)lists.jboss.org > >> https://lists.jboss.org/mailman/listinfo/hibernate-dev > > > > _______________________________________________ hibernate-dev mailing list hibernate-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev

Andrea and I discussed on IRC and since we were the only 2 involved and both agreed, that constitutes a quorum! :) So I am going to override Oracle12cDialect#contributeTypes (implicit Type determination) such that: - recognize byte[] as MaterializedBlobType (Types#BLOB) rather than BinaryType (Types#LONGVARBINARY) - recognize Byte[] as WrappedMaterializedBlobType (Types#BLOB) rather than WrapperBinaryType (Types#LONGVARBINARY). I am a very leery about the VARCHAR, LONG, CLOB side of it mainly because the approach to access data from a VARCHAR and a CLOB are so very different and we'd have to account for that at some point based on size, etc. Ultimately what I'd love to see is to expand the distinction between SqlTypeDescriptor and JavaTypeDescriptor and to piece them together (along with AttributeConverter, MutabilityPLan, etc) into a BasicType "just in time". The reason I bring this up in that this question is really a discussion about the resolution for a SqlTypeDescriptor given just a JavaTypeDescriptor. That's fundamentally what BasicTypeRegistry is about. But ultimately, breaking that down further and instead asking for the SqlTypeDescriptor given a JavaTypeDescriptor is more flexible. Especially when you start to consider that that question can much more easily incorporate size information and other "contextual" data. Anyway, that's long term... For now I will override #contributeTypes in Oracle12cDialect to provide the new registrations mentioned above. I will do this on master (5.1). IMO we should also consider backporting this, but wanted to get everyone's (anyone) thoughts first. Opinions? On Tue, Jan 26, 2016 at 11:48 AM Steve Ebersole <steve(a)hibernate.org&gt; wrote: > This has come up again under HHH-10345[1]. > > The original request here claims that Oracle have deprecated LONG ( > LONGVARCHAR) and LONG RAW (LONGVARBINARY). It refers to the 9i release > notes as "proof" of that[2]. The 9i release notes do mention LONG (LONGVARCHAR) > datatype being deprecated. However, it does not mention LONG RAW. The > next 2 references it links are from 10g and 11i; both of these do mention > LONG RAW specifically being deprecated. So LONG was deprecated as of 9i. > It is a bit unclear exactly when LONG RAW was deprecated. > > Unfortunately Oracle has issues with both LONG and LOB forms. In fact the > list of restrictions for LONG and LONG RAW is incredibly long. As far as > I can tell newer Oracle releases have alleviated some of the crazier > "limitations" of LOB usage; but its unfortunately been many years since I > have "used Oracle in anger", so I do not really know the complete current > state of its LOB support. > > While I personally agree that we should not be retroactively changing our > legacy Oracle-based dialects to map LONGVARCHAR to (N)CLOB and LONGVARBINARY > to BLOB, I do think that is something we want to change moving forward. > Specifically, I wonder if Oracle12cDialect is "foward". We knew about > these deprecations when Oracle12cDialect was developed and clearly these > types are deprecated prior to Oracle 12c even if exactly when Oracle > deprecated them is a bit murky. So specifically I wonder if, for 5.1, we > ought to make this change for Oracle12cDialect. What do y'all think? > > > [1] - https://hibernate.atlassian.net/browse/HHH-10345 > [2] - > http://docs.oracle.com/cd/A91202_01/901_doc/server.901/a90120/ch4_dep.htm... > > > > On Mon, Oct 17, 2011 at 6:11 PM Gail Badner <gbadner(a)redhat.com&gt; wrote: > >> There is a discussion about direct binding (fast) and stream binding >> (slow) at [1]. I think it answers Scott's questions. Rather than repeating >> the info here, please take a look at the Oracle documentation ( >> http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/datacc.htm#a... >> ). >> >> After thinking about this more, I think we should keep the Oracle >> dialects as is. Here are my reasons: >> - Hibernate defines the text/image types consistent with how Oracle maps >> LONGVARCHAR/LONGVARBINARY; >> - the bug lies with Oracle; >> - Oracle has effectively deprecated valid SQL data types (LONGVARCHAR and >> LONGVARBINARY); >> - the migration from "text" to "materialized_clob" has already been >> documented since 3.5; "image" is a new type as of 3.6; >> - anyone actually using text/image with LONG/LONGRAW columns may run into >> new migration issues in Hibernate 4.0.0; more testing would need to be done >> to determine the true impact; >> - users can easily create a new dialect making the changes I mentioned >> earlier; >> - this avoids having to make a confusing recommendation (i.e., using >> Oracle11gDialect for accessing an Oracle 10g DB). >> >> Regarding Oracle tests, IMO, the tests using "text" and "image" should be >> disabled. If there is no similar test using clob/blob, it would be a good >> idea to add one. >> >> ----- Original Message ----- >> > From: "Łukasz Antoniak" <lukasz.antoniak(a)gmail.com&gt; >> > To: "Strong Liu" <stliu(a)hibernate.org&gt; >> > Cc: "Scott Marlow" <smarlow(a)redhat.com&gt;, "Gail Badner" < >> gbadner(a)redhat.com&gt;, hibernate-dev(a)lists.jboss.org >> > Sent: Sunday, October 16, 2011 11:36:49 AM >> > Subject: Re: [hibernate-dev] HHH-6726 LONG and LONG RAW column types in >> Oracle >> > >> > Currently Oracle supports database versions from 10.1 to 11.2 [1]. >> > LONG >> > and LONG RAW data types are deprecated since version 8 and 8i >> > (released >> > before September 2000) [2]. Oracle keeps those column types only for >> > backward compatibility [3]. >> > >> > I tried the following scenario (Oracle 10gR2): >> > 1. Create schema with "hibernate.hbm2ddl.auto" set to "create". The >> > LONG >> > column is created. >> > 2. Insert some data. >> > 3. Modify Oracle dialect as Gail suggested. Avoid setting >> > "hibernate.hbm2ddl.auto". >> > 4. Insert some data. >> > To my surprise the test actually passed :). However, I think that we >> > cannot guaranty the proper behavior in every situation. >> > >> > As for performance, ImageType is extracted by calling >> > ResultSet.getBytes() method, which fetches all data in one call [4]. >> > I >> > don't suppose a major performance difference when data is streamed in >> > another call. oracle.jdbc.driver.LongRawAccessor.getBytes also >> > fetches >> > data by reading the stream. >> > >> > The bug reading LONG column affects JDBC drivers since version >> > 10.2.0.4. >> > I think that we have to choose between: >> > - changing Oracle10gDialect. Make a not about it in migration guide >> > to >> > 4.0 and update "5.2.2. Basic value types" chapter in Hibernate >> > documentation. >> > - introducing Oracle11gDialect. It can sound weird to access Oracle >> > 10g >> > database with Oracle 11g dialect. >> > - disabling execution of Hibernate tests that fail because of this >> > issue >> > with @SkipForDialect (and maybe develop another version of them with >> > CLOBs and BLOBs, @RequiresDialect). Hibernate is written correctly >> > according to "Default Mappings Between SQL Types and Java Types" >> > (referenced earlier by Gail) and this is more Oracle's JDBC >> > implementation issue. This option came to my mind, but it's weird :P. >> > >> > I would vote for the first option. >> > >> > Regards, >> > Lukasz Antoniak >> > >> > [1] >> > >> http://www.oracle.com/us/support/library/lifetime-support-technology-0691... >> > (page 4) >> > [2] >> > >> http://download.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90120/ch... >> > [3] >> > >> http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm >> > [4] "Getting a LONG RAW Data Column with getBytes" >> > >> http://download.oracle.com/docs/cd/B19306_01/java.102/b14355/jstreams.htm >> > >> > Strong Liu pisze: >> > > I think oracle 11g is the only one supported DB version by oracle, >> > > can we just introduce a new oracle dialect with suggested changes, >> > > and deprecate all other existed oracle dialects? this won't >> > > affects users app >> > > >> > > ----------- >> > > Strong Liu <stliu(a)hibernate.org&gt; >> > > http://hibernate.org >> > > http://github.com/stliu >> > > >> > > On Oct 15, 2011, at 11:14 AM, Scott Marlow wrote: >> > > >> > >> How does this impact existing applications? Would they have to >> > >> convert >> > >> LONGs to CLOBs (and LONGRAWs to BLOBs) to keep the application >> > >> working? >> > >> >> > >> As far as the advantage of CLOB over TEXT, if you read every >> > >> character, >> > >> which one is really faster? I would expect TEXT to be a little >> > >> faster, >> > >> since the server side will send the characters before they are >> > >> asked >> > >> for. By faster, I mean from the application performance point of >> > >> view. :) >> > >> >> > >> Could this be changed in a custom Oracle dialect? So new >> > >> applications/databases could perhaps use that and existing >> > >> applications >> > >> might use LONGs a bit longer via the existing Oracle dialect. >> > >> >> > >> On 10/14/2011 09:22 PM, Gail Badner wrote: >> > >>> In [1], I am seeing the following type mappings: >> > >>> >> > >>> Column type: LONG -> java.sql.Types.LONGVARCHAR -> >> > >>> java.lang.String >> > >>> Column type: LONGRAW -> java.sql.Types.LONGVARBINARY -> byte[] >> > >>> >> > >>> org.hibernate.type.TextType is consistent with the mapping for >> > >>> LONG. >> > >>> >> > >>> org.hibernate.type.ImageType is consistent with the mapping for >> > >>> LONGRAW. >> > >>> >> > >>> From this standpoint, the current settings are appropriate. >> > >>> >> > >>> I understand there are restrictions when LONG and LONGRAW are >> > >>> used and I see from your other message that there is Oracle >> > >>> documentation for migrating to CLOB and BLOB. >> > >>> >> > >>> I agree that changing column type registration as follows (for >> > >>> Oracle only) should fix this: >> > >>> registerColumnType( Types.VARBINARY, 2000, "raw($l)" ); >> > >>> registerColumnType( Types.VARBINARY, "blob" ); >> > >>> >> > >>> registerColumnType( Types.LONGVARCHAR, "clob" ); >> > >>> registerColumnType( Types.LONGVARBINARY, "blob" ); >> > >>> >> > >>> registerColumnType( Types.VARCHAR, 4000, "varchar2($l >> char)" ); >> > >>> registerColumnType( Types.VARCHAR, "clob" ); >> > >>> >> > >>> Steve, what do you think? Is it too late to make this change for >> > >>> 4.0.0? >> > >>> >> > >>> [1] Table 11-1 of Oracle® Database JDBC Developer's Guide and >> > >>> Reference, 11g Release 1 (11.1) >> > >>> ( >> http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/datacc.htm#g... >> ) >> > >>> [2] Hibernate Core Migration Guide for 3.5 >> > >>> (http://community.jboss.org/wiki/HibernateCoreMigrationGuide35) >> > >>> [3] Table 2-10 of Oracle® Database SQL Language Reference >> > >>> 11g Release 1 (11.1) >> > >>> ( >> http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elemen... >> ) >> > >>> >> > >>> ----- Original Message ----- >> > >>>> From: "Łukasz Antoniak&quot;&lt;lukasz.antoniak(a)gmail.com&gt; >> > >>>> To: hibernate-dev(a)lists.jboss.org >> > >>>> Sent: Thursday, October 13, 2011 12:50:13 PM >> > >>>> Subject: [hibernate-dev] HHH-6726 LONG and LONG RAW column types >> > >>>> in Oracle >> > >>>> >> > >>>> Welcome Community! >> > >>>> >> > >>>> I have just subscribed to the list and wanted to discuss >> > >>>> HHH-6726 >> > >>>> JIRA >> > >>>> issue. >> > >>>> >> > >>>> Gail Badner wrote >> > >>>> ( >> http://lists.jboss.org/pipermail/hibernate-dev/2011-October/007208.html ): >> > >>>> HHH-6726 (Oracle : map TextType to clob and ImageType to blob) >> > >>>> https://hibernate.onjira.com/browse/HHH-6726 >> > >>>> There have been a number of issues opened since the change was >> > >>>> made >> > >>>> to >> > >>>> map TextType (LONGVARCHAR) 'long' and ImageType (LONGVARBINARY) >> > >>>> to >> > >>>> 'long >> > >>>> raw'. This change was already documented in the migration notes. >> > >>>> Should >> > >>>> the mapping for Oracle (only) be changed back to clob and blob? >> > >>>> >> > >>>> HHH-6726 is caused by an issue in Oracle JDBC driver (version >> > >>>> 10.2.0.4 >> > >>>> and later). This bug appears when LONG or LONG RAW columns are >> > >>>> accessed >> > >>>> not as first or last while processing SQL statement. >> > >>>> >> > >>>> I have discussed the topic of mapping TextType to CLOB and >> > >>>> ImageType >> > >>>> to >> > >>>> BLOB (only in Oracle dialect) with Strong Liu. Reasons for doing >> > >>>> so: >> > >>>> - Oracle allows only one LONG / LONG RAW column per table. This >> > >>>> might >> > >>>> be >> > >>>> the most important from Hibernate's perspective. >> > >>>> - LONG / LONG RAW - up to 2 GB, BLOB / CLOB - up to 4 GB. >> > >>>> - In PL/SQL using LOBs is more efficient (random access to >> > >>>> data). >> > >>>> LONG >> > >>>> only sequential. >> > >>>> - LONG and LONG RAW are deprecated. >> > >>>> >> > >>>> What is your opinion? >> > >>>> >> > >>>> Regards, >> > >>>> Lukasz Antoniak >> > >>>> _______________________________________________ >> > >>>> hibernate-dev mailing list >> > >>>> hibernate-dev(a)lists.jboss.org >> > >>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >> > >>>> >> > >>> _______________________________________________ >> > >>> hibernate-dev mailing list >> > >>> hibernate-dev(a)lists.jboss.org >> > >>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >> > >> _______________________________________________ >> > >> hibernate-dev mailing list >> > >> hibernate-dev(a)lists.jboss.org >> > >> https://lists.jboss.org/mailman/listinfo/hibernate-dev >> > > >> > >> > >> >> _______________________________________________ >> hibernate-dev mailing list >> hibernate-dev(a)lists.jboss.org >> https://lists.jboss.org/mailman/listinfo/hibernate-dev > > _______________________________________________ hibernate-dev mailing list hibernate-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev