May 26, 2005

Oracle sqlplus "&" issue

I ran into an interesting issue today with sqlplus for Oracle 10g. I was trying to insert a row using a statement like:

INSERT INTO tablex (SomeVarcharField) VALUES ('Cats & Dogs');

When the script ran I got a message like this:

"enter a value for dogs:"

When I press enter the value that was inserted was 'Cats '.

WIERD!!

I found out that sqlplus treats ampersands as special escape characters and that the only way it will accept it is if it is the last character in the string. So the work around is to end the string at the ampersand and concatenate the rest of the string. For example: 'Cats &'||' Dogs'

That worked. In my case I had a bunch of INSERT statements in a SQL file and I didn't want to change each one individually so I asked some of my coworkers what I could do. They suggested a nifty command (using SED) to do the trick:

sed -i.bak "s/\&/\&\'\|\|\'/g" MyFileOfInserts.sql

This creates a file called MyFileOfInserts.sql.bak with my old data and then replaces & with &'||' . That nasty stuff in the middle is a regular expression. You gotta LUV IT!

15 comments:

Anonymous said...

Why not just use SET DEFINE OFF
so SQL*Plus ignores ampersands?

Mike Farmer said...

Excellent! That is much easier. Thanks for the comment. The solution that I posted was from an Oracle web site. I wish they would have provided more information on the SET DEFINE option.

I guess this is where my noviceness (if that is a word) comes in.

Thanks again.

Anonymous said...

"SET DEFINE OFF" is the coolest thing to happen to me after a long long time.. Mr. Anonymous, you are such a messiah. I take a bow.

Anonymous said...

s/\&[^$]/\&\'\|\|\'/g

2 years later, a slightly better regular expression which will not put '||' if the & is already at the end of the string.

Anonymous said...

PS: Obviously you could still use "set define off", but as for me, I need to use the ampersands as a special character in other parts of my query.

Anonymous said...

set define off

in lower case !!!!

Anonymous said...

After 4 years this tip by anonymous was still useful.

Suresh Lakshmanan said...

its pretty cool!

Unknown said...

Thank you, anonymous.

Anonymous said...

ahhh set define off....

thanks!

Anonymous said...

I struggled with &-ampersand acting wierdly. This post was a god sent, thanks to "Anonymous" for answering. Mike - Thanks tio you for asking the questions, that others are embarassed to ask. You are right there with Anonymous, in my list fo rtoday :-)

Anonymous said...

Use SET ESCAPE to prevent isolated occurrences of "&" from being treated as the substitution variable prefix:

SQL> set escape \
SQL> select 'B\&W' MyHeading from dual;

MYH

---
B&W

Any "&" without the escape character is treated as a variable prefix.

See SET ESCAPE in the SQL*Plus User's Guide and Reference.

Anonymous said...

NIce post

sap support pack installation guide said...

I did also faced this thing few years back when I started working on Oracle sqlplus but have solved this problem by executing a simple query i.e set define off. This has solved my problem and hope you do also have got a solution to this.

sap support pack installation guide said...

I did also faced this thing few years back when I started working on Oracle sqlplus but have solved this problem by executing a simple query i.e set define off. This has solved my problem and hope you do also have got a solution to this.