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

[fb777]

很奇怪!

其他存储过程都好好的.

唯独这个存储过程, 一编译, 或者运行, 就崩溃了.

在10g上面什么事情都没有了.

10g是测试机的, 9i是生产机的, 因为是需要发布N个省份的系统, 所以只能这样发布.

其他存储过程好好的. 就一个存储过程.

而且更奇怪的是, 故意写一些错误的代码进去. 它就能编译, 找出错误.

但是没有错误的情况下反而编译, 运行不了, 我快疯了

[ACMAIN_CHM]

你的代码是什么，应该可以啊。

[fb777]

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;

[fb777]

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;

[fb777]

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;