526互联

Oracle作业执行与SQL Develop运行语句（脚本）的区别

发布时间 2023-09-06 10:49:22作者: Chr☆s

问题背景：在SQL Developer界面上执行UPDATE语句是成功的，但在作业上这个UPDATE语句会报错。如目标表有如下触发器：

create or replace TRIGGER CONCEPT."SICKBED_TRG" AFTER UPDATE OF USEDCODEID OR DELETE ON "CONCEPT"."SICKBED" FOR EACH ROW
DECLARE
v_ip varchar2(30);
v_user varchar2(30);
BEGIN
   SELECT sys_context('userenv', 'IP_ADDRESS'), sys_context('userenv', 'SESSION_USER') INTO v_ip, v_user FROM dual;

   CASE
     WHEN UPDATING ('USEDCODEID') THEN
        IF :new.USEDCODEID != :old.USEDCODEID THEN
           INSERT INTO "CONCEPT"."SICKBEDMODIFIEDTRACE"(SICKBEDID, NO, NAME, ORDINAL, GENDERCODEID, ORGANIZATIONID, MODIFYEMPLOYEEID, ISDELETED, ROWVERSION
              , USEDCODEID, PRICEPERDAY, AIRCONDITIONFEE, ITEMID, ISADDFLAG, TYPECODEID, RELEASEDATETIME, REMARK, ISBOOKINGFLAG, ISHIGHQUALITYFLAG, GETWARMTHFEE, GETWARMTHITEMID
              , AIRCONDITIONITEMID, SENDMEDICINEGROUPTYPECODEID, BASEMEDICINEORGANIZATIONID, CHANGEREASONCODEID, GROUPNO, ISTURNOVER, ISHIDE, SICKBEDKINDCODEID, LOCATIONID
              , USEDCODECHANGEDON, ISSPECIAL, ISDAYTIME, OBLIGATECODEID, EMERGENCYFLAG
              , N_NO, N_NAME, N_ORDINAL, N_GENDERCODEID, N_ORGANIZATIONID, N_MODIFYEMPLOYEEID, N_ISDELETED, N_ROWVERSION
              , N_USEDCODEID, N_PRICEPERDAY, N_AIRCONDITIONFEE, N_ITEMID, N_ISADDFLAG, N_TYPECODEID, N_RELEASEDATETIME, N_REMARK, N_ISBOOKINGFLAG, N_ISHIGHQUALITYFLAG, N_GETWARMTHFEE, N_GETWARMTHITEMID
              , N_AIRCONDITIONITEMID, N_SENDMEDICINEGROUPTYPECODEID, N_BASEMEDICINEORGANIZATIONID, N_CHANGEREASONCODEID, N_GROUPNO, N_ISTURNOVER, N_ISHIDE, N_SICKBEDKINDCODEID, N_LOCATIONID
              , N_USEDCODECHANGEDON, N_ISSPECIAL, N_ISDAYTIME, N_OBLIGATECODEID, N_EMERGENCYFLAG
              , USERNAME, IP)
           VALUES(:old.SICKBEDID, :old.NO, :old.NAME, :old.ORDINAL, :old.GENDERCODEID, :old.ORGANIZATIONID, :old.MODIFYEMPLOYEEID, :old.ISDELETED, :old.ROWVERSION
              , :old.USEDCODEID, :old.PRICEPERDAY, :old.AIRCONDITIONFEE, :old.ITEMID, :old.ISADDFLAG, :old.TYPECODEID, :old.RELEASEDATETIME, :old.REMARK, :old.ISBOOKINGFLAG, :old.ISHIGHQUALITYFLAG, :old.GETWARMTHFEE, :old.GETWARMTHITEMID
              , :old.AIRCONDITIONITEMID, :old.SENDMEDICINEGROUPTYPECODEID, :old.BASEMEDICINEORGANIZATIONID, :old.CHANGEREASONCODEID, :old.GROUPNO, :old.ISTURNOVER, :old.ISHIDE, :old.SICKBEDKINDCODEID, :old.LOCATIONID
              , :old.USEDCODECHANGEDON, :old.ISSPECIAL, :old.ISDAYTIME, :old.OBLIGATECODEID, :old.EMERGENCYFLAG
              , :new.NO, :new.NAME, :new.ORDINAL, :new.GENDERCODEID, :new.ORGANIZATIONID, :new.MODIFYEMPLOYEEID, :new.ISDELETED, :new.ROWVERSION
              , :new.USEDCODEID, :new.PRICEPERDAY, :new.AIRCONDITIONFEE, :new.ITEMID, :new.ISADDFLAG, :new.TYPECODEID, :new.RELEASEDATETIME, :new.REMARK, :new.ISBOOKINGFLAG, :new.ISHIGHQUALITYFLAG, :new.GETWARMTHFEE, :new.GETWARMTHITEMID
              , :new.AIRCONDITIONITEMID, :new.SENDMEDICINEGROUPTYPECODEID, :new.BASEMEDICINEORGANIZATIONID, :new.CHANGEREASONCODEID, :new.GROUPNO, :new.ISTURNOVER, :new.ISHIDE, :new.SICKBEDKINDCODEID, :new.LOCATIONID
              , :new.USEDCODECHANGEDON, :new.ISSPECIAL, :new.ISDAYTIME, :new.OBLIGATECODEID, :new.EMERGENCYFLAG
              , v_user, v_ip);
        END IF;
     WHEN DELETING THEN
          RAISE_APPLICATION_ERROR(-20003, '不能物理删除床位表记录');
   END CASE;
END;

