14 Replies Latest reply on Aug 20, 2012 3:49 PM by Joe Upshaw

    How to Export APEX Pages

    Joe Upshaw
      First of all, not wanting to harp on an ancient thread but, my God, why doesn;t APEX provide a way to export the application pages without having to do it one-page-at-a-time via the GUI?!

      So; accepting this inexplicable road block, and bowing to our SDLC controls, I tried to write my own PL/SQL to do this.
      CREATE OR REPLACE PACKAGE BODY US_RISK_DEV.RISK_APEX_UTILITY
      AS
      
          FUNCTION EXPORT_APPLICATION_PAGES( as_OracleDirectoryName    IN ALL_DIRECTORIES.DIRECTORY_NAME%TYPE,
                                             an_ApexApplicationID      IN APEX_040100.APEX_APPLICATION_PAGES.PAGE_ID%TYPE )
          RETURN NUMBER
          IS
              
              TYPE TAB_PAGE_ID IS TABLE OF APEX_040100.WWV_FLOW_STEPS.ID%TYPE
                  INDEX BY BINARY_INTEGER;
      
              lclb_PageExport     CLOB;
                  
              ltb_PageID          TAB_PAGE_ID;   
              
              ls_ExportFileName   VARCHAR2(128);
              
              CURSOR lcsr_GetApplicationPageIDs IS
                  SELECT P.ID PAGE_ID
                  FROM   APEX_040100.WWV_FLOW_STEPS P
                  WHERE FLOW_ID = an_ApexApplicationID;
                  
          BEGIN
              
              OPEN lcsr_GetApplicationPageIDs;
              FETCH lcsr_GetApplicationPageIDs BULK COLLECT INTO ltb_PageID;
              CLOSE lcsr_GetApplicationPageIDs;
              
              FOR ln_PageNdx IN ltb_PageID.FIRST..ltb_PageID.LAST
              LOOP        
              
                  lclb_PageExport := WWV_FLOW_UTILITIES.EXPORT_PAGE_TO_CLOB(  
                                                          p_application_id => an_ApexApplicationID, 
                                                          p_page_id        => ltb_PageID(ln_PageNdx) );
                         
                  ls_ExportFileName := 'APP_' || an_ApexApplicationID || '_' || 
                                       'PAGE_' || ltb_PageID(ln_PageNdx) || '.sql';
                                                          
                  DBMS_XSLPROCESSOR.CLOB2FILE( lclb_PageExport, 
                                                  as_OracleDirectoryName,
                                                  ls_ExportFileName, 0 );                                                    
                                                                                                                          
              END LOOP;
          
          END;        
      
      END RISK_APEX_UTILITY;
      However, when I run it, I get this error stack from down in the guts of the wrapped package:
      >
      ORA-20987: APEX - Application 101 not found logged in as database user JUPSHAW. - Contact your application administrator.
      ORA-06512: at "APEX_040100.WWV_FLOW_ERROR", line 752
      ORA-06512: at "APEX_040100.WWV_FLOW_ERROR", line 1064
      ORA-06512: at "APEX_040100.WWV_FLOW_UTILITIES", line 9013
      ORA-06512: at "US_RISK_DEV.RISK_APEX_UTILITY", line 32
      ORA-06512: at line 10
      >

      I tried adding this into an application page to see if I could get in a security context that would make APEX happy but:

      A. This defeats the whole purpose which is to be able to dump this out in an automated fashion and
      B. Didn't work anyway. I get a page that changes my URL to: http://rv-in0-cerk-01.intcx.net:7777/pls/apex/wwv_flow.accept. The output on this page is:
      >
      404 Not Found

      The requested URL /pls/apex/wwv_flow.accept was not found.
      >

      How do I get around this?

      -Joe

      APEX 4.1.1.00.23
        • 1. Re: How to Export APEX Pages
          Mehabub Sheikh
          I think you have to log in as parsing schema of your application.
          Is JUPSHAW is the parsing schema of your application?

          Thanks,
          Mehabub
          • 2. Re: How to Export APEX Pages
            Joe Upshaw
            Mehabub,

            For sure that is the problem.

            However, my desire is for a particular user to be able to export that pages from any application. So, I want JUPSHAW (in this example) to be able to export all of the applications. Is there any set of credentials that can be granted which will allow this? Baring grants, is there any set of additional PL/SQL lines that would allow me to set the context in such a way that APEX would permit the export?

            -Joe
            • 3. Re: How to Export APEX Pages
              Mehabub Sheikh
              Joe,

              In APEX application is always created in parsing schema. So when you export the page from APEX, the installation script which it creates takes the metadata from parsing schema. So many of the things are hard coded in the script. So when you import them you have to import into the same scema. I think it's a good control from development perspective.

              You can think of shell script where user can login with their user id but behind the scene it will execute the script as parsing schema.

              Thanks,
              Mehabub
              • 4. Re: How to Export APEX Pages
                Joe Upshaw
                No idea how to do that...:-(
                • 5. Re: How to Export APEX Pages
                  fac586
                  Joe

                  Why are you trying to do this?
                  • 6. Re: How to Export APEX Pages
                    Joe Upshaw
                    Because the SDLC control folks want each* page versioned.

                    The good news is that we have replicated our workspaces with import/export and now we are able to promote pages, one at a time, through Dev, QA, Staging, Integration and on to Production. Yay!

                    The bad news is that we are supposed to check these pages in and out of a code repository when making these promotions.

                    Right now, the only way we have of doing this is to go into the GUI, one page and a time, and click through the dialogs (hundreds of pages)...and our intern went back to school ;-) We just want to dump them out and stick them into the repository folders for the associated applications. Once that's done, from that point forward, using the GUI to export/import won't be that big of a deal.

                    -Joe
                    • 7. Re: How to Export APEX Pages
                      fac586
                      Why not use the command line export and splitter utilities?
                      • 8. Re: How to Export APEX Pages
                        Joe Upshaw
                        Forgot to mention that I granted APEX_ADMINISTRATOR_ROLE to JUPSHAW for this test. I understand the desire to lock down the security but, I guess if APEX doesn't provide a way to export multiple pages, it could at least supply some sort of role that would allow this sort of access unilaterally....some sort of APEX_GRAND_POOBAH role.


                        -Joe
                        • 9. Re: How to Export APEX Pages
                          Joe Upshaw
                          Paul,

                          Our updates crossed.

                          Frankly, I was totally unaware of them. I just searched through the online documentation and cant find any reference for them. That works for us if the end result will be bulk export to readily identifiable files. Can you point me to any documentation?

                          Thanks,

                          -Joe
                          • 10. Re: How to Export APEX Pages
                            fac586
                            Joe Upshaw wrote:
                            Paul,

                            Our updates crossed.

                            Frankly, I was totally unaware of them. I just searched through the online documentation and cant find any reference for them. That works for us if the end result will be bulk export to readily identifiable files. Can you point me to any documentation?
                            No official documentation :-( (But better IMO than poking around in undocumented/unsupported metadata and APIs like you're attempting above)

                            John Scott put me on to them:

                            http://jes.blogs.shellprompt.net/2006/12/12/backing-up-your-applications/

                            Sadly he never seems to have got round to writing Part 2 on the Splitter.

                            Get busy on Google and you should find something that fits your environment with respect to OS and version control system etc.
                            • 11. Re: How to Export APEX Pages
                              Joe Upshaw
                              So...trade one set of unsupported, undocumented functionality for another....hmmm.

                              The Google informs me that files produced by the splitter actually fail on import due to a PK violation (http://stackoverflow.com/questions/11077367/apex-export-only-app-components-programmatically-how).

                              sigh why is this so hard???

                              I did find a recent post where a guy is doing basically the same thing as me, and he says successfully. (http://apexplained.wordpress.com/2012/03/20/workspace-application-and-page-export-in-plsql) but, he leaves off the very part that is tripping me up. How to set the user context properly to execute the export. How indeed.

                              -Joe
                              • 12. Re: How to Export APEX Pages
                                Joe Upshaw
                                Got it working...

                                Needed these two lines:
                                wwv_flow_api.set_security_group_id(1334002381221980);
                                apex_custom_auth.set_user('admin');
                                This was buried in a method that I wasn't using from the sample post (in my last post).

                                The workspace ID is hard coded in the above example but, I will change that to be query driven and then, I will be in business. This dumped them all out in less than a second.

                                I am going to publish a finished version here (on Monday!) so that others wont have to beat their heads against the wall.

                                -Joe
                                • 13. Re: How to Export APEX Pages
                                  Joe Upshaw
                                  See my last post
                                  • 14. Re: How to Export APEX Pages
                                    Joe Upshaw
                                    Here's the final working version. Full Credit to Nick Buytaert (http://apexplained.wordpress.com/) without whom I would never have gotten it working.

                                    Aside form the small package below, I created a table to just hold the applications that I want to have exported and four oracle directories to which the exported files should be written.
                                    CREATE TABLE US_RISK_DEV.APEX_APP_BACKUP_TARGETS
                                    (
                                      APEX_APPLICATION_ID    NUMBER,
                                      APEX_APPLICATION_NAME  VARCHAR2(256 BYTE)     NOT NULL, 
                                      CONSTRAINT APEX_APP_BACKUP_TARGETS_PK
                                     PRIMARY KEY
                                     (APEX_APPLICATION_ID)
                                    )
                                    ORGANIZATION INDEX
                                    CREATE OR REPLACE PACKAGE APEX_UTILITY
                                    AS
                                    
                                        FUNCTION EXPORT_APEX_OBJECTS
                                        RETURN NUMBER;
                                    
                                    END APEX_UTILITY;
                                    /
                                    
                                    
                                    CREATE OR REPLACE PACKAGE BODY RISK_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_040100.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;
                                    /