RE: Insert &

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 06 Mar 2001 13:39:49 -0800
Message-ID: <F001.002C5250.20010306134058@fatcity.com>

> -----Original Message-----
> From: Krishna Rao K [mailto:master_dba_at_hotmail.com]
> Sent: mardi, 6. mars 2001 12:07
>
> What is the best way to Insert when we have '&' as part of
> our string. I
> want to frame INSERT statements such as the one below, and
> pass on to the
> user.
>
> insert_customers.sql:
>
> INSERT INTO customers (customer_name) VALUES ('Sun Microsystems');
> INSERT INTO customers (customer_name) VALUES ('HP');
> INSERT INTO customers (customer_name) VALUES ('AT & T');
> bla bla bla
>
> This script asks for Input and does not Insert correctly.

Several options:

a) do the inserts from inside a program, not using SQL*Plus
b) turn off the "define" character: SQL> SET DEFINE OFF
c) change the "define" character to something else: SQL> SET DEFINE '~'
d) in case you need to keep '&' as the "define" character:

insert into customers (customer_name) values ('AT ' || chr (38) || ' T') ; to find out what number will be converted to '&' by the chr function, you can use the following statements in SQL*Plus
SQL> SET DEFINE OFF
SQL> select dump ('&') from dual ;
Though I'm pretty sure that you will always get 38.

---

any ignorant comments made are the sole responsibility of J. R. Kilchoer and should not reflect adversely upon my employer.

 
Jacques R. Kilchoer
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com Received on Tue Mar 06 2001 - 15:39:49 CST