Look up the define parameter in SQL*Plus

i.e set define ~

Basically set your variable definition character to a value you know isn't
within your data !!

HTH

Mitchell Baldwin
Oracle Database Administrator
Financial Portal & Online Bank
Zurich Financial Services (UKISA) Ltd
http://www.zurichbank.co.uk





"Heather James"
<H.[Email Address Removed] To: "LazyDBA.com Discussion"
ive.co.uk> <[Email Address Removed]
cc:
30/09/02 16:25 Subject: Re: SELECT statement doesn't like "&"






Hello Gurus

I have a series of select statements in one script which I am using to
check data in two fields prior to running an update script.

However, when I run the select script if there is an "&" in the string then
it stops and asks me for the value of customer_name. If I hit return it
prints out the same company_name but misses out the "&" + the next part of
the string. If there is anything beyond "&" + the next part of the string
it is kept in the field .

e.g.

SELECT rep_id, customer_name FROM schema.customer_table WHERE customer_id
= '111155' AND customer_name = 'AAA BBB & Fixings Company Ltd';

UPDATE customer_table SET rep_id = 'LS.' WHERE schema,customer_id
= '111155' AND customer_name = 'AAA BBB & Fixings Company Ltd';

What happens when running the select scripts in SQL*PLUS:

Enter value for fixings:
old 1: SELECT rep_id, customer_name FROM schema.customer_table WHERE
customer_id = '111155' AND customer_name = 'AAA BBB & Fixings Company Ltd'
new 1: SELECT rep_id, customer_name FROM schema.customer_table WHERE
customer_id = '111155' AND customer_name = 'AAA BBB Company Ltd'

The script carries on okay until it hits a similar problem.

Can someone enlighten me about this and give some clues that I can follow
to solve the problem?.

Thank you.

Rgds,
Heather James

Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
Operating system: Windows 2000
This e-mail and any attached files are for the explicit use of the
addressee ONLY.
If you are not the intended recipient, any disclosure, reproduction,
copying, distribution
or any other dissemination or use of this communication is prohibited. If
you have received
this communication in error, please would you notify the sender either via
e-mail
or by telephoning 0117 9086500 and immediately delete this file.

E-mail transmission cannot be guaranteed to be secure or error free, as
information can be
intercepted, corrupted, lost, destroyed, arrive late or be incomplete, or
contain viruses. The sender
does not accept any liability for any error, omission or corruption which
arises from this e-mail
transmission.

--------
Oracle documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to oracledba-[Email Address Removed] subscribe: send a blank email to oracledba-[Email Address Removed] the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these terms:
http://www.lazydba.com/legal.html







___________________________________________________________________________

The information contained in this message is confidential and may be
legally privileged. If you are not the intended recipient, please do not
read, copy or otherwise use it and do not disclose it to anyone else.
Please notify the sender of the delivery error and then delete the
message from your system.

Any views or opinions expressed in this email are those of the author only.

Communications will be monitored regularly to improve our service and for
security and regulatory purposes.

Thank you for your assistance.

___________________________________________________________________________

Oracle LazyDBA home page