How do I commit a select statement in a distributed query

[mary.neech@jhg.com.au]

I have a TOracleDataSet that has a SELECT
statement that uses several databases eg:

SELECT project_no
FROM ledger l
UNION
SELECT project_no
FROM ledger@db.melbourne.home.com l
UNION
SELECT project_no
FROM ledger@db.sydney.home.com l

This query's results are used in a Quick Report. However, after the report has been closed, a transaction TX lock that Oracle created is not released. From reading around I believe that it is necessary to commit even SELECT statements due to two-phase commit used for distributed databases.

However, have tried using the OracleSession.Commit directly after opening the query but this has no affect.

Any advice greatly appreciated.

 

[Marco Kalter]

A TOracleSession.Commit should do the trick. Note that if the dataset fetches new records (e.g. a refresh), Oracle will start a new transaction due to the database link.

------------------
Marco Kalter
Allround Automations

 

[mary.neech@jhg.com.au]

Thanks very much. The query had a master,so I put a TOracleSession.Commit in the AfterPrint of the master query's report band and hey presto!