Toad World® Forums

Toad Data Point 4.3.0.718: Using variables for dynamic filename


#1

Hi,

I am trying to use a variable in an automation script to generate dynamic filenames and then use the variable in “Select to File” activity to export data.

Using “Set Variable” activity I have defined a variable like so:

Next in “Select to File” activity I am trying to assign a dynamic value like so:

Next I have a “Log Comment” activity to display the value like so:

But when I run it the value is not displayed. The second last line where I expect a value

I must missing something very simple here!

Thank you for your help!

Aleem


#2

I cant see the images. Can you insert them using the insert media button in the posting editor?


#3

Thank you Debbie,

What a luck! It was through your posts/training videos that I was trying to solve my requirement, but I must have missed some parts.

Here are the screenshots!

Defining variable through “Set Variable”

Variable Defined.png

Value Assignment through “Select to File” using Oracle PL/SQL block

Setting output using “Log Comment”

Variable Output.png

Execution output


#4

Okay, I see.

You could do this a couple of ways but all of them set the variable using one of the two variable activities. Don’t use Pl/SQL block in select to file.

Option 1:

Use Set Variable activity. Variable is type string and use the expression elevator to build your value. The expression evaluator is the button at the end of variable value box. ‘X=’. You don’t need to start with empty string. Just set your expression in the value box.

Concat(Concat(‘K0173’,To_char(Current_date(), ‘YYYY_MM_DD’)), ’ Auto Run.xlsx’)

Option 2:

Use set Variable activity. Variable type is SQL. Chose your Oracle connection and execute the SQL.

select ‘K0173’ || to_char(sysdate, ‘YYYY_MM_DD’) || ’ Auto Run.xlsx’ from dual

Attached is example of both.
variableExample.tas (8.16 KB)