ORA-01002: 读取违反顺序求指教

灰太狼11111 2013-03-18 10:04:58

最近遇到一个问题，程序经常报ORA-01002: 读取违反顺序这个错，百度原因貌似是读取游标commit的问题，但是程序日志貌似不是游标出问题了，而且程序对每一步进行了异常处理，但是异常却没有出现在预期的地方而是出现在最后的异常捕获中，请高手指点

日志：生开始生成产品数据2013-03-10 01:01:59,生成产品主数据成功；开始更新产品接入号数据2013-03-10 01:06:10,更新产品接入号数据成功；开始更新宽带类产品账号为空情况2013-03-10 01:06:13,更新宽带类产品账号为空情况成功；开始更新产品密码2013-03-10 01:06:13,开始更新产品密码成功；开始更新产品状态数据2013-03-10 01:06:46,更新产品状态数据成功2013-03-10 01:06:59,生成产品数据异常:ORA-01002: 读取违反顺序生成全部数据完成

下面是程序

--新统一认证接口增量比对过程
Procedure makeUpDateForNewUAM Is
Seq_Num Number := 0;
Data_Num Varchar2(100) := '';
prodNum Number :=0;
partyNum Number :=0;
acctNum Number :=0;
areaCode bss_sys.area.zone_number%type;
errMsg varchar2(400) := '';
v_imsi varchar2(30);
v_count Number := 0;
v_mgkh_flag NUMBER(2):=0;
v_sql VARCHAR2(500);
V_IDNUM VARCHAR2(40);
V_CUSTNAME VARCHAR2(200);
V_RESULT NUMBER(2);
cur ref_cur;
partyInfo partyInfo_record;
v_GENDER Varchar2(9) := '';
v_fee_type inst.offer_prod_fee_type.fee_type%type;
v_REDU_ACCESS_NUMBER inst.offer_prod.redu_access_number%type;
v_prod_status inst.offer_prod_status.prod_status_cd%type;
v_access_number inst.offer_prod_2_access_number.access_number%type;
v_redu_access_number_2 inst.offer_prod.redu_access_number%type;
Begin

--开始生成产品数据
begin
begin
Insert Into INTF_UAM.CRM_USER_DELTA(USER_ID,USER_TYPE_CODE,AREA_CODE,USER_NAME,STATUS_CODE,CUST_NO,ACCOUNT_ID,PASSWORD,STATE_TIME,PRODUCT_CODE)
Select
/*+rule+*/ prod.prod_id,
prod.Redu_Prod_Spec_Id,
areaCode,
prod.Redu_Access_Number,
null,
prod.Redu_Owner_Id,
prod.redu_acct_id,
Null,
Sysdate,
prod.Redu_Prod_Spec_Id
From (select a.* from inst.offer_prod a,
(select max(version) v, b.redu_access_number, b.redu_prod_spec_id
from inst.offer_prod b where trunc(b.VERSION) =trunc(sysdate -1)
group by b.redu_access_number, b.redu_prod_spec_id) c
where a.version = c.v
and a.redu_prod_spec_id = c.redu_prod_spec_id
and a.redu_access_number = c.redu_access_number
and trunc(a.VERSION) =trunc(sysdate -1)) Prod,crm.ua_synchronization_configure usc
Where prod.redu_prod_spec_id = usc.prod_spec_id
And trunc(Prod.VERSION) =trunc(sysdate -1);

Commit;
Update INTF_UAM.CRM_CONTROL_DELTA Set Remark = Remark || ',生成产品主数据成功；开始更新产品接入号数据' || to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') Where State =

'N';
exception when others then
rollback;
errMsg := substr(sqlerrm,1,50);
Update INTF_UAM.CRM_CONTROL_DELTA Set Remark = Remark || ',生成产品主数据异常:' || errMsg || '；开始更新产品接入号数据' || to_char(sysdate,'yyyy-mm-dd

hh24:mi:ss') Where State = 'N';
commit;
end;

--更新产品状态
begin
for rec in (select rowid as tRowid ,cu.*
from INTF_UAM.CRM_USER_DELTA cu
where cu.area_code = areaCode ) loop
begin
select a.prod_status_cd into v_prod_status
from (Select /*+rule+*/
ps.prod_status_cd
From inst.offer_prod_status ps
Where ps.prod_id = rec.user_id
and ps.status_cd != 22
order by ps.start_dt desc) a
where rownum < 2 ;
update INTF_UAM.CRM_USER_DELTA cu set cu.STATUS_CODE = v_prod_status where rowid =rec.TROWID;
EXCEPTION
when others then
null;
end;
end loop;
commit;
Update INTF_UAM.CRM_CONTROL_DELTA Set Remark = Remark || ',更新产品状态数据成功' || to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') Where State = 'N';
exception when others then
rollback;
errMsg := substr(sqlerrm,1,50);
Update INTF_UAM.CRM_CONTROL_DELTA Set Remark = Remark || ',更新产品状态数据异常:' || errMsg || to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') Where State = 'N';
commit;
end;

--增加账号类型
begin
update INTF_UAM.CRM_USER_DELTA cu set cu.AN_TYPE =
(select AN_TYPE_CD from inst.offer_prod_2_access_number c where c.access_number = cu.user_name and c.status_cd !=22 and rownum=1)
where cu.user_type_code='9';
commit;
Update INTF_UAM.CRM_CONTROL_DELTA Set Remark = Remark || ',更新账号类型数据成功' || to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') Where State = 'N';
exception when others then
rollback;
errMsg := substr(sqlerrm,1,50);
Update INTF_UAM.CRM_CONTROL_DELTA Set Remark = Remark || ',更新账号类型数据异常:' || errMsg || to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') Where State = 'N';
commit;
end;

--更新IMIS信息
begin
for rec in (select rowid as tRowid ,cu.*
from INTF_UAM.CRM_USER_DELTA cu
where cu.user_type_code = '379' ) loop
begin
select o.dev_num
into v_imsi
from RM.DEVICE_NUMBER o
where o.an_type_cd = 509
and o.dev_num_id in
(select a.dev_num_id
from rm.TERMINAL_DEV_2_DEV_NUM a
where a.terminal_dev_id in
(select b.terminal_dev_id
from inst.offer_prod_2_td b
where b.status_cd !=22 and b.prod_id in
(select c.prod_id
from inst.offer_prod c
where c.status_cd !=22 and c.redu_access_number = rec.user_name)))
and rownum < 2;
update INTF_UAM.CRM_USER_DELTA cu set cu.imsi = v_imsi where rowid =rec.TROWID;
commit;
EXCEPTION
when others then
rollback;
end;
end loop;
Update INTF_UAM.CRM_CONTROL_DELTA Set Remark = Remark || ',更新IMIS信息成功' || to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') Where State = 'N';
commit;
end;

--管理账号标识
begin
for rec in (select rowid as tRowid ,cu.*
from INTF_UAM.CRM_USER_DELTA cu) loop
begin
select count(*)
into v_count
from crm.party_profile a
where a.party_profile_catg_cd = 315508 and a.profile_value = rec.user_id and a.party_id = rec.cust_no;
if v_count > 0 then
update INTF_UAM.CRM_USER_DELTA cu set cu.manage_account_flag = 1 where rowid =rec.TROWID;[/b]
ElsIf v_count = 0 then
update INTF_UAM.CRM_USER_DELTA cu set cu.manage_account_flag = 2 where rowid =rec.TROWID;
End If;
commit;
EXCEPTION
when others then
rollback;
end;
end loop;
Update INTF_UAM.CRM_CONTROL_DELTA Set Remark = Remark || ',更新管理账号标识成功' || to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') Where State = 'N';
commit;
end;

begin
for rec in (select rowid as tRowid, cu.* from INTF_UAM.CRM_USER_DELTA cu) loop
--更新产品付费类型
begin
select a.fee_type
into v_fee_type
from inst.offer_prod_fee_type a
where a.prod_id = rec.user_id
and a.status_cd != 22
AND ROWNUM < 2;
update INTF_UAM.CRM_USER_DELTA cu
set cu.pay_type = v_fee_type
where rowid = rec.TROWID;
commit;
EXCEPTION
when others then
null;
end;
--更新宽带类产品绑定电话
begin
SELECT /*+rule+*/
P.REDU_ACCESS_NUMBER
into v_REDU_ACCESS_NUMBER
FROM inst.offer_prod P, PROD_2_PROD P2P
WHERE P.PROD_ID = P2P.RELATED_PROD_ID
AND P2P.RELA_REASON_CD = 6
AND P2P.RELATES_PROD_ID = rec.user_id
and rec.USER_TYPE_CODE = '9'
and p.status_cd != 22
AND ROWNUM < 2;
update INTF_UAM.CRM_USER_DELTA cu
set cu.bind_phone = v_REDU_ACCESS_NUMBER
where rowid = rec.TROWID;
commit;
EXCEPTION
when others then
null;
end;
end loop;
Update INTF_UAM.CRM_CONTROL_DELTA
Set Remark = Remark || ',更新产品付费类型、宽带类产品绑定电话成功' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')
Where State = 'N';
commit;
end;

Select count(1) Into prodNum From INTF_UAM.CRM_USER_DELTA where STATE_TIME > sysdate -1;
Update INTF_UAM.CRM_CONTROL_DELTA Set Remark = Remark || ',生成产品数据完成，数据割接结束！',Data_Count = Data_Count || ',生成产品数据量 = ' || prodNum Where State

= 'N';
commit;
exception when others then
errMsg := substr(sqlerrm,1,50);
Update INTF_UAM.CRM_CONTROL_DELTA Set Remark = Remark || ',生成产品数据异常:' || errMsg,Data_Count = Data_Count || ',生成产品数据量 = ' || prodNum Where State =

'N';
commit;
end;
--生产产品数据结束
--完成总控表操作
Update INTF_UAM.CRM_CONTROL_DELTA
Set End_Dt = Sysdate, State = 'C', Remark = Remark || '生成全部数据完成'
Where State = 'N';
Commit;
Exception When Others Then
Begin
--生成产品数据异常
Errmsg := substr(sqlerrm,1,400);
Update INTF_UAM.CRM_CONTROL_DELTA Set Remark = Errmsg Where State = 'N';
commit;
exception when others then
null;
end;
End;

...全文