ORACLE - Exporting Procedures / Packages to a file

Question

I would like to programmatically export my Procedures / Functions and Packages into individual files (as a backup) and using Oracle 9.2.

The closest solution i found was using DBMS_METADATA.GET_DDL , but how do i output the CLOB to a text file, without losing any parts (due to length or indentation) ?

Or maybe do you have other solutions to backup packages or other functions individually (only the one i want, not all of them) ?

Thanks

Answer 1

Trying to get CLOBS (and LONGS) from command line utilities like SQL*Plus always seems to give me formatting/truncation problems. My solution was to write a simple utility in a non- type checking language (Perl) that uses DBMS_METADATA to bring the CLOB back into a string.

Snippet:

...

$sthRef = $dbhRef->prepare("select dbms_metadata.get_ddl(?,?) from dual");

$sthRef->execute('PACKAGE', $thisName);

while (($thisDDL) = $sthRef->fetchrow()) {

  print $thisDDL;

}

$sthRef->finish;

...

Answer 2

If you want to get the DDL, there really is no way except DBMS_METADATA like you already said.

Usually, this kind of a backup is done with exp (or expdp), although this doesn't create a SQL file like you would get with most other DBMS systems.