From Jacques.Kilchoer@quest.com Tue, 06 Mar 2001 13:39:49 -0800 From: Jacques Kilchoer Date: Tue, 06 Mar 2001 13:39:49 -0800 Subject: RE: Insert & Message-ID: MIME-Version: 1.0 Content-Type: text/plain Title: RE: Insert & > -----Original Message----- > From: Krishna Rao K [mailto:master_dba@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