Skip to Main Content
  • Questions
  • Inserting values into a table with '&'

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Vivek.

Asked: March 15, 2012 - 9:50 am UTC

Answered by: Tom Kyte - Last updated: October 09, 2019 - 10:33 am UTC

Category: Database - Version: 11.1.0

Viewed 100K+ times! This question is

You Asked

Hi,

I want to insert a values into a table as follows:

create table test (name varchar2(35));
insert into test values ('&Vivek');

I tried the escape character '\' but the system asks for a value of the substitution variable.

I also did a "set define off" but then also the system asks for a value in a window.

I use Toad for Oracle 10" to interact with the database.

Please help.

and we said...

Please ask the makers of toad how to turn off substitution variables in their product.

What you did would work for our tool sqlplus - but not for someone else's tool called 'Toad' apparently.


and you rated our response

  (23 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Just execute as a script

March 15, 2012 - 10:51 am UTC

Reviewer: G

set define off
create table test (name varchar2(35));
insert into test values ('&Vivek');


press 'F5' to execute the above commands.

the q' operator

March 15, 2012 - 10:53 am UTC

Reviewer: asktom fan from Chicago

Hi Tom,

After reading the question above, I thought the q' operator might be able to escape character & in sqlplus without using "set define off". Obviously, it does not:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> set null null
SQL> col lets_see for a10
SQL> select q'<&test>' lets_see from dual;
Enter value for test: 
old   1: select q'<&test>' lets_see from dual
new   1: select q'<>' lets_see from dual

LETS_SEE
----------
null


So q' is not magical enough to escape everything? :)


Tom Kyte

Followup  

March 15, 2012 - 1:51 pm UTC

It isn't a "sql" problem.

SQLPlus, a client program, by default looks through all text for & and does the substitution way before the SQL layer gets it. q' is a SQL think, & is a sqlplus - the client tool - thing.

Inserting values with '&'

March 16, 2012 - 1:12 am UTC

Reviewer: Vivek popat from India

set define off;
create table test (name varchar2(35));
insert into test values ('&Vivek');

I pressed the F5 key to execute the above statements as a script and it worked.

Thank You all for help.

optionally you could try this

March 18, 2012 - 10:08 pm UTC

Reviewer: Barry Chase from Louisville, KY

Optionally you could replace your ampersands with CHR(38) which is the equivalent. There are loss of ways to handle in the end.
Tom Kyte

Followup  

March 19, 2012 - 10:11 am UTC

it is not that simple, you have to not only replace them but add concatenation

insert into test values (chr(38)||'Vivek');

Concatenation works

March 20, 2012 - 3:26 am UTC

Reviewer: AndyP from UK

And, if you are going the concatenation route, you can just
insert into table1 values('&'||'hello world');

1 row created.

select * from table1;

COL1
--------------------------------------------------
&hello world


Tom Kyte

Followup  

March 20, 2012 - 9:39 am UTC

nifty, i learned something new again today!

Another way

March 21, 2012 - 7:12 am UTC

Reviewer: Valério from Brazil

Hi,

Another way that worked for me, incluindg in SQL Developer, was the use of '"' around the '&'. Example:

insert into test values ('"&"Vivek');

Thanks
Tom Kyte

Followup  

March 21, 2012 - 10:21 am UTC

but that adds the double quotes to it

user1%ORA10GR2> select '"&"Hello world' from dual;

'"&"HELLOWORLD
--------------
"&"Hello world

user1%ORA10GR2>

This may be cleaner

March 21, 2012 - 10:45 am UTC

Reviewer: Tony Fernandez from Atlanta, GA

as this may not imply changing, but only 1 line, if in large imports:

just adding SET SCAN OFF; before the operation.

SQL> create table test2(x varchar2(100));

Table created.

SQL> insert into test2(x) values('&hello world');
Enter value for hello: 
old   1: insert into test2(x) values('&hello world')
new   1: insert into test2(x) values(' world')

1 row created.

SQL> rollback;

Rollback complete.

SQL> SET SCAN OFF;
SQL> insert into test2(x) values('&hello world');

1 row created.

SQL> commit;

Commit complete.
SQL> select * from test2;

X
---------------------------------------

&hello world

Tom Kyte

Followup  

March 21, 2012 - 10:56 am UTC

or, set define off

scan is deprecated
http://docs.oracle.com/cd/E11882_01/server.112/e16604/apc.htm#SQPUG142


This may be cleaner

March 21, 2012 - 11:03 am UTC

Reviewer: Tony Fernandez from Atlanta, GA

Thanks for the update Tom. I have been using SCAN for too long. Will now change into SET DEFINE OFF;

hi tom, can u help me in this

May 27, 2012 - 8:56 am UTC

Reviewer: Viknesh from India

how come i insert record using returning into clause with bulk collect.


SQL> declare
  2  type t1 is table of employees.employee_id%type;
  3  type t2 is table of employees.last_name%type;
  4  empno t1;
  5  empname t2;
  6  begin
  7  insert into tab select * from employees where employee_id between 100 and 200
  8  returning employee_id,first_name  bulk collect into empno,empname;
  9  for i in 1..empno.count loop
 10  dbms_output.put_line('Deleted items are  '||empno(i)||'  '||empname(i));
 11  end loop;
 12  end;
 13  /
returning employee_id,first_name  bulk collect into empno,empname;
*
ERROR at line 8:
ORA-06550: line 7, column 79:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 7, column 1:
PL/SQL: SQL Statement ignored

Tom Kyte

Followup  

May 27, 2012 - 12:40 pm UTC

who is "u"? I don't know them so I cannot get them to help you. sorry :(

documentation might be useful thought:

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9014.htm#i2121671

according to it, the only time returning is valid with insert it when you use VALUES

Thanks

May 27, 2012 - 1:37 pm UTC

Reviewer: Viknesh from Pondichery,India

Hi tom am a new user and my name is viknesh and i keep refer your blog for solutions.

Thanks anyway i tried using VALUES in INSERT statement and got inserted successfully. and now my question is can use sub query to insert more than one row using returning into clause?
If possible give some example..

Thanks Viknesh
Tom Kyte

Followup  

May 28, 2012 - 2:17 am UTC

you can only do this with values - not with the subquery.

You can do something like:

select a, b, c, d, ... bulk collect into some_array
  from employees where employee_id between 100 and 200;

forall i in 1 .. some_array.count
  insert into t values some_array(i);


Thanks

May 28, 2012 - 3:52 pm UTC

Reviewer: Viknesh from India

Thank for your help tom :)

