Skip to Main Content


Question and Answer

Tom Kyte

Thanks for the question, Casey .

Asked: May 02, 2000 - 1:21 pm UTC

Last updated: September 24, 2004 - 9:37 am UTC


Viewed 10K+ times! This question is

You Asked

Is there an escape character or something I can use so
I can insert an '&' (ampersand) into a field without getting
prompted for a value?

and Tom said...

I assume you are using SQLPlus. By default, the macro definition character in sqlplus is an &. We can either

- change this character
- turn off macro preprocessing.

To change this character you would:

ops$tkyte@8i> set define ^
ops$tkyte@8i> select '&1' from dual;


ops$tkyte@8i> select '^1' from dual;
Enter value for 1: Hello
old 1: select '^1' from dual
new 1: select 'Hello' from dual


That made the define character a ^. To turn off this feature, you could either issue:

SQL> set define off
SQL> set scan off

for example:

ops$tkyte@8i> set scan off
ops$tkyte@8i> select '^1' from dual;



  (6 ratings)

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


succinct soln to freq occuring problem

Paul, December 06, 2001 - 10:28 am UTC

straight to the point

using & in PL/SQL

Wael, March 28, 2004 - 2:37 am UTC

I noticed that even when using '&' (ampersand) in PL/SQL block comment (e.g. --following stmt does this & this & this ..), you will be prompted to enter the value of "this".

Any comments.


Tom Kyte
March 28, 2004 - 9:11 am UTC

SQL> set define off

in sqlplus will disable that. 

inserting '&' into a table with out getting prompt

srinivasa, March 28, 2004 - 3:33 pm UTC

There is another way to do this.

select '&'||'1' from dual;


SQL*Plus: Release - Production on Sun Mar 28 15:29:00 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release - Production

wrok@acharya> set define on
wrok@acharya> set scan on
wrok@acharya> select '&1' from dual;
Enter value for 1: hello
old 1: select '&1' from dual
new 1: select 'hello' from dual


wrok@acharya> select '&'||'1' from dual;


wrok@acharya> select '^1' from dual;



wrok@acharya> select '&' from dual;



suppress "old" and "new" lines

A reader, August 13, 2004 - 3:00 pm UTC

Sorry for the stupid q.  I can't seem to find this in the docs.

I want to suppress the display of the "old" and "new" lines.  How can this be accomplished?

Thanks in advance.

SQL> select &1 from dual;
Enter value for 1: 32
old   1: select &1 from dual   <-- hide me
new   1: select 32 from dual   <-- hide me


Tom Kyte
August 13, 2004 - 6:38 pm UTC

set verify off

Another solution

Jason Vogel, September 22, 2004 - 4:31 pm UTC


Don't use the literal...


Tom Kyte
September 22, 2004 - 5:56 pm UTC

as long as you are in a single byte character set where & is chr(38) sure....

I'd use the literal, use set define off to disable the sqlplus'ism.

Why this ?

A reader, September 23, 2004 - 3:52 pm UTC

In sqlplus
1)I run some one insert statement and do not execute commit after that also autocommit feature is set to off(default)
2) Now I execute exit command
3) I log back in and see that the row is committed.

Why does ORACLE "autocommit on exit". How to change this behaviour so that it commits only when commit is issued by me.

Tom Kyte
September 24, 2004 - 9:37 am UTC

SQLPlus the application (not Oracle the database) issues a commit upon succesful exit.  It also issues a commit during "connect" and "disconnect".  SQLPlus, the interactive application, assumes you want to end your current transaction successfully.

In order to rollback upon exit, you would


This is the behaviour of the command line interfact that is sqlplus -- it is not the "database" really, sqlplus intentionally does this.