We have script which is performing set of create-drop -create-select statements.
When we run in Editor using Script execution it works perfectly fine without any errors.
The same script when we put in Toad automation export data set, we start getting ORA-00942 error - Table or view does not exists.
One observation, we have 4 selects and we get 4 times ORA-00942 error. We have 14 Drops, 28 create statments and followed by 14 drops for cleanup at the end.
ex SQL file
create table test1 as select sysdate as rundate from dual;
drop table test1;
create table test1 as select * from employee where emp_id < 50;
--sheetname: test1
select * from test1;
create table test2 as select sysdate as rundate from dual;
drop table test2;
create table test2 as select * from test1 where emp_name like '%xyz%';
The SQLFile in Export Dataset is not meant to do anything except SELECT statements. I can look at enhancing that in the future.
In the meantime - Can you do your DROP/CREATE in a different File, and use an Execute Script action immediately before your Export Dataset action? I think that would solve this.
It works for some scripts but some scripts giving this error. Behavior is not consistent.
Is it that select is getting executed even before the create table is executed?
Can you give me some details of your actions in Automation Desginer (with some screen shots) so I can try to reproduce the problem?
If I right-click on the app on the left, then click "Run", the actions on the right are run in order from top to bottom. So in this case: Execute Script1 would run first, then Export DataSet1, then Execute Script 2.
You can always turn on Spool SQL to see what Toad is doing the background. This is helpful for finding the cause of errors. from main menu: Database -> Spool SQL -> Spool to Screen.
i appreciate your help but it's still not working even after creating different files as suggested by you.
script1 > DataSet1 > script2
i have tried adding if else block but sometime it works and sometime it does not works.
did you have any update for having Create/Drop as part of export dataset SQL.
I looked at the code. We intentionally look for only comments which specify sheetname, filename, etc, and SELECT statements. We don't run any other type of statement in the script file in Export Dataset. It was not meant to be a full script engine.
I can possibly add support to run the entire script, and only export the SELECTs, but it is too late to add this to version 17.0. I can possibly add it to version 17.1.
17.0 is slated to be released around the end of October 2023.
17.1 will be released as beta likely beginning sometime in November 2023. It will likely be released as on official version around 6 months after that.
