10g的存储过程拿到9i上面编译 运行不了.

Question

很奇怪!其他存储过程都好好的.唯独这个存储过程, 一编译, 或者运行, 就崩溃了. 在10g上面什么事情都没有了. 10g是测试机的, 9i是生产机的, 因为是需要发布N个省份的系统, 所以只能这样发布.其他存储过程好好的. 就一个存储过程.而且更奇怪的是, 故意写一些错误的代码进去. 它就能编译, 找出错误.但是没有错误的情况下反而编译, 运行不了, 我快疯了

Answer 1

create or replace package PKG_ODS_CM_TO_BI
as
  -- 作者: 冯兵
  -- 时间: 2009-09-03
  -- 功能: 从合同管理系统的ABCD中间表抽取数据到BI1数据中心  -- 定义动态游标
  TYPE REF_CURSOR IS REF CURSOR;   /* 写入抽取日志表
      REPORT_LOG_ID  上报数据的日志记录ID   本次抽取的数据，是属于哪一条上报日志记录表的记录
      UNIT_CODE  省公司编号 符合全国统一的规范
      BELONG_YM  数据所属日期  必须与对应的A数一样
      OPERATE_TYPE  操作类型  正常抽取数据/紧急抽取数据/展现数据/确认数据展现/确认数据修改
      ETL_TYPE  数据抽取方式  ALL：全量上报/ADD：增量上报/HURRY：紧急上报
      ETL_START_TIME  本次抽取开始时间点   上报方式为增量/紧急时必填
      ETL_MAX_TIME  本次抽取的数据中的最大的最后更新时间
    */
    procedure P_ETL_ETL_LOG
    ( p_report_log_id varchar2,
      p_pro_code varchar2,
      p_user varchar2,
      p_dblink varchar2,
      p_belong_ym date,
      p_opertae_type varchar2,
      p_etl_type varchar2,
      p_etl_start_time date,
      p_etl_max_time date
    );  /* 从合同管理的中间表到TODS    p_etl_type  ETL方式, ALL: 全量,  ADD: 增量
    p_pro_code 省公司编号  A+省公司编号 为BI1内该省对应的A表,同理BD
    p_user     该省合同管理系统中间表所属的用户名
    p_dblink   该省合同管理系统的数据库的DBLINK
    p_etl_start_time  增量/紧急抽取开始时间点
  */
  procedure P_ETL_TODS_ABCD(
     p_log_up_id varchar2,
     p_belong_ym date,
     p_etl_type varchar2,
     p_pro_code varchar2,
     p_user varchar2,
     p_dblink varchar2,
     p_etl_start_time date
  );  /* 从TODS到ODS    p_etl_type  ETL方式, ALL: 全量,  ADD: 增量
    p_pro_code 省公司编号  A+省公司编号 为BI1内该省对应的A表,同理BD
    p_etl_start_time  增量/紧急抽取开始时间点
  */
  procedure P_ETL_ODS_ABCD( p_belong_ym date,p_etl_type varchar2, p_pro_code varchar2); /* 从ODS到汇总中间表
    总表会比分表多  省公司编号(根据参数得到), BI主键ID  两个字段, 都是自己生成的
    ABCD表自动生成一个只属于BI的主键, BCD表会多一个关联A表的BI主键的外键    p_etl_type  ETL方式, ALL: 全量,  ADD: 增量
    p_pro_code 省公司编号  A+省公司编号 为BI1内该省对应的A表,同理BD
    p_etl_start_time  增量/紧急抽取开始时间点
  */
  procedure P_ETL_TOTAL_ABCD( p_belong_ym date,p_etl_type varchar2, p_pro_code varchar2);  -- 更新BCD 表之中的 bi_a_res_id, 在上面插入的时候由于没有 commit 所以无法关联插入
  procedure UPDATE_BCD_BI_A_RES_ID(p_pro_code varchar2);  /* 总体控制程序 */
  procedure P_ETL_ABCD_MAIN;  /* 紧急抽取控制程序  1小时运行一次
     不会写进 P_ETL_ABCD_MAIN 里面, 直接新建一个一小时运行一次的JOB来运行
  */
  procedure P_ETL_ABCD_HURRY;end PKG_ODS_CM_TO_BI;

