While you are happily developing PL/SQL with your favorite tool be it TOAD, SQL Navigator or SQL Developer, all of a sudden your boss comes and tells you that you have to extract all stored programs in your Oracle Database Schema and save them to the file system. Since you have 45678457 PL/SQL packages out there you know you have to automate this process. You know Oracle saves these stored programs somewhere, but how do you get them back? As most of the time, Oracle has a built-in solution for this need as well: the DBMS_METADATA. package in the SYS schema.

DBMS_METADATA.GET_DDL (
object_type     IN VARCHAR2,
name            IN VARCHAR2,
schema          IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

The function works as advertised but there are some tricks that you might need to know.

First of all, the resulting DDL script is schema specific. When you extract the DDL of a package named “Package1″ in schema HR, the generated DDL starts with;

CREATE OR REPLACE PACKAGE "HR"."Package1" . 

This is not always desired. Here is a small trick that removes schema name,

select replace(dbms_metadata.get_ddl('PACKAGE','PACKAGE1'),'"'||user||'"."PACKAGE1"','PACKAGE1') from dual

Below is a function that removes schema name from DDL scripts;

CREATE OR REPLACE
FUNCTION GETDDL ( in_object_type_tx     IN  VARCHAR2,
                  in_name_tx            IN  VARCHAR2
                ) return clob  is
  v_username_tx varchar2(255);
  v_cl clob;
  v_number_nr number;
begin
  select user into v_username_tx from dual;
  v_cl := dbms_metadata.get_ddl(in_object_type_tx,in_name_tx,v_username_tx);
  -- remove schema name...
  v_cl := replace(v_cl,upper('"'||v_username_tx||'"."'||in_name_tx||'"'),upper(in_name_tx));
  return v_cl;
end;

Another trick that you should remember is that, Oracle stores all object types and object names in upper case. If you do not use upper case you will get an error even though object name you provide seems correct.This tip may seem obvious to PL/SQL Developers but not so much if you are primarily a Java Developer who happens to maintain some PL/SQL code.

Final advice is about the object_type input parameter of the GET_DDL function. As you can guess, the valid values for this input parameter are the object types that are available in Oracle. The most commonly used ones are;

FUNCTION
INDEX
PACKAGE
PACKAGE_BODY
PROCEDURE
SEQUENCE
TABLE
TRIGGER
TYPE
VIEW

Well, the problem is, when you extract ‘PACKAGE’, resulting DDL  includes the package specification and the package body. However, if you need to extract only the package specification, the types above will not get the job done.

There is another object type that is not well documented; ‘PACKAGE_SPEC’. If you need to extract only Package specification, you should use ‘PACKAGE_SPEC’ as object type.

Serkan Kasapbaşı