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
,0, ‘/* no thanks’
,1, ‘– yes please’
— To test this, simply comment out the where clause
set termout on
— SQL block A
SELECT ‘hello’ INTO v FROM dual;
— SQL block B
SELECT ‘world’ INTO v FROM dual;