何时或为何使用"SET DEFINE OFF"在Oracle数据库中 [英] When or Why to use a "SET DEFINE OFF" in Oracle Database

问题描述

我正在看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数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！