Solution 1:
If you are not using substitution variables (&1 &2 &3 etc.) you can do a “SET DEFINE OFF” or “SET SCAN OFF” to turn off the definition of the ampersand as a substitution variable.
Example:
SQL> SET DEFINE OFF
SQL> INSERT INTO <table_name> VALUES (‘AT&T’);
1 row created
Solution 2:
If you are using substitution variables, set the substitution character to one which will not be encountered when inserting data.
Example:
SQL> SET DEFINE %
SQL> INSERT INTO <temp_table> VALUES (‘AT&T’)
/
1 row created.
Solution 3:
If you precede the special character with a backslash and the ESCAPE setting in SQL*Plus is set to ON, then SQL*Plus will understand the special character following the escape symbol is to be treated as a regular character.
Example:
SQL>set escape on
SQL>show escape
escape “\” (hex 5c)
SQL> INSERT INTO temp_table VALUES (‘select * from emp where ename = \&
1′);
1 row created.
Know-Oracle 
I want to insert £ in my VARCHAR column. HOw can i insert because after running my procedure value is getting converted into £18000
LikeLike
how to insert query use special character ???????????
LikeLike
I don’t know whether it’s just me or if perhaps
everybody else experiencing issues with your blog.
It seems like some of the written text in your posts are running off the screen.
Can somebody else please comment and let me know if this is happening
to them too? This might be a problem with my browser because I’ve had this happen before. Thanks
LikeLike
I am using XMLElement to display the insert string into table column.
Here I am not able give the ‘ (single coat) to declare variables. Please see my code below:
SELECT UOM_DESC INTO uomdesc FROM R5UOMS_TEST WHERE UOM_CODE = :new.PRIMARYKEYVALUE;
select UOM_CLASS into uomclass from R5UOMS_TEST WHERE UOM_CODE = :new.PRIMARYKEYVALUE;
select UOM_NOTUSED into uomnotused from R5UOMS_TEST WHERE UOM_CODE = :new.PRIMARYKEYVALUE;
select UOM_CLASS_ORG into uomclassorg from R5UOMS_TEST WHERE UOM_CODE = :new.PRIMARYKEYVALUE;
select UOM_UPDATECOUNT into uomupdatecount from R5UOMS_TEST WHERE UOM_CODE = :new.PRIMARYKEYVALUE;
select UOM_CREATED into uomcreate from R5UOMS_TEST WHERE UOM_CODE = :new.PRIMARYKEYVALUE;
select UOM_UPDATED into uomupdated from R5UOMS_TEST WHERE UOM_CODE = :new.PRIMARYKEYVALUE;
select UOM_SOAUOM into uomsoauom from R5UOMS_TEST WHERE UOM_CODE = :new.PRIMARYKEYVALUE;
— concat = uomdesc || uomclass || uomnotused || uomclassorg || uomupdatecount || uomcreate || uomupdated || uomsoauom;
if :new.Action = ‘INSERT’ THEN
INSERT INTO cmtranslines (TRANSID,SCRIPT) VALUES ( :new.TRANSACTIONID,
xmlelement(“script”,’insert into R5UOMS (UOM_CODE,UOM_DESC,UOM_CLASS,UOM_NOTUSED,UOM_CLASS_ORG,UOM_UPDATECOUNT,UOM_CREATED,UOM_UPDATED,UOM_SOAUOM)
VALUES(‘, :new.PRIMARYKEYVALUE||’,’||||”uomdesc||’, ‘||uomclass||’, ‘||uomnotused||’, ‘||uomclassorg||’, ‘||uomupdatecount||’, ‘||uomcreate||’, ‘||uomupdated||’, ‘||uomsoauom));
Hear I need to add single code for all variables. Please help on the same.
LikeLike
i want to insert 12′-10″ (means 12 feet – 10 inch),
how should i insert?
LikeLike
i want insert 12′-10″ (12 feet – 10 inch),
please tell me how should i insert …!!!
LikeLike
Running sql with SET DEFINE OFF
hope it will help You..!
LikeLike
Nice tip but SET SCAN OFF is obsolete now. Use SET DEFINE OFF.
LikeLike
Awsome!! tooooo good!!
thanks
LikeLike
Above methods are not working if we are giving some script for this and running this script from cmd sql prompt. provide us any solution for this…
begin
/* Formatted on 2010/08/27 13:28 (Formatter Plus v4.8.8) */
SET DEFINE OFF;
UPDATE ms_sox_certification_type
SET certification_type = ‘Policy & Procedure’
WHERE certification_type_id = 6 AND area_of_compliance_id = 5;
end;
/
SQL> @update_script.sql
Enter value for procedure:
old 5: SET certification_typ
new 5: SET certification_typ
SET DEFINE%;
*
ERROR at line 3:
ORA-06550: line 3, column 5:
PL/SQL: ORA-00922: missing or inv
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored
LikeLike
well i came into this problem….
SQL> INSERT INTO VALUES (‘let’s play ball’);
how do you fix the ‘ character in let’s
LikeLike
sql>insert into values(‘let”s play ball’);
this will 100% work.
LikeLike
you can insert it as ‘india”s houses’
Two times single quoates
But if u r using JDBC use Parameterized query.
Which will never create any problem and u can inser any kind of a data
LikeLike
how can i insert ‘india’s houses’ field in database field defined as varchar2(20).please note that the problem is to inset the ‘ special character.
LikeLike
Another way is to add them using the numeric code and chr(). Back in 1986 (when sqlplus was all we had) I’d insert VT[12]00 escape codes this way for fonts and cursor positioning into the database, wrap them around field values to get color “forms”. This works for all sorts of evil things like control characters as well as “printable” ones.
But yes, I /did/ make extensive use of set scan off when I might be getting something unusual from a user input via a parameter or ACCEPT.
I don’t think we really had SET ESCAPE then, but then, you couldn’t trust users not to use /some/ metacharacter.
LikeLike
very nice post, very useful to me
LikeLike
Thank You for the post. I am new bee to Oracle and had trouble inserting special character from SQL plus. This post was very helpful
LikeLike
very nice post, I encountered the same problem, but i did that with the || sign. But this post is no doubt is nice one
LikeLike