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 for solutions — Thomas G
– Thomas G, Commented May 3, 2018 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
– Alex Poole, Commented May 3, 2018 at 13:27
I suggested the question title change to "Query using Oracle substitution variables". — Hilarion
– Hilarion, Commented May 3, 2018 at 13:55

Pelin · Accepted Answer · 2018-05-03 13:24:19Z

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 3, 2018 at 13:24

Pelin

9625 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hilarion Over a year ago

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).

David Faber · Accepted Answer · 2018-05-03 13:25:36Z

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 3, 2018 at 13:25

David Faber

12.5k2 gold badges33 silver badges41 bronze badges

1 Comment

Pasha Md Over a year ago

tried with the syntax , and it worked . thanks for the script .

Alex Poole · Accepted Answer · 2018-05-03 13:30:28Z

1

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.

answered May 3, 2018 at 13:30

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

1 Comment

Alex Poole Over a year ago

It worked when I tested it so not sure what to tell you. Depends what "did not work" means.

Collectives™ on Stack Overflow

Query using Oracle substitution variables

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related