Oracle / String length > 4000 / issue

antoine.magnier · **Joined:** Fri Jan 05, 2007 12:21 pm **Posts:** 9

Hi,
I must use a column in an oracle table (9i) for a String from a java bean.
Sometimes the string is more than 4000 car long so it's not posible to map this on a VARCHAR2 due to oracle limitations.

What is the best practice for this issue ?

best reguards

Antoine

France

roger_rf · **Posted:** Thu Apr 19, 2007 9:55 am

Salut Antoine,
I've never dealt with this problem myself, but I see two possible solutions:

1) Create a few VARCHAR2 columns in your table in order to store the long String. Break the String in pieces and store each piece in a column when you save a record, and concatenate those pieces in a single String when you retrieve the record. Pro: All the data stays in a single table. Con: The table design gets denormalized, there's a potential waste of space, and you will have to restructure the table if you need more space in the future.

2) Create a second table, which will contain child records of the first table. You still have to break your String into pieces when you save, and concatenate those pieces when you retrieve records. Pro: You will only use the space you actually need. Con: The need to do JOIN's in order to retrieve the long String.

I personally like the second approach better, but depending of the needs of your application the first one could work just as fine. Just keep those tradeoffs in mind when you decide on your design. Cheers,
Roger

evdelst · **Joined:** Wed Aug 24, 2005 11:49 am **Posts:** 63

Why not use a blob? (clob, lob, etc.)

antoine.magnier · **Joined:** Fri Jan 05, 2007 12:21 pm **Posts:** 9

i tried

- Oracle 9i or 10g
- Hibernate 3.2.3
- jdk 1.5
- tomcat 5.5

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
   
<hibernate-mapping package="cs.chequerose.beans">
   <class name="FeedbackLetter" table="FEEDBACKLETTER">
      <id name="id" column="IDFEEDBACKLETTER">
         <generator class="increment"/>
       </id>   
       <property column="ID_COMPTE" name="idCompte"/>
       <property column="IMMATNO" name="immatNo"/>
       <!-- <property column="LETTER_CONTENT" name="letterContent"/>-->
       <property column="LETTER_CONTENT2" type="clob" name="letterContent"/>
       <property column="LETTER_DATE" name="date"/>
       <property column="USERNAME" name="username"/>
     </class>
</hibernate-mapping>

bean i try to map (letterContent can be more than 4000 caracters long)

Code:

package cs.chequerose.beans;

import java.util.Date;

/**
 * Lettre de relance intermediaire (historisée)
 * @author Inf15
 *
 */
public class FeedbackLetter {
   
   public FeedbackLetter() {
      super();
   }
   
   /**
    * id de la lettre
    */
   private Integer id;
   
   /**
    * id compte de l'adhérent auquel la lettre est adressée
    */
   private Integer idCompte;
   
   /**
    * immatNo du compte auquel la lettre est adressée
    */
   private String immatNo;
   
   /**
    * XML du contenu
    */
   private String letterContent;
   
   /**
    * date d'edition de la lettre
    */
   private Date date;
   
   /**
    * login de l'utilisateur ayant lancé l'édition
    */
   private String username;

   public FeedbackLetter(Integer idCompte, String immatNo, String letterContent, Date date, String username) {
      super();
      this.idCompte = idCompte;
      this.immatNo = immatNo;
      this.letterContent = letterContent;
      this.date = date;
      this.username = username;
   }

   public Date getDate() {
      return date;
   }

   public void setDate(Date date) {
      this.date = date;
   }

   public Integer getId() {
      return id;
   }

   public void setId(Integer id) {
      this.id = id;
   }

   public Integer getIdCompte() {
      return idCompte;
   }

   public void setIdCompte(Integer idCompte) {
      this.idCompte = idCompte;
   }

   public String getImmatNo() {
      return immatNo;
   }

   public void setImmatNo(String immatNo) {
      this.immatNo = immatNo;
   }

   public String getLetterContent() {
      return letterContent;
   }

   public void setLetterContent(String letterContent) {
      this.letterContent = letterContent;
   }

   public String getUsername() {
      return username;
   }

   public void setUsername(String username) {
      this.username = username;
   }
   
}

but when i try to insert

Code:

   Session session = getSessionFactory().openSession();
         Transaction tx = session.beginTransaction();
         //creation objet feedbackletter avant envoi
         FeedbackLetter newLetter = new FeedbackLetter(idCompte,"test",generateXmlFeedbackLetter(pIdCompte),new java.util.Date(),"invite");
         //sauvegarde en bdd oracle par hibernate
         session.save(newLetter);
         tx.commit();
         session.close();

i get

Code:

Etat HTTP 500 -

type Rapport d'exception

message

description Le serveur a rencontré une erreur interne () qui l'a empêché de satisfaire la requête.

exception

javax.servlet.ServletException: java.lang.String
   org.apache.struts.action.RequestProcessor.processException(RequestProcessor.java:508)
   org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:415)
   cs.chequerose.ihmactions.requestProcessor.CRappRequestProcessor.processActionPerform(CRappRequestProcessor.java:70)
   org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:225)
   org.apache.struts.action.ActionServlet.process(ActionServlet.java:1858)
   org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:446)
   javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
   javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

cause mère

java.lang.ClassCastException: java.lang.String
   org.hibernate.type.ClobType.set(ClobType.java:50)
   org.hibernate.type.ClobType.nullSafeSet(ClobType.java:118)
   org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:1997)
   org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2243)
   org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2660)
   org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:52)
   org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
   org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)
   org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)
   org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
   org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
   org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
   org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
   org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
   cs.chequerose.edition.FeedbackLetterGenerator.sendLetter(FeedbackLetterGenerator.java:601)
   cs.chequerose.ihmactions.SendLetter.execute(SendLetter.java:42)
   org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:413)
   cs.chequerose.ihmactions.requestProcessor.CRappRequestProcessor.processActionPerform(CRappRequestProcessor.java:70)
   org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:225)
   org.apache.struts.action.ActionServlet.process(ActionServlet.java:1858)
   org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:446)
   javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
   javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

