oracle apex. Create an Export Stored procedure

Question

I have applications created in apex in ,each application there are number of pages.

I want to create a stored procedure to export each of these applications and pages.

I came across

SELECT * FROM APEX_APPLICATIONS;

select table_name from all_tables where owner='APEX_030200';

select username from all_users where username like 'APEX_0%' or username like 'FLOWS_0%'; 

But this doesn't help me.

Answer 1

CREATE OR REPLACE PACKAGE BODY APEX_UTILITY AS

PROCEDURE EXPORT_WORKSPACE( as_OracleDirectoryName    IN ALL_DIRECTORIES.DIRECTORY_NAME%TYPE,
                            an_WorkspaceID            IN APEX_040100.APEX_APPLICATIONS.WORKSPACE_ID%TYPE,
                            as_WorkspaceName          IN APEX_040100.APEX_APPLICATIONS.WORKSPACE%TYPE )
IS
    lclb_WorkspaceExport    CLOB;

    ls_ExportFileName       VARCHAR2(128);

BEGIN

    lclb_WorkspaceExport := WWV_FLOW_UTILITIES.EXPORT_WORKSPACE_TO_CLOB( p_workspace_id             => an_WorkspaceID,
                                                                         p_include_team_development => FALSE );

    ls_ExportFileName := 'WS_' || as_WorkspaceName || '.sql';   

    DBMS_XSLPROCESSOR.CLOB2FILE( lclb_WorkspaceExport, 
                                    as_OracleDirectoryName,
                                    ls_ExportFileName, 0 );                                                                             
END;

PROCEDURE EXPORT_APPLICATION( as_OracleDirectoryName    IN ALL_DIRECTORIES.DIRECTORY_NAME%TYPE,
                              an_ApexApplicationID      IN APEX_040100.APEX_APPLICATIONS.APPLICATION_ID%TYPE )
IS
    lclb_AppExport      CLOB;

    ls_ExportFileName   VARCHAR2(128);

BEGIN

    lclb_AppExport := WWV_FLOW_UTILITIES.EXPORT_APPLICATION_TO_CLOB( p_application_id            => an_ApexApplicationID,
                                                                     p_export_ir_public_reports  => 'N',
                                                                     p_export_ir_private_reports => 'N',
                                                                     p_export_ir_notifications   => 'N');

    ls_ExportFileName := 'APP_' || an_ApexApplicationID || '.sql';   

    DBMS_XSLPROCESSOR.CLOB2FILE( lclb_AppExport, 
                                    as_OracleDirectoryName,
                                    ls_ExportFileName, 0 );  

END;                                  

PROCEDURE EXPORT_APPLICATION_PAGE( as_OracleDirectoryName    IN ALL_DIRECTORIES.DIRECTORY_NAME%TYPE,
                                   an_ApexApplicationID      IN APEX_040100.APEX_APPLICATION_PAGES.APPLICATION_ID%TYPE,
                                   an_ApexPageID             IN APEX_040100.APEX_APPLICATION_PAGES.PAGE_ID%TYPE )
IS
    lclb_PageExport     CLOB;

    ls_ExportFileName   VARCHAR2(128);

BEGIN

    lclb_PageExport := WWV_FLOW_UTILITIES.EXPORT_PAGE_TO_CLOB(  
                                                p_application_id => an_ApexApplicationID, 
                                                p_page_id        => an_ApexPageID );

    ls_ExportFileName := 'APP_' || an_ApexApplicationID || '_' || 
                         'PAGE_' || an_ApexPageID || '.sql';

    DBMS_XSLPROCESSOR.CLOB2FILE( lclb_PageExport, 
                                    as_OracleDirectoryName,
                                    ls_ExportFileName, 0 );      
END;        


