Sometimes we work on a PL/SQL block that can possibly make some content or even object changes we don’t want to do right away. At the same time you want to make sure that your block will compile just fine. A simple solution might be to turn your block into a stored procedure and try compiling it:
DECLARE --declarations BEGIN --any executable content END;
All we need to do is to replace DECLARE with “CREATE OR REPLACE PROCEDURE procedure name AS” as follows:
CREATE OR REPLACE PROCEDURE sp_test AS --declarations BEGIN --any executable content END;
According to Oracle documentation, “to create a procedure in your own schema, you must have the CREATE PROCEDURE system privilege.”
In some rare cases, you may not have such privilege granted, or, if for example, you work in production database, you are not allowed create any new objects. In such circumstances, you can use a very elegant oracle feature, called Inline Procedure.
DECLARE PROCEDURE test AS BEGIN --any executable content END test; BEGIN NULL; END;
Inline procedures don’t require any privileges and don’t need to be executed inside of PL/SQL block so they can be used for validating the code as well as for code reuse.
For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.