How to enter special characters like "&" in oracle database?

Question

I want to insert special character & in my insert statement. My insert is:

INSERT INTO STUDENT(name, class_id) VALUES ('Samantha', 'Java_22 & Oracle_14');

If I try to run this query I am getting a popup and it asks me to enter value for Oracle_14.

How can I enter special characters like & in the insert statement for oracle db?

Solution 1

If you are in SQL*Plus or SQL Developer, you want to run

SQL> set define off;

before executing the SQL statement. That turns off the checking for substitution variables.

SET directives like this are instructions for the client tool (SQL*Plus or SQL Developer). They have session scope, so you would have to issue the directive every time you connect (you can put the directive in your client machine's glogin.sql if you want to change the default to have DEFINE set to OFF). There is no risk that you would impact any other user or session in the database.

Solution 2

Try 'Java_22 '||'&'||' Oracle_14'

Solution 3

Justin's answer is the way to go, but also as an FYI you can use the chr() function with the ascii value of the character you want to insert. For this example it would be:

INSERT INTO STUDENT(name, class_id) VALUES ('Samantha', 'Java_22 '||chr(38)||' Oracle_14'); 

Solution 4

you can simply escape & by following a dot. try this:

INSERT INTO STUDENT(name, class_id) VALUES ('Samantha', 'Java_22 &. Oracle_14');

Solution 5

To Insert values which has got '&' in it. Use the folloiwng code.

Set define off;

Begin

INSERT INTO STUDENT(name, class_id) VALUES ('Samantha', 'Java_22 & Oracle_14');

End ;

And Press F5 from Oracle or Toad Editors.

View more solutions

Comments

[OMG Ponies]

17 seconds... I was so close

[Rachel]

so will this only turn defination off for my sql or for the complete table or complete database also can i have something like this in production insert script ?

[Andrew Barber]

IMO, your production code should be using substitution, or at minimum, handling it itself. I believe that code does the setting for your login session, though.

[Justin Cave]

@Rachel - SET commands are client directives that have session scope. The database has no idea what directives, if any, you've given your client so it has no impact on a table or on any other database session.

[Justin Cave]

@OMG Ponies- Sweet, sweet vengeance for all the times you've beaten me :-)

[Rachel]

@Justin: but in that case, lets say if am running the insert script with set directive off in production than will it be only for one insert or for the complete list of inserts for that particular session, can explain a little more on this, am only concern is should i have set directive off in the insert script which will be running on production ?

[Rachel]

your first approach does not work.

[Rachel]

It prints bracket too for second approach and \& for first approach.

[Justin Cave]

@Rachel - It's perfectly fine to SET DEFINE OFF in a production script, yes. It will affect all the other statements running in the same session (you could, of course, SET DEFINE ON later in the script if you wanted to). But it would generally be unusual to have substitution variables in a script running in production-- you normally don't want production scripts to prompt the user for information, you normally want that to be part of the script.

[Rachel]

@Justin: Thanks Justin for Clarification.

[Pubby]

Anonymous user says: Remark: this is originally instructed in Oracle documentation but not working in client like SQL Developer.

[Alex Poole]

Thats's what EternalNoob said, and close to what Craig said, when the question was first asked.

[Teju MB]

But this will insert "." as well. Any thoughts?

[TroniPM]

chr(38) saved my day. Thanks

[Stamatis Tiniakos]

Not sure why this is not the most upvoted answer.