The ProblemA 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 SolutionBecause we cannot stay only inside Toad, solution is done in two parts:
- Automation Designer
- Scripting part on OS file level
Automation DesignerIn 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
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" exitThis 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.
Once again, the whole solution is run by execution windows bat file call_export.bat.
The Solution2If 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 EndWhat is important in this solution is to check that no user variables (in mine case "XXX") are not defined inside Toad.
Hope this helps someone.