Q
Problem solve Get help with specific problems with your technologies, process and projects.

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 design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close