Annoying “Enter Substitution Variable” Dialog Box

Introduction

Have you ever noticed when running PL/SQL scripts in Oracle’s SQL Developer that an annoying “Enter Substitution Variable” dialog pops up, and you have no idea why? I’ve had this happen numerous times and have been meaning to write an article on it to explain how to resolve the issue.

The Problem

I noticed recently it could be easily duplicated in a WHERE clause with an AND operator in which I have comments that have an ampersand in the comments. This is a sample of the code I had that produced the problem:

AND B.SGBSTDN_MAJR_CODE_1 IN (
   'LANL', -- Liberal Arts: Natural & Life Science
   'LANP' -- Liberal Arts: Natural & Physical Science
)

Notice in the above code the “& Life” and also “& Physical”. When I execute this in SQL Developer, I get this pop up dialog:

So the “Enter Substitution Variable” is prompting for a value for “Life”.

Workaround

A quick workaround is to simply remove the ampersand (&) symbols. For example, the following code:

AND B.SGBSTDN_MAJR_CODE_1 IN (
   'LANL', -- Liberal Arts: Natural and Life Science
   'LANP' -- Liberal Arts: Natural and Physical Science
)

Better Fix

An even better fix is to run the following code in SQL Developer:

SET DEFINE OFF;

Once you run that, you won’t ever see the “Enter Substitution Variable” again!

So you can decide. Hopefully, this helps someone who has run into the problem before.