FUNCTION EXPORT_APEX_OBJECTS
RETURN NUMBER
IS    
    ls_OracleDirectoryName      ALL_DIRECTORIES.DIRECTORY_NAME%TYPE;

    ls_WorkspaceName            APEX_APPLICATIONS.WORKSPACE%TYPE;
    ln_WorkspaceID              APEX_APPLICATIONS.WORKSPACE_ID%TYPE;

    lb_WorkspaceAlreadyAdded    BOOLEAN := FALSE;

    lclb_PageExport             CLOB;

    ln_ExportObjectCnt          NUMBER := 0;        

    ls_ExportFileName           VARCHAR2(128);

    CURSOR lcsr_GetApexAppBackupTargets IS
        SELECT APEX_APPLICATION_ID
        FROM APEX_APP_BACKUP_TARGETS;

    lrec_ApexAppBackupTarget    lcsr_GetApexAppBackupTargets%ROWTYPE; 

    CURSOR lcsr_GetExportAppAndWorkspace IS
        SELECT APPLICATION_ID, APPLICATION_NAME, WORKSPACE_ID, WORKSPACE
        FROM APEX_APPLICATIONS
        WHERE APPLICATION_ID =  lrec_ApexAppBackupTarget.APEX_APPLICATION_ID;

    lrec_ExportAppAndWorkspace  lcsr_GetExportAppAndWorkspace%ROWTYPE;

    CURSOR lcsr_GetExportPages IS
        SELECT P.ID AS PAGE_ID
        FROM   APEX_040200.WWV_FLOW_STEPS P
        WHERE FLOW_ID = lrec_ExportAppAndWorkspace.APPLICATION_ID;

    lrec_ExportPage             lcsr_GetExportPages%ROWTYPE;   

    TYPE TAB_WORKSPACE_LIST IS TABLE OF APEX_APPLICATIONS.WORKSPACE_ID%TYPE
        INDEX BY APEX_APPLICATIONS.WORKSPACE%TYPE;

    ltb_DistinctTablespaces     TAB_WORKSPACE_LIST;

    APEX_WORKSPACE_EXP_DIR      CONSTANT ALL_DIRECTORIES.DIRECTORY_NAME%TYPE := 'APEX_WS_EXPORT_DIR';
    APEX_APPLICATION_EXP_DIR    CONSTANT ALL_DIRECTORIES.DIRECTORY_NAME%TYPE := 'APEX_APP_EXPORT_DIR';
    APEX_PAGE_EXP_DIR           CONSTANT ALL_DIRECTORIES.DIRECTORY_NAME%TYPE := 'APEX_PAGE_EXPORT_DIR';

BEGIN

    APEX_CUSTOM_AUTH.SET_USER('admin');

    OPEN lcsr_GetApexAppBackupTargets;

    LOOP
        FETCH lcsr_GetApexAppBackupTargets INTO lrec_ApexAppBackupTarget;
        EXIT WHEN lcsr_GetApexAppBackupTargets%NOTFOUND;

        OPEN lcsr_GetExportAppAndWorkspace;

        LOOP
            FETCH lcsr_GetExportAppAndWorkspace INTO lrec_ExportAppAndWorkspace;
            EXIT WHEN lcsr_GetExportAppAndWorkspace%NOTFOUND;

            -- Compile a distinct list of container APEX Workspaces to be
            -- exported later

            lb_WorkspaceAlreadyAdded := ltb_DistinctTablespaces.EXISTS( lrec_ExportAppAndWorkspace.WORKSPACE );

            If ( lb_WorkspaceAlreadyAdded = FALSE ) Then
                ltb_DistinctTablespaces( lrec_ExportAppAndWorkspace.WORKSPACE ) := lrec_ExportAppAndWorkspace.WORKSPACE_ID;
            End If;

            WWV_FLOW_API.SET_SECURITY_GROUP_ID(ltb_DistinctTablespaces( lrec_ExportAppAndWorkspace.WORKSPACE ));

            -- Export the application

            ls_OracleDirectoryName := APEX_APPLICATION_EXP_DIR;

            EXPORT_APPLICATION( ls_OracleDirectoryName, lrec_ExportAppAndWorkspace.APPLICATION_ID );

            ln_ExportObjectCnt := ln_ExportObjectCnt + 1;

            OPEN lcsr_GetExportPages;

            LOOP
                FETCH lcsr_GetExportPages INTO lrec_ExportPage;
                EXIT WHEN lcsr_GetExportPages%NOTFOUND;

                ls_OracleDirectoryName := APEX_PAGE_EXP_DIR;

                EXPORT_APPLICATION_PAGE( ls_OracleDirectoryName, 
                                         lrec_ExportAppAndWorkspace.APPLICATION_ID,
                                         lrec_ExportPage.PAGE_ID ); 

                ln_ExportObjectCnt := ln_ExportObjectCnt + 1;

            END LOOP;

            CLOSE lcsr_GetExportPages;                    

        END LOOP;

        CLOSE lcsr_GetExportAppAndWorkspace;

    END LOOP;

    CLOSE lcsr_GetApexAppBackupTargets;

    ls_OracleDirectoryName := APEX_WORKSPACE_EXP_DIR;

    ls_WorkspaceName := ltb_DistinctTablespaces.FIRST;

    WHILE ls_WorkspaceName IS NOT NULL
    LOOP

        ln_WorkspaceID := ltb_DistinctTablespaces(ls_WorkspaceName);

        WWV_FLOW_API.SET_SECURITY_GROUP_ID(ln_WorkspaceID);

        EXPORT_WORKSPACE( ls_OracleDirectoryName, ln_WorkspaceID, ls_WorkspaceName );

        ln_ExportObjectCnt := ln_ExportObjectCnt + 1;

        ls_WorkspaceName := ltb_DistinctTablespaces.NEXT(ls_WorkspaceName);

    END LOOP;

    RETURN ln_ExportObjectCnt;

END;        

END APEX_UTILITY; /