根据你的情况!!看看我写的过程你参考下DECLARE T_OWNER VARCHAR2(10); T_OBJECT_NAME VARCHAR2(30); T_OBJECT_TYPE VARCHAR2(20); C_SQL NUMBER; S_SQL VARCHAR2(500); RET NUMBER; v_n int; CURSOR C_CONS IS SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM ALL_OBJECTS WHERE STATUS ='INVALID' AND OBJECT_TYPE<>'TABLE' and (owner not in ('SYS','SYSTEM')) order by owner,object_type,object_name; BEGIN /*注意运行3次编译,因第一次编译又可能先后排序导致其他函数失效*/ for v_n in 1..3 loop C_SQL := DBMS_SQL.OPEN_CURSOR; OPEN C_CONS; LOOP BEGIN FETCH C_CONS INTO T_OWNER,T_OBJECT_NAME,T_OBJECT_TYPE; EXIT WHEN C_CONS%NOTFOUND; IF T_OBJECT_TYPE = 'PACKAGE BODY' THEN S_SQL :='ALTER PACKAGE '||T_OWNER||'.'||T_OBJECT_NAME||' COMPILE BODY'; else S_SQL :='ALTER '||T_OBJECT_TYPE||' '||T_OWNER||'.'||T_OBJECT_NAME||' COMPILE'; END IF; dbms_output.put_line('正在进行第'||to_char(v_n)||'次编译:'||s_sql||' ...'); DBMS_SQL.PARSE( C_SQL, S_SQL, DBMS_SQL.v7 ) ; ret := DBMS_SQL.EXECUTE( C_SQL ) ; EXCEPTION WHEN OTHERS THEN NULL; END; END LOOP; CLOSE C_CONS; DBMS_SQL.CLOSE_CURSOR(C_SQL); end loop; END; /
T_OWNER VARCHAR2(10);
T_OBJECT_NAME VARCHAR2(30);
T_OBJECT_TYPE VARCHAR2(20);
C_SQL NUMBER;
S_SQL VARCHAR2(500);
RET NUMBER;
v_n int;
CURSOR C_CONS IS
SELECT OWNER,OBJECT_NAME,OBJECT_TYPE
FROM ALL_OBJECTS
WHERE STATUS ='INVALID' AND OBJECT_TYPE<>'TABLE' and (owner not in ('SYS','SYSTEM'))
order by owner,object_type,object_name;
BEGIN
/*注意运行3次编译,因第一次编译又可能先后排序导致其他函数失效*/
for v_n in 1..3 loop
C_SQL := DBMS_SQL.OPEN_CURSOR;
OPEN C_CONS;
LOOP
BEGIN
FETCH C_CONS INTO T_OWNER,T_OBJECT_NAME,T_OBJECT_TYPE;
EXIT WHEN C_CONS%NOTFOUND;
IF T_OBJECT_TYPE = 'PACKAGE BODY' THEN
S_SQL :='ALTER PACKAGE '||T_OWNER||'.'||T_OBJECT_NAME||' COMPILE BODY';
else
S_SQL :='ALTER '||T_OBJECT_TYPE||' '||T_OWNER||'.'||T_OBJECT_NAME||' COMPILE';
END IF;
dbms_output.put_line('正在进行第'||to_char(v_n)||'次编译:'||s_sql||' ...');
DBMS_SQL.PARSE( C_SQL, S_SQL, DBMS_SQL.v7 ) ;
ret := DBMS_SQL.EXECUTE( C_SQL ) ;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
CLOSE C_CONS;
DBMS_SQL.CLOSE_CURSOR(C_SQL);
end loop;
END;
/
DBMS_SQL.PARSE( C_SQL, S_SQL, DBMS_SQL.v7 ) ;
RET := DBMS_SQL.EXECUTE( C_SQL ) ;
并没有执行啊?