Toad v12.10 Environment Variables not substituted in Execute Script (text) action of Automation Designer

Toad for Oracle & DevOps Toolkit
1

Toad v12.10

When I run something simple like Select ‘&x’ from dual; in the Execute Script action in automation designer, I do get the popup to set the value of the variable when it is run.

But assigning any environment variable to that substitution variable just causes that env variable name to appear in the output instead of its value ??

old: Select ‘&x’ from dual
new: Select ‘ACTIVESESSIONUSER’ from dual
‘ACTIVESESSIONUSER’

ACTIVESESSIONUSER
1 row selected.

How can we get this to work?

Some other unrelated bugs/issues I ran into while struggling with this for several days:

  1. Doing the same thing in Export Dataset action with export query option, produces a modal dialog with this error and permanently hangs Toad, requiring taskkill.

ORA-00933: SQL command not properly ended

I would like a pointer to any documentation on this action and on what kind of sql that can go in that box. Can we use pl/sql ? Can it only be a single query ?

  1. Toad spool files are set to output UTF-8 with BOM by default, which is incompatible with sqlplus (if you want to execute spooled, generated sql code).

This default is different from the ANSI default encoding for saving all other Toad files, i.e,

Options --> General --> Default Encoding

What is the reason for this inconsistency in encoding defaults?

How do we change the spool file encoding locally within an Automation Designer app to ANSI, without requiring a Toad-wide default change for all users?

  1. What is the meaning of the [] visible in front of many of the automation actions ? Are things like this explained in an Automation Designer User Manual that you can point me to ?

  2. In the FTP or Rexec actions, how do I parametrize the Hostname, Username and password like all the other fields? For example with $uid or %ACTIVESESSIONUSER% or %USERNAME% etc.

  3. Export DDL action (export dataset) does not produce a sqlplus compatible Insert/Merge script when table data contains ‘/’ forward slashes or semicolons on lines by themselves, or if it has any blank lines. To workaround this we need to set sqlterminator off and setblanklines on.

How can we change the default semicolon statement separators that are in the generated insert scripts from this action?

2

You can pass Toad env. variables into Execute Script actions starting with Toad 12.11. There is an example of how to do that in my dropbox folder (look at the docs about Automation Designer): Dropbox - Toad - Simplify your life

  1. It can only be a select statement in the Export Dataset action. Yes, single query only. If you want to export multiple sql statements, then create multiple actions (or you can export multiple tables/views by specifying objects to export instead of a query)

  2. not sure maybe someone else will answer this one.

  3. it just means that the action doesn't have enough details configured for Toad to give you a meaningful hint about what it does.

  4. not sure maybe someone else will answer this one.

  5. There is no way to change the default semicolon statement separators in the export DDL/dataset action. However, Toad 12.12 will produce insert statements like below, so that blank lines inside of data do not cause a problem.

(EMPNO, ENAME, JOB, MGR, HIREDATE,
SAL, COMM, DEPTNO)
Values
(7499, ''||CHR(13)||CHR(10)||'/'||CHR(13)||CHR(10)||'', 'SALESMAN', 7698, TO_DATE('2/12/2018', 'MM/DD/YYYY'),
150, 300, 30);

3
  1. Go into Toad’s options to the “Script” page. In there, you can change your default encoding for spooled files. By default, it’s UTF-8, but you can change it to ANSI.

-John

4

Thanks John Dorlon for your detailed response.

John Bowman,

How to change the default for spool files in Toad options wasn’t my question.

I was asking:

2a. What is the justification for the Toad design choice of using an inconsistent default just for spool files, different from the normal ANSI default in the General section of Toad options?

2b. Given this design choice, how can the spool file encoding be changed to ANSI within an Automation Designer action without requiring a global change in the Toad options Script section? This Automation Designer app needs to be deployed to multiple users computers, and we don’t want to require each user of the app to have to A) manually change their defaults in Toad option each time before they run this app and then B) restore it back after running the app if their normal workflow is aligned to the defaults.

5

Hey Tadpole,

My apologies – I thought you were asking how to change the default encoding for just the spooled output so it didn’t affect Toad’s default encoding as a whole.

2a. I can’t speak to what the reason was for changing the default value for spool files to be different than Toad’s default encoding. I’ll have to research that to find out. If there’s no specific reason, it can always be changed to match the default Encoding in Toad.

2b. Right now, there’s no way to define the encoding at an action level. All users would need to make sure they’re using the same encoding settings; however, I can see the potential advantage of defining this at an action level. This kind of a change, though, is something that could potentially affect a number of different actions. As a result, let me do a little research and see what might be able to be done for this next release of Toad.

-John

1 Like
6

Hey Tadpole,

I’ve changed the default for spool encoding back to ANSI to match Toad’s default encoding. I’ve also added encoding options to the execute script action for Monday’s beta. Feel free to give it a shot and see if that will work for you. Note, these new options will only work for Toad 13 and higher. They won’t work work with Toad 12.12.

-John

1 Like