Using Automation to control flow of various processes (can it do user input?)

Toad Data Point
1

I am using TOAD Data Point 3.3.0.1526 connecting to an Oracle Database. I have a rather intense number of stored procedures to run and at various points these will require human intervention. Specifically I want to kick off the import of FOUR files (two from state ‘a’ and two from state ‘b’). Sometimes all are processed at the same time - but other times only one state would be processed at a time. There is no ‘front end’ to manage the process.

I’d like to have toad automation (if section) ask the user which states would they want to process now:

declare and initialize a variable to zero (numeric)

Do you wish to import TEXAS information?

If YES - run script - when complete ask user if they desire to import ARKANSAS information. (and sets a variable that was initialized to zero - to ONE.)
if NO - ask user if they desire to import ARKANSAS information.
If YES - run import script and then (and sets a variable that was initialized to zero - to ONE.)
If NO - move forward

If the Variable is ZERO then the script ends (that means the user desires to import no data.
If the variable is ONE then the script will launch a long stream of oracle stored procedures.

Please let me know if this is possible - and if so - how the heck to do it!

I’m a rather experienced SQL and VB programmer. I have the import templates and stored procedures completed that are required for this.

2

No, this is not possible. Automation is for scheduling various tasks for them to be able to run unattended.

Igor.

3

As Igor mentioned, the automation designer is not an interactive window. But you could advise your users to just change the value of the first variable and then run the script with the logic you mention.

4

If the actions all are done in a SQL script you can use SQL Plus Replacements vars. &

when the script is run in the SQL editor the user will be prompted for the current value to use.

5

Can I use the substitution variables within the SQL statement of an “Execute Script” activity within Automation? That way - the user could be prompted and the variable would be saved within toad and the flow of the whole thing could work… (if that can be done - please let me know how because I’ve tried for a few hours and the SQL wont prompt me)

6

The purpose of the automation designer is to build scripts to be scheduled and run without attendance. There are no prompts in the designer. The only thing you can do is to tell your user where to change the variable value in order to run the script for his use. In the screenshot below I have one variable and that value is used in the Execute Script activity. The user would need to change the value in the variable activity

bindvarone.png
bindvarone.png706×639 22 KB
.

8372.bindvartwo.png
8372.bindvartwo.png593×581 20.6 KB

7

Debbie - U R AWESOME! THANKS!

8

If you don’t need automation, Toad Data Point can prompt users that run SQL through toad. There is a bug starting in 3.0 (not sure if it got fixed in 3.3 or not) that does not let multiple prompts work unless you highlight all the code (CTRL-A) and then run the SQL. The prompting code works nicely when it does work. Example - All of the TOAD: PROMPT comment lines below actually prompt the user with the text inside the single quotes and stores the result in the variable before the = sign:

– Highlight all the text (Ctrl-A) and then run the SQL or it may only prompt for 1 item and fail to insert a record.

– TOAD: PROMPT j = ‘Enter the Job Name (must match the value in the Job_Name variable in your job - limit varchar(50))’

– TOAD: PROMPT s = ‘Enter the Job start time (MM/DD/YYYY HH24:MI)’

– TOAD: PROMPT f = ‘Enter the Frequency (M-Monthly, D-Daily, W-Weekly, H-Hourly, Y-Yearly)’

– TOAD: PROMPT n = ‘Enter the Frequency Number (1=every week/month/day, 2=biweekly or every 2 weeks/months/days, etc.)’

– TOAD: PROMPT d = ‘Estimate max duration of job in hours (1.5 = 1 hour 30 minutes, .25 = 15 minutes, etc.)’

– TOAD: PROMPT o = ‘Enter Job Owners email (First.Last@Lovelace.com)’

Insert into GDAVIS.SCHEDULED_JOBS

(JOB_NAME, START_TIME, FREQUENCY, FREQ_NUMBER, STATUS, EST_DURATION, EST_COMPLETION, OWNER_EMAIL)

Select ‘&j’, to_Date(’&s’, ‘MM/DD/YYYY HH24:MI’),

‘&f’, &n, ‘S’, (.041666667 * &d),

to_Date(’&s’, ‘MM/DD/YYYY HH24:MI’) + (.041666667 * &d),

‘&o’

From DUAL;

commit;

9

I totally forgot about Toad Scripting. This is a good solution too. Thanks Greg!