asked on

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

ASKER CERTIFIED SOLUTION

sujit_kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anand_2000v

or
select '\&x' from dual;

NarcisF

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

DrSQL - Scott Anderson

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_2000v

My bad,
DrSQL as always is correct. '\' can be used as escape for other characters like % and _ only....not &