Recompile

When doing create or replace procedure, all other proc. func. that refer to this proc will become invalid and needs to be recomplied. Could someone explain what this really mean, and give an simple example as to how to actually recompile the other proc and functions? Thanks.

J.T,

I found third-party tools such as SQL Navigator excellent for object browsing and query execution. But many of these tools are just too buggy. SQL Navigator in particular. For example, it does not return a ref cursor as an output parameter, compiling is a big issue - sometimes it works sometimes it doesn't. There are many other issues that I won't mention.

This being said, SQL*PLUS is the only reliable tool that Oracle has, as ugly as it is. I personally hate it. But I have yet to have any problems with it.

So, my recommendation would be: Use NOTEPAD to write all you packages/procedures/functions and save them as a file with the .sql extension. Then in SQL*PLUS use the '@' sign to do all your compilations. This will ensure very little or no problems at all.

To compile, use the following command:

SQL> @C:\my_dbscripts\test
(NOTE: with 'test' being you packages, procedure, or function)

Good Luck!

- Gary

Here is a Very excellent Tool used for Oracle Development.
TOAD -- Tool for Oracle Application Developers.

It has all the optiuons shows all invalid objects and you have the options to Compile, Even compile all.

Otherwise just Execute this Oracle Package

Exec Dbms_Utility.Compile_Schema('Schema Name in Upper case');

It will compile all the Objects in your Schema... If want specific

Use Dbms_DDL.Compile_Object(''')

Thanks

jt

To check if which object is invalid,

use sqlplus and

select object_name,OBJECT_TYPE from user_objects where status='INVALID';


then for the procedure or function having invalid status

alter procedure object_name compile;
alter function object_name compile;


for package or package body

alter package object_name compile packge;
alter package object_name compile body;

Hi, htpse- It is correct: You do these manual recompile only when you are concerned that on the fly recompile by oracle when the proc. func.or package is called would take too much time. If time is not a concern, you can just wait for oracle to compile, and you don't have to manually recompile necessary? Or is my concept here totally wrong?

jt,

Don't rely on oracle to compile invalid object.

I've experince that oracle will not recompile invalid object.

Once example is when you have refer a column type
eg. aValue TAB%COLUMN_A
in a function and you have alter the table to add extra column , you will have the stored procedure failed during revalidation by the rdbms.

The best practice is to recompile object if possible.

Thanks!