Re: Update varchar2 column containing special character.

From: Tim X <>
Date: Wed, 18 Nov 2009 19:27:53 +1100
Message-ID: <>

trub3101 <> writes:

> Hi all,
> I am trying to update a 'You "&" Me' to 'You & Me' i.e.
> update table US
> set col1 = 'You & Me'
> where col1 = 'You "&" Me';
> The update works in TOAD but requests a value for Me in SQLPlus.
> The update is part of a shell script that calls SQLPlus so I would be
> grateful if someone could tell me how I can bypass SQLPlus requesting
> a value.
> I would also be more than grateful if someone could explain why I get
> the desired outcome in TOAD!


set define off

at the top of your script and then

set define &

at the end to restore it.

Not 100% sure why you don't get the behavior in TOAD, but I suspect that as this is a sqlplus feature, TOAD doesn't have that facility (or it is turned off by default). Keep in mind that sqlplus has some very handy/useful features for running scripts and generating reports that are specific to sqlplus i.e. not part of SQL or PL/SQL.

Many don't bother learning about sqlplus because it is seen as a little dated, but personally, I think its a good tool to know for the same reasons that vi is a good editor to know if you work on unix - its something you can almost always guarantee is available.


tcross (at) rapttech dot com dot au
Received on Wed Nov 18 2009 - 02:27:53 CST

Original text of this message