When or Why to use a “SET DEFINE OFF” in Oracle Database

by

By default, SQL Plus treats ‘&’ as a special character that begins a substitution string. This can cause problems when running scripts that happen to include ‘&’ for other reasons:

SQL> insert into customers (customer_name) values ('Marks & Spencers Ltd');
Enter value for spencers: 
old   1: insert into customers (customer_name) values ('Marks & Spencers Ltd')
new   1: insert into customers (customer_name) values ('Marks  Ltd')

1 row created.

SQL> select customer_name from customers;

CUSTOMER_NAME
------------------------------
Marks  Ltd

If you know your script includes (or may include) data containing ‘&’ characters, and you do not want the substitution behaviour as above, then use set define off to switch off the behaviour while running the script:

SQL> set define off
SQL> insert into customers (customer_name) values ('Marks & Spencers Ltd');

1 row created.

SQL> select customer_name from customers;

CUSTOMER_NAME
------------------------------
Marks & Spencers Ltd

You might want to add set define on at the end of the script to restore the default behaviour.

More Related Contents:

  1. Determining location of relevant tnsnames.ora file
  2. ORA-12514 TNS:listener does not currently know of service requested in connect descriptor
  3. Oracle: Import CSV file
  4. PL/SQL ORA-01422: exact fetch returns more than requested number of rows
  5. Sql*plus always returns exit code 0?
  6. SQL*Plus how to accept text variable from prompt?
  7. Convert epoch to date in sqlplus / Oracle
  8. oracle SQL plus how to end command in SQL file?
  9. How can I issue a single command from the command line through sql plus?
  10. How to View Oracle Stored Procedure using SQLPlus?
  11. When do I need to use a semicolon vs a slash in Oracle SQL?
  12. Calculate difference between 2 date / times in Oracle SQL
  13. Can you SELECT everything, but 1 or 2 fields, without writer’s cramp?
  14. How to generate entire DDL of an Oracle schema (scriptable)?
  15. How to get records randomly from the oracle database?
  16. Oracle PL/SQL – Raise User-Defined Exception With Custom SQLERRM
  17. Oracle Search all tables all columns for string [duplicate]
  18. Oracle PL/SQL – How to create a simple array variable?
  19. Number of rows affected by an UPDATE in PL/SQL
  20. How to use a table type in a SELECT FROM statement?
  21. Spool Command: Do not output SQL statement to file
  22. Why are Oracle table/column/index names limited to 30 characters?
  23. Is there any way to have the JBoss connection pool reconnect to Oracle when connections go bad?
  24. Get ID of last inserted record in oracle db
  25. Why is the GETDATE() an invalid identifier
  26. ORA-04091: table [blah] is mutating, trigger/function may not see it
  27. How to use parameters in a ‘where value in…’ clause?
  28. How to generate a version 4 (random) UUID on Oracle?
  29. Find out the history of SQL queries
  30. How to test an Oracle Stored Procedure with RefCursor return type?

Leave a Comment