Substitution Variable

Substitution Variable is nothing but a request to input value and write that in a SQL Statement. This can be best explained using the example. The query below request the value for employee name when running the script.

SELECT * 
FROM   employee 
WHERE  employee_name = '&dummy'; 

Isn't this cool huh! but make a note ampersand (&) creates problem with INSERT. If you are inserting a string that contains ampersand sign, the SOL developer opens a window and asks you to enter the value for that field. Why? because Oracle consider ampersand sign on a data value as a substitution variable. You are actually trying to insert a string with & sign not a substitution variable. You will need to disable this feature to complete the insert statement. Let's see how you can disable the substitution variable and make the insert work.

Problem Insert Statement:
Running this query under SQL Developer will ask you insert a value for a substitution variable.

INSERT INTO team_huskers 
            (id, 
             team_name) 
VALUES      ( 55, 
              'Red & White') 

The query will open a new window asking you to input the value.

Solution:

SET define OFF; 
INSERT INTO team_huskers 
            (id, 
             team_name) 
VALUES      ( 55, 
              'Red & White') 
COMMIT; 

Set Define OFF resolves the problem, by default this setting is ON.