oracle什么情况insert失败,执行INSERT 语句报 ORA-00036 错误，非常怪异请帮忙分析原因...

执行一个INSERT 语句报错：

INSERT INTO

RE_CC_INST(EVENT_INST_ID,ACCT_ID,RE_TYPE,RE_ID,EVENT_TIME,STATE_DATE,BILLING_CYCLE_ID,INSTALMENT_TYPE_ID,CANCEL_EVENT_INST_ID,REF_ATTR,SUBS_ID,SP_ID,CONTACT_CHANNEL_ID,CUST_ID,PARTY_TYPE,PARTY_CODE,ACM_LIST)

VALUES(2751085,397912,'3',975,to_date('2013-10-22 10:55:37','yyyy-mm-dd hh24:mi:ss'),to_date('2013-10-22 10:55:37','yyyy-mm-dd hh24:mi:ss'),null,null,null,null,703246,7,5,null,'E',null,null)

报错：

Caused by: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 2

ORA-00036: maximum number of recursive SQL levels (50) exceeded

ORA-06512: at line 37

ORA-06512: at line 37

ORA-06512: at line 37

ORA-06512: at line 37

ORA-06512: at line 37

ORA-06512: at line 37

开了 SQL_TRACE ，附件

SQLTRACE 中 看到时由于TRIGGER GTSC_DDL_TRIGGER 引起的， 确实在 DISABLE 这个TRIGGER 后 这个故障就消失了，

但是这个TRIGGER 只监控 DDL 语句 按道理不会对INSERT 产生影响。实在搞不懂出什么问题了。

GTSC_DDL_TRIGGER 原文：

CREATE OR REPLACE TRIGGER GTSC_DDL_TRIGGER

BEFORE CREATE OR ALTER OR DROP ON DATABASE

DECLARE

L_CLIENTIP      VARCHAR2(20);

L_CLIENTMACHINE VARCHAR2(200);

L_STEXT  VARCHAR2(2000);

L_ORA_DICT_OBJ_NAME VARCHAR2(200);

L_ORA_DICT_OBJ_TYPE VARCHAR2(200);

BEGIN

begin

SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') INTO L_CLIENTIP FROM DUAL;

SELECT SYS_CONTEXT('USERENV', 'TERMINAL') INTO L_CLIENTMACHINE FROM DUAL;

exception when others then

L_CLIENTIP:='-';

L_CLIENTMACHINE:='-';

end;

begin

SELECT ORA_DICT_OBJ_NAME INTO L_ORA_DICT_OBJ_NAME FROM DUAL;

SELECT ORA_DICT_OBJ_TYPE INTO L_ORA_DICT_OBJ_TYPE FROM DUAL;

exception when others then

L_ORA_DICT_OBJ_NAME:='-';

L_ORA_DICT_OBJ_TYPE:='-';

end;

begin

select SQL_TEXT INTO L_STEXT FROM v$open_cursor WHERE UPPER(sql_text) LIKE 'ALTER%' OR UPPER(sql_text) LIKE 'DROP%' OR UPPER(sql_text) LIKE 'CREATE%' ;

exception when others then

L_STEXT:='-';

end;

IF L_ORA_DICT_OBJ_TYPE<>'TABLE' OR (L_ORA_DICT_OBJ_TYPE='TABLE' AND L_ORA_DICT_OBJ_NAME not like 'TEMP%'

and  L_ORA_DICT_OBJ_NAME not like 'TMP%' AND  L_ORA_DICT_OBJ_NAME not like 'DB/_%' escape '/' AND

L_ORA_DICT_OBJ_NAME NOT like 'P/_%' escape '/' AND  L_ORA_DICT_OBJ_NAME not like '%bak') then

INSERT INTO GTSC_DDL_LOG

(OPERATIONCLASS,

OBJECT_OWNER,

OBJECT_NAME,

OBJECT_TYPE,

OPERATIONUSER,

OPERATIONDATE,

CLIENTMACHINE,

CLIENTIP,

SQL_TEXT)

SELECT ORA_SYSEVENT,

ORA_DICT_OBJ_OWNER,

ORA_DICT_OBJ_NAME,

ORA_DICT_OBJ_TYPE,

USER,

SYSDATE,

L_CLIENTMACHINE,

L_CLIENTIP,

L_STEXT

FROM DUAL;

end if;

END GTSC_DDL_TRIGGER;

/

2013-10-23 12:39 上传

点击文件名下载附件

24.86 KB, 下载次数: 807