Re: parameter substitution in SQL*PLUS

There are two ways:

(1) Change the "define" character to something besides "&". For example to change the define character to ":", run

       set define :

Then you may use "&". (To see all the values that can be set use "show all".)

(2) Use the chr function instead of "&". For example, since 38 = ascii('&'),

     select 'long ' || chr(38) || ' lean' from dual;

displays

     long & lean

mdavies_at_elekom.com wrote:
>
> I would like to execute an update statement as follows:
>
> update table1 set field1 = 'long & lean' where record_id = nn;
>
> where the literal string 'long & lean' contains the ampersand character
> (&) as a literal character. While there is nothing in SQL to dissallow
> this, the SQL*PLUS window environment keys on this character as a
> parameter substitution character and prompts me to enter a value.
>
> If I have this update statement in a script in a file, how can I escape
> the "&" character when submitting the script to the database thru
> SQL*PLUS?
>
> Thanks in advance for anyone's help on this.
>
> mdavies_at_elekom.com
> ELEKOM Corp. a developer of business to business commerce software.
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
 

-- 
Frank Hubeny            
fhubeny_at_ntsource.com            
Wheaton, IL 60187