Need help with @@executefromthisdir.sql in Automation Designer

Toad for Oracle
1

I am new to TOAD and its Automation

The following executes fine when executing by itself, after including as a script in the Automation Designer when run I get

Script C:\DATALO~1\dir~1\CHANGE~1\dir\MASTER~1.SQL was executed with 3 errors and 1 compile error.

set define off
set echo on
set term on
set time on
set timing on
set verify on

spool Master_Log.log
Prompt "START Master "

@@executefromthisdir1.sql
@@executefromthisdir2.sql
@@executefromthisdir3.sql

spool off
Prompt "END Master "

I believe it is not recognizing the @@

I have many similar files to execute that are generated by a program and I need to execute them against many databases (have done this with a list of sql files, but without the @@). Thus the automation need.

Please help me to move in the right direction

aTdHvAaNnKcSe

Fred

2

Toad’s current directory if probably set to something other than the one containing your script. This sounds vaguely familiar like it’s either been fixed in a more recent version or some other area of the product had a similar problem that was resolved. Anyway, what is your version of Toad? Fully qualifying your script references to include path info should work or you can configure SQLPATH environment variable and add the path to your scripts to it. That too should work.

3

Toad for Oracle 12.9.0.71

Your solution would work except that each file is in a different directory, and the SQL files have otherwise identical names.

The provided sample is automatically generated from a .cmd file that I have no control over and no access to the source.

Thanks

Fred

From: Michael Staszewski [mailto:bounce-mstaszew@toadworld.com]

Sent: Thursday, August 25, 2016 5:06 PM

To: toadoracle@toadworld.com

Subject: [EXTERNAL] RE: [Toad for Oracle - Discussion Forum] Need help with @@executefromthisdir.sql in Automation Designer

RE: Need help with @@executefromthisdir.sql in Automation Designer

Reply by Michael Staszewski

Toad’s current directory if probably set to something other than the one containing your script. This sounds vaguely familiar like it’s either been fixed in a more recent version or some other area of the product had a similar problem that was resolved. Anyway, what is your version of Toad? Fully qualifying your script references to include path info should work or you can configure SQLPATH environment variable and add the path to your scripts to it. That too should work.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Nothing in this message is intended to constitute an electronic signature unless a specific statement to the contrary is included in this message.

Confidentiality Note: This message is intended only for the person or entity to which it is addressed. It may contain confidential and/or privileged material. Any review, transmission, dissemination or other use, or taking of any action in reliance upon this message by persons or entities other than the intended recipient is prohibited and may be unlawful. If you received this message in error, please contact the sender and delete it from your computer.

4

What do you get when you run the script in the editor using F5?

5

F5 runs successfully

From: Gregory Liss [mailto:bounce-GTDG@toadworld.com]

Sent: Thursday, August 25, 2016 5:46 PM

To: toadoracle@toadworld.com

Subject: [EXTERNAL] RE: [Toad for Oracle - Discussion Forum] Need help with @@executefromthisdir.sql in Automation Designer

RE: Need help with @@executefromthisdir.sql in Automation Designer

Reply by Gregory Liss

What do you get when you run the script in the editor using F5?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Nothing in this message is intended to constitute an electronic signature unless a specific statement to the contrary is included in this message.

Confidentiality Note: This message is intended only for the person or entity to which it is addressed. It may contain confidential and/or privileged material. Any review, transmission, dissemination or other use, or taking of any action in reliance upon this message by persons or entities other than the intended recipient is prohibited and may be unlawful. If you received this message in error, please contact the sender and delete it from your computer.

6

In the script action, are you running it as Text or from a script? If from text, try having the text as a SQL file in the same folder as the @@ files.

Another thought is to your SQLPATH env variable to the location of the @@ files.

From: fred.bonjour [mailto:bounce-fredbonjour@toadworld.com]

Sent: Thursday, August 25, 2016 6:54 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Need help with @@executefromthisdir.sql in Automation Designer

RE: Need help with @@executefromthisdir.sql in Automation Designer

Reply by fred.bonjour

F5 runs successfully

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

7

Everything is in the same folder for each case I’m executing (there are many cases)

Regarding the SQLPATH is there a way I can dynamically change it for each case? I typically have 10-20 cases or folders holding identically named sql files, all in a different folder.

From: Gregory Liss [mailto:bounce-GTDG@toadworld.com]

Sent: Friday, August 26, 2016 8:24 AM

To: toadoracle@toadworld.com

Subject: [EXTERNAL] RE: [Toad for Oracle - Discussion Forum] Need help with @@executefromthisdir.sql in Automation Designer

RE: Need help with @@executefromthisdir.sql in Automation Designer

Reply by Gregory Liss

In the script action, are you running it as Text or from a script? If from text, try having the text as a SQL file in the same folder as the @@ files.

Another thought is to your SQLPATH env variable to the location of the @@ files.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Nothing in this message is intended to constitute an electronic signature unless a specific statement to the contrary is included in this message.

Confidentiality Note: This message is intended only for the person or entity to which it is addressed. It may contain confidential and/or privileged material. Any review, transmission, dissemination or other use, or taking of any action in reliance upon this message by persons or entities other than the intended recipient is prohibited and may be unlawful. If you received this message in error, please contact the sender and delete it from your computer.

8

Fred, I see the problem and I'm logging it. Toad needs to be setting its current directory to the path containing your first script just before executing it. In the meantime there is a workaround if you use SQL*Plus to execute instead. Create an execution harness BAT file. Paste in these contents...


@ECHO OFF
SETLOCAL
FOR %%i IN ("%1") DO (
set filepath=%%~pi
)
cd %filepath%
echo exit | sqlplus user/pwd@db @%1

In the Automation Designer use a Shell Execute action instead. Set the program to the bat file you just created. Set the Parameters as the script file to execute.

3107.Capture.png
3107.Capture.png468×553 13.3 KB

Adjust the harness BAT to suite your needs. If you need to save the output somewhere you'll need to modify your scripts or the bat file to spool as needed.

9

This is fixed for 12.11 and will be in next beta.