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
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;
/

About these ads