何时或为何使用“SET DEFINE OFF"在 Oracle 数据库中

问题描述

我正在 Oracle 中观看脚本，但看到了一些我不认识的内容

I'm watching a Script in Oracle and I see something I don't recognize

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

我正在寻找有关set define off"的文档，它的字面意思是禁用命令解析以将替换变量替换为其值"

I'm looking for documentation about "set define off" and it's literally writing "disable the parsing of commands to replace substitution variable with their values"

我真的不明白他们想说什么.

I don't really understand what they want to say.

有人可以帮我吗?

推荐答案

默认情况下，SQL Plus 处理&"作为开始替换字符串的特殊字符.这在运行包含&"的脚本时可能会导致问题其他原因:

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

如果您知道您的脚本包含(或可能包含)包含&"的数据字符，并且您不希望出现上述替换行为，然后使用 set define off 在运行脚本时关闭行为:

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

您可能希望在脚本末尾添加 set define on 以恢复默认行为.

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

这篇关于何时或为何使用“SET DEFINE OFF"在 Oracle 数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！