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

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:

[sourcecode language=”sql”]
— 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
WHERE 1=2
;
— To test this, simply comment out the where clause

set termout on

PROMPT &_if_you_want_this

DECLARE
v varchar2(20);
BEGIN
— SQL block A
SELECT ‘hello’ INTO v FROM dual;
dbms_output.put_line(v);

— SQL block B
&_if_you_want_this
SELECT ‘world’ INTO v FROM dual;
dbms_output.put_line(v);
— */
END;
/
[/sourcecode]

One thought on “Conditionally commenting out PL/SQL code in a SQL*Plus script

Leave a Reply

Your email address will not be published. Required fields are marked *