I've got the same problem nicofari "Out of Memory with TOracleDataset" - I found no real answer to his questions.

We have a query returning 145000 rows of data.

The problem seems to be that TOracleDataset does not handle data from functions in sql results very well.

I made a test application - when I do not use any functions I get a memory user of 72 Mb - even though the grid displays 145000 rows with the same ammount of columns, and same amount of data.

When I use a function to populate one of the rows the test application suddenly use 747 Mb of memory. Still 145000 rows.

The original sql uses 8 functions for populating the result. So I assume this cause the out of memory. Any idea why? And is there any way of getting around this problem?
I would cosider this a bug...

Another thing I noticed.
If I set "QueryAllRecords" and "CountAllRecords" to false - things work as long as the user does not scroll all the way to the bottom of the grid.
If I set "QueryAllRecords" to false and "CountAllRecords" to true I get the "out of memory" when I run the query. Why is that? Does setting "CountAllRecords" cause "QueryAllRecords" to be set as well?

Example:
select a.jurpersonid, a.Etternamn, a.fornamn, reskontro.f_test (b.maalepktnr) kommunenr
from felles.efjurperson a, kunde.ekkontrakt b
where a.jurpersonid = b.kundenr

When running this sql - returning 15000 rows of data my application will use 84Mb of memory.
If I change the function call "reskontro.f_test(b.maalepktnr)" to "b.maalepktnr" - the same ammount of data will be returned but my application only use 24 Mb of memory.

For 145.000 rows in a result grid you will need to activate QBE for a user to allow him to find the records he is interested in. Nobody can view 145.000 records. QueryAllRecords must be set to False as well.

The issue is not really the 145.000 rows but the huge memory usage when querying using functions i TOracleDataset - why is that?

Depending om what limitations the customer set they may get 10 rows in the report or 150.000. Do you mean that I will need to check every sql sendt and find number of rows returned then decide if QBE/QueryAllRecords should be active or not? That does not seem like a good solution to me...

We tested the solution you suggested (QBE) - as for just setting QueryAllRecord and CountAllRecords to false, it works fine as long as the customer does not chose to export the data. Something they do since this is a yearly report they have to run...

The amount of that is not at all huge - so this should not cause "out of memory"!

As a test I removed the TDBGrid, so the only thing that happend is that data is fetched into at TOracleDataset and directly exported to excel.
We also manipulated the oracle functions so that they just return a fixed number/string. To rule out any bugs in the functions.

The behavior of the program is the same: "Out of memory".

Our version of DOA: 4.1.1.0

If you need to export these amounts of data from a TOracleDataSet, you can set the UniDirectional property to True. This way the records will not be buffered in memory. The dataset cannot be connected to a grid in this case.

That is not really an option since we then would need to change how our application work.

Can you confirm the problem/bug with regards to using function results in sql's in TOracleDataset? Have you tested it yet?

I have just tested implementing a component called ODAC (http://www.devart.com/odac/components.html) and populating TDBGrid with data from that control.
When I run the report using TOraQuery from ODAC the result is a memory use of 99 MB - when using TOracleDataset the application chrash with "Out of memory" when memory usage exceeds 1300MB.

What happens with a TOracleQuery?

TOracleQuery has a normal memory usage.

If a function returns VARCHAR2 - the memory consuption of TOracleDataset is too high - for number the memory usage seem to be normal...