How to get Oracle table count ?

[CodeLover55]

I have a SQL Server database which I populate from various sources - all of which work fine.
4 of my processes run nightly at 2am without a problem. However, on Mondays, I can't run the jobs until the processes on the source system are completed, sometime between 6:30am and noon. What I'm having trouble doing is "looping" until I can run the jobs. Basically what happens is:

I can find out if the source processes are done 1 of 2 ways:
1) There is an Oracle table which I can read which has records in it as long as the processes are running...as the processes complete, those records are deleted. When that table is empty, I can kick off my jobs.
-or-
2) There is a log file that is created when the process is complete. When that log file appears, I can kick off my jobs. The only difficulty with this one is that a-the log file is written to a user directory which may not be viewable by the account running the jobs on SQL Server, b-the directory contains the current date, and c-the log file name also contains the current date. I feel it would be easier to just read the table on Oracle until it's empty (say, every 30 mins or so) and then kick off my jobs.

Either way, whether I'm reading the Oracle table or looking for the existence of the log file, I need to "loop" until my success criteria are met (table is empty, or log file exists).

My big problem is that I am not familiar with how to do this. I can write an Oracle query to determine if the table has 0 rows...but I don't know how to keep looping that until it does, indeed, return 0 rows. Returning 0 rows would be considered "Success" - and I can use that success as the trigger for my extract & load processes.

How do I retrieve the count from the Oracle table in a variable on the SQL server and then use that variable every time to decide whether to start my main job or not ?

Thanks.

 

[sguslan]

You did not explain what sort of jobs they are, i.e. DTS or stored procedures.
But, if it was a DTS, then use the DTS workflow between 2 tasks.
Otherwise, you can created a linked server to Oracle then use something like:

Declare @OracleCount int
set @OracleCount=1
while @oracleCount>0
begin
select @OracleCount=OracleCount
from OpenQuery(ORcleLinkedServer,
'select count(*) OracleCount from OracleTable'
end ________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[sguslan]

Forgot to mention, if you were not to create a linked server to ORacle for any reason, then you can use OpenDataSource instead of OpenQuery ... Search Books Online for help on OpenDataSource ________________________________________________________________________________
If you do not like change, get out of the IT business...