在 Oracle 数据库中使用“ SET DEFINE OFF”的时间和原因

我在看甲骨文中的一个脚本，我看到了一些我不认识的东西

REM INSERTING into database1."Users"
SET DEFINE OFF;
Insert into database1."Users" ("id","right") values ('1','R');

我正在寻找关于“ set Definition off”的文档，它的字面意思是“禁用命令解析以用它们的值替换替换变量”

我不知道他们想说什么。

有人能帮我吗？

218320 次浏览
小开

Here is the example:

SQL> set define off;
SQL> select * from dual where dummy='&var';


no rows selected


SQL> set define on
SQL> /
Enter value for var: X
old   1: select * from dual where dummy='&var'
new   1: select * from dual where dummy='X'


D
-
X

With set define off, it took a row with &var value, prompted a user to enter a value for it and replaced &var with the entered value (in this case, X).

小开
最佳答案

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.


提交答案