Invalid stored procedures

When running an application that is connected to an Oracle server/database and trying to add rows or make a call to the DB, I am getting various errors that the stored procedures are invalid.

Hello, Brian. When running an application that is connected to an Oracle server/database, and trying to add rows or make a call to the DB, I am getting various errors that I believe are stating that the stored procedures are invalid. This is the first time I am using Oracle (10g). Is there anything I can look for to change -- either in the .sql scripts that are run when creating the DB or something else that Oracle might be looking for regarding privileges? Thanks for the assistance.

The first thing to do is to recompile all of your invalid objects. Sign on to the database as SYS or SYSTEM and run the ORACLE_HOME/rdbms/admin/utlrp.sql script. This will recompile all objects that can be recompiled successfully.

There may be some objects that will not compile. For instance, the stored procedure may refer to a table that has been dropped. The best way to find out why the stored procedure will not compile correctly is to figure out which object is invalid with the following query in SQL*Plus:

SELECT owner,object_name,object_type
FROM dba_objects WHERE status='INVALID';

Next, sign on to the schema that owns the stored procedure. Then issue the following:

ALTER PROCEDURE procedure_name COMPILE;

You will get an error saying that the procedure is not valid. Then type "SHOW ERRORS." This should give you an indication of what the real problem is.

Dig Deeper on Oracle database administration

Data Management
Business Analytics
SearchSAP
TheServerSide.com
Data Center
Content Management
HRSoftware
Close