Insert statements in Oracle/SQL

Experts,

How can I insert values into a table via

Insert statements , when I have a description field having values like :
 P& G , J &J, M&N , J & Bros , G & Sons etc
(Basically having & in the Insert values )
Normally, SQL is asking for some value at &, because it is treating it as a bind variable

(Can I use some kind of escape characters and insert & )

Thanks a lot for the inputs
vsuripeddiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sujit_kumarCommented:
Use SET DEFINE OFF before the insert statement. See the example below,

SQL>select '&5' from dual;
Enter value for 5: err
old   1: select '&5' from dual
new   1: select 'err' from dual

'ER
---
err

SQL> set define off
SQL> select '&5' from dual;

'&
--
&5

SQL>

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anand_2000vCommented:
or
select '\&x'  from dual;
NarcisFCommented:
You can concatenate with with the result of the chr function.
Use select ascii('&') from dual; to find out the ascii code for &.
the in your sql statement use.
Eg: 'P' || chr(ascii_code) || 'G'

Good luck,
Narcis
DrSQLCommented:
vsuripeddi,
    You should be aware that this is only an issue for sql*plus.  The ampersand character has no meaning in SQL, so you don't have to worry unless sql*plus is compiling your source code (like when you compile a procedure, function, or package).  If you compile in OEM, there's no problem.  If you're selecting data that may have ampersands and then inserting that data into another table, that is also no problem.  If you're executing a package or procedure that has ampersands (and it isn't an anonymous pl/sql block), that isn't a problem.  Only when sql*plus evaluates a sql statement in its buffer and sees an ampersand IN ITS BUFFER is there an issue.

   Also, the "\" does NOT escape the ampersand.  SET DEFINE OFF is the approved solution.

Good luck!
DrSQL
anand_2000vCommented:
My bad,
DrSQL as always is correct. '\' can be used as escape for other characters like % and _ only....not &
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.