由于UPDATE  CONCEPT.SICKEBD表时，会通过触发器插入跟踪表数据，跟踪表的字段是不允许空的，如下获取数据库上下文数据在SQL Developer上运行得到正确的值，而在作业获取IP地址则为空（导致UPDATE语句的作业失败）：
SELECT sys_context('userenv', 'IP_ADDRESS'), sys_context('userenv', 'SESSION_USER') INTO v_ip, v_user FROM dual;
所以上述语句需改为：SELECT NVL(sys_context('userenv', 'IP_ADDRESS'), NVL(sys_context('USERENV','HOST'),'JOB_IP')), sys_context('userenv', 'SESSION_USER') INTO v_ip, v_user FROM dual;

其次，关于分布式事务在SQL Developer上能正常运行，而在SQL Developer正常执行，而在作业里面就不能用分布式事务（只能提交Oracle部分，再提交SQL Server链接数据库部分），譬如下面存储过程

create or replace PROCEDURE "APPS"."JOBCORRECTSICKBEDUSEDCODE"
AS
  v_sickbedids VARCHAR2(8000);
BEGIN
  DELETE CONCEPT.SICKBEDMODIFIEDTRACE WHERE TRUNC(MODIFIEDON) < TRUNC(SYSDATE) - 30;
  
  --更新床位状态
  INSERT INTO "CONCEPT"."TBL_ORGANIZATIONSICKBEDTOMODIFY"(ORGANIZATIONID, SICKBEDID)
  select s.ORGANIZATIONID, s.SICKBEDID from concept.sickbed s, entity.organization o
  where s.ORGANIZATIONID=o.ORGANIZATIONID and s.ISDELETED=0 and o.ISDELETED=0 and s.USEDCODEID=1 -- and s.ORGANIZATIONID not in (430,1606) 
  and s.SICKBEDID>0 and s.SICKBEDID not in (select sickbedid from prpa.encounter e
  where e.isdeleted=0 and e.encounterkindcodeid=1 and e.sickbedid>0 --and e.partitionflag=0
  and (e.statuscodeid=1 or (e.statuscodeid=3 and trunc(e.dischargeon)>trunc(sysdate))));

  UPDATE CONCEPT.SICKBED t
  SET t.UsedCodeId = 0
  WHERE t.SICKBEDID IN ( SELECT SICKBEDID FROM CONCEPT.TBL_ORGANIZATIONSICKBEDTOMODIFY WHERE ISMODIFIED=0 );

  SELECT wmsys.wm_concat(ii.EXTENSION) INTO v_sickbedids FROM CONCEPT.TBL_ORGANIZATIONSICKBEDTOMODIFY s, CONCEPT.SICKBEDII ii WHERE s.SICKBEDID=ii.SICKBEDID and s.ISMODIFIED=0;
  --dbms_output.put_line ('v_sickbedids:' || v_sickbedids);

  IF v_sickbedids IS NOT NULL THEN
    DBO.pHIS30_ReleaseSickBedStatus@IP(-1, v_sickbedids);
  END if;

  UPDATE CONCEPT.TBL_ORGANIZATIONSICKBEDTOMODIFY SET ISMODIFIED=1, MODIFIEDON=SYSDATE WHERE ISMODIFIED=0 ;
  COMMIT;
END;

需要改为非分布式事务来处理（作业用时）：

create or replace PROCEDURE "APPS"."JOBCORRECTSICKBEDUSEDCODE"
AS
  v_sickbedids VARCHAR2(8000);
BEGIN
  DELETE CONCEPT.SICKBEDMODIFIEDTRACE WHERE TRUNC(MODIFIEDON) < TRUNC(SYSDATE) - 30;
  
  --更新床位状态
  INSERT INTO "CONCEPT"."TBL_ORGANIZATIONSICKBEDTOMODIFY"(ORGANIZATIONID, SICKBEDID)
  select s.ORGANIZATIONID, s.SICKBEDID from concept.sickbed s, entity.organization o
  where s.ORGANIZATIONID=o.ORGANIZATIONID and s.ISDELETED=0 and o.ISDELETED=0 and s.USEDCODEID=1 -- and s.ORGANIZATIONID not in (430,1606) 
  and s.SICKBEDID>0 and s.SICKBEDID not in (select sickbedid from prpa.encounter e
  where e.isdeleted=0 and e.encounterkindcodeid=1 and e.sickbedid>0 --and e.partitionflag=0
  and (e.statuscodeid=1 or (e.statuscodeid=3 and trunc(e.dischargeon)>trunc(sysdate))));

  UPDATE CONCEPT.SICKBED t
  SET t.UsedCodeId = 0
  WHERE t.SICKBEDID IN ( SELECT SICKBEDID FROM CONCEPT.TBL_ORGANIZATIONSICKBEDTOMODIFY WHERE ISMODIFIED=0 );

  SELECT wmsys.wm_concat(ii.EXTENSION) INTO v_sickbedids FROM CONCEPT.TBL_ORGANIZATIONSICKBEDTOMODIFY s, CONCEPT.SICKBEDII ii WHERE s.SICKBEDID=ii.SICKBEDID and s.ISMODIFIED=0;
  --dbms_output.put_line ('v_sickbedids:' || v_sickbedids);

  UPDATE CONCEPT.TBL_ORGANIZATIONSICKBEDTOMODIFY SET ISMODIFIED=1, MODIFIEDON=SYSDATE WHERE ISMODIFIED=0 ;
  COMMIT;

  IF v_sickbedids IS NOT NULL THEN
    DBO.pHIS30_ReleaseSickBedStatus@IP(-1, v_sickbedids);
  END if;
  COMMIT;
END;