Re: inserting the value "&"

"Moritz Klein" <mklein_at_students.uni-mainz.de> wrote in message news:<aupctp$l4f$1_at_bambi.zdv.Uni-Mainz.DE>...
> Rauf Sarwar wrote:
>
> > Try this,
> >
> > INSERT INTO dirs
> > (dirName,
> > ip)
> > VALUES
> > ('/BALD RUNTER/Simon '|| CHR(38) ||' Garfunkel - The Definitive
> > (1994)/', '134.93.40.167');
> >
> > CHR(38) is the ASCII value of &.
>
> Thanks for your answer, but I managed to solve the problem by myself.
> Only thing I had to do was to:
> set escape '\';
> INSERT INTO dirs
> (dirName,
> ip)
> VALUES
> ('/BALD RUNTER/Simon \& Garfunkel - The Definitive
> (1994)/', '134.93.40.167');
>
> Regards Moritz

You can also change the prefix of the substition variable to something other than '&' using the SET DEFINE SQL*Plus command. To set prefix to tilde:

SQL> set define ~
SQL> select '1 & ~num' from dual;
Enter value for num: 2
old 1: select '1 & ~num' from dual
new 1: select '1 & 2' from dual

'1&2'