How to insert & character or special character into Database using SQL*Plus.


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.

Advertisements

20 thoughts on “How to insert & character or special character into Database using SQL*Plus.

  1. 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

    Like

  2. 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.

    Like

  3. 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

    Like

  4. well i came into this problem….
    SQL> INSERT INTO VALUES (‘let’s play ball’);

    how do you fix the ‘ character in let’s

    Like

  5. 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

    Like

  6. 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.

    Like

  7. 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.

    Like

  8. 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

    Like

Want to give some comment to author ( Shivmohan Purohit )

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s