重新编译无效数据库组件，编译无效数据库组件
You can validate different components in the database by running the script $ORACLE_HOME/rdbms/admin/catpatch.sql via SQL*Plus:
spool catpatch.log
connect / as sysdba
shutdown immediate
startup migrate
@?/rdbms/admin/catpatch.sql
@?/rdbms/admin/utlrp.sql
SELECT comp_name, version, status
FROM dba_registry;
spool off
If you are on 10g Release 2, then perform:
connect / as sysdba
spool dictreload.log
startup restrict
alter system set shared_pool_size = 512M scope=spfile;
alter system set java_pool_size = 150M scope=spfile;
alter system set aq_tm_processes = 1 scope=spfile;
alter system set streams_pool_size = 10M scope=spfile;
alter system set cluster_database = false scope=spfile; -- If on RAC
shutdown immediate
startup upgrade
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/catupgrd.sql
spool off
alter system set cluster_database = true scope=spfile; -- If on RAC
shutdown immediate
startup
@?/rdbms/admin/utlrp.sql
编译完成,不代表里面语句没问题,这种情况是存储过程里存在某种错误
在 SQL *Plus 或者 PL/SQL Developer 的 Command Windows 中,执行
show errors procedure USP_EXCEPTION;
查看一下错误是什么.
我的修改如下:
CREATE OR REPLACE PROCEDURE USP_EXCEPTION(EMPNO INTEGER, --批次ID
P_FM NUMBER, --分母
P_FZ NUMBER, --分子
P_RESULT OUT NUMBER --结果
) IS
V_RAISE EXCEPTION; --异常处理
type type_table_pcmx is varray(4) of varchar2(20);
TABLE_PCMX type_table_pcmx;
BEGIN
IF P_FZ = 0 THEN
RAISE V_RAISE;
END IF;
P_RESULT := P_FM / P_FZ;
SELECT ENAME, EMPNO, JOB, DEPTNO
INTO TABLE_PCMX(1),TABLE_PCMX(2),TABLE_PCMX(3),TABLE_PCMX(4)
FROM EMP
WHERE EMPNO = EMPNO;
EXCEPTION
WHEN V_RAISE THEN
RAISE_APPLICATION_ERROR(-20010, 'ERROR：分子为零！');
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20011, 'ERROR：批次明细不存在！');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20012, 'ERROR：数据错误！');
END;
重新编译，就是某个过程或者函数哪里做了修改 需要重新编译成可以执行的文件，并不是说重新跑某个过程。编译完成之后，把编译好了的过程再重新运行。