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

Answers


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.


Need Your Help

Access token for anonymous users - JWT

authentication laravel-5 authorization access-token jwt

I have been making research to switch to a community supported authorization system rather than the one I've built. I made the mistake of rebuilding the wheel, instead of implementing a community d...

Changing form php sendmail to SMTP?

php

We created this contact form a long time ago, but we have recently had issues with our sendmail, now i need to change this to use SMTP and that i haven't done before. Is it much work or just a matt...