-2

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.

SYMA
  • 119
  • 3
  • 15
  • Please post ( the relevant part of ) your code. – Aleksej Mar 09 '17 at 12:54
  • 1
    Post your code. Also you should use `execute immediate` in every reference to the `table` that may or may not exit. Otherwise you will get compilation errors. – PKey Mar 09 '17 at 12:55
  • Hi Plirkee, yes I tried using execute immediate by taking the query in a string. But the query is very large So I am getting error as string literal tooo long. – SYMA Mar 09 '17 at 12:59
  • [Please see this question](http://stackoverflow.com/q/33628951/266304). – Alex Poole Mar 09 '17 at 13:01
  • 1
    If your query string is too long (more than 32k??) then you'll have to build it up as a CLOB, in chunks shorter than that. That isn't what you asked about though... – Alex Poole Mar 09 '17 at 13:02
  • I'll bet the code is based on the table itself where you are "doing calculation" on the table. This code has a direct reference to the table, also called a dependency. When you dropped the table, you broke the dependency, making your procedure invalid. It will not compile until the table is in place. Or, as mentioned above, you wrap the dependencies in EXECUTE IMMEDIATE blocks, which removes the direct dependency. – unleashed Mar 09 '17 at 22:15

1 Answers1

2

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.

APC
  • 144,005
  • 19
  • 170
  • 281