Out of Memory with TOracleDataset

[altink]

Dear All

I am trying to query some 90,000 rows from an Oracle 10g database via DOA vrs 4.1 TOracleDataset, but I also receive "Out of memory" error. There are only number, date and string datatypes on this table.
Exactly the same query, to the same Oracle server, from the same PC where I am working, I can have from PL/SQL Developer, which I believe makes use of DOA components. I exported the result set to a CSV file which amounted for 60Mb.

90,000 rows/60Mb doesn't seem to much for a IBM Pentium 4 dual-thread, 512 Mb RAM, furthermore PL/SQL Developer can open and export it to the same. My application really needs this feature.

Can anything be done so that TOracleDataset supports the same ?

regards
Altin

 

[Andrey Maximenko. Ukraine]

SELECT ?
DDL Table ?

 

[altink]

It is a select command.
What do you mean by DDL table ?

 

[Marco Kalter]

Can you provide the creation DDL of the table and the select statement of the TOracleDataSet?

Generally, if the TOracleDataSet runs out of memory due to a large result set, you can use the following solutions:

1. Use a TOracleQuery instead
2. Enable TOracleDataSet.UniDirectional

In both cases the results are not buffered, so you cannot navigate backwards.

 

[altink]

Thank You Mr. Kalter

But I need to use a DBGrid on the result.

What about PL/SQL ? Does it make use of TOracleDataset ?
I can query same amount of data with that. I have seen that it has a functionality to fetch records Next N records (like in the web) or All.

Any workaround on this ?

Thanks and Regards
Altin

 

[Marco Kalter]

Can you provide the creation DDL of the table and the select statement of the TOracleDataSet?

What about PL/SQL ? Does it make use of TOracleDataset ?

No. For the TOracleDataSet you can set QueryAllRecords to False though. This way the dataset will fetch only the records that are needed. If you fetch up to the last record, you will still get the same issue though.

Also note that it is typically not necessary to display 90.000 rows in a dbgrid. Nobody can view that much records, so a where clause or QBE mode may be appropriate.

 

[nicofari]

Hello

I have a similar problem but in a report.
I am using D7 and DOA 4.0.7.1.
I have a TOracleDataset which must fetch all records because
all records are needed for this report (and records are many).
Lot of customers, though, still have old pc with not enough
memory to run it.
I can't use a TOracleQuery since my report engine (RB) needs
a TDataset descendant.
Is there any way to use file instead of memory as a cache for
TOracleDataset ?
Thanks for any tips/help
Bye
Nicola

 

[Marco Kalter]

If the report accesses the records sequentially from first to last, you can set TOracleDataSet.UniDirectional to True. This way there is no memory overhead.

 

[nicofari]

Hi,
Unfortunately they (the report support) told me that their report engine cannot work with unidirectional datasets....
And they have to use a TDataset descendant so using TOraclequery isn't possible ....
any other option ?
Thanks for replying
( btw I haven't been notified of your reply .. the old
version of your forum used to do this ... don't know how to
enable it with this version ? )

Bye and Merry Christmas
Nicola

 

[Marco Kalter]

any other option ?

You can only try to minimize the size of the result set by omitting fields and records that you do not need.

( btw I haven't been notified of your reply .. the old
version of your forum used to do this ... don't know how to
enable it with this version ? )

You can add a topic to your "Watched Topics" through the "Topic Options" menu, and enable the "By default should anything added to your Watch Lists be emailed to you?" preference.