Invalid SQL statement that worked in prior version

Toad Data Point & Toad Intelligence Central
1

Had an Automation Script that worked fine in prior version. First time running after last major upgrade it did not work and I’ve just avoided researching (or posting) by just running the native sql that the *.tas doesn’t like.

the TAS has three pieces. Import + SQL script + SQL script. Import works file. Dies upon executing first SQL script. I can run exact SQL in the editor and it works fine.

“Execution Error: ORA-00900: invalid SQL statement”

It’s an INSERT statement.

I’ve tried rebuilding the template section many times in various ways (entering sql, referring to sql file etc.) to no avail.

added and removed COMMIT from the SQL too and neither version worked.

Any thoughts?

Here’s the SQL, but as I said this works in the editor:

INSERT INTO vaggrd.vagg_deleted_product_impact
(SELECT ID,
ITEM_NUMBER,
TICKET_ISSUE,
‘’,
‘’,
‘’,
‘’,
‘’,
‘’,
STATUS,
‘ckasisch’,
SYSDATE,
‘’,
‘’
FROM VAGGRD.CK_DELETE_IMPACT_ADD
WHERE CK_DELETE_IMPACT_ADD.STATUS <> ‘OLD’);

2

I would try the following:

  1. Check the connection that the Execute script is associated to. Just confirm it is the correct one.
  2. Remove the semi-colon from the script.

If there are no issues found in items one and two, turn on verbose logging and run the script. Post the log file, tas file and two scripts. We will have a deeper look.

Debbie

3

removing the semi-colon at end of Query solved issue.

Thanks.

4

Glad to hear.

Debbie

5

When I run the below script from TOAd Automation it fails, but when I run the script from Toad Editor it runs fine.

TRUNCATE TABLE TEST;

INSERT INTO TEST
(
SELECT ,
FROM TABLE
);

COMMIT

6

What error do you get? What is the connection type? Can you turn on logging to verbose, run the script and then post several lines before the error and including the error?

Debbie

7

Please find the attached.

Also When I renamed the file for example test.sql and run the Automated task it runs fine. Please assist.

8

I was hoping I could see the SQL in the log file so we could see what was being run. When I turn on Verbose and I am using the Execute Scrript I can see the SQL. I would double check the Logging level. It is in the settings activity at the top of the script.

What you mention is interesting, you say if you change the name of the script it corrects it. I wonder if it somehow is finding the script in another directory. I do see that it is fully qualified.

Is there a need to read the script from a file? As opposed to embedding the SQL in the automation script? If the SQL is not changing outside of the automation script I would just embed it.

Debbie

9

The script changes every week. Also I changed the logging level to Verbose and still the same issue. The only way I resolved this is by changing the file name. But what I am looking is the reason why TAS triggers the invalid sql statement even when the underlying SQL is correct.

10

This doesn’t make sense to me. What version of TDA are you using?

Debbie

11

TDA 2.7.0.348

12

SqlScript and SqlScriptEmbed. SqlScriptEmbed needs to be an empty string. If this has the path to the file this would be the error.

Debbie

I installed TDA 2.7 and looked into this a bit further. In your case it is trying to execute the path name to the file.

When you look at the log file where it should show what is executing we see this.

Execution SQL: C:\IAE-FILES\Production\Report - EODAllocations\db\Scripts\q_incorrect_cusips_update.sql

I can make the SQL error and the file looks like this.
Executing script: \alvvsar\DebDocArchive\PROD_SUPPORT\Oracle Collection\CommunityTest.sql
Execution Error: ORA-03291: Invalid truncate option - missing STORAGE keyword
Execution SQL: TRUNCATE TABLE QUEST_AUTO.DEBTEST

–INSERT INTO QUEST_AUTO.DEBTEST
–(
–SELECT ADDRESS_ID, ADDRESS, ADDRESS_LINE_2, CITY, S

So the question is why is the path being used as the SQL.

There are different attributes in the tas file to designate if a file is to be used or if SQL is embedded. Is the *tas file being changed?

Open up the tas file in wordpad and look for the xml values for