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:
Why not just use SET DEFINE OFF
so SQL*Plus ignores ampersands?
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.
"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.
s/\&[^$]/\&\'\|\|\'/g
2 years later, a slightly better regular expression which will not put '||' if the & is already at the end of the string.
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.
set define off
in lower case !!!!
After 4 years this tip by anonymous was still useful.
its pretty cool!
Thank you, anonymous.
ahhh set define off....
thanks!
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 :-)
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.
NIce post
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.
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.
Post a Comment