Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
314 views
in Technique[技术] by (71.8m points)

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

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...