Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

[b]Hibernate version:3.1.3

[b]Full stack trace of any exception that occurs: java.lang.OutOfMemoryError

[b]Name and version of the database you are using:Oracle 9i

[b]The generated SQL (show_sql=true):

select * from ( select this_.NH_ID_NATURAL_HOLDER_CK as NH1_48_0_, this_.HO_ID_HOLDER_CK as HO2_48_0_, this_.NH_ID_REFERENCE_FK as NH3_48_0_, this_.NH_ST_MINOR as NH4_48_0_, this_.NH_ST_REPRESENTANT as NH5_48_0_, this_.NH_DT_BIRTH as NH6_48_0_, this_.NH_CD_NATIONALITY as NH7_48_0_, this_.NH_CD_RESIDENT as NH8_48_0_, this_.NH_SEX as NH9_48_0_, this_.NH_ST_DISABLED as NH10_48_0_, this_.NH_IND_DOCUMENT as NH11_48_0_, this_.NH_NR_DOCUMENT as NH12_48_0_, this_.NH_NAME as NH13_48_0_, this_.NH_PATERNAL_LAST_NAME as NH14_48_0_, this_.NH_MATERNAL_LAST_NAME as NH15_48_0_, this_.NH_CD_COUNTRY as NH16_48_0_, this_.NH_CD_DEPARMENT as NH17_48_0_, this_.NH_CD_CITY as NH18_48_0_, this_.NH_CD_DISTRICT as NH19_48_0_, this_.NH_IND_DOCUMENT_MIGRATION as NH20_48_0_, this_.GL_GEOGRAPHIC_LOCATION_FK as GL21_48_0_, this_.NH_ADDRESS as NH22_48_0_, this_.NH_NR_DOCUMENT_MIGRATION as NH23_48_0_, this_.NH_DT_REGISTRY as NH24_48_0_, this_.NH_EMAIL as NH25_48_0_, this_.NH_NR_PHONE as NH26_48_0_, this_.NH_NR_FAX as NH27_48_0_, this_.NH_NR_CARD_REGISTRY as NH28_48_0_, this_.NH_FULL_NAME as NH29_48_0_, this_.JH_ID_LEGAL_REPR_FK as JH30_48_0_ from NATURAL_HOLDER this_ where this_.NH_NAME like ? and this_.NH_ST_REPRESENTANT=? ) where rownum <= ?

I am getting 800K records .. how to handle this....

Is there any possibility to handle all the records...

I am getting out of memory exception...

Can I know how many records does hibernate can handle ie the maximum limit...

Can any one please help.....

Hello!

What are you doing with 800.000 records at one time?

read about batch fetching in Hibernate Reference.

Add memory to your JVM.

I want to print a report...

I have a query such that it retrieves 800K records. In that case how to handle that many records. I could able to get upto 20K records. After that I am getting out of memory. Is there any limit for the retrieval of rows in Hibernate. I have using the ScrollResults, setFirstResult(), setMaxResult(). But I couldnt able to solve the problem. How to handle all the records? I have tried SatelessSession also. But at max. I can retrieve upto 20K. I have also used session.clear(), session.evict(). Can u help me in getting a solution... please...

I wouldn't say that you have a hibernate problem. Hibernate does provide batch processing features but that's not really going to help you much with a query that returns 800,000 rows. I'd start by trying to optimize your query. First, don't return everything (select *) if you don't need all the data. You also may need to implement a strategy in your applicatoin code to limit how many rows you process at one time.

You might get more help if you post your query and not the generated SQL.

Grant

You don't have to run everything at once. If you do extra processing to then it's simple - need to create a temp table that stores the outcome of that processing.

Then it's just a simple query. and you may want to handle it page by page or group of pages at time. Further more if it's a file report (txt,csv,pdf) you don't need to read all data - read row by row and send it to the file.

For pagination, check this link
http://www.hibernate.org/248.html
I'm using a similar approach in my projects