How to check if an anonymous PL/SQL block will compile without trying to execute it, by Zahar Hilkevich

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s