Oracle Package erroring when a table doesn't exist
I'm using Oracle 9g at the moment and writing a package, which I'm fairly new to. I have some procedures in the package that load data into tables from external tables. It drops those tables first, recreates it with some transformations from the external table, and then creates the indexes for it. I can't just reference the external tables as I need indexes and a few changes to the data.
I then have some other procedures later on in the package that reference these temporary tables to do their work. It all works fine, except if, when running the procedures in order, the procedure that creates the table gets interrupted after it drops it, but before it creates it.
No if I make a change to the package body it compiles with errors as the later procedure that reference that table inform me that the table does not exist. Nor can I now run any of the procedures due to this validation error.
Can anyone advise of any best practices or how best to do these without getting these validation errors? Or is there a way to turn off this validation somehow?
Many thanks, Dan
Make two packages. One that drops and creates the tables and the other with the data manipulation code. The second package will become invalid once you drop the tables but the first one will still be usable.