If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Monitoring Oracle SQL queries in VBA

I currently use VBA with Excel to generate reports based on data within an Oracle 9 database. The amount of data is very large and the reports can take a long time to process. I have added status bar progress indicators for the majority of the report processing tasks to inform the user of what is going on.

The problem is that I need to be able to monitor the progress of the main SQL queries since they take upwards of a few minutes to run each (mainly because I need to rank and order data). I currently use code similar to that shown below to query the database from VBA.

When the DBCreateDynaset command is executed VBA waits until the dynaset data is retrieved from oracle. I would like to be able to do something similar to the pseudo code below.

Execute SQL to create dynaset
Do while still obtaining dynaset
Check v$session_longops - Indicate time taken / remaining on status bar
Loop

I have found that if you execute a large query without any tasks that require the whole dataset before they can begin (ranks, ordering etc) the dynaset is created almost instantly and excel does not freeze up.

With non dynaset operations such as inserts it is possible to run the query in non blocking mode. I am able to monitor the progress of the query and indicate it to the user.

I have tried using pl/sql to insert the data requested into a temporary table and then the normal select statement to get the data back out. However since the data stored in a table is not ordered it isnít much help to me since I still have to do the order by command.