代码如下,有省略
CREATE OR REPLACE TRIGGER tr_for_user AFTER INSERT OR DELETE ON masa_user DECLARE
user_sum NUMBER; BEGIN
SELECT COUNT(t.USERNAME) INTO USER_SUM
FROM (SELECT T.F_USERID USERNAME
FROM MASA_USER T
MINUS
SELECT USERNAME USERNAME FROM tUserinfo@ABC) t; //ABC为一个dblink名称 END; 报错信息:
Error: PL/SQL: ORA-04052: error occurred when looking up remote object [email protected]
ORA-00604: error occurred at recursive SQL level 1
ORA-03106: fatal two-task communication protocol error
ORA-02063: preceding line from ICD
Line: 14
Text: SELECT COUNT(t.USERNAME) Error: PL/SQL: SQL Statement ignored
Line: 14
Text: SELECT COUNT(t.USERNAME) 将其中select语句单独执行没问题,放到触发器中就不能通过报错了,抛出的三个oracle错误不知道如何解决
CREATE OR REPLACE TRIGGER tr_for_user AFTER INSERT OR DELETE ON masa_user DECLARE
user_sum NUMBER; BEGIN
SELECT COUNT(t.USERNAME) INTO USER_SUM
FROM (SELECT T.F_USERID USERNAME
FROM MASA_USER T
MINUS
SELECT USERNAME USERNAME FROM tUserinfo@ABC) t; //ABC为一个dblink名称 END; 报错信息:
Error: PL/SQL: ORA-04052: error occurred when looking up remote object [email protected]
ORA-00604: error occurred at recursive SQL level 1
ORA-03106: fatal two-task communication protocol error
ORA-02063: preceding line from ICD
Line: 14
Text: SELECT COUNT(t.USERNAME) Error: PL/SQL: SQL Statement ignored
Line: 14
Text: SELECT COUNT(t.USERNAME) 将其中select语句单独执行没问题,放到触发器中就不能通过报错了,抛出的三个oracle错误不知道如何解决
2 AFTER INSERT ON emp
3 FOR EACH ROW
4 DECLARE
5 -- local variables here
6 PRAGMA AUTONOMOUS_TRANSACTION;
7 v_num NUMBER;
8 BEGIN
9 SELECT COUNT(*)
10 INTO v_num
11 FROM (SELECT to_char(empno) eno
12 FROM emp
13 MINUS
14 SELECT useruri FROM info_user@to_201);
15 COMMIT;
16 dbms_output.put_line(v_num);
17 END tri_emp;
18 /
Trigger created
SQL>
SQL> insert into emp(empno)
2 values(1);
14
1 row inserted
SQL> commit;
Commit complete
SQL>
SQL> insert into emp(empno)
2 values(2);
15
1 row inserted
SQL> commit;
Commit complete
SQL>
确认表存在
确认表所在的用户为dblink锁连接的用户
如果都没问题那就比较奇怪了另外你在更改一个表的时候又要读取它,会报错,需要使用自治事物
user_sum NUMBER; BEGIN
SELECT COUNT(t.USERNAME) INTO USER_SUM
FROM (/*SELECT T.F_USERID USERNAME
FROM MASA_USER T
MINUS*/
SELECT USERNAME USERNAME FROM tUserinfo@ABC) t; //ABC为一个dblink名称 END; 结果就是发现:使用了dblink这段代码就会出错!!而单独为什么就可以执行
SELECT COUNT(t.USERNAME)
FROM (SELECT T.F_USERID USERNAME
FROM MASA_USER T
MINUS
SELECT USERNAME USERNAME FROM tUserinfo@ABC) t;输出
-----------------------
6万分郁闷中,急求解
是不是有比较特殊的字段,比如lob,long,long raw什么的
或者在触发器中使用动态SQL
由于后面还有insert操作,所以使用视图应该不行,使用触发器可以通过编译,但是触发器在被触发之后报错了,这样只是相当于将错误隐藏起来,等到执行的时候还是会出错的
只有最基本的number 和 varchar2-- Create table
create table tUserinfo
(
USERNAME VARCHAR2(20) not null,
SUBCCNO NUMBER(6) not null,
VDN NUMBER(6) not null,
DESCRIPTION VARCHAR2(200)
)
tablespace ABC_SERVICE
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create/Recreate primary, unique and foreign key constraints
alter table TUSERINFO
add constraint PK_TUSERINFO primary key (USERNAME)
using index
tablespace ABC_SERVICE_IND
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
问题跟进,我另外创建了一个tUserinfo_temp临时表,替换掉tUserinfo表还是出错,然后逐步注释测试,发现注释掉它的主键之后就不出错了alter table TUSERINFO
add constraint PK_TUSERINFO primary key (USERNAME)
using index
tablespace ABC_SERVICE_IND
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);这段代码一般会出现什么问题?我需要select和insert这个表
add constraint PK_TUSERINFO primary key (USERNAME)
using index 对这段的作用不太清楚,还需要多学习