Query using Oracle substitution variables

Question

I have a example SQL code like below

select *
  from Engg_Studens
 where Student_Name = '&Student_Name'
   And Department = 'computer science & Engineering' ;

My question here is: while running the SQL, it is asking for the substitution variable for '& engineering' which is in department name. I should only be asked for '&student_name'.

This happens because you have not properly escaped the `&` in `'computer science & Engineering'` and that it consider it as a bind variable. Read [this](https://stackoverflow.com/questions/12961215/escaping-ampersand-character-in-sql-string) for solutions — Thomas G, May 03 '18 at 13:24
Not really relevant to the question, but that is a substitution variable, not a bind parameter. They are different things. — Alex Poole, May 03 '18 at 13:27
I suggested the question title change to "Query using Oracle substitution variables". — Hilarion, May 03 '18 at 13:55

score 3 · Answer 1 · answered May 03 '18 at 13:24

3

You can not write a statement which includes the "&" character.

Try like below:

select * from Engg_Studens where Student_Name = '&Student_Name' 
and Department = 'computer science '|| chr(38) ||' Engineering' ;

answered May 03 '18 at 13:24

Pelin

936
5
12

Actually you can. You would either have to escape the character properly, change the setting for substitution variables character (to use a different character) or turn off the substitution variables at all (and use parameterized queries instead). – Hilarion May 03 '18 at 13:56

score 1 · Accepted Answer · answered May 03 '18 at 13:25

1

You can escape the second ampersand (&) but honestly I never remember how to do that off the top of my head. I usually do something like this:

SELECT *
  FROM engg_studens
 WHERE student_Name = '&Student_Name'
   AND department = 'Computer Science &' || ' Engineering';

Using the concatenation (||) operator here avoids the replacement variable.

answered May 03 '18 at 13:25

David Faber

12,277
2
29
40

tried with the syntax , and it worked . thanks for the script . – Pasha Md May 17 '18 at 06:36

score 1 · Answer 3 · answered May 03 '18 at 13:30

Another approach is to change the substitution character to something you are sure will not be used elsewhere:

SQL> set define ^

SQ> select *
  from Engg_Studens
 where Student_Name = '^Student_Name'
   And Department = 'computer science & Engineering'

Enter value for student_name: Arif
old   3:  where Student_Name = '^Student_Name'
new   3:  where Student_Name = 'Arif'

Only the ^Student_Name is now treated as a substitution variable so you only get a prompt for that.

You could also potentially separate the prompt from the query and switch to using an actual bind variable, but that seems like overkill here.

It worked when I tested it so not sure what to tell you. Depends what "did not work" means. — Alex Poole, May 17 '18 at 06:38

Query using Oracle substitution variables

3 Answers3