Toad World® Forums

[TDP 3.3 w Oracle] Waiting on Tables to Load - While Loop with SQL

Greetings,

I searched through some old posts and found some answers that almost, but not quite, address the issue that I have :slight_smile:

We have an Oracle database where the tables are not uniformly updated, and we’re having an issue with our Toad automations against that database - either because one or more of the tables don’t exist when the script runs, or they haven’t been refreshed and the scripts pull old or no data.

The Oracle database does have a “status” table that lists all of the refreshed tables, their status and when they were last updated. Older scripts written in VBScript have a built-in loop that checks their dependencies on this table, and if all of the tables aren’t ready, they go back to sleep for a while - 10 mins, 5 mins, just depends on the author.

I’ve been trying to accomplish the same thing in TDP using a WHILE loop, but I can’t quite seem to get it right. All I’m trying to do is check that I get X number of rows returned from my Execute Script activity which is what checks against the status table - if all the tables are loaded, X number of rows will be returned; anything less than that and I want it to go back to sleep for a while and then check again.

Anyone had to do something like this that’s willing to share their solution?

Please find an example in the attachment. Example uses Toad Simple Database to imitate your use case. Provide your valid sql for Execute_1 activity. Also, change conditions in If_Condition_1 and If_Condition_2 activities to reflect desired number of rows (5 in my example).

Unfortunately, 3.3 has a bug which prevents while loop from indefinite execution. Instead, you can submit a reasonably big number for the loop limit of While_Condition_1 activity (1000 in my example). Also, choose desired time interval script execution should be postponed in the Pause_1 activity (60 seconds in my example).

Does it describe your use case?

Igor.

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/36/Script_5F00_1.tas:550:0]

Hi Igor,

Yes, that describes my use case perfectly! I was almost at the same point as your example, but I was getting hung up on using a different variable to control the while loop - rather, a separate variable from the one generated by my statement.

The diagramming also doesn’t quite make the logic flow easy to understand, but now that I’ve seen the example it makes sense.

I’ve implemented it into one of our more troublesome scripts for tomorrow morning so we’ll see what happens.

Thank you!

Hi Igor,

I followed your example, and while TDP is looping, it never evaluates the expression to ‘True’, even though it should be. I’ve defined a variable - [tag:ready]# - and set the initial value as ‘False’. Then in my while condition, I have the following:

SELECT COUNT(TABLE_NAME) CNT FROM LOADER.TABLE_STATUS

WHERE TABLE_NAME IN (‘TABLE1’,‘TABLE2’,‘TABLE3’) AND STATUS = ‘COMPLETE’

AND SYSDATE >= LAST_ACTIVITY

The idea being that once the condition is satisfied, the row count variable - which I’ve named [tag:tables]# - will equal 3. The If conditional that follows is configured to alter the variable [tag:ready]# to ‘True’ if the row count variable equals 3, and if it is less than 3 to pause for 5 minutes. However, even when the variable equals 3, it continues to pause until the 4 hour time limit has passed.

However, it appears that once the timer finally expires, TDP continues with the last activity - regardless of whether the condition for the while loop was ever satisfied. I am using 3.3, so maybe this was fixed in 3.4 but we’re not ready to begin deploying 3.4 yet.

Any ideas?

Hello,

Can you send me your script (igor.manokhin@quest.com)? I don’t think the problem is in your sql so changing it and adapting to my environment shouldn’t prevent me from reproducing your issue.

Of course, trying 3.4 (or even 3.5 beta) is not a bad idea at all but let’s analyze your script anyway.

Thanks,

Igor.