Toad Automation Designer - Export Dataset Bug

I just added a script action which sets a package variable. I definitely see the package variable as default with "Execute scripts in Toad session" unchecked, and whatever I set it to retained when I check that option.

I added this

And added a function which returns its value here:

The output is as expected -- 99 when "Execute scripts in Toad session" is checked, default otherwise

My "package_with_state" package is as follows:

CREATE OR REPLACE PACKAGE package_with_state AS

  global_variable NUMBER := 2;
  
  function global_var return number;
  
END package_with_state;
/

CREATE OR REPLACE PACKAGE body package_with_state AS
  
  function global_var return number as
  begin
    return global_variable;
  end;
  
END package_with_state;
/

Sorry for the dumb question, but you don't have a "disconnect" command anywhere in your script, do you? That could cause package variable values to be forgotten too.

Looking at the source, I can't think of any other possible causes for this.

Try turning on spool sql and looking at the output for clues.

Hi John.

No disconnect command.

Here's the structure of the automation:

DeleteFile (log file)
Connection Iterator (12 connections)
  RunScripts (set package variables, logging)
  QueryIterator (QI runs a query to read some schema values)
    Set Variable 1 (based on QI)
    Set Variable 2 (based on QI)
  ExportDataset (Query is reliant upon package variables from earlier and filename based on Variables 1 and 2) 
  RunScripts (Teardown for connection)  

I was finding that ExportDataset didn't see package variable state.

Hi Paul,

I just tried this and it still worked correctly for me. Script1 sets the package global. Export Dataset1 exported it correctly. Script2 changed the package global to something else.

If you can email me the spool sql (maybe just for 1 connection, not all 12), I'll look through it for clues. You can feel free to take out anything that might be proprietary. john.dorlon@quest.com

You can temporarily "disable" some of the connections so you don't have to remove and re-add them.

Hi John, I ended up implementing a workaround using CTE function which also called the stored proc.

Perhaps my state loss observation was caused by a background build (we have code to avoid package state invalidated errors). So don't worry about it for now, I'll keep my eye on it going forward.