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.
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;
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.