Please help me in this

May 31, 2012 - 4:46 am UTC

Reviewer: Vikky from Pondichery,India

hi tom,

This is viknesh from pondichery India. can u give me the query to find the employee whose salary is less than the average salary of the specific department.
and i want to display all the departments and employees getting less than avg salary of the their department.
Tom Kyte

Followup  

May 31, 2012 - 10:17 am UTC

I smell homework :)


How about this, I'll lead you along....


can you write a query to get the average salary in a given department? It is pretty easy right?

select avg(sal) from emp where deptno = ?


Now, if you wanted to get employees whose salary was less than something you would write:

select * from emp where salary < "something"


So, can you see how to plug in that first query into the second query?


Although, if your question is truly:

"all the departments"



then we have to do a bit more work, is that really a requirement (i doubt it, since this is homework and the correlate subquery you are supposed to be learning about is complex enough without throwing in an outer join)

The mutating thread!!

May 31, 2012 - 1:26 pm UTC

Reviewer: Greg from Toronto

Hey Tom,

I'm reading this thread, it's about one topic.
It then suddenly changes to a completely unrelated topic!
How do I fix it??
;)

Viknesh

May 31, 2012 - 11:54 pm UTC

Reviewer: Viknesh from Pondichery,India

Hi Tom,

I found the solution for my previous question. and i framed a query like this.

select e1.department_id,e1.salary from employees e1,(select department_id,trunc(avg(salary))as salary from employees
group by department_id)e2
where
e1.salary<e2.salary and
e1.department_id=e2.department_id
order by 1,2


So my question is is there any better idea to write query more efficient than this??
Tom Kyte

Followup  

June 01, 2012 - 6:38 am UTC

that is definitely one way. here are others:


ops$tkyte%ORA11GR2> select e1.deptno,e1.ename, e1.sal
  2    from emp e1,(select deptno,trunc(avg(sal))as sal
  3                   from emp
  4                  group by deptno) e2
  5   where e1.sal<e2.sal
  6     and e1.deptno=e2.deptno
  7   order by 1,2
  8  /

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450
        10 MILLER           1300
        20 ADAMS            1100
        20 SMITH             800
        30 JAMES             950
        30 MARTIN           1250
        30 TURNER           1500
        30 WARD             1250

8 rows selected.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select deptno, ename, sal
  2    from (select deptno, ename, sal, trunc(avg(sal) over (partition by deptno)) avg_sal
  3          from emp)
  4   where sal <avg_sal
  5   order by 1, 2
  6  /

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450
        10 MILLER           1300
        20 ADAMS            1100
        20 SMITH             800
        30 JAMES             950
        30 MARTIN           1250
        30 TURNER           1500
        30 WARD             1250

8 rows selected.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select deptno, ename, sal
  2    from emp e1
  3   where sal < (select trunc(avg(sal))
  4                  from emp e2
  5                 where e2.deptno = e1.deptno)
  6   order by 1, 2
  7  /

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450
        10 MILLER           1300
        20 ADAMS            1100
        20 SMITH             800
        30 JAMES             950
        30 MARTIN           1250
        30 TURNER           1500
        30 WARD             1250

8 rows selected.

