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

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Vivek.

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

Last updated: October 09, 2019 - 10:33 am UTC

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.


Rating

  (23 ratings)

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

Comments

Just execute as a script

G, March 15, 2012 - 10:51 am UTC

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


press 'F5' to execute the above commands.

the q' operator

asktom fan, March 15, 2012 - 10:53 am UTC

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
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 '&'

Vivek popat, March 16, 2012 - 1:12 am UTC

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

Barry Chase, March 18, 2012 - 10:08 pm UTC

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

AndyP, March 20, 2012 - 3:26 am UTC

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
March 20, 2012 - 9:39 am UTC

nifty, i learned something new again today!

Another way

Valério, March 21, 2012 - 7:12 am UTC

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

Tony Fernandez, March 21, 2012 - 10:45 am UTC

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
March 21, 2012 - 10:56 am UTC

This may be cleaner

Tony Fernandez, March 21, 2012 - 11:03 am UTC

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

Viknesh, May 27, 2012 - 8:56 am UTC

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

Viknesh, May 27, 2012 - 1:37 pm UTC

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

Viknesh, May 28, 2012 - 3:52 pm UTC

Thank for your help tom :)

Please help me in this

Vikky, May 31, 2012 - 4:46 am UTC

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

Greg, May 31, 2012 - 1:26 pm UTC

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

Viknesh, May 31, 2012 - 11:54 pm UTC

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
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! :)

Viknesh, June 01, 2012 - 10:06 am UTC

Thanks alot tom! much useful! :)

To Greg from Toronto

Autonomous, June 01, 2012 - 8:23 pm UTC

You can fix a mutating thread using an autonomous poster but the result may not be what you expect
Tom Kyte
June 02, 2012 - 2:49 am UTC

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

offtopic

Sokrates, June 04, 2012 - 7:26 am UTC

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

A reader, September 11, 2012 - 6:34 am UTC


Barry's solution works

Jack, November 10, 2015 - 3:52 pm UTC

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

Sets the column to 'A&S'

Use the UNISTR() function

Thomas Bender, September 20, 2016 - 3:24 pm UTC

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

Monika soni, August 05, 2019 - 7:39 am UTC

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


Pranav, October 07, 2019 - 11:37 am UTC

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

Pranav, October 09, 2019 - 4:45 am UTC

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
October 09, 2019 - 10:33 am UTC

No problem!