本帖最后由 wntds 于 2014-8-6 16:19 编辑
原有oracle数据库中存储过程，执行正常，最近数据库升级建新库后 按月分表 每天来生成一个表分区 例分表：T_HTTPDETAIL201407 T_HTTPDETAIL201408，存储过程修改后 怎么也执行不了了，提示存储过程已编译但是有错误 ；因小白一直无法找出错误 请高手指教：
存储过程如下：
1、原有存储过程：
create or replace procedure lxy_getshare_pro(nowdate date) is
V_SQL_STRvarchar2(2000);
V_STARTdate;
CURSOR MYCUSOR IS
select ob.SUBOBJECT_NAME partition_name from all_objects ob
where ob.OBJECT_NAME ='T_HTTPDETAIL' and ob.OBJECT_TYPE = 'TABLE PARTITION'
and trunc(ob.CREATED) = trunc(nowdate-1);
begin
select t.starttime into V_START from LXY_SHAREACCOUNT t where rownum=1 order by t.starttime desc;
if trunc(nowdate) != trunc(V_START) then
FOR TAB IN MYCUSOR LOOP
V_SQL_STR := 'insert into LXY_SHAREACCOUNT (select a1.loginid,b1.o,b1.areacode,a1.ip1,a1.ip2,a1.starttime,a1.endtime,sysdate,lxy_share_seq.nextval from
(select distinct a.loginid,a.applicationid,a.host as ip1,b.host as ip2,
case when a.logintime > b.logintime then a.logintime + 0 else b.logintime + 0 end as starttime,case when a.logouttime > b.logouttime
then b.logouttime + 0 else a.logouttime + 0 end as endtime from (
select loginid,applicationid,host,min(packagetime) as logintime,max(packagetime) as logouttime from t_httpdetail partition(' ||tab.partition_name || ') t
group by httpmasterid, loginid, applicationid,host) a
inner join (select loginid,applicationid,host,min(packagetime) as logintime, max(packagetime) as logouttime from t_httpdetail partition(' ||tab.partition_name || ') t
group by httpmasterid, loginid, applicationid,host) b on a.loginid = b.loginid and a.hostb.logouttime) or (b.logintime between a.logintime and a.logouttime and b.logouttime > a.logouttime))
order by a.loginid) a1,(select * from ICDPUB.V_UCP_STAFFBASICINFO@CRM_ACCOUNT_NEW) b1
where a1.loginid = b1.accid)';
dbms_output.put_line(V_SQL_STR);
execute immediate V_SQL_STR;
commit;
END LOOP;
end if;
delete from LXY_SHAREACCOUNT a where a.rowid not in (select max(b.rowid) from LXY_SHAREACCOUNT b where a.account=b.account and a.orgcode=b.orgcode and a.areacode=b.areacode and a.ip1=b.ip1 and a.ip2 =b.ip2 and a.starttime = b.starttime and a.endtime=b.endtime) or to_char(a.starttime,'yyyymmdd hh24:mi') = to_char(a.endtime,'yyyymmdd hh24:mi');
delete from lxy_shareaccount t
where (t.account,t.orgcode,t.ip1,t.ip2) in (select account,orgcode,ip1,ip2 from lxy_shareaccount group by account,orgcode,ip1,ip2 having count(*) > 1)
and rowid not in (select min(rowid) from lxy_shareaccount group byaccount,orgcode,ip1,ip2 having count(*)>1)
and t.starttime > sysdate-2;
commit;
/*delete from LXY_SHAREACCOUNT t where t.createtimeb.logintime then a.logintime + 0 else b.logintime + 0 end as starttime,case when a.logouttime > b.logouttime
then b.logouttime + 0 else a.logouttime + 0 end as endtime from (
select loginid,applicationid,host,min(packagetime) as logintime,max(packagetime) as logouttime from '|| tb || ' partition(' ||tab.partition_name || ') t
group by httpmasterid, loginid, applicationid,host) a
inner join (select loginid,applicationid,host,min(packagetime) as logintime, max(packagetime) as logouttime from'|| tb || 'partition(' ||tab.partition_name || ') t
group by httpmasterid, loginid, applicationid,host) b on a.loginid = b.loginid and a.hostb.logouttime) or (b.logintime between a.logintime and a.logouttime and b.logouttime > a.logouttime))
order by a.loginid) a1,(select * from ICDPUB.V_UCP_STAFFBASICINFO@CRM_ACCOUNT) b1
where a1.loginid = b1.accid)';
--dbms_output.put_line(V_SQL_STR);
execute immediate V_SQL_STR;
commit;
end if;
delete from LXY_SHAREACCOUNT a where a.rowid not in (select max(b.rowid) from LXY_SHAREACCOUNT b where a.account=b.account and a.orgcode=b.orgcode and a.areacode=b.areacode and a.ip1=b.ip1 and a.ip2 =b.ip2 and a.starttime = b.starttime and a.endtime=b.endtime) or to_char(a.starttime,'yyyymmdd hh24:mi') = to_char(a.endtime,'yyyymmdd hh24:mi');
delete from lxy_shareaccount t
where (t.account,t.orgcode,t.ip1,t.ip2) in (select account,orgcode,ip1,ip2 from lxy_shareaccount group by account,orgcode,ip1,ip2 having count(*) > 1)
and rowid not in (select min(rowid) from lxy_shareaccount group byaccount,orgcode,ip1,ip2 having count(*)>1)
and t.starttime > sysdate-2;
commit;
/*delete from LXY_SHAREACCOUNT t where t.createtime LXY_GETSHARE_PRO.jpg (182.87 KB, 下载次数: 0)
下载附件
2014-8-6 16:17 上传
存储过程编译出错.jpg (178.19 KB, 下载次数: 0)
下载附件
2014-8-6 16:18 上传
|
