Oracle Stored Procedure/Function Load Ordering

Question

I have many stored procedures and functions in a folder. I have a batch file that loops over the .sql files in the folder and writes the file names to a single SQL file for execution. After this, the batch file executes the single file in SQLPlus. The problem that I am running into is the ordering of the creation of the stored procedures and functions. I am getting compilation errors because stored procedure C needs function A to execute, but stored procedure C is created before function A. Is there a way to get around this without ordering the stored procedures and functions? My assumption is no there's not, but I want to be sure before I start ordering the scripts.

Answer 1

You could load them then walk through the dependencies view to try to work out the correct order for next time, or just load them then recompile, but in fact the best way to work is with packages instead of standalone procedures and functions.

In that case you would create all of the package specifications first, then create the package bodies. They are separated into specification and body for exactly this reason.

Answer 2

Just create the objects (some will be invalid) and compile them all in a subsequent step.

[Edit]

I just saw David's answer after I posted and his suggestion about converting procedures and function into packages is a good one, but if you later add other objects into your folder such as views, types, etc. you'll still have the dependency problem. I do the recompilation with nested loops - the inner loop selects all invalid objects and recompiles each. This is inside of an outer loop that quits when there are no invalid objects or some max pass value is reached (for those cases where you have actual compilation errors that need to be fixed)

Answer 3

The compilation errors probably don't matter. The first time you call one of the invalid procedures it will compile.

The problem is the "broken windows" syndrome, whereby a genuine missing dependency gets lost in all the noise. If that thought bothers you, you can compile all the invalidated program units. There are various ways of doing this, but the best way is to use the UTL_RECOMP package (in 10g or later) or to use the utlrp.sql script, which you can find in %ORACLE_HOME%/rdbms/admin in earlier versions.

Update

Solomon Yakobson wrote a PL/SQL program to recompile invalid programs; it isn't version dependent and doesn't require SYSDBA privileges. Download it.

Answer 4

You could enhance your batch file in a way, that it evaluates dependency information, which you could store as special comments in your sql files or perhaps in your version control system as tags or part of the general comment.

Should you ever get an error resulting from a dependency issue, when executing the generated sql script, you know that some new dependency has been introduced and needs to be "described" for your batch to recognize it.

Yes, it would be manual work. But the installing sql script would still be generated, as it is now.

Answer 5

The way we do it is create all the stored procedures and then try to compile them 5 times. If there are still some stored procedures that failed to compile even after 5 times it causes a build failure. In my experience and in our code base 5 repetitions have proved to be sufficient.