Thank you very much tom! much useful! :)

June 01, 2012 - 10:06 am UTC

Reviewer: Viknesh from Pondichery,India

Thanks alot tom! much useful! :)

To Greg from Toronto

June 01, 2012 - 8:23 pm UTC

Reviewer: Autonomous from Australia

You can fix a mutating thread using an autonomous poster but the result may not be what you expect
Tom Kyte

Followup  

June 02, 2012 - 2:49 am UTC

;) it took me a minute to get this - very funny....

offtopic

June 04, 2012 - 7:26 am UTC

Reviewer: Sokrates

here another way to insert values into a table with '&':
sql > create table "table_with_&" ( i int);

Table created.

sql >
sql > insert into "table_with_&" values(0);

1 row created.

Tom Kyte

Followup  

June 04, 2012 - 9:17 am UTC

that doesn't show how to insert an ampersand into a table though?

that shows that & is ignored when sql plus realizes it is in a quoted identifier


it is close to this:

ops$tkyte%ORA11GR2> select 'hello world &' from dual;

'HELLOWORLD&'
-------------
hello world &

ops$tkyte%ORA11GR2> select 'hello world &x' from dual;
Enter value for x: xxx
old   1: select 'hello world &x' from dual
new   1: select 'hello world xxx' from dual

'HELLOWORLDXXX'
---------------
hello world xxx




which was demonstrated (when the & is followed by ', sqlplus doesn't do the substitution)


&' and &" apparently are the same, if you used &something" it wouldn't work:


ops$tkyte%ORA11GR2> select * from "DUAL&X"
  2  /
Enter value for x: 
old   1: select * from "DUAL&X"
new   1: select * from "DUAL"

D
-
X

ops$tkyte%ORA11GR2> 

September 11, 2012 - 6:34 am UTC

Reviewer: A reader


Barry's solution works

November 10, 2015 - 3:52 pm UTC

Reviewer: Jack from Boston

UPDATE <tablename>
SET ACAD_GROUP = 'A' || CHR(38) || 'S'
WHERE ...

Sets the column to 'A&S'

Use the UNISTR() function

September 20, 2016 - 3:24 pm UTC

Reviewer: Thomas Bender from United Kingdom

You can use the UNISTR() function:

INSERT INTO test (name) VALUES (UNISTR ('\0026Vivek'));


... and if you can stop lazy programmers from writing "&" instead of "and" in comments ("-- close crsr & ret.") your scripts will never have issues with client-side substitution again.

print prime numbers

August 05, 2019 - 7:39 am UTC

Reviewer: Monika soni from india

hi i want to print all prime numbers less than or equal to . Print your result on a single line, and use the ampersand () character as your separator (instead of a space).
Connor McDonald

Followup  

August 05, 2019 - 9:28 am UTC

This sounds a *lot* like homework to me :-)

So I'll help with the primes....your task is search this site for many of the examples of LISTAGG

SQL> with digits as ( select level n from dual connect by level <= 100 )
  2  select n
  3  from digits d1
  4  where d1.n = 1
  5  or not exists
  6   ( select 1
  7     from   digits d2
  8     where  mod(d1.n,d2.n)=0
  9     and    d2.n > 1
 10     and    d2.n < d1.n
 11   )
 12  order by 1;

         N
----------
         1
         2
         3
         5
         7
        11
        13
        17
        19
        23
        29
        31
        37
        41
        43
        47
        53
        59
        61
        67
        71
        73
        79
        83
        89
        97

26 rows selected.


October 07, 2019 - 11:37 am UTC

Reviewer: Pranav from India

Hello Tom,

How can we achieve the same DML through Execute Immediate?

When below block is executed , the input is requested from user.

begin
execute immediate 'insert into test values (''&Vivek'')';
end;

set define off doesn't work in this case.
Can you please provide solution for this?
Chris Saxon

Followup  

October 07, 2019 - 12:38 pm UTC

What are the exact commands you're using and in which client (SQL*Plus, SQL Dev, ...)?

Because it all looks fine to me:

SQL> create table test ( c1 varchar2(10) );

Table created.

SQL> set define off
SQL> begin
  2  execute immediate 'insert into test values (''&Vivek'')';
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select * from test;

C1
----------
&Vivek


PS - you don't need execute immediate!

SQL> begin
  2    insert into test values ('&Vivek');
  3  end;
  4  /

PL/SQL procedure successfully completed.

October 09, 2019 - 4:45 am UTC

Reviewer: Pranav from India

Oh.. Thanks a ton.

I was doing mistake of using set define off inside the begin block
as below:

begin
set define off;
execute immediate 'insert into test values (''&Vivek'')';
end;

I am using TOAD. I was just doing RND on how '&' is to be handled in dynamic query so was trying to insert using execute immediate.

Thank you once again for the solution.
Chris Saxon

Followup  

October 09, 2019 - 10:33 am UTC

No problem!