0

I am querying an Oracle database using SQL developer.

When searching a string that contains the ampersand in SQL, I currently have to run the command "set define off;" preceding the query in order for it to not be treated as a 'define' variable.

For example:

set define off;
select 'Dungeons&Dragons' from dual;
set define on;

Works as expected and the '&' is treated as a literal character. But I wonder if there was a way to do this by marking the string as a literal such as:

select l'Dungeons&Dragons' from dual;

Or something along those lines.

markmacw
  • 186
  • 2
  • 8
  • I think that's the best way to do it. You could use an escape character ('\'), e.g. 'Dungeons\&Dragons', but that doesn't seem worth the trouble. http://stackoverflow.com/questions/12961215/escaping-ampersand-character-in-sql-string – Vivek Chavda Jul 13 '16 at 20:43
  • 1
    For another alternative, see second option in answer: http://stackoverflow.com/a/118210/121544 – Shannon Severance Jul 13 '16 at 22:05
  • 1
    Possible duplicate of [How do I ignore ampersands in a SQL script running from SQL Plus?](http://stackoverflow.com/questions/118190/how-do-i-ignore-ampersands-in-a-sql-script-running-from-sql-plus) – Shannon Severance Jul 13 '16 at 22:06
  • 1
    @VivekChavda, Escaping like that does not work in SQL*Plus. – Shannon Severance Jul 13 '16 at 22:08

2 Answers2

0

Use a login.sql script with SET DEFINE OFF on sql developer startup. Go to Tools-Preferences-Database and on Filename for connection startup script select the new login.sql file.

vercelli
  • 4,717
  • 2
  • 13
  • 15
0

You can set the prompt character to a different character too:

SET DEFINE '^'

Then issue your query like normal.

Gary_W
  • 9,933
  • 1
  • 22
  • 40