CODEWITHSUNDEEP
oracle-databaseplsql
Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

How to make a copy of a database package

I have a package (potentially, many) that I'd like to copy to a new package with a different name.

Ideally I'd like to run this:

begin
  copy_package('MY_PACKAGE_NAME','MY_PACKAGE_NAME$BK');
end;
/

This will find the package named MY_PACKAGE_NAME, and create/replace a new package called MY_PACKAGE_NAME$BK with all references to MY_PACKAGE_NAME (if any) changed over as well.

It will be reasonable to assume that the package names are not case-sensitive, and any references to the package name in the source will be either all UPPERCASE or all lowercase (i.e. there won't be any references to My_Package_Name in the source).

Upvotes: 2

Views: 10231

Answers (1)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

This procedure will copy the specified package, replacing any references to the package name (all uppercase or lowercase) as it goes - including any references in comments.

procedure copy_package
  (old_name IN VARCHAR2
  ,new_name IN VARCHAR2
  ) is
  ddl clob;
begin
  ddl := dbms_metadata.get_ddl
    (object_type => 'PACKAGE_SPEC'
    ,name => old_name
    );
  ddl := REPLACE(ddl, UPPER(old_name), UPPER(new_name));
  ddl := REPLACE(ddl, LOWER(old_name), LOWER(new_name));
  EXECUTE IMMEDIATE ddl;
  ddl := dbms_metadata.get_ddl
    (object_type => 'PACKAGE_BODY'
    ,name => old_name);
  ddl := REPLACE(ddl, UPPER(old_name), UPPER(new_name));
  ddl := REPLACE(ddl, LOWER(old_name), LOWER(new_name));
  EXECUTE IMMEDIATE ddl;
end copy_package;

If the new package name already exists in the schema, it will be overwritten without warning.

The old package is left untouched.

If there are any grants or synonyms needed, they are not copied across.

If the package has code that happens to include the package name within a longer identifier (e.g. NOT_MY_PACKAGE_NAME) this will fail as it will replace that text indiscriminately.

If the package spec or body is greater than 32K in size, this procedure will fail.

If the package spec or body is not found, this procedure raises ORA-31603. If the spec was found but body was not, the spec will be copied, and then it will raise the exception.

Upvotes: 6

Related Questions