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;
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%';
select * from test2;
drop table test1;
drop table test2;
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.
Please find screen shot of the very simple app that we created and added the our SQL
Thanks. I'll see if I can get that working.
Does it work if you take the drop and creates out and run them separately as in my screen shot?
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.
So, to be clear:
Script 1 creates the objects
Dataset one calls a file that only has select statements
Script 2 drops the objects
Is that correct?
If you look at Spool SQL, you will see why it is not working. Please post that here if you want help.
Main Menu -> Database -> Spool SQL -> Spool to Screen
Run it your action so it fails
Post the spool sql output.
We did the setup from scratch and now its working.
by the way, did you have any update for having Create/Drop as part of export dataset SQL.
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.
Thanks. Do we know when 17.1 will be released approx?
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.
Thanks for the quick response