这个是日志里面报错的:
[2011-11-03 03:44:17] Error:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "WZ_NOK_OMCR4_UPLOAD.NC_UPLOAD_BTS_OTHER_INFO", line 31
ORA-06512: at line 1其中报错的sql语句为:
SELECT obj_inst INTO v_bts_inst FROM syn_dic_obj_inst
WHERE dist_id = p_dist_id AND net_id = p_net_id AND prod_id = p_prod_id AND
class_id = p_bts_class_in_nmc AND obj_name like '%'|| v_cell_id;我把syn_dic_obj_inst这个表的数据重复查过了,没有发现重复,我百度搜索,有人说是游标里面有重复,我也将处理加在了游标的下面,在线等人解决!
下面为全部sql:
CREATE OR REPLACE PROCEDURE WZ_NOK_OMCR4_UPLOAD."NC_UPLOAD_BTS_OTHER_INFO" /************************************************************/
/** 生成BTS的OBJ_INST,并获得其它配置信息 **/
/************************************************************/
(p_dist_id VARCHAR2, p_net_id VARCHAR2, p_prod_id VARCHAR2, p_bcf_class_in_omc NUMBER, p_bts_class_in_nmc VARCHAR2,
p_prod_name VARCHAR2) IS
v_lac_id nok_bts_con_temp.lac_id%TYPE;
v_cell_id nok_bts_con_temp.cell_id%TYPE;
v_bts_inst VARCHAR2(17);
v_bts_inst_last NUMBER(5);
v_new_bts NUMBER(1);
CURSOR c_bts_code IS SELECT lac_id, cell_id FROM nok_bts_con_temp;BEGIN
delete from nok_bts_con_temp a
where a.rowid >(select min(rowid) from nok_bts_con_temp b
where a.cell_id=b.cell_id);
DELETE FROM syn_dic_obj_inst
WHERE OBJ_INST IS NULL ;
commit;
OPEN c_bts_code;
LOOP
FETCH c_bts_code INTO v_lac_id, v_cell_id;
EXIT WHEN c_bts_code%NOTFOUND;
/* 在数据字典DIC_OBJ_INST表中查找当前BTS是否存在 */
v_new_bts := 0;
BEGIN
SELECT obj_inst INTO v_bts_inst FROM syn_dic_obj_inst
WHERE dist_id = p_dist_id AND net_id = p_net_id AND prod_id = p_prod_id AND
class_id = p_bts_class_in_nmc AND obj_name like '%'|| v_cell_id;
EXCEPTION /* 如果不存在,则生成新的OBJ_INST;否则,获取旧的OBJ_INST */
WHEN NO_DATA_FOUND THEN
SELECT syn_seq_bts_inst.NEXTVAL INTO v_bts_inst_last FROM dual;
v_bts_inst := p_net_id || p_prod_id || p_bts_class_in_nmc || LPAD(v_bts_inst_last, 5, '0');
v_new_bts := 1;
END;
/* 保存BTS的OBJ_INST到临时表中,并标识是否为新BTS(C9字段) */
/* C10 = 1表示为新版的配置数据(假定) */
UPDATE nok_bts_con_temp SET c10 = 1, time_stamp = SYSDATE, obj_inst = v_bts_inst,
c9 = v_new_bts, prod_name = p_prod_name, name = lac_id || '-' || cell_id
WHERE cell_id = v_cell_id;
COMMIT;
END LOOP;
CLOSE c_bts_code; /* 根据BTS所属SITE的INT_ID,从OBJECTS_TEMP表中获得所属SITE的名称,存入BTS临时表中 */
/* 由于NOK_SITE_CON_TEMP表的C1字段保存的只是组成SITE的一个BCF的INT_ID(最大的),所以无法通过该INT_ID
获取BTS所属SITE的相关信息 */
UPDATE nok_bts_con_temp SET related_site = substr(p_dist_id,4,1)||substr(cell_id,2,1)
||substr(cell_id,5,1)
||substr(cell_id,3,2);
COMMIT; /* 根据BTS所属SITE的名称,从NOK_SITE_CON_TEMP表中获得SITE的OBJ_INST、所属BSC、MSC、所属OMC,
存入BTS临时表中 */
UPDATE nok_bts_con_temp A SET (A.obj_inst, A.p_obj_inst, A.related_site, A.related_bsc, A.related_msc, A.related_omc) =
(SELECT SUBSTR(B.obj_inst, 1, 6) || A.obj_inst, B.obj_inst, NVL(B.c_name, B.name), B.related_bsc, B.related_msc, B.related_omc FROM nok_site_con_temp B
WHERE A.related_site = B.ne_code) WHERE c9 = 1;
UPDATE nok_bts_con_temp A SET (A.p_obj_inst, A.related_site, A.related_bsc, A.related_msc, A.related_omc) =
(SELECT B.obj_inst, NVL(B.c_name, B.name), B.related_bsc, B.related_msc, B.related_omc FROM nok_site_con_temp B
WHERE A.related_site = B.ne_code) WHERE c9 = 0;
COMMIT; /* 更新中文名称 */
UPDATE nok_bts_con_temp SET c_name = related_site || '_' || name;
COMMIT;
/* 更新GPRS相关内容
C13: 开通GPRS载频数;
C14: 开通EGPRS载频数;
C15: 小区EGPRS信道数;
C16: 是否开通GPRS业务
C17: 开通EGPRS标志
C19: 静态信道数
C20: 动态信道数
*/
delete from egprs_bts_conf_temp;
insert into egprs_bts_conf_temp
select t2.int_id,t2.la_id_lac,t2.cell_id,GPRS_ENABLED,DECODE(GPRS_ENABLED,1,DECODE(EGPRS_ENABLED,1,1,0),0) EGPRS,
decode(GPRS_ENABLED,0,0,1,GPRS_ENABLED_TRX) GPRS_ENABLED_TRX,
DECODE(GPRS_ENABLED,1,DECODE(EGPRS_ENABLED,1,GPRS_ENABLED_TRX,0),0) EGPRS_ENABLED_TRX,
decode(sign(aa*DEDICATED_GPRS_CAPACITY/100-0),-1,0,0,1,
1,decode(sign(aa*DEDICATED_GPRS_CAPACITY/100-1),-1,round(aa*DEDICATED_GPRS_CAPACITY/100+0.5),
0,round(aa*DEDICATED_GPRS_CAPACITY/100-0.5),
1,round(aa*DEDICATED_GPRS_CAPACITY/100-0.5))) pdch_static,
decode(sign(aa*DEFAULT_GPRS_CAPACITY/100-0),-1,0,0,1,
1,decode(sign(aa*DEFAULT_GPRS_CAPACITY/100-1),-1,round(aa*DEFAULT_GPRS_CAPACITY/100+0.5),
0,round(aa*DEFAULT_GPRS_CAPACITY/100-0.5),
1,round(aa*DEFAULT_GPRS_CAPACITY/100-0.5)))-decode(sign(aa*DEDICATED_GPRS_CAPACITY/100-0),-1,0,0,1,
1,decode(sign(aa*DEDICATED_GPRS_CAPACITY/100-1),-1,round(aa*DEDICATED_GPRS_CAPACITY/100+0.5),
0,round(aa*DEDICATED_GPRS_CAPACITY/100-0.5),
1,round(aa*DEDICATED_GPRS_CAPACITY/100-0.5))) pdch_dynamic,
DECODE(GPRS_ENABLED,1,DECODE(EGPRS_ENABLED,1,decode(sign(aa*DEFAULT_GPRS_CAPACITY/100-0),-1,0,0,1,
1,decode(sign(aa*DEFAULT_GPRS_CAPACITY/100-1),-1,round(aa*DEFAULT_GPRS_CAPACITY/100+0.5),
0,round(aa*DEFAULT_GPRS_CAPACITY/100-0.5),
1,round(aa*DEFAULT_GPRS_CAPACITY/100-0.5))),0),0) tch_egprs
from (
select b.parent_int_id,sum(decode(a.GPRS_ENABLED_TRX,1,GPRS_ENABLED_TRX*8-(DECODE(CH_0_TYPE,3,1,4,1,8,1,0)+DECODE(CH_1_TYPE,3,1,4,1,8,1,0)+DECODE(CH_2_TYPE,3,1,4,1,8,1,0)+DECODE(CH_3_TYPE,3,1,4,1,8,1,0)+DECODE(CH_4_TYPE,3,1,4,1,8,1,0)+DECODE(CH_5_TYPE,3,1,4,1,8,1,0)+DECODE(CH_6_TYPE,3,1,4,1,8,1,0)+DECODE(CH_7_TYPE,3,1,4,1,8,1,0)),0)) aa,
sum(GPRS_ENABLED_TRX) GPRS_ENABLED_TRX
--c.DEDICATED_GPRS_CAPACITY
from c_trx_temp a,objects_temp b --,c_bts c
where a.int_id=b.int_id
group by b.parent_int_id) t1,c_bts_temp t2
where t1.parent_int_id = t2.int_id;
commit;
UPDATE nok_bts_con_temp A SET (C13,C14,C15,C16,C17,C19,C20) =
(SELECT GPRS_ENABLED_TRX,EGPRS_ENABLED_TRX,tch_egprs,GPRS_ENABLED,EGPRS,pdch_static,pdch_dynamic
FROM egprs_bts_conf_temp B
WHERE A.C1= B.INT_ID);
COMMIT;
/********c18:配置开通半速率信道数*********************/
UPDATE nok_bts_con_temp c SET c18 = (select sum(decode(ch_0_type,2,1,0)+decode(ch_1_type,2,1,0)+decode(ch_2_type,2,1,0)+decode(ch_3_type,2,1,0)+decode(ch_4_type,2,1,0)+decode(ch_5_type,2,1,0)+decode(ch_6_type,2,1,0)+decode(ch_7_type,2,1,0)) tch_hr_conf
from c_trx_temp a,objects_temp b
where a.int_id = b.int_id
and c.c1 = b.parent_int_id
group by b.parent_int_id);
commit;
END;
/
[2011-11-03 03:44:17] Error:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "WZ_NOK_OMCR4_UPLOAD.NC_UPLOAD_BTS_OTHER_INFO", line 31
ORA-06512: at line 1其中报错的sql语句为:
SELECT obj_inst INTO v_bts_inst FROM syn_dic_obj_inst
WHERE dist_id = p_dist_id AND net_id = p_net_id AND prod_id = p_prod_id AND
class_id = p_bts_class_in_nmc AND obj_name like '%'|| v_cell_id;我把syn_dic_obj_inst这个表的数据重复查过了,没有发现重复,我百度搜索,有人说是游标里面有重复,我也将处理加在了游标的下面,在线等人解决!
下面为全部sql:
CREATE OR REPLACE PROCEDURE WZ_NOK_OMCR4_UPLOAD."NC_UPLOAD_BTS_OTHER_INFO" /************************************************************/
/** 生成BTS的OBJ_INST,并获得其它配置信息 **/
/************************************************************/
(p_dist_id VARCHAR2, p_net_id VARCHAR2, p_prod_id VARCHAR2, p_bcf_class_in_omc NUMBER, p_bts_class_in_nmc VARCHAR2,
p_prod_name VARCHAR2) IS
v_lac_id nok_bts_con_temp.lac_id%TYPE;
v_cell_id nok_bts_con_temp.cell_id%TYPE;
v_bts_inst VARCHAR2(17);
v_bts_inst_last NUMBER(5);
v_new_bts NUMBER(1);
CURSOR c_bts_code IS SELECT lac_id, cell_id FROM nok_bts_con_temp;BEGIN
delete from nok_bts_con_temp a
where a.rowid >(select min(rowid) from nok_bts_con_temp b
where a.cell_id=b.cell_id);
DELETE FROM syn_dic_obj_inst
WHERE OBJ_INST IS NULL ;
commit;
OPEN c_bts_code;
LOOP
FETCH c_bts_code INTO v_lac_id, v_cell_id;
EXIT WHEN c_bts_code%NOTFOUND;
/* 在数据字典DIC_OBJ_INST表中查找当前BTS是否存在 */
v_new_bts := 0;
BEGIN
SELECT obj_inst INTO v_bts_inst FROM syn_dic_obj_inst
WHERE dist_id = p_dist_id AND net_id = p_net_id AND prod_id = p_prod_id AND
class_id = p_bts_class_in_nmc AND obj_name like '%'|| v_cell_id;
EXCEPTION /* 如果不存在,则生成新的OBJ_INST;否则,获取旧的OBJ_INST */
WHEN NO_DATA_FOUND THEN
SELECT syn_seq_bts_inst.NEXTVAL INTO v_bts_inst_last FROM dual;
v_bts_inst := p_net_id || p_prod_id || p_bts_class_in_nmc || LPAD(v_bts_inst_last, 5, '0');
v_new_bts := 1;
END;
/* 保存BTS的OBJ_INST到临时表中,并标识是否为新BTS(C9字段) */
/* C10 = 1表示为新版的配置数据(假定) */
UPDATE nok_bts_con_temp SET c10 = 1, time_stamp = SYSDATE, obj_inst = v_bts_inst,
c9 = v_new_bts, prod_name = p_prod_name, name = lac_id || '-' || cell_id
WHERE cell_id = v_cell_id;
COMMIT;
END LOOP;
CLOSE c_bts_code; /* 根据BTS所属SITE的INT_ID,从OBJECTS_TEMP表中获得所属SITE的名称,存入BTS临时表中 */
/* 由于NOK_SITE_CON_TEMP表的C1字段保存的只是组成SITE的一个BCF的INT_ID(最大的),所以无法通过该INT_ID
获取BTS所属SITE的相关信息 */
UPDATE nok_bts_con_temp SET related_site = substr(p_dist_id,4,1)||substr(cell_id,2,1)
||substr(cell_id,5,1)
||substr(cell_id,3,2);
COMMIT; /* 根据BTS所属SITE的名称,从NOK_SITE_CON_TEMP表中获得SITE的OBJ_INST、所属BSC、MSC、所属OMC,
存入BTS临时表中 */
UPDATE nok_bts_con_temp A SET (A.obj_inst, A.p_obj_inst, A.related_site, A.related_bsc, A.related_msc, A.related_omc) =
(SELECT SUBSTR(B.obj_inst, 1, 6) || A.obj_inst, B.obj_inst, NVL(B.c_name, B.name), B.related_bsc, B.related_msc, B.related_omc FROM nok_site_con_temp B
WHERE A.related_site = B.ne_code) WHERE c9 = 1;
UPDATE nok_bts_con_temp A SET (A.p_obj_inst, A.related_site, A.related_bsc, A.related_msc, A.related_omc) =
(SELECT B.obj_inst, NVL(B.c_name, B.name), B.related_bsc, B.related_msc, B.related_omc FROM nok_site_con_temp B
WHERE A.related_site = B.ne_code) WHERE c9 = 0;
COMMIT; /* 更新中文名称 */
UPDATE nok_bts_con_temp SET c_name = related_site || '_' || name;
COMMIT;
/* 更新GPRS相关内容
C13: 开通GPRS载频数;
C14: 开通EGPRS载频数;
C15: 小区EGPRS信道数;
C16: 是否开通GPRS业务
C17: 开通EGPRS标志
C19: 静态信道数
C20: 动态信道数
*/
delete from egprs_bts_conf_temp;
insert into egprs_bts_conf_temp
select t2.int_id,t2.la_id_lac,t2.cell_id,GPRS_ENABLED,DECODE(GPRS_ENABLED,1,DECODE(EGPRS_ENABLED,1,1,0),0) EGPRS,
decode(GPRS_ENABLED,0,0,1,GPRS_ENABLED_TRX) GPRS_ENABLED_TRX,
DECODE(GPRS_ENABLED,1,DECODE(EGPRS_ENABLED,1,GPRS_ENABLED_TRX,0),0) EGPRS_ENABLED_TRX,
decode(sign(aa*DEDICATED_GPRS_CAPACITY/100-0),-1,0,0,1,
1,decode(sign(aa*DEDICATED_GPRS_CAPACITY/100-1),-1,round(aa*DEDICATED_GPRS_CAPACITY/100+0.5),
0,round(aa*DEDICATED_GPRS_CAPACITY/100-0.5),
1,round(aa*DEDICATED_GPRS_CAPACITY/100-0.5))) pdch_static,
decode(sign(aa*DEFAULT_GPRS_CAPACITY/100-0),-1,0,0,1,
1,decode(sign(aa*DEFAULT_GPRS_CAPACITY/100-1),-1,round(aa*DEFAULT_GPRS_CAPACITY/100+0.5),
0,round(aa*DEFAULT_GPRS_CAPACITY/100-0.5),
1,round(aa*DEFAULT_GPRS_CAPACITY/100-0.5)))-decode(sign(aa*DEDICATED_GPRS_CAPACITY/100-0),-1,0,0,1,
1,decode(sign(aa*DEDICATED_GPRS_CAPACITY/100-1),-1,round(aa*DEDICATED_GPRS_CAPACITY/100+0.5),
0,round(aa*DEDICATED_GPRS_CAPACITY/100-0.5),
1,round(aa*DEDICATED_GPRS_CAPACITY/100-0.5))) pdch_dynamic,
DECODE(GPRS_ENABLED,1,DECODE(EGPRS_ENABLED,1,decode(sign(aa*DEFAULT_GPRS_CAPACITY/100-0),-1,0,0,1,
1,decode(sign(aa*DEFAULT_GPRS_CAPACITY/100-1),-1,round(aa*DEFAULT_GPRS_CAPACITY/100+0.5),
0,round(aa*DEFAULT_GPRS_CAPACITY/100-0.5),
1,round(aa*DEFAULT_GPRS_CAPACITY/100-0.5))),0),0) tch_egprs
from (
select b.parent_int_id,sum(decode(a.GPRS_ENABLED_TRX,1,GPRS_ENABLED_TRX*8-(DECODE(CH_0_TYPE,3,1,4,1,8,1,0)+DECODE(CH_1_TYPE,3,1,4,1,8,1,0)+DECODE(CH_2_TYPE,3,1,4,1,8,1,0)+DECODE(CH_3_TYPE,3,1,4,1,8,1,0)+DECODE(CH_4_TYPE,3,1,4,1,8,1,0)+DECODE(CH_5_TYPE,3,1,4,1,8,1,0)+DECODE(CH_6_TYPE,3,1,4,1,8,1,0)+DECODE(CH_7_TYPE,3,1,4,1,8,1,0)),0)) aa,
sum(GPRS_ENABLED_TRX) GPRS_ENABLED_TRX
--c.DEDICATED_GPRS_CAPACITY
from c_trx_temp a,objects_temp b --,c_bts c
where a.int_id=b.int_id
group by b.parent_int_id) t1,c_bts_temp t2
where t1.parent_int_id = t2.int_id;
commit;
UPDATE nok_bts_con_temp A SET (C13,C14,C15,C16,C17,C19,C20) =
(SELECT GPRS_ENABLED_TRX,EGPRS_ENABLED_TRX,tch_egprs,GPRS_ENABLED,EGPRS,pdch_static,pdch_dynamic
FROM egprs_bts_conf_temp B
WHERE A.C1= B.INT_ID);
COMMIT;
/********c18:配置开通半速率信道数*********************/
UPDATE nok_bts_con_temp c SET c18 = (select sum(decode(ch_0_type,2,1,0)+decode(ch_1_type,2,1,0)+decode(ch_2_type,2,1,0)+decode(ch_3_type,2,1,0)+decode(ch_4_type,2,1,0)+decode(ch_5_type,2,1,0)+decode(ch_6_type,2,1,0)+decode(ch_7_type,2,1,0)) tch_hr_conf
from c_trx_temp a,objects_temp b
where a.int_id = b.int_id
and c.c1 = b.parent_int_id
group by b.parent_int_id);
commit;
END;
/
或者把 select into 这个改为用游标,然后循环读数据继续处理
SELECT obj_inst INTO v_bts_inst FROM syn_dic_obj_inst
WHERE dist_id = p_dist_id AND net_id = p_net_id AND prod_id = p_prod_id AND
class_id = p_bts_class_in_nmc AND obj_name like '%'|| v_cell_id;
你这里的obj_inst值有多条记录,然后你把它放到一个变量里就报错了,
open test_cursor for SELECT obj_inst FROM syn_dic_obj_inst
WHERE dist_id = p_dist_id AND net_id = p_net_id AND prod_id = p_prod_id AND
class_id = p_bts_class_in_nmc AND obj_name like '%'|| v_cell_id;
delete from nok_bts_con_temp a
where a.rowid >(select min(rowid) from nok_bts_con_temp b
where a.cell_id=b.cell_id);FETCH c_bts_code INTO v_lac_id, v_cell_id;
你看一下你 update 语句后的 select 语句是否只返回一行数据
where a.rowid >(select min(rowid) from nok_bts_con_temp b
where a.cell_id=b.cell_id);
DELETE FROM syn_dic_obj_inst
WHERE OBJ_INST IS NULL ;
,这段sql已经加到了那里面了,有重复也在游标打开之前就处理了。
你把报错行语句拷贝出来执行,然后再看数据