ORA-03113：end-of-file on communication channel解决方法

SELECT *
FROM (SELECT *
    FROM (SELECT ROW_NUMBER () OVER (ORDER BY NULL) AS row_num, A .*
      FROM (SELECT p.proc_inst_id_ inst_id,p.name_ inst_name,overtimeProcInst.TEMPL_NAME,p.fbs_state,(SELECT u.username FROM fbs_user u WHERE u.id = p.start_user_id_) start_user,p.start_time_ start_time,overtimeProcInst.templ_limit_time,
             (NVL((select sum(do_time) from FBS_ZXSJ where begin_time >= p.START_TIME_ and end_time <= nvl(p.End_Time_, sysdate)),0) 
            +nvl((select (end_time -CAST(p.START_TIME_ AS DATE)) * 24 from FBS_ZXSJ where begin_time < p.START_TIME_ and end_time > p.START_TIME_ AND end_time<=NVL(p.End_Time_,SYSDATE)),0)
            +nvl((select (CAST(nvl(p.End_Time_, sysdate) AS DATE) - begin_time) * 24 from FBS_ZXSJ where begin_time>=p.Start_Time_ AND begin_time < nvl(p.End_Time_, sysdate) and end_time > nvl(p.End_Time_, sysdate)), 0)
            +NVL((SELECT (CAST(NVL(p.End_Time_, SYSDATE) AS DATE)-CAST(p.Start_Time_ AS DATE))*24 FROM fbs_zxsj z WHERE z.begin_time<p.Start_Time_ AND z.end_time>p.End_Time_),0)
             ) templ_cost_time
          FROM act_hi_procinst p,
           (select distinct procinstid,TEMPL_NAME,templ_limit_time
            from (select task.proc_inst_id_ procinstid,templ_node.node_limit_time,templ_node.templ_limit_time,templ_node.TEMPL_NAME,
                 (NVL((select sum(do_time) from FBS_ZXSJ where begin_time >= task.start_time_ and end_time <= nvl(task.end_time_, sysdate)),0) 
                +nvl((select (end_time - CAST(task.start_time_ AS DATE)) * 24 from FBS_ZXSJ where begin_time < task.start_time_ and end_time > task.start_time_ AND end_time<= NVL(task.end_time_,sysdate)), 0)
                +nvl((select (nvl(CAST(task.end_time_ AS DATE), sysdate) - begin_time) * 24 from FBS_ZXSJ where begin_time>=task.start_time_ AND begin_time < nvl(task.end_time_, sysdate) and end_time > nvl(task.end_time_, sysdate)),0)
                +nvl((SELECT (CAST(NVL(task.end_time_,SYSDATE) AS DATE)-CAST(task.start_time_ AS DATE))*24 FROM fbs_zxsj z WHERE z.begin_time<task.start_time_ AND z.end_time>NVL(task.end_time_, SYSDATE)),0)
                ) node_cost_time
              from ACT_HI_TASKINST task,
                (select templ.DEPLOY_ID,templ.WFE_TEMPL_NAME TEMPL_NAME,node.Wfe_Node_Id,node.Limittime node_limit_time,templ.WFE_TEMPL_LIMITTIME templ_limit_time
                 from FBS_WFE_TEMPL templ, FBS_WFE_TEMPL_Node node
                 where templ.ID = node.Wfe_Templ_Id) templ_node
              where task.proc_def_id_ = templ_node.deploy_id and task.task_def_key_ = templ_node.wfe_node_id)
            where node_cost_time > node_limit_time) overtimeProcInst
          WHERE p.proc_inst_id_ = overtimeProcInst.procinstid and 1=1 and NAME_ like '%设方案申请流程2%' and TEMPL_NAME like '%程模板1%' and START_TIME_ >= to_date('2016-01-12 11:03:30', 'YYYY-MM-DD HH24:MI:SS') and START_TIME_ <= to_date('2016-01-13 11:03:34', 'YYYY-MM-DD HH24:MI:SS') 
          ORDER BY start_time_) A) b
    WHERE b.row_num <= 10) c
WHERE c.row_num > 0;

直接执行以上SQL会报错：

该问题可通过视图来解决。建视图：

create or replace view ProcInst_With_OvertimeTask as
       SELECT p.proc_inst_id_ inst_id,p.name_ inst_name,overtimeProcInst.TEMPL_NAME,p.fbs_state,(SELECT u.username FROM fbs_user u WHERE u.id = p.start_user_id_) start_user,p.start_time_ start_time,overtimeProcInst.templ_limit_time,
             (NVL((select sum(do_time) from FBS_ZXSJ where begin_time >= p.START_TIME_ and end_time <= nvl(p.End_Time_, sysdate)),0) 
              +nvl((select (end_time -CAST(p.START_TIME_ AS DATE)) * 24 from FBS_ZXSJ where begin_time < p.START_TIME_ and end_time > p.START_TIME_ AND end_time<=NVL(p.End_Time_,SYSDATE)),0)
              +nvl((select (CAST(nvl(p.End_Time_, sysdate) AS DATE) - begin_time) * 24 from FBS_ZXSJ where begin_time>=p.Start_Time_ AND begin_time < nvl(p.End_Time_, sysdate) and end_time > nvl(p.End_Time_, sysdate)), 0)
              +NVL((SELECT (CAST(NVL(p.End_Time_, SYSDATE) AS DATE)-CAST(p.Start_Time_ AS DATE))*24 FROM fbs_zxsj z WHERE z.begin_time<p.Start_Time_ AND z.end_time>p.End_Time_),0)
             ) templ_cost_time
        FROM act_hi_procinst p,
           (select distinct procinstid,TEMPL_NAME,templ_limit_time
            from (select task.proc_inst_id_ procinstid,templ_node.node_limit_time,templ_node.templ_limit_time,templ_node.TEMPL_NAME,
                     (NVL((select sum(do_time) from FBS_ZXSJ where begin_time >= task.start_time_ and end_time <= nvl(task.end_time_, sysdate)),0) 
                      +nvl((select (end_time - CAST(task.start_time_ AS DATE)) * 24 from FBS_ZXSJ where begin_time < task.start_time_ and end_time > task.start_time_ AND end_time<= NVL(task.end_time_,sysdate)), 0)
                      +nvl((select (nvl(CAST(task.end_time_ AS DATE), sysdate) - begin_time) * 24 from FBS_ZXSJ where begin_time>=task.start_time_ AND begin_time < nvl(task.end_time_, sysdate) and end_time > nvl(task.end_time_, sysdate)),0)
                      +nvl((SELECT (CAST(NVL(task.end_time_,SYSDATE) AS DATE)-CAST(task.start_time_ AS DATE))*24 FROM fbs_zxsj z WHERE z.begin_time<task.start_time_ AND z.end_time>NVL(task.end_time_, SYSDATE)),0)
                      ) node_cost_time
                from ACT_HI_TASKINST task,
                    (select templ.DEPLOY_ID,templ.WFE_TEMPL_NAME TEMPL_NAME,node.Wfe_Node_Id,node.Limittime node_limit_time,templ.WFE_TEMPL_LIMITTIME templ_limit_time
                     from FBS_WFE_TEMPL templ, FBS_WFE_TEMPL_Node node
                     where templ.ID = node.Wfe_Templ_Id) templ_node
                where task.proc_def_id_ = templ_node.deploy_id and task.task_def_key_ = templ_node.wfe_node_id)
            where node_cost_time > node_limit_time) overtimeProcInst
        WHERE p.proc_inst_id_ = overtimeProcInst.procinstid

然后查询视图：

SELECT *
FROM (SELECT *
    FROM (SELECT ROW_NUMBER () OVER (ORDER BY NULL) AS row_num, A .*
      FROM (SELECT * 
            FROM ProcInst_With_OvertimeTask 
            WHERE inst_name like '%设方案申请流程2%' and TEMPL_NAME like '%程模板1%' and start_time >= to_date('2016-01-12 11:03:30', 'YYYY-MM-DD HH24:MI:SS') and start_time <= to_date('2016-01-13 11:03:34', 'YYYY-MM-DD HH24:MI:SS') 
            ORDER BY start_time) A) b
    WHERE b.row_num <= 10) c
WHERE c.row_num > 0;