Conditionally commenting out PL/SQL code in a SQL*Plus scriptMarch 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 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; /