Oracle - Procedure to Export DDL (SELECT * FROM ALL_OBJECTS) - Write in Directory of Oracle

P_EXPORT_DDL.sql

create or replace PROCEDURE     P_EXPORT_DDL 
(
  pOWNER        IN ALL_OBJECTS.OWNER%TYPE DEFAULT 'SYS',
  pOBJECT_TYPE  IN ALL_OBJECTS.OBJECT_TYPE%TYPE DEFAULT 'PROCEDURE',
  pOBJECT_NAME  IN ALL_OBJECTS.OBJECT_NAME%TYPE DEFAULT 'P_EXPORT_DDL'
) AS
    data         CLOB;
    fname        varchar2(256);
    vDiretorio   ALL_DIRECTORIES.DIRECTORY_NAME%TYPE;
    vOBJECT_TYPE ALL_OBJECTS.OBJECT_TYPE%TYPE;
BEGIN
    
    vOBJECT_TYPE := 
        CASE pOBJECT_TYPE 
            WHEN 'PACKAGE BODY' THEN 'PACKAGE' 
            WHEN 'TYPE BODY'    THEN 'TYPE'
            --When ['JOB'|'PROGRAM'|'SCHEDULE'] ref [Oracle's dbms_metadata.get_ddl for object_type JOB](https://stackoverflow.com/a/37630480/2057854)
            WHEN 'JOB'          THEN 'PROCOBJ'
            WHEN 'SCHEDULE'     THEN 'PROCOBJ'
            WHEN 'PROGRAM'      THEN 'PROCOBJ'
            ELSE pOBJECT_TYPE END;              
    
    --Reference: [Table 30-3 - DBMS_METADATA: Object Types](https://docs.oracle.com/cd/A97385_01/appdev.920/a96612/d_metad2.htm#1031458)
    --Reference: [File Extension](http://www.orafaq.com/wiki/File_extensions)
    
        vOBJECT_TYPE := REGEXP_REPLACE(vOBJECT_TYPE, '[[:space:]]', '_');
        
        BEGIN
            SELECT
            directory_name
            INTO vDiretorio
            FROM all_directories
            WHERE directory_name = 'DDL_CVS_' || pOWNER
            ;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN vDiretorio := 'DDL_SCHEMA_EXPORTED_CVS';
        END;
        
        fname := '[' || pOWNER || ']_[' || vOBJECT_TYPE || ']_[' || pOBJECT_NAME || '].' || 
            CASE vOBJECT_TYPE
                WHEN 'PACKAGE'      THEN 'sql' 
                WHEN 'PROCEDURE'    THEN 'sql'
                ELSE 'sql' END;
        BEGIN
            SELECT dbms_metadata.get_ddl(vOBJECT_TYPE,pOBJECT_NAME,pOWNER) into data from dual;
            DBMS_XSLPROCESSOR.CLOB2FILE(data,vDiretorio,fname);
        EXCEPTION
            WHEN OTHERS THEN 
                NULL;
                --DBMS_OUTPUT.PUT_LINE(fname || ' ' || SQLERRM);
                --RAISE_APPLICATION_ERROR(-20000, fname || ' ' || SQLERRM);
        END;
END P_EXPORT_DDL;

Reference/Credits:

• Backing up DDL in Oracle – the DBA way!! / Archive.org
• Stack DBA Admin - Script out Oracle DDL in an automated fashion

Example of use:

BEGIN

FOR cOBJ IN (
    SELECT 
    O.*
    FROM ALL_OBJECTS O
    WHERE O.OWNER NOT IN (
    'SYS','PUBLIC', 'SYSTEM', 'SYSMAN','OUTLN'
    , 'WMSYS','DBSNMP', 'ORACLE_OCM'
    , 'APPQOSSYS', 'EXFSYS', 'CTXSYS', 'XDB'
    , 'ASD', 'FLOWS_FILES'
    ) AND OBJECT_NAME NOT LIKE ('%$%')
    AND O.OBJECT_TYPE NOT IN ('LOB', 'JOB', 'INDEX')
    AND O.TEMPORARY NOT IN ('Y')
) LOOP
    SYS.P_EXPORT_DDL(
        pOWNER          => cOBJ.OWNER,
        pOBJECT_TYPE    => cOBJ.OBJECT_TYPE,
        pOBJECT_NAME    => coBJ.OBJECT_NAME
        );
END LOOP;

END;
/

is a need to:

• analyze what owners and type of objects will be generated
• analyze if there is some object type that has to be a filter (in the example LOB JOB INDEX

I made some changes to allow put in different folders for each OWNER

Thats the final code:

create or replace PROCEDURE     P_EXPORT_DDL 
(
  pOWNER        IN ALL_OBJECTS.OWNER%TYPE DEFAULT 'SYS',
  pOBJECT_TYPE  IN ALL_OBJECTS.OBJECT_TYPE%TYPE DEFAULT 'PROCEDURE',
  pOBJECT_NAME  IN ALL_OBJECTS.OBJECT_NAME%TYPE DEFAULT 'P_EXPORT_DDL'
) AS
    data         CLOB;
    fname        varchar2(256);
    vDiretorio   ALL_DIRECTORIES.DIRECTORY_NAME%TYPE;
    vOBJECT_TYPE ALL_OBJECTS.OBJECT_TYPE%TYPE;
BEGIN
    
    IF (pOBJECT_TYPE IN ('PACKAGE BODY', 'TYPE BODY')) THEN
      vOBJECT_TYPE := CASE pOBJECT_TYPE WHEN 'PACKAGE BODY' THEN 'PACKAGE' WHEN 'TYPE BODY' THEN 'TYPE' ELSE pOBJECT_TYPE END;              
    END IF;
    --Reference: [Table 30-3 - DBMS_METADATA: Object Types](https://docs.oracle.com/cd/A97385_01/appdev.920/a96612/d_metad2.htm#1031458)
    --Reference: [File Extension](http://www.orafaq.com/wiki/File_extensions)
            
        vOBJECT_TYPE := REGEXP_REPLACE(pOBJECT_TYPE, '[[:space:]]', '_');
        
        BEGIN
            SELECT
            directory_name
            INTO vDiretorio
            FROM all_directories
            WHERE directory_name = 'DDL_CVS_' || pOWNER
            ;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN vDiretorio := 'DDL_CVS_DEFAULT_PATH';
        END;
        
        fname := '[' || pOWNER || ']_[' || vOBJECT_TYPE || ']_[' || pOBJECT_NAME || '].' || 
            CASE vOBJECT_TYPE
                WHEN 'PACKAGE'      THEN 'sql' 
                WHEN 'PROCEDURE'    THEN 'sql'
                ELSE 'sql' END;
        BEGIN
            SELECT dbms_metadata.get_ddl(vOBJECT_TYPE,pOBJECT_NAME,pOWNER) into data from dual;
            DBMS_XSLPROCESSOR.CLOB2FILE(data,vDiretorio,fname);
        EXCEPTION
            WHEN OTHERS THEN 
                NULL;
                --DBMS_OUTPUT.PUT_LINE(fname || ' ' || SQLERRM);
                --RAISE_APPLICATION_ERROR(-20000, fname || ' ' || SQLERRM);
        END;
END P_EXPORT_DDL;

Based in this last change, It's important that have directories defined. See example:

At least the default DDL_CVS_DEFAULT_PATH

or for each OWNER with the name DDL_CVS_[OWNER_NAME]
Example: DDL_CVS_MEGA

Example of procedure for generate de DDL object modified for the last day:

create or replace PROCEDURE p_export_ddl_last_modified AS
BEGIN
    BEGIN
        FOR cobj IN(
            SELECT
                *
            FROM
                all_objects
            WHERE
                owner NOT IN(
                    'PUBLIC',
                    'SYSTEM',
                    'SYSMAN',
                    'OUTLN',
                    'WMSYS',
                    'DBSNMP',
                    'ORACLE_OCM',
                    'APPQOSSYS',
                    'EXFSYS',
                    'CTXSYS',
                    'XDB',
                    'ASD',
                    'FLOWS_FILES',
                    'APEX_180100',
                    'SALVACAO',
                    'FWC',
                    'SVCO',
                    'TOCONTROL',
                    'MGGLO2604'
                )
                AND owner NOT LIKE '%BKP'
                AND object_name NOT LIKE('%$%')
                AND object_type NOT IN(
                    'LOB'
                )
                AND temporary NOT IN(
                    'Y'
                )
                AND status IN(
                    'VALID'
                )
                AND last_ddl_time >= trunc(SYSDATE)- 1
            ORDER BY
                8 DESC,
                1,
                2,
                6
        )LOOP
            sys.p_export_ddl(powner => cobj.owner,pobject_type => cobj.object_type,pobject_name => cobj.object_name);
        END LOOP;

    END;
END p_export_ddl_last_modified;