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.
