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

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

...

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

Solution 3

SET pages 0
spool proclist.sql
SELECT
CASE line
WHEN 1 THEN
'CREATE OR REPLACE ' || TYPE || ' ' || NAME || CHR(10) || text
ELSE
text
END
FROM user_source
WHERE TYPE IN ( 'PROCEDURE','FUNCTION')
ORDER BY name, line;
spool OFF
exit