Re: Update varchar2 column containing special character.
Date: Wed, 18 Nov 2009 19:27:53 +1100
trub3101 <trub3101_at_sky.com> 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 auReceived on Wed Nov 18 2009 - 02:27:53 CST