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

sql database oracle oracle-sqldeveloper
Rachel picture Rachel · Nov 14, 2010 · Viewed 273.5k times · Source

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?

Answer

Justin Cave picture Justin Cave · Nov 14, 2010

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.

Related questions

copy from one database to another using oracle sql developer - connection failed

I am trying to copy a table from one database to another using oracle sql developer. I have the username, password and SIDs. copy from uname1/password1@SID1 to uname2/pwd2@SID2 insert table1 (*) using (select * from message_table); However …

Read More
Execute Statement or Run Script?

While entering a SQL statement in Oracle SQL Developer, I noticed that I have two choices. I can either "Run Statement" or "Run Script". A similar choice seems to be available in SQL Maestro as well, although named "Execute query" …

Read More
Oracle SQL Developer copy database step by step

I'm having big trouble in copying an Oracle DB to the same server but with another name, to use as a development DB. I'm used to SQL Server, I'm new to Oracle (11g). I wanted to use the 'Database copy' …

Read More