Toad’s Automation Designer and sqlplus defined variables

After one year pause from blogging, I have decided to write one post. Subject is mine second love-Toad for Oracle!

The Problem

A poster posted a question on Toad forum User Defined Variables in Options.

I'm trying to export a file with a variable name by using the user defined variables in options. When I try to define my variable as select to_char(sysdate,'mmm yyyy') from dual it puts that statement in the file name rather than the result of the statement. how do I format this in the variable so that it recognizes it as a formula rather than text?

Even thought Toad has made some nice features in variables manipulation, what user wanted, define variable through Sql*Plus commands, is not so clear and obvious how to do it. Reason is that Toad has nowhere exposed application interface for defining such an action. And this was a reason, why no one answered to poster...

The Solution

Because we cannot stay only inside Toad, solution is done in two parts:

1. Automation Designer
2. Scripting part on OS file level

Idea is to create one .bat file which will initialize environment variable and then call Automation designer, through it's command line interface.

Automation Designer

In Automation Designer,

• create one App named Do_Export
• Inside SQL part, place your statement for export, define export type (in mine case Delimited Text)
• In File definition place resulting destination file, which was the main problem, because it must have value derived from sql command. so define it like on the picture

In this way you are telling Toad to use global environment variable which will hold dynamic value (in our case "mm_yyyy" of some date value) for export file name.
After App definition

• Right click on chosen App (Do_Export) and use Create Parameter file option.
• Save file it in same directory as this file should be create it's export (for easy control). I will name it define_date_value.sql, which in fact looks very simple:

  set head off
  set timi off
  set time off
  set ver off
  SET ECHO OFF;
  set feedback off;
  SET SERVEROUTPUT ON SIZE unlimited format wrapped;
  SET PAGES 2000;
  SET LINESIZE 1000;
  SET PAGESIZE 9999;
  SET TRIMSPOOL ON;
  
  select to_char(sysdate,'mm_yyyy') from dual;
  
  exit;
  

• Create windows bat file call_export.bat, which will be starter of action with content:

  echo on
  FOR /F %%i IN ('sqlplus -s a_user/"pwd"@a_database @define_date_value.sql') DO set XXX=%%i
  echo %XXX%
  
  "C:\Program Files\Dell\Toad for Oracle 2016 R2 Suite\Toad for Oracle 12.10\Froggy1210.exe" -a "Do_Export | C:\Temp\Do_Export.ini"
  
  exit
  

  This .bat is initially executing SQL*Plus by calling define_date_value.sql script. Result from that SQL session is passed to XXX environment variable.
• Line before exit is calling Toad Automation Designer through command line, using previously created Do_Export parameter file.
• Result of dataset export is (in mine case!) 10_2016.txt file.

  A_ONE,A_TWO
  1,2
  

As you see to_char (sysdate,''mm_yyyy') from sql is used as file name what was the main problem at the beginning. In your case change sql from that file to something what is useful in you case...sql or even PL/SQL.
Once again, the whole solution is run by execution windows bat file call_export.bat.

The Solution2

If you wan to stay fully inside Toad, and run only from Automation designer, then you need to create another App, "DO_Call_Export" which consist of just one action-"Shell Execute". This would be a wrapper which call directly from Toad windows bat file call_export.bat.

With that approach you are fully inside Toad execution.

The End

What is important in this solution is to check that no user variables (in mine case "XXX") are not defined inside Toad.

If XXX variable is defined inside Toad, then this was not working...what I find more as a small Toad dis perfection but a bug (tested inside 12.10 x64) ... but we have to live with it.
Hope this helps someone.

Cheers!