How to change three hundred procedures at once

oracleoracle-10gstored-procedures

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?

Best Answer

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 

DECLARE
  l_ddl CLOB;
BEGIN
  FOR x IN (SELECT * FROM dba_objects WHERE <<objects you want to change>>)
  LOOP
    l_ddl := dbms_metadata.get_ddl( x.object_type,
                                    x.object_name,
                                    x.owner );
    search_and_replace( l_ddl, 
                        '/file/folder/documents',
                        'DIRECTORY_NAME' );
    EXECUTE IMMEDIATE l_ddl;
  END LOOP;
END;
Related Question