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

Question

The first step of created procedure is to check if table XYZ exist. If it does then go ahead with further calculation on the table XYZ but if it does not exist create the table and then go ahead with the calculation(inserting new records in the table).

So for testing purpose I dropped the table. The moment I dropped the table I am getting compilation error from the procedure saying that table does not exist.

How should I solve this issue.I can not change the logic.

Answer 1

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.

begin
    pkg_ddl.build_table_xyz;
    pkg_calc.run_xyz_job;
end;

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.