Re: Update varchar2 column containing special character.

> 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!
>

put

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.

Tim

-- 
tcross (at) rapttech dot com dot au