Installing APEX Packaged Applications – the easy way !

When installing Oracle APEX you also get a wealth of additional resources to explore: Packaged Applications.

 

I’m sure everyone already looked at one or two of them. But honestly, it is a bit cumbersome to install them, when upgrading APEX version we often forget to re-install the packaged apps.

And so we waste a unique opportunity to learn from all those 30+ Packaged applications. Each of them has something to offer. This might be a plugin, an interesting idea how to present data, a nifty UI, or it might even solve a business problem

With APEX 18.1 we finally have a documented API to install Packaged Applications from SQL (Plus or Developer): APEX_PKG_APP_INSTALL

Please note that this package already exists in APEX 5.1, but is not documented there.

With that simple API, we can craft a script to install all not yet installed Packaged Applications:

DECLARE
    l_installed_app_id   NUMBER;
    l_workspace          VARCHAR2(200) := 'MYWORKSPACE';
BEGIN
    DBMS_OUTPUT.PUT_LINE('Installing Packaged Apps in Workspace '||l_workspace);
    --
    APEX_UTIL.SET_WORKSPACE(l_workspace);
    --
    FOR cAPP IN 
      ( SELECT PKG_APP_ID
             , PKG_APP_NAME
          FROM APEX_PKG_APPS
         WHERE PKG_APP_TYPE    = 'Database'
           AND APPLICATION_ID IS NULL
           AND WORKSPACE       = l_workspace
         ORDER BY PKG_APP_ID
      )
    LOOP
        DBMS_OUTPUT.PUT_LINE('Install Packaged App: '||cAPP.PKG_APP_ID);
        BEGIN
            l_installed_app_id := APEX_PKG_APP_INSTALL.INSTALL 
                                    ( p_app_id              => cAPP.PKG_APP_ID
                                    , p_authentication_type => APEX_AUTHENTICATION.C_TYPE_APEX_ACCOUNTS
                                    , p_schema              => l_workspace 
                                    );
        EXCEPTION
            WHEN OTHERS THEN 
                DBMS_OUTPUT.PUT_LINE
                  ( 'Installing Packaged App: '||cAPP.PKG_APP_ID||' failed!'||CHR(10)||
                    SQLERRM||' - '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
                  );
        END;
    END LOOP;
END;

Simple as that. Review the log afterwards to find out which Packaged Applications couldn’t be installed and run them manually.

Then pick a weekend to review all apps :-)

Leave a Reply

Your email address will not be published. Required fields are marked *