Table created in a procedure is dropped, Getting compilation error for procedure

Code which needs to check whether a table exists indicates bad software architecture. There should be no need to create tables on the fly. It’s an anti-pattern (at least in Oracle). However, we see variations on this problem often enough, so it’s obvious that this anti-pattern is thriving in the wild.

If you really need to implement such a solution (for whatever reason) the correct approach is to separate table building code from the table using code. Have separate packages for them.


If table XYZ doesn’t exist pkg_calc.run_xyz_job() is invalid. However it’s invalidity won’t prevent pkg_ddl.build_table_xyz() from executing. Then, when the outer program calls pkg_calc.run_xyz_job() it will compile the procedure.

Leave a Comment