note La trace complète de la cause mère de cette erreur est disponible dans les fichiers journaux de Apache Tomcat/5.5.20.
Apache Tomcat/5.5.20
"

bean

sorry for french comments ;)

roger_rf · **Posted:** Thu Apr 19, 2007 12:19 pm

Hi Antoine,
Perhaps the following page helps?
http://www.hibernate.org/56.html

Edwin: Hmm, I never used BLOB's before, maybe I should check that. However, Hibernate's documentation points issues about handling BLOB's in Java:
http://www.hibernate.org/hib_docs/v3/re ... pping.html
"Type mappings for the JDBC classes java.sql.Clob and java.sql.Blob. These types may be inconvenient for some applications, since the blob or clob object may not be reused outside of a transaction. (Furthermore, driver support is patchy and inconsistent.)"

Cheers,
Roger

gskempes · **Joined:** Mon Nov 22, 2004 11:21 am **Posts:** 42

Use a CLOB column in the database and map it as a string. For me this works good enough.

antoine.magnier · **Joined:** Fri Jan 05, 2007 12:21 pm **Posts:** 9

Hi,
I found the answer :) !

here comes the workaround :

- update hibernate (not sure it came from here but they have recently corrected memory leaks bugs)
- update driver jdbc Oracle : they resolved bugs dealing with the standard CLOB JDBC support (hibernate use this ...)
- add type="text" in the hibernate mapping (for the element corresponding to the string you want to map to the clob COLUMN )

(symptom of the jdbc driver bug : if the String was longer than 4000 caracteres the insert query didn't worked)

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
   
<hibernate-mapping package="cs.chequerose.beans">
   <class name="FeedbackLetter" table="FEEDBACKLETTER">
      <id name="id" column="IDFEEDBACKLETTER">
         <generator class="increment"/>
       <property column="ID_COMPTE" name="idCompte"/>
       <property column="IMMATNO" name="immatNo"/>
    <property column="LETTER_CONTENT" name="letterContent"  type="text"/
       <property column="LETTER_DATE" name="date"/>
       <property column="USERNAME" name="username"/>
     </class>
</hibernate-mapping>

see property LETTER_CONTENT

bobolito · **Joined:** Mon May 21, 2007 7:51 am **Posts:** 3

<b>Hibernate version : </b>3
<b>Database : </b>Oracle 9i

Hello,

I have the same issue : I need to store a String that is more than 4000 car long. But I use JPA annotations.
What is the equivalent of the type="TEXT" mapping in these kind of annotations ? I tried with the columnDefinition attribute of the @Column annotation but I can't find a suitable value.
If I just declare :

Code:

@Lob
public String myString;

SQL will send me an error message.
I use the following hibernate properties as well :

Code:

<property name="hibernate.jdbc.use_streams_for_binary" value="true"/>
<property name="hibernate.jdbc.batch_size" value="0"/>

Thanks for the help.

mopont · **Joined:** Thu Jan 18, 2007 4:45 pm **Posts:** 10

antoine.magnier wrote:

Hi,
I must use a column in an oracle table (9i) for a String from a java bean.
Sometimes the string is more than 4000 car long so it's not posible to map this on a VARCHAR2 due to oracle limitations.

What is the best practice for this issue ?

best reguards

Antoine

France

Use oracle's clob fields......

bobolito · **Joined:** Mon May 21, 2007 7:51 am **Posts:** 3

Hibernate version : 3
Database : Oracle 9i
JPA

Hello,

I've tried everything I found to save Strings > 4000 cars but I can't find any solution.

I tried the usual :

Code:

@Lob
public String myString;

and

Code:

@Lob
public byte[] myString;

getting CLOBS and BLOBS,
both with and without hibernate parameters (you never know...)

Code:

<property name="hibernate.jdbc.use_streams_for_binary" value="true"/>
<property name="hibernate.jdbc.batch_size" value="0"/>

I also tried using "text" type, as suggested :

Code:

@Type(type="text")
public String myString

I even tried defining UserTypes as specified in :
http://www.hibernate.org/56.html

but no results and usually a "no more data to read from socket" JDBC exception.
I think I'll finish with queries in native SQL, but if I could do this with HIbernate and JPA annotations, it would be great...

Is anyone in the same situation. Any solution ?

Thank you !

dharmendra.pandey · **Posted:** Fri May 25, 2007 6:22 am

FYI

Have a look on that may be useful for you

http://hibernate.org/329.html

@Serialized was working for me, and now that I use @Lob it fails
Due to a change in the EJB3 specification, @Serialized is gone and has been replaced by a mapping through a blob. Many JDBC drivers have issues with Blob. @Serialized can be replaced by @Type(type="serializable").

bobolito · **Joined:** Mon May 21, 2007 7:51 am **Posts:** 3

Hello

thanks for the answer.
I tried that as well, but the problem is that a serializable object is mapped to a RAW type in my Oracle DB, and a RAW type is limited in terms of octets (2K). There could be a solution with using Oracle LONG RAW, but I don't know which hibernate type is mapped to LONG RAW...

mopont · **Joined:** Thu Jan 18, 2007 4:45 pm **Posts:** 10

bobolito wrote:

Hello

thanks for the answer.
I tried that as well, but the problem is that a serializable object is mapped to a RAW type in my Oracle DB, and a RAW type is limited in terms of octets (2K). There could be a solution with using Oracle LONG RAW, but I don't know which hibernate type is mapped to LONG RAW...

The Hibernate type is "long"