Answer 2

create or replace package body PKG_ODS_CM_TO_BI
as
  -- 作者: 冯兵
  -- 时间: 2009-09-03
  -- 功能: 从合同管理系统的ABCD中间表抽取数据到BI1数据中心
  
  /* 写入抽取日志表
    REPORT_LOG_ID  上报数据的日志记录ID   本次抽取的数据，是属于哪一条上报日志记录表的记录
    p_pro_code UNIT_CODE  省公司编号 符合全国统一的规范
    BELONG_YM  数据所属日期  必须与对应的A数一样
    OPERATE_TYPE  操作类型  正常抽取数据/紧急抽取数据/展现数据/确认数据展现/确认数据修改
    ETL_TYPE  数据抽取方式  ALL：全量上报/ADD：增量上报/HURRY：紧急上报
    ETL_START_TIME  本次抽取开始时间点   上报方式为增量/紧急时必填 
    ETL_MAX_TIME  本次抽取的数据中的最大的最后更新时间
  */
  procedure P_ETL_ETL_LOG
  ( p_report_log_id varchar2,
    p_pro_code varchar2, 
    p_user varchar2, 
    p_dblink varchar2, 
    p_belong_ym date, 
    p_opertae_type varchar2,
    p_etl_type varchar2,
    p_etl_start_time date,  
    p_etl_max_time date
  )
  is
    v_insert_sql     varchar2(1000);
    
    v_belong_ym      varchar2(20);
    v_etl_start_time varchar2(20);
    v_etl_max_time   varchar2(20);
  begin
    BICODE.PKG_ETL_CONTROLLER.P_ETL_WRITE_LOG('一般','插入抽取日志记录表的存储过程 P_ETL_WRITE_LOG 开始','TODS');
    
    v_belong_ym := to_char(p_belong_ym,'yyyy-mm-dd');
    
    if p_etl_start_time is not null then 
       v_etl_start_time := to_char(p_etl_start_time,'yyyy-mm-dd hh24:mi:ss') ;
    end if;
    
    if p_etl_max_time is not null then 
       v_etl_max_time := to_char(p_etl_max_time,'yyyy-mm-dd hh24:mi:ss') ;
    end if;
    
    v_insert_sql := 'insert into ods.CCSBI_LOG_ETL_'|| p_pro_code ||'
    values
    (
      sys_guid(),
      '''|| p_report_log_id || ''',
      sysdate,
      '''|| p_pro_code || ''',
      to_date('''||v_belong_ym||''',''yyyy-mm-dd''),
      '''|| p_opertae_type || ''',
      '''|| p_etl_type || ''',
      to_date('''||v_etl_start_time||''',''yyyy-mm-dd hh24:mi:ss''),
      to_date('''||v_etl_max_time||''',''yyyy-mm-dd hh24:mi:ss''),
      ( select sum( RMB_AMOUNT - TOTAL_FINA_INCOME ) from tods.CCSBI_A_CONTRACT_'|| p_pro_code ||' ),
      ( select count(*) from tods.CCSBI_A_CONTRACT_'|| p_pro_code ||' ),
      ( select sum( WORK_AMOUNT ) from tods.CCSBI_B_WORKLOAD_'|| p_pro_code ||' ),
      ( select count(*) from tods.CCSBI_B_WORKLOAD_'|| p_pro_code ||' ),
      ( select sum( GATHERING_AMOUNT ) from tods.CCSBI_C_GATHERING_'|| p_pro_code ||' ),
      ( select count(*) from tods.CCSBI_C_GATHERING_'|| p_pro_code ||' ),
      ( select sum( INCOME_AMOUNT ) from tods.CCSBI_D_INCOME_'|| p_pro_code ||' ),
      ( select count(*) from tods.CCSBI_D_INCOME_'|| p_pro_code ||' )
    )';
    
    execute immediate v_insert_sql;    commit;
    
    -- 将抽取日志写入 CM 的抽取日志记录表
    v_insert_sql := 'insert into  '|| p_user ||'.CCSBI_LOG_ETL@'|| p_dblink ||'
    values
    (
      sys_guid(),
      '''|| p_report_log_id || ''',
      sysdate,
      '''|| p_pro_code || ''',
      to_date('''||v_belong_ym||''',''yyyy-mm-dd''),
      '''|| p_opertae_type || ''',
      '''|| p_etl_type || ''',
      to_date('''||v_etl_start_time||''',''yyyy-mm-dd hh24:mi:ss''),
      to_date('''||v_etl_max_time||''',''yyyy-mm-dd hh24:mi:ss''),
      ( select sum( RMB_AMOUNT - TOTAL_FINA_INCOME ) from tods.CCSBI_A_CONTRACT_'|| p_pro_code ||' ),
      ( select count(*) from tods.CCSBI_A_CONTRACT_'|| p_pro_code ||' ),
      ( select sum( WORK_AMOUNT ) from tods.CCSBI_B_WORKLOAD_'|| p_pro_code ||' ),
      ( select count(*) from tods.CCSBI_B_WORKLOAD_'|| p_pro_code ||' ),
      ( select sum( GATHERING_AMOUNT ) from tods.CCSBI_C_GATHERING_'|| p_pro_code ||' ),
      ( select count(*) from tods.CCSBI_C_GATHERING_'|| p_pro_code ||' ),
      ( select sum( INCOME_AMOUNT ) from tods.CCSBI_D_INCOME_'|| p_pro_code ||' ),
      ( select count(*) from tods.CCSBI_D_INCOME_'|| p_pro_code ||' )
    )';
    
    execute immediate v_insert_sql;
    
    commit;
    
    BICODE.PKG_ETL_CONTROLLER.P_ETL_WRITE_LOG('一般','插入抽取日志记录表的存储过程 P_ETL_WRITE_LOG 结束','TODS');
    
    exception
     when others then
         rollback;
         BICODE.PKG_ETL_CONTROLLER.P_ETL_WRITE_LOG('异常','插入抽取日志记录表的存储过程 P_ETL_WRITE_LOG 异常.原因: '|| sqlcode || sqlerrm,'TODS');      
           
  end P_ETL_ETL_LOG;

Answer 3

procedure P_ETL_TODS_ABCD( 
     p_log_up_id varchar2,
     p_belong_ym date,
     p_etl_type varchar2, 
     p_pro_code varchar2, 
     p_user varchar2, 
     p_dblink varchar2, 
     p_etl_start_time date
  )
  is
    v_select_sql      varchar2(1000);
    
    v_del_sql_a       varchar2(1000);
    v_del_sql_b       varchar2(1000);
    v_del_sql_c       varchar2(1000);
    v_del_sql_d       varchar2(1000);
    v_del_sql_log     varchar2(1000);
    
    v_inse_sql_a      varchar2(1000);
    v_inse_sql_b      varchar2(1000);
    v_inse_sql_c      varchar2(1000);
    v_inse_sql_d      varchar2(1000);
    v_inse_sql_log    varchar2(1000);
         
    v_temp_cur        REF_CURSOR;
    
    v_etl_max_time    date; -- 本次增量/紧急抽取中最大的最后更新时间
    v_tmp_max_time    date; -- 用来临时存放以作比较
    
    v_etl_start_time varchar2(20);
    
    v_operate_type  varchar2(20);
  begin
     BICODE.PKG_ETL_CONTROLLER.P_ETL_WRITE_LOG('一般','抽取CM系统中间表至TODS的存储过程 P_ETL_TODS_ABCD 开始','TODS');     if p_etl_start_time is not null then 
       v_etl_start_time := to_char(p_etl_start_time,'yyyy-mm-dd hh24:mi:ss') ;
     end if;
    
     -- 把上一次抽取过来的数据放入历史表
     v_inse_sql_a:='
       insert into tods.CCSBI_A_CONTRACT_'|| p_pro_code ||'_his
       select * from tods.CCSBI_A_CONTRACT_'|| p_pro_code ||'';
           
     v_inse_sql_b:='
       insert into tods.CCSBI_B_WORKLOAD_'|| p_pro_code ||'_his
       select * from tods.CCSBI_B_WORKLOAD_'|| p_pro_code ||'';
           
     v_inse_sql_c:='
       insert into tods.CCSBI_C_GATHERING_'|| p_pro_code ||'_his
       select * from tods.CCSBI_C_GATHERING_'|| p_pro_code ||'';
             
     v_inse_sql_d:='
       insert into tods.CCSBI_D_INCOME_'|| p_pro_code ||'_his
       select * from tods.CCSBI_D_INCOME_'|| p_pro_code ||'';
             
     execute immediate v_inse_sql_a;
     execute immediate v_inse_sql_b;
     execute immediate v_inse_sql_c;
     execute immediate v_inse_sql_d;
           
     commit;
           
     --  并清空TODS
     v_del_sql_a := 'truncate table tods.CCSBI_A_CONTRACT_'||p_pro_code;
     v_del_sql_b := 'truncate table tods.CCSBI_B_WORKLOAD_'||p_pro_code;
     v_del_sql_c := 'truncate table tods.CCSBI_C_GATHERING_'||p_pro_code;
     v_del_sql_d := 'truncate table tods.CCSBI_D_INCOME_'||p_pro_code;
     v_del_sql_log := 'truncate table tods.CCSBI_LOG_UP_'||p_pro_code;     execute immediate v_del_sql_a;
     execute immediate v_del_sql_b;
     execute immediate v_del_sql_c;
     execute immediate v_del_sql_d;
     execute immediate v_del_sql_log;
           
     commit;
         
     -- 这里要跟ODS的上报日志表进行比较, 因为TODS为了节省插入时间, 每次抽取前皆清空
     -- 为了效率,直接用时间进行比较, 用主键ID来进行比较的话,时间上可能会比较恐怖
     v_inse_sql_log:='
       insert into tods.CCSBI_LOG_UP_'||p_pro_code||'
       select * from '|| p_user ||'.CCSBI_LOG_UP@'|| p_dblink ||' cml
       where cml.LOG_DATE > (select max(LOG_DATE) from ods.CCSBI_LOG_UP_'|| p_pro_code ||' bil )
          or (select max(LOG_DATE) from ods.CCSBI_LOG_UP_'|| p_pro_code ||' bil ) is null ';
          
     execute immediate v_inse_sql_log;
         
     commit;
               
     -- 全量抽取
     if p_etl_type = 'ALL' then        -- 插入新的数据
       v_inse_sql_a:='
         insert into tods.CCSBI_A_CONTRACT_'|| p_pro_code ||'
         select * from '|| p_user ||'.CCSBI_A_CONTRACT@'|| p_dblink ||'';
           
       v_inse_sql_b:='
         insert into tods.CCSBI_B_WORKLOAD_'|| p_pro_code ||'
         select * from '|| p_user ||'.CCSBI_B_WORKLOAD@'|| p_dblink ||'';
           
       v_inse_sql_c:='
         insert into tods.CCSBI_C_GATHERING_'|| p_pro_code ||'
         select * from '|| p_user ||'.CCSBI_C_GATHERING@'|| p_dblink ||'';
             
       v_inse_sql_d:='
         insert into tods.CCSBI_D_INCOME_'||p_pro_code||'
         select * from '|| p_user ||'.CCSBI_D_INCOME@'|| p_dblink ||'';       execute immediate v_inse_sql_a;
       execute immediate v_inse_sql_b;
       execute immediate v_inse_sql_c;
       execute immediate v_inse_sql_d;
           
       commit;
           
     -- 增量抽取 或者 紧急抽取
     else
       -- 插入新的数据
       v_inse_sql_a:='
         insert into tods.CCSBI_A_CONTRACT_'|| p_pro_code ||'
         select * from '|| p_user ||'.CCSBI_A_CONTRACT@'|| p_dblink ||' cma
         where cma.last_update_time>= to_date('''||v_etl_start_time||''',''yyyy-mm-dd hh24:mi:ss'') ';
           
       v_inse_sql_b:='
         insert into tods.CCSBI_B_WORKLOAD_'|| p_pro_code ||'
         select * from '|| p_user ||'.CCSBI_B_WORKLOAD@'|| p_dblink ||' cmb
         where cmb.last_update_time>= to_date('''||v_etl_start_time||''',''yyyy-mm-dd hh24:mi:ss'') ';
           
       v_inse_sql_c:='
         insert into tods.CCSBI_C_GATHERING_'|| p_pro_code ||'
         select * from '|| p_user ||'.CCSBI_C_GATHERING@'|| p_dblink ||' cmc
         where cmc.last_update_time>= to_date('''||v_etl_start_time||''',''yyyy-mm-dd hh24:mi:ss'') ';
             
       v_inse_sql_d:='
         insert into tods.CCSBI_D_INCOME_'||p_pro_code||'
         select * from '|| p_user ||'.CCSBI_D_INCOME@'|| p_dblink ||' cmd
         where cmd.last_update_time>= to_date('''||v_etl_start_time||''',''yyyy-mm-dd hh24:mi:ss'') ';
             
       execute immediate v_inse_sql_a;
       execute immediate v_inse_sql_b;
       execute immediate v_inse_sql_c;
       execute immediate v_inse_sql_d;       commit;
       
     end if;
    
    -- 无论是增量, 还是全量, 都应该插入这个时间
    -- v_etl_max_time = min( max(a.last_update_time), max(b.last_update_time), max(c.last_update_time), max(last_update_time) )
    v_select_sql := 'select max(last_update_time) from tods.CCSBI_A_CONTRACT_'|| p_pro_code ||'';
          
    open v_temp_cur for v_select_sql;
    fetch v_temp_cur into v_tmp_max_time;
    close v_temp_cur;
               
    if v_etl_max_time is null or v_etl_max_time > v_tmp_max_time then
      v_etl_max_time := v_tmp_max_time;
    end if;    v_select_sql := 'select max(last_update_time) from tods.CCSBI_B_WORKLOAD_'|| p_pro_code ||'';
          
    open v_temp_cur for v_select_sql;
    fetch v_temp_cur into v_tmp_max_time;
    close v_temp_cur;
               
    if v_etl_max_time is null or v_etl_max_time > v_tmp_max_time then
      v_etl_max_time := v_tmp_max_time;
    end if;    v_select_sql := 'select max(last_update_time) from tods.CCSBI_C_GATHERING_'|| p_pro_code ||'';
          
    open v_temp_cur for v_select_sql;
    fetch v_temp_cur into v_tmp_max_time;
    close v_temp_cur;
               
    if v_etl_max_time is null or v_etl_max_time > v_tmp_max_time then
      v_etl_max_time := v_tmp_max_time;
    end if;    v_select_sql := 'select max(last_update_time) from tods.CCSBI_D_INCOME_'|| p_pro_code ||'';
          
    open v_temp_cur for v_select_sql;
    fetch v_temp_cur into v_tmp_max_time;
    close v_temp_cur;
               
    if v_etl_max_time is null or v_etl_max_time > v_tmp_max_time then
      v_etl_max_time := v_tmp_max_time;
    end if;
     
    select decode(p_etl_type,'HURRY','紧急抽取','正常抽取') into v_operate_type from dual ; 
      
    -- 写入本地的抽取日志记录表与及 回写到CM系统的抽取日志记录表
    P_ETL_ETL_LOG
    ( p_log_up_id,
      p_pro_code,
      p_user,
      p_dblink,
      p_belong_ym, 
      v_operate_type,
      p_etl_type,
      p_etl_start_time,  
      v_etl_max_time
    );
    
    commit;
    
    BICODE.PKG_ETL_CONTROLLER.P_ETL_WRITE_LOG('一般','抽取CM系统中间表至TODS的存储过程 P_ETL_TODS_ABCD 结束','TODS');
    
    -- 异常处理
    exception
      when others then
        if v_temp_cur%isopen then
          close v_temp_cur;
        end if;
        rollback;
        BICODE.PKG_ETL_CONTROLLER.P_ETL_WRITE_LOG('异常','抽取CM系统中间表至TODS的存储过程 P_ETL_TODS_ABCD 异常.原因: '|| sqlcode || sqlerrm,'TODS'); 
        
  end P_ETL_TODS_ABCD;