Oracle Pl/SQL 通过 SQL*PLUS 触发编译错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1673312/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Pl/SQL trigger compilation error via SQL*PLUS
提问by Kieran
I have a problem with compiling an Oracle trigger via SQL*PLUS - I don't think I'm being dumb but I can't see what the problem is.
我在通过 SQL*PLUS 编译 Oracle 触发器时遇到问题 - 我不认为我很笨，但我看不出问题是什么。
We have an installer script which is essentially a batch file which creates/refreshes all the objects in the database by calling SQLPLUS on multiple scripts, each containing one view, trigger, etc. The tables and views are created first, then then triggers. The V_BS_GRIDFIELDSview below may or may not be created at this point, or may be created later by a different process. The view is an updatable view, so we have a trigger placed on it to push updates to different tables, as below:
我们有一个安装程序脚本，它本质上是一个批处理文件，它通过在多个脚本上调用 SQLPLUS 来创建/刷新数据库中的所有对象，每个脚本包含一个视图、触发器等。首先创建表和视图，然后是触发器。V_BS_GRIDFIELDS下面的视图可能会或可能不会在此时创建，或者可能稍后由不同的过程创建。该视图是一个可更新的视图，因此我们在其上放置了一个触发器来将更新推送到不同的表，如下所示：
CREATE OR REPLACE FORCE TRIGGER TR_INSTUPD_BS
INSTEAD OF INSERT OR UPDATE OR DELETE
ON V_BS_GRIDFIELDS
FOR EACH ROW
BEGIN
IF INSERTING OR DELETING THEN
NULL;
END IF;
IF UPDATING THEN
-- Can only change these fields
IF (:OLD.VISIBLE <> :NEW.VISIBLE) OR (:OLD.COMPULSORY <> :NEW.COMPULSORY) THEN
-- Source Table = BS_GRIDFIELDS
IF (:OLD.SOURCE_TYPE = 0) THEN
UPDATE BS_GRIDFIELDS BS_GF
SET BS_GF.VISIBLE = :NEW.VISIBLE,
BS_GF.COMPULSORY = :NEW.COMPULSORY
WHERE BS_GF.FIELD_NAME = :OLD.FIELD_NAME;
END IF;
END IF;
END IF;
END;
The issue is that oracle SQL*PLUS seems to stop compiling the trigger after the first blank line, on line 6:
问题是 oracle SQL*PLUS 似乎在第 6 行的第一个空行之后停止编译触发器：
SQL> @"TR_INSTUPD_BS.sql";
SP2-0734: unknown command beginning "IF INSERTI..." - rest of line ignored.
SP2-0042: unknown command "NULL" - rest of line ignored.
SP2-0042: unknown command "END IF" - rest of line ignored.
SP2-0734: unknown command beginning "IF UPDATIN..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "IF (:OLD.V..." - rest of line ignored.
SP2-0734: unknown command beginning "IF (:OLD.S..." - rest of line ignored.
SP2-0552: Bind variable "OLD" not declared.
SP2-0042: unknown command "END IF" - rest of line ignored.
SP2-0042: unknown command "END IF" - rest of line ignored.
SP2-0042: unknown command "END IF" - rest of line ignored.
SP2-0042: unknown command "END" - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
If you remove the blank line on line 6, it seems to stop compiling at the first semicolon, on line 7:
如果删除第 6 行的空行，它似乎在第 7 行的第一个分号处停止编译：
SQL> @"TR_INSTUPD_BS.sql";
Warning: Trigger created with compilation errors.
SP2-0042: unknown command "END IF" - rest of line ignored.
SP2-0734: unknown command beginning "IF UPDATIN..." - rest of line ignored.
SP2-0734: unknown command beginning "IF (:OLD.V..." - rest of line ignored.
SP2-0734: unknown command beginning "IF (:OLD.S..." - rest of line ignored.
SP2-0552: Bind variable "OLD" not declared.
SP2-0042: unknown command "END IF" - rest of line ignored.
SP2-0042: unknown command "END IF" - rest of line ignored.
SP2-0042: unknown command "END IF" - rest of line ignored.
SP2-0042: unknown command "END" - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL>
We have lots of triggers created in this way, and all of them have spaces, semicolons, etc, and get created OK. I've tested and seen the same issue on Oracle 9, 10, 11. Can anyone shed light on this?
我们有很多以这种方式创建的触发器，并且它们都有空格、分号等，并且可以正常创建。我已经在 Oracle 9、10、11 上测试并看到了同样的问题。有人能解释一下吗？
Thanks.
谢谢。
回答by Vincent Malgrat
in its default setting SQL*Plus won't deal properly with blank lines, you need to issue the following command:
在默认设置中，SQL*Plus 不会正确处理空行，您需要发出以下命令：
SQL> SET SQLBLANKLINES on
See this other SO.
请参阅其他 SO。
Update:I answered too fast, the blank line doesn't seem to be the problem here. I tried your code on my database and the issue seems to come from the FORCEkeyword. The 10gR2 documentationdoesn't mention this keyword. The trigger compiles when you remove it.
更新：我回答得太快了，这里的空行似乎不是问题。我在我的数据库上尝试了您的代码，问题似乎来自FORCE关键字。在10gR2中的文件并没有提到这个关键字。当您删除触发器时，它会编译。
