MattsBits
MattsBits

MattsBits

Oracle SQL Developer SQL Worksheet And Ampersands  

by Matt Hawkins, 30/04/2009
Categories : Oracle

If you attempt to run SQL queries in a SQL worksheet and that query includes ampersands then SQL Developer will prompt you for bind variable values. It assumes anything starting with an ampersand must be a bind variable.

For example the query below :

SELECT * FROM TBL_COLOURS WHERE colour = 'Red&Blue';


would result in a pop-up window asking for a value for "&Blue".

If you are running a few thousand update queries containing lots of text it is very annoying.

There are 2 ways of avoiding it. Either replace all occurances of & with ' || '&' || '

SELECT * FROM TBL_COLOURS WHERE colour = 'Red' || '&' || 'Blue';


Or use the SQL Plus command :

SET DEFINE OFF;

Author : Matt Hawkins  Last Edit By : Matt Hawkins
PHP Powered  MySQL Powered  Valid XHTML 1.0  Valid CSS  Firefox - Take Back The Web  EUKHost - Recommended Webhosting Solutions

MattHawkins CMS v3.0 - Copyright 2009-2017