尝试使用SQL Developer在Oracle 11g数据库中创建触发器时,我遇到了一个奇怪的错误.这是我做的:
我的桌子:
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;
创建表和序列非常有效,但是当我尝试创建触发器时,我收到此错误:
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
有谁知道这个错误？
谢谢
我终于找到了问题的答案:
添加这个:
ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:NONE';
或者在Oracle SQL Developer中:
转到工具| 喜好
选择数据库| PL/SQL编译器
将PLScope标识符从All更改为None
点击确定
这解决了这个问题......
京公网安备 11010802040832号 | 京ICP备19059560号-6 