SQL - Oracle 11g - PL/SQL - 触发器编译错误

发布于 2024-12-26 17:32:14 字数 1271 浏览 2 评论 0原文

我在运行这段代码时遇到问题。它说触发器是在编译错误的情况下创建的，但没有解释错误所在，也没有给出任何输出。下面是代码和我给出的错误。我使用的是 Oracle 11g R1 并使用 PL/SQL。

CREATE OR REPLACE TRIGGER checkRecommendedAge
BEFORE INSERT OR UPDATE ON Loan
FOR EACH ROW
DECLARE
    borAge number;
    ex     exception;

BEGIN
    SELECT count(*) INTO borAge
    FROM Loan
    WHERE dateOut =: new.dateOut;
    IF borAge < 18 THEN
        dbms.output.put_line('Row added to Book table succesful');
    ELSE
        raise ex;
    END IF;

    EXCEPTION
    WHEN EX THEN
    raise_application_error("Error");
END;
/

错误：

    Warning: Trigger created with compilation errors.

SQL> SHOW ERRORS
Errors for TRIGGER CHECKRECOMMENDEDAGE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PLS-00801: internal error [ph2csql_strdef_to_diana:bind]
6/2      PL/SQL: SQL Statement ignored
7/7      PL/SQL: ORA-06544: PL/SQL: internal error, arguments:
         [ph2csql_strdef_to_diana:bind], [], [], [], [], [], [], []

10/3     PL/SQL: Statement ignored
10/3     PLS-00201: identifier 'DBMS.OUTPUT' must be declared
17/2     PL/SQL: Statement ignored
17/27    PLS-00201: identifier 'Error' must be declared

如有任何帮助，我们将不胜感激

，谢谢

原文

I'm having trouble running this piece of code. It says that the trigger was created with compilation errors but does not explain what's wrong and does not give any output. Below is the code and the error I'm given. I'm on Oracle 11g R1 and am using PL/SQL.

CREATE OR REPLACE TRIGGER checkRecommendedAge
BEFORE INSERT OR UPDATE ON Loan
FOR EACH ROW
DECLARE
    borAge number;
    ex     exception;

BEGIN
    SELECT count(*) INTO borAge
    FROM Loan
    WHERE dateOut =: new.dateOut;
    IF borAge < 18 THEN
        dbms.output.put_line('Row added to Book table succesful');
    ELSE
        raise ex;
    END IF;

    EXCEPTION
    WHEN EX THEN
    raise_application_error("Error");
END;
/

Error:

    Warning: Trigger created with compilation errors.

SQL> SHOW ERRORS
Errors for TRIGGER CHECKRECOMMENDEDAGE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PLS-00801: internal error [ph2csql_strdef_to_diana:bind]
6/2      PL/SQL: SQL Statement ignored
7/7      PL/SQL: ORA-06544: PL/SQL: internal error, arguments:
         [ph2csql_strdef_to_diana:bind], [], [], [], [], [], [], []

10/3     PL/SQL: Statement ignored
10/3     PLS-00201: identifier 'DBMS.OUTPUT' must be declared
17/2     PL/SQL: Statement ignored
17/27    PLS-00201: identifier 'Error' must be declared

Any help would be appreciated

Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

只想待在家 2025-01-02 17:32:14

您正在指定 dbms.output。在触发器中尝试 dbms_output.put_line 。

编辑：

您错误地调用了 RAISE_APPLICATION_ERROR 。首先，不要在这里使用双引号字符串 - 它表示标识符，而不是字符串。其次，此过程的语法为：

raise_application_error(error_number, message[, {TRUE | FALSE}]);

请参阅文档< /a> 进行更彻底的治疗。

您还可能会遇到可怕的“ORA-04091：表 XXXXX 正在发生变化”错误，因为您是从触发触发器的同一个表中进行选择。有关详细信息，请参阅这篇 AskTom 文章。

You're specifying dbms.output. Try dbms_output.put_line in your trigger.

EDIT:

You're calling RAISE_APPLICATION_ERROR incorrectly. First, don't use a double-quoted string here - that indicates an identifier, not a string. Second, the syntax for this procedure is:

raise_application_error(error_number, message[, {TRUE | FALSE}]);

See the documentation for a more thorough treatment.

You're also likely to hit the dreaded "ORA-04091: Table XXXXX is mutating" error, because you're selecting from the same table that the trigger is firing for. See this AskTom article for more information.

回复收藏 0 原文

混浊又暗下来 2025-01-02 17:32:14

尝试一下。

更改：

dbms.output 更改为 dbms_output

dateOut =: 更改为 dateOut =< /code>

raise_application_error("Error"); 到 raise_application_error('Error');

CREATE OR REPLACE TRIGGER checkRecommendedAge
BEFORE INSERT OR UPDATE ON Loan
FOR EACH ROW
DECLARE
    borAge number;
    ex     exception;

BEGIN
    SELECT count(*) INTO borAge
    FROM Loan
    WHERE dateOut = new.dateOut;
    IF borAge < 18 THEN
        dbms_output.put_line('Row added to Book table succesful');
    ELSE
        raise ex;
    END IF;

    EXCEPTION
    WHEN EX THEN
    raise_application_error('Error');
END;
/

Give this a try.

Changes:

dbms.output to dbms_output

dateOut =: to dateOut =

raise_application_error("Error"); to raise_application_error('Error');

CREATE OR REPLACE TRIGGER checkRecommendedAge
BEFORE INSERT OR UPDATE ON Loan
FOR EACH ROW
DECLARE
    borAge number;
    ex     exception;

BEGIN
    SELECT count(*) INTO borAge
    FROM Loan
    WHERE dateOut = new.dateOut;
    IF borAge < 18 THEN
        dbms_output.put_line('Row added to Book table succesful');
    ELSE
        raise ex;
    END IF;

    EXCEPTION
    WHEN EX THEN
    raise_application_error('Error');
END;
/

回复收藏 0 原文