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?

  • 4
    You are doing a migration this weekend and you just now are thinking about this? You have bigger problems ahead than just fixing 300 sps. – HLGEM Apr 13 '12 at 17:26
  • This was a point that was not in my hands, I inherited this problem because other people could not handle this yet. I fully agree that at this time, this problem should have the solution. – Rainier Morilla Apr 13 '12 at 19:37

I'd probably do something like

  • Call DBMS_METADATA.GET_DDL to get the DDL for each object in a CLOB.
  • Write code that does a search and replace on that CLOB.
  • Use EXECUTE IMMEDIATE to execute the newly modified CLOB.

Something like this where search_and_replace implements whatever logic you need

  l_ddl CLOB;
  FOR x IN (SELECT * FROM dba_objects WHERE <<objects you want to change>>)
    l_ddl := dbms_metadata.get_ddl( x.object_type,
                                    x.owner );
    search_and_replace( l_ddl, 
                        'DIRECTORY_NAME' );
| improve this answer | |
  • 1
    I do not know if my logic is correct? I could store the data extracted by DBMS_METADATA clob type in a table and then do a: SELECT TRANSLATE(<column_with_ddl>, '/file/folder/documents', 'DIRECTORY_NAME') from ddl_table – Rainier Morilla Apr 13 '12 at 17:00
  • @RainierMorilla - Obviously, you'll want to test it thoroughly but that looks reasonable. Of course, code has a general tendency to resist simple approaches because inevitably someone decided to put things on multiple lines or to do something else that would break a simple TRANSLATE – Justin Cave Apr 13 '12 at 17:06

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


(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?

| improve this answer | |
  • That was my first thought. Extract all the ddl and make the change manually. I'll be with the bank in exclusive mode, after backing up everything I will make these changes. Also I'm doing the tests in another test database. I'm not crazy to do this directly in my database. But I think making the change to the database through a script and only replicate the script to the other side would be in addition to simpler, more secure. Through a script I can remove a portion of problems that would cause a human error. – Rainier Morilla Apr 13 '12 at 16:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.