Recompile the invalid database component and compile the invalid database component.
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 = 512 M scope = spfile;
Alter system set java_pool_size = 150 M scope = spfile;
Alter system set aq_tm_processes = 1 scope = spfile;
Alter system set streams_pool_size = 10 M 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
Oracle recompilation is also invalid.
After compilation is completed, it does not mean that the statements in it are okay. This situation is caused by some errors in the stored procedure.
In Command Windows of SQL * Plus or PL/SQL Developer, run
Show errors procedure USP_EXCEPTION;
Check the error.
My modifications are as follows:
Create or replace procedure USP_EXCEPTION (empno integer, -- Batch ID
P_FM NUMBER, -- denominator
P_FZ NUMBER, -- molecule
P_RESULT out number -- result
) IS
V_RAISE EXCEPTION; -- EXCEPTION Handling
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: the molecule is zero! ');
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20011, 'error: the batch details do not exist! ');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20012, 'error: Data ERROR! ');
END;
What does the oracle Database Developer say, 'need to recompile it? Is to re-run a process
Re-compilation is a process or a function that requires re-compilation into executable files. It does not mean re-running a process. After the compilation is complete, re-run the compiled process.