1

Can we use ampersand in alias names which we use in Oracle select statement. any work around to get this working?

select ename||','||job as name&job from employees ;

ps: I am using plsqldeveloper IDE.

Prashant Mishra
  • 619
  • 9
  • 25

1 Answers1

3

It's entirely possible - you have to put the identifier in quotes, and you also have to turn the substitution prompting off - either by selecting the relevant option in whichever IDE you're using, or by using set define off. E.g.:

set define off;

select dummy "JOB&NAME" from dual;

JOB&NAME
--------
X

Don't forget to set define back on afterwards, if this is part of a script.

Boneist
  • 22,910
  • 1
  • 25
  • 40
  • Don't forget to educate the table designers about why using an ampersand in an object name is a bad idea! – Gary_W Jul 29 '15 at 14:49
  • 1
    I'm guessing that this is required for some sort of report output. I certainly wouldn't encourage its use as a permanent identifier (eg. a column name)! Ugh, I've got a headache just thinking about all the double-quotes needed in subsequent queries... *{;-) – Boneist Jul 29 '15 at 15:01
  • 1
    Not to mention the Case sensitivity! – bob dylan Jul 29 '15 at 15:22