自己写了个存储过程，打算弄成JOBS每天执行，目地是删除分区表TRACE_BASE_INFO_ASK中旧分区，只保留5个最新的分区。
如果删除分区是出错，则将异常信息记录到表ExpTable中
现在创建这存储过程后，系统提示该存储过程处于无效状态，但不知道存储过程哪里出的错，有劳高手指明
代码如下：
CREATE OR REPLACE procedure SYSTEM.AutoDrop_ASKPart
as
BEGIN
declare i_rowcount int;
begin
SELECT count(1) into i_rowcount FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='TRACE_BASE_INFO_ASK' AND PARTITION_NAME LIKE 'SYS_%';
if i_rowcount>5 then
declare cursor c_job is
SELECT PARTITION_NAME FROM ALL_TAB_PARTITIONS
WHERE TABLE_NAME='TRACE_BASE_INFO_ASK' and PARTITION_NAME like 'SYS_%' and rownum<=(i_rowcount-5);
c_row c_job%rowtype;
begin
for c_row in c_job loop
begin
execute immediate 'alter table TRACE_BASE_INFO_ASK drop partition ' || c_row.PARTITION_NAME;
exception
when others then
insert into ExpTable(ExpText)VALUES(SQLERRM);
end;
end loop;
end;
end if;
end;
END;
------解决思路----------------------
随意给你调整了下 （未编译测试）
CREATE OR REPLACE PROCEDURE system.autodrop_askpart IS
i_rowcount NUMBER;
CURSOR c_job(p_rowcount) IS
SELECT partition_name
FROM all_tab_partitions
WHERE table_name = 'TRACE_BASE_INFO_ASK'
AND partition_name LIKE 'SYS_%'
AND rownum <= (p_rowcount - 5);
c_row c_job%ROWTYPE;
BEGIN
SELECT COUNT(1)
INTO i_rowcount
FROM all_tab_partitions
WHERE table_name = 'TRACE_BASE_INFO_ASK'
AND partition_name LIKE 'SYS_%';
IF i_rowcount > 5 THEN
BEGIN
OPEN c_job(i_rowcount);
LOOP
FETCH c_job
INTO c_row;
EXIT WHEN c_job%NOTFOUND;
BEGIN
EXECUTE IMMEDIATE 'alter table TRACE_BASE_INFO_ASK drop partition '
------解决思路----------------------
c_row.partition_name;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO exptable
(exptext)
VALUES
(SQLERRM);
END;
END LOOP;
CLOSE c_job;
END;
END IF;
END;
------解决思路----------------------
-- 在 1# 的基础上编辑的
-- 1 。游标的参数，加一个数据类型
-- 2。SQLERRM 先赋给一个变量
CREATE OR REPLACE PROCEDURE autodrop_askpart IS
i_rowcount NUMBER;
i_error VARCHAR2(500);
CURSOR c_job(p_rowcount varchar2) IS
SELECT partition_name
FROM all_tab_partitions
WHERE table_name = 'TRACE_BASE_INFO_ASK'
AND partition_name LIKE 'SYS_%'
AND rownum <= (p_rowcount - 5);
c_row c_job%ROWTYPE;
BEGIN
SELECT COUNT(1)
INTO i_rowcount
FROM all_tab_partitions
WHERE table_name = 'TRACE_BASE_INFO_ASK'
AND partition_name LIKE 'SYS_%';
IF i_rowcount > 5 THEN
BEGIN
OPEN c_job(i_rowcount);
LOOP
FETCH c_job
INTO c_row;
EXIT WHEN c_job%NOTFOUND;
BEGIN
EXECUTE IMMEDIATE 'alter table TRACE_BASE_INFO_ASK drop partition '
------解决思路----------------------
c_row.partition_name;
EXCEPTION
WHEN OTHERS THEN
i_error:= SQLERRM;
INSERT INTO exptable
(exptext)
select i_error from dual ;
END;
END LOOP;
CLOSE c_job;
END;
END IF;
END;