在Oracle 11g中创建触发器时遇到问题 [英] Problems creating a trigger in Oracle 11g

问题描述

在尝试使用SQL Developer在Oracle 11g数据库中创建触发器时，出现一个奇怪的错误.这是我所做的:

I get a weird error while trying to create a trigger in my Oracle 11g database using SQL Developer. Here is what I did:

我的桌子:

CREATE TABLE COUNTRY_CODE(
   ID NUMBER(19,0)      PRIMARY KEY NOT NULL, 
   Code             VARCHAR2(2) NOT NULL,
   Description  VARCHAR2(50),
   created                  TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
   created_by                   VARCHAR2(40) DEFAULT USER, 
   last_updated                 TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
   last_updated_by          VARCHAR2(40) DEFAULT USER,
   archived CHAR(1) DEFAULT '0' NOT NULL );

序列:

CREATE SEQUENCE COUNTRY_CODE_ID_SEQ START WITH 1 INCREMENT BY 1;

触发器:

CREATE OR REPLACE TRIGGER COUNTRY_CODE_TRIGGER
BEFORE INSERT ON COUNTRY_CODE
FOR EACH ROW
DECLARE
    max_id number;
    cur_seq number;
BEGIN
    IF :new.id IS NULL THEN
    SELECT COUNTRY_CODE_ID_SEQ.nextval
    INTO :new.id
    FROM dual;
ELSE
    SELECT GREATEST(NVL(MAX(id),0), :new.id)
    INTO max_id
    FROM COUNTRY_CODE;

    SELECT COUNTRY_CODE_ID_SEQ.nextval
    INTO cur_seq
    FROM dual;

    WHILE cur_seq < max_id
    LOOP
        SELECT COUNTRY_CODE_ID_SEQ.nextval
        INTO cur_seq
        FROM dual;
    END LOOP;
END IF;
END;

创建表和序列效果很好，但是当我尝试创建触发器时，出现此错误:

Creating the table and the sequence works very well, but when I try to create my trigger, I get this error:

Error report:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kqlidchg0], [], [], [], [], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated
00603. 00000 -  "ORACLE server session terminated by fatal error"
*Cause:    An ORACLE server session is in an unrecoverable state.
*Action:   Login to ORACLE again so a new server session will be created

有人知道这个错误吗?

谢谢

推荐答案

我终于找到了解决问题的答案:

I finally found the answer to my problem:

添加此内容:

ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:NONE';

或在Oracle SQL Developer中:

Or in Oracle SQL Developer:

1. 转到工具|首选项
2. 选择数据库| PL/SQL编译器
3. 将PLScope标识符从全部"更改为无"
4. 单击确定

这解决了问题...

这篇关于在Oracle 11g中创建触发器时遇到问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！