How can I change three hundred procedures at once?

I need to change about 300 procedures and packages in the database, due to a migration that will accomplish this weekend. We will do a migration from one server to Exadata.

However the database has been developed in a very sloppy way. The bank carries out a number of text files written directly to disk, but nobody uses directories. In Exadata, the path to writing the files will be different due to the use of DBFS, for that I must change all calls via UTL_FILE.

Let me give an example: Currently the code is this:

file: utl_file.fopen = ('/file/folder/documents', filename, 'W');

What I want to do:
Create a directory

create or replace directory directory_name as '/file/folder/documents';

Change the 300 procedures for:

file: = utl_file.fopen ('directory_name', filename, 'W');

During migration only change the directory:

create or replace directory directory_name as '/dbfs/documents';

The real question: Is there a way I make a search and replace changing all 300 procedures in the database at once? I mean, there's a way to change all '/file/folder/documents' to directory_name?

If you script the procedures out to a file, the search/replace can be trivially dome with a sed script along the lines of

s/\/file\/folder\/documents/directory_name/g

(note not tested, just off the top of my head, but you can fiddle with it).

Then you can re-load the stored procedures. Note that if you're frigging with the code base you should really test what you're doing rather than doing a blind search/replace on production code. What could possibly go wrong?

StackExchange Database Administrators Q#16484, answer score: 7