Conditionally commenting out PL/SQL code in a SQL*Plus script

March 20, 2012 at 1:35 pm | Posted in Oracle Developement | 1 Comment

If you need to create a SQL*Plus script that contains PL/SQL code that needs to executed only when certain conditions exist, you can use the following trick.

In this example I want SQL Block B, only to be executed when the SELECT statement on the dual table returns a row.  To do this I select a text string containing the beginning comment marker “/*” into a substitution variable. Then I place the variable before the code I want to conditionally comment out. At the end of the conditional code I place the ending comment marker “*/” preceded by a line comment marker ‘–‘. This way, the ending comment marker is only effective when the beginning comment marker exists.

BTW: This is somewhat similar to an earlier post where I describe how to do conditional branching in SQL*Plus.

Give it a try, and copy this code into a SQL*Plus script:

-- SQL*Plus script
-- Script Name: test_condition.sql
-- Author     : Christoph Ruepprich
-- Purpose    : To demonstrate how to dynamically comment out PL/SQL code
--              in a SQL*Plus script.
-- Usage      : To toggle between the second SQL block being executed,
--              comment out the WHERE clause from the first SQL statment on the
--              DUAL table. If that query returns zero (0), SQL block B
--              will not get executed.

set echo off termout off verify off

col test new_value _if_you_want_this

SELECT decode(count(*)
             ,0, '/* no thanks'
             ,1, '-- yes please'
             ) test
FROM dual
-- To test this, simply comment out the where clause

set termout on

PROMPT &_if_you_want_this

  v varchar2(20);
  -- SQL block A
  SELECT 'hello' INTO v FROM dual;

  -- SQL block B
  SELECT 'world' INTO v FROM dual;
  -- */

1 Comment »

RSS feed for comments on this post. TrackBack URI

  1. Great idea!

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

Entries and comments feeds.

%d bloggers like this: