Using the & (ampersand) character in Oracle SQL*Plus scripts

Using the & (ampersand) character in Oracle SQL*Plus scripts

It can be very frustrating when using Oracle SQL*PLus (sqlplus) to run a SQL script that contains an ampersand (&) character only to have the script stop mid-way through and ask you for a value :


 

Enter value for .... :

For example, this simple script :

-- This is a test script to show
-- what happens when an & (ampersand)
-- is in a SQL file

drop table t;
create table t (id number, name varchar2(32));
insert into t values (1, 'Bodget & Scarper');
commit;

When run via sqlplus generates this 'error' :

[steve@barney ~]$ sqlplus @test.sql
SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 30 13:05:27 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Table dropped.
Table created.
Enter value for scarper:

And simply stops waiting for some input from us.  To disable this simply add 'set define off' to the top of the script :

set define off
-- This is a test script to show
-- what happens when an & (ampersand)
-- is in a SQL file
.....

And voila!

[steve@barney ~]$ sqlplus @test.sql
SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 30 13:06:46 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Table dropped.
Table created.
1 row created.
Commit complete.
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
[steve@barney ~]$

The 'set define' option is actually used to tell SQL*Plus which character identifies a substitution variable which, by default, is the & character.  Using 'off' we're turning off substitutions but you could equally change the character something else if you needed to.


Comments (0)


Add a Comment

Please login to comment.