oracle触发器 ORA-01722:invalid number 解决方法
问题在于,远程库是nvarchar2类型,本地是number类型,同步的时候有问题。
create or replace trigger tri_org_department after insert or update on bjlt.sys_org for each row declare v_uuid varchar2(36); v_id varchar2(36); begin v_uuid:=sys_guid(); v_id:=:new.ID; if inserting then insert into KEYMS.KEY_DEPARTMENT@TOBSMS_BETTERY_LOCAL.REGRESS.RDBMS.DEV.US.ORACLE.COM(depid,depcode,depname,parentid) values(:new.ID,:new.RESERVE4,:new.NAME,:new.P_ID); insert into KEYMS.KEY_USER@TOBSMS_BETTERY_LOCAL.REGRESS.RDBMS.DEV.US.ORACLE.COM(userid,username,password,truename,mobile,depid,isuse,issys,isproject) values(v_uuid,:new.RESERVE4,:new.RESERVE4,:new.NAME,:new.RESERVE4,:new.ID,1,1,0); insert into KEYMS.KEY_USERROLE@TOBSMS_BETTERY_LOCAL.REGRESS.RDBMS.DEV.US.ORACLE.COM(USERID,ROLEID) values(v_uuid,'0002'); end if; if updating then update KEYMS.KEY_DEPARTMENT@TOBSMS_BETTERY_LOCAL.REGRESS.RDBMS.DEV.US.ORACLE.COM set depcode = :new.RESERVE4, depname = :new.NAME, parentid = :new.P_ID where depid = v_id; end if; end;