Automation - Insert Statement does not produce results

Toad Data Point
1

Hello

i have created an Automation script using a SQL that inserts data into the table by using Execute Script activity. For whatever reason when I test the Automation script data is not getting inserted even though I don’t get any error messages. “0 rows affected”. When I ran the query in the Editor window it inserts data into the table.

Any advice is greatly appreciated.

thanks.

Lyuba

2

What version of TDP are you using? I’ve just specifically checked in current TDP 3.2 and latest 3.3 beta - works fine.

Igor.

Message was edited by: IgorM

3

If you are using Oracle, we do not show how many rows are affected when executing insert or deletes. We only show rows affected when executing select.

If you want to see more of what is being executed turn on verbose and use the text output display type.

If you want number of rows inserted, I would use the import wizard.

Debbie

4

Thank you both. Let me try these solutions. I am using oracle and toad for Data analysts 2.6

5

I am still not doing something right. When I try to set up Import Wizard based on Debbie’s suggestion I get the error message (see attached .txt). But again, the query processes in the Editor window without any issues with the same db connections.

Your assistance is greatly appreciated.

Regards

Lyuba
ONIX report error.txt (1.3 KB)

6

Okay, as far as we’re talking about inserting rows in Automation’s Execute Script activity, it seems that it really depends on what provider you’re using. SQL Server works fine, Oracle doesn’t as I see it in the latest beta. The good news is it works for Oracle in current developer build. It means the fix should be available in the next beta. It still doesn’t return number of affected rows but row itself is inserted successfully.

Igor.

7

Thank you Igor. The problem is that rows ARE NOT getting inserted.

8

When you use the Import Wizard we execute the query without returning rows to determine the column types for the import table. It appears to be having a problme in this area.

Please take your query and execute in an editor. Then save the SQl and Result set as *.tef fille. (Toad Editor File) Please post this file or send directly to me. dpeabody@quest.com (refer to this thread)

Also, what happens if you right click and Send To ExportWizard from the above editor file?

Debbie

9

One more simple thing to try. Do you have a Commit; after you insert script?

Debbie

10

Hi Debbie

attached are 2 files

also, I don’t get an option Send to Export Wizard when I right click. :frowning:

thank you so much for your help. I am just not sure what to do at this point.
DSR 234 Onix Report - Insert.sql (5.96 KB)

11

Hi Debbie

attached are 2 files

also, I don’t get an option Send to Export Wizard when I right click. :frowning:

thank you so much for your help. I am just not sure what to do at this point.
DSR 234 Onix Report - Insert.tef (22.8 KB)

12

Thanks for the files. Can you do one more thing? Comment out the insert part of the statement and just execute the select. Save that as the tef file. I need to see the columns and the data that is returned from the select to see if this is affecting the issue. Can you send this?

Debbie

13

If I save it in .tef format it still saves only the query. I have saved as .csv.
CSVFile_2012-12-19T15_54_13.tef.csv (815 KB)

14

Sorry I didnt’ respond earlier. I can reproduce the issue. We found an error in our code and fixed if with CR105785. So if you want to pick of the next Beta and check it out you can.

Debbie

15

P.S. As a workaround you could try adding an execute script activity with only COMMIT in it. Do not have a semi-colin at the end.

Debbie

16

Debbie,
6 years later it appears I am having this same issue. Using TDP 5.0.4.45 64 Bit on Windows 10. SQL works perfect but when I run the SQL through automation the INSERTs do not work. What's odd, it runs on 4.2.0.229 32 Bit on Windows 7 perfectly. The INSERT should insert 22 records but getting 0.

Here is a portion of my SQL with the INSERT.....
INSERT INTO TCCH101.TWC_REPEATS

SELECT TWCREPEATHIST2.SYS_OJB,
TWCREPEATHIST2.PRIN_OJB,
TWCREPEATHIST2.H_CREATE_DTE,
TWCREPEATHIST2.H_COMPL_DTE,
TWCREPEATHIST2.CUST_ACCT_NO_OJB,
TWCREPEATHIST2.HSE_KEY_OJB,
TWCREPEATHIST2.H_JOB_CLASS,
TWCREPEATHIST2.H_JOB_NO,
TWCREPEATHIST2.H_ORDER_NO,
TWCREPEATHIST2.LS_DATETIME,
2 AS REPEAT,
TWC_REPEATS.H_COMPL_DTE - MAXHISTJOBS.MAX_H_COMPL_DTE AS DAYS
FROM (TCCH101.MAXHISTJOBS MAXHISTJOBS
INNER JOIN TCCH101.TWCREPEATHIST2 TWCREPEATHIST2
ON (MAXHISTJOBS.SYS_OJB = TWCREPEATHIST2.SYS_OJB)
AND (MAXHISTJOBS.PRIN_OJB = TWCREPEATHIST2.PRIN_OJB)
AND (MAXHISTJOBS.CUST_ACCT_NO_OJB =
TWCREPEATHIST2.CUST_ACCT_NO_OJB)
AND (MAXHISTJOBS.HSE_KEY_OJB = TWCREPEATHIST2.HSE_KEY_OJB)
AND (MAXHISTJOBS.MAX_H_COMPL_DTE = TWCREPEATHIST2.H_COMPL_DTE)
AND (MAXHISTJOBS.MAX_LS_DATETIME = TWCREPEATHIST2.LS_DATETIME))
INNER JOIN TCCH101.TWC_REPEATS TWC_REPEATS
ON (TWC_REPEATS.SYS_OJB = MAXHISTJOBS.SYS_OJB)
AND (TWC_REPEATS.PRIN_OJB = MAXHISTJOBS.PRIN_OJB)
AND (TWC_REPEATS.CUST_ACCT_NO_OJB = MAXHISTJOBS.CUST_ACCT_NO_OJB)
AND (TWC_REPEATS.HSE_KEY_OJB = MAXHISTJOBS.HSE_KEY_OJB)
WHERE TWC_REPEATS.REPEAT = 1
AND TWC_REPEATS.H_JOB_CLASS='T'

GROUP BY TWCREPEATHIST2.SYS_OJB,
TWCREPEATHIST2.PRIN_OJB,
TWCREPEATHIST2.H_CREATE_DTE,
TWCREPEATHIST2.H_COMPL_DTE,
TWCREPEATHIST2.CUST_ACCT_NO_OJB,
TWCREPEATHIST2.HSE_KEY_OJB,
TWCREPEATHIST2.H_JOB_CLASS,
TWCREPEATHIST2.H_JOB_NO,
TWCREPEATHIST2.H_ORDER_NO,
TWCREPEATHIST2.LS_DATETIME,
2,
TWC_REPEATS.H_COMPL_DTE - MAXHISTJOBS.MAX_H_COMPL_DTE
HAVING (TWC_REPEATS.H_COMPL_DTE - MAXHISTJOBS.MAX_H_COMPL_DTE <= 30);
COMMIT WORK;