This site has been destroyed by Google forced upgrade to new way of WEB site.
All files links are not working. Many images has been lost in conversation.
Have to edit 190 pages manually. Will try to do ASAP but for this I need time ...
THANK YOU GOOGLE !

Tuesday, October 11, 2016

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!

4 comments :

  1. > If XXX variable is defined inside Toad, then this was not working...what I find mre as a small Toad bug

    Hello Damir,

    It's not a bug, just a misunderstanding of how it works, I think.

    You can set OS variables from the OS.
    You can set Toad variables from Toad.
    You cannot set Toad variables from the OS.

    When evaluating a variable, Toad first for Toad variables. If Toad does not find a Toad variable by that name, only then it looks for OS variables.

    I hope that clears things up.

    -John Dorlon

    ReplyDelete
  2. Hi John,

    Thx for your clarification.
    What I think, if variable is inside Toad defined as global, then Toad should pass a value as it is.
    I know that global in Toad context has different meaning, but then you should declare Toad call of variables like $XXX or somehow different than %XXX%, what is core OS based call, so people should not think about this ...if there any variable inside Toad which will prevent execution.
    Brg
    Damir

    ReplyDelete
  3. Toad for analyst has a feature like shown directly supported.
    More in http://dev.toadfordataanalyst.com/webhelp/content/Automation/Tutorials/Automation_Variables.htm
    Maybe this will be a nice example how to solve the same thing in Toad as well....

    ReplyDelete
  4. solution 1 is not working with result multiple rows of define_date_value.sql script.

    try it:
    @echo on
    FOR /F %%i IN ('sqlplus ETL/etl123@10.2.16.206:1521/BIP @define_date_value.sql') DO (
    if not "%%i"=="SQL*Plus:" if not "%%i"=="Copyright" if not "%%i"=="Last" if not "%%i"=="Connected" if not "%%i"=="Oracle" if not "%%i"=="With" if not "%%i"=="Disconnected" (
    set /A x=%%i
    call :SetVar))
    goto :eof
    :SetVar
    set y=%x:~2,2%/%x:~0,2%/%x:~4,4%
    echo %y%
    type Export1.ini | repl "ABCXYZ1" %y% > Export%x%.ini
    "C:\Program Files\Quest Software\Toad for Oracle 2017 R2 Edition\Toad for Oracle 12.12\toad.exe" -a "App1|C:\Users\hoang\Documents\Baocaokhaivi\Export%x%.ini"
    goto :eof

    ReplyDelete

Zagreb u srcu!

Copyright © 2009-2018 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign