An Entrepreneur, Coach, IT Consultant, Strategic Adviser, and a Traveler craving to explore and contribute to forming a better society.

Monday, February 8, 2010

Oracle - Single Quotes (') and Ampersands (&) in Insert Query

2 comments :

Oracle - Special Characters in SQL Query (& ampersand, ' single quote, etc.,)

Ever wonder how to skip special characters such as '&' and ' in oracle sql queries?

Problem #1:  
Insert Query in Oracle doesn't allow '&'

Reason: 
ampersand's (&) are treated as input variables. Example: if given insert into table values('&name'). It will

Solution: 
Use "SET DEFINE OFF" before executing such queries and use "SET DEFINE ON" after execution.

Example:
SQL> SET DEFINE OFF
SQL> INSERT INTO TABLE VALUES('&name');
SQL> SET DEFINE ON

------------------------

Problem #2:  
Insert Query values in Oracle doesn't skip single quotes (')

Reason: 
Single quotes are not skipped by default in oracle as values are enclosed by single quotes ('). 
Example: insert into table values('Sister's Kitchen')

Solution: 
Use another single quote in front of such values to skip (')

Example:
SQL> INSERT INTO TABLE VALUES('Sister''s Kitchen');

2 comments :

Krishna said...

Great article, and address the issues to the point. Thanks !

Krishna said...

Great blog, and address the issues to the point. Thanks !