X

The Oracle APEX blog is your source for APEX news, technical tips and strategic direction

Remote Deployment of your APEX App is just One Click Away!

Chaitanya Koratamaddi
Senior Principal Product Manager, Oracle APEX | Database Tools

When developing with Oracle Application Express (APEX), you can follow standard development lifecycle practices, such as having different environments for Development, QA/Test and Production. These environments generally reside in separate databases and these environments can either be on-premises or use hosted services, such as the Oracle Autonomous Database Cloud Service, or a combination of both. 

Oracle APEX enables you to export and import apps to and from the Oracle Autonomous Database Cloud Service, private and public clouds, on-premises, and even the free public evaluation service, apex.oracle.com. APEX application export files can be imported into any other Oracle Database, providing APEX (that release or higher) is installed in the target database. You may choose to perform development and testing in the Oracle Autonomous Database Cloud Service, but deploy the production applications on premise, or vice versa.

To deploy an application from one APEX instance to another, you must copy both the application definition and underlying database objects used by the application. In APEX, you can package the app definition along with database objects, images, and seed data necessary for your application to run correctly using functionality in APEX  called Supporting Objects. You can then export this app and deploy into your target Oracle APEX instance, which will both install the application definition and create the underlying database objects.

Oracle APEX 20.1 simplifies this process by introducing the One-click Remote Application Deployment wizard. Using a simple wizard, you can now directly export and deploy your application along with the supporting objects into the target system. You no longer need to separately export the app and its objects from the source system into files and then import into the target system. With just one-click, you can directly deploy your app  definition along with the objects from the source system to the target system. For example, from within your development environment, you can deploy your app to the remote production environment. You do not need to directly access your production environment to import the app.

The physical location of your APEX environments does not impact the application deployment. For example, you can use the One-Click Remote Application Deployment option in any of the following scenarios:

  • Deploying an app from one workspace to another in the same APEX instance
  • Deploying an app from a workspace in your local APEX instance to a workspace in a remote APEX instance

To use Remote Application Deployment, a developer simply REST Enables the schema associated with their target workspace. This can easily be done in RESTful Services in the SQL Workshop. One-click Remote Application Deployment leverages the Oracle REST Data Services (ORDS) REST Enabled SQL Service.

Prerequisites

To perform One-click Remote Application Deployment, the following are the prerequisites: 

Source System:

  • Must have Oracle APEX 20.1 or above installed
  • Must be configured to access RESTful services
    • Your DBA will use DBMS_NETWORK_ACL_ADMIN package to configure a network access control list (ACL) to permit outbound web service calls from your APEX instance. For more information, see the Oracle Application Express Installation Guide.

    • Your DBA must create a Wallet on the database server. The instance administrator must configure the Wallet attributes on the Instance Settings page to specify the file system path to the Wallet and the Wallet password (if required). To learn about how to create a Wallet and enable it for Oracle APEX Workspaces, see the APEX, HTTPS, certificates and the Oracle Wallet blog post. For more information, see the Oracle Application Express Administration Guide.                

Note: Once Oracle APEX 20.1 is available on the Autonomous Database, you will be able to use your APEX on Autonomous instance for remote deployment. The Autonomous Database is pre-configured with an Oracle Wallet and a network access control list (ACL). No further configuration is necessary.

Target System:

  • Must have Oracle APEX 20.1 or above installed
  • Must include Oracle REST Data Services (ORDS) 19.x or later.
  • ORDS deployment defaults.xml file must include this entry:
    <entry key="restEnabledSql.active">true</entry> 
  • The schema should be enabled for use with ORDS RESTful Data Services. If not already registered, then perform the following steps:
  1. Navigate to SQL Workshop > RESTful Services.
  2. Click Register Schema with ORDS
  3. Review the ORDS Schema Attributes and click Save Schema Attributes. Now, you see a message that the schema has been successfully registered.​

One-click Remote Application Deployment: Example

In this example, you will deploy an application from your development environment – APEXDEV to your testing environment – APEXSTAGE. In the first scenario, you will deploy your app along with its underlying supporting objects into the target system. In the second scenario, you will overwrite this app and upgrade the supporting objects in the target system.

To perform the steps in this example, download  and unzip the remote_app_dep_example.zip to your working directory. This zip file includes two scripts: demo_emp_table.sql and demo_emp_update.sql.

Deploying an Application with Supporting Objects

In this section, you will create the DEMO_EMP table and then build an application on top of that table. Next, you will add the supporting objects installation script to this app definition. Finally, you will use the One-click Remote Application Deployment wizard, to deploy this app to the testing instance.

Creating an Application:

Create a table and then create an application on top of this table. Perform the following steps:

  1. Log in to your development environment (APEXDEV in this example) and create an application on the DEMO_EMP table. To create the table and load sample data, you can use the demo_emp_table.sql. Navigate to SQL Workshop > SQL Scripts and then click Upload.
  2. Click Choose File and select demo_emp_table.sql from your working directory. Click Next.
  3. The script is now uploaded. Click Run.
  4. Click Run Now.
  5. The script is run successfully, and the table has been created. Now, navigate to Object Browser.
  6. Under Tables, locate and click DEMO_EMP. Click Data to review.
  7. Click Table and then click Create App
  8. Click Create App again.
  9. In the Create Application wizard, enter Employees App for Name and then click Create Application.
  10. Click Run Application. Enter your workspace username and password and then click Sign In. Your application should look like: 

Adding the Supporting Objects Installation Script:

Add the supporting object installation scripts so that you can include them along with the application definition. In this example, you will add demo_emp_table.sql under Installation scripts. This script includes the DDL for DEMO_EMP table and also the INSERT statements for the sample data.

  1. In your development environment (APEXDEV in this example), navigate to your application home page and click Supporting Objects.
  2. Under Installation, click Installation Scripts.
  3. Click Create.
  4. Click Create From File.
  5. For Name, enter DEMO_EMP_TABLE and click Next.
  6. Click Choose File and select the DEMO_EMP_TABLE.sql from your working directory
  7. Click Create Script

Remotely Deploying your Application:

Your application along with the supporting objects is now ready for deployment. Perform the following steps:

  1. In your development environment (APEXDEV in this example), navigate to your application home page. Click Export / Import.
  2. Select Remote Deployment and click Next.
  3. Choose an existing REST Enabled SQL Service or create a new one. In this example, click the Add Deployment Server ( + ) icon next to the Deployment Server select list.
  4. Enter the base URL for the REST Enabled SQL Service defined for your deployment server (APEXSTAGE in this example). The schema in your deployment server has already been enabled for use with ORDS RESTful Data Services in prerequisites.

    In a new browser tab, log in to your deployment workspace and navigate to SQL Workshop > Restful Services.

    Expand RESTful Data Services > Modules > oracle.example.hr > empinfo/ GET. Copy the Full URL.

  5. Switch to the browser tab of your development workspace (APEXDEV) and in the Create REST Enabled SQL Service wizard, for Name, enter APEXSTAGE. For Endpoint URL, paste the base URL copied from the deployment instance in the previous step. The base URL should include the ORDS context root and schema URL prefix. For example, https://host:8096/ords/apexstage  Then, click Next.

  6. Select the credential to authenticate against. If you have an existing credential, navigate to Credentials in Shared Components. In this example, you create new credentials.

    ORDS REST Enabled SQL Service supports Basic Authentication and OAuth2 Client credentials flows. Oracle recommends always using empty credentials for remote application deployment in shared development environments so only those with the knowledge of those credentials can deploy applications or run SQL or PL/SQL on the REST Enabled SQL reference. If Store Credentials is turned off, the named credential will be created, with NULL values stored as Client ID and Client Secret.
    In essence, this allows you to just create the reference to the REST enabled SQL without supplying credentials. 
    Enter APEXSTAGE for Credential Name, select Basic Authentication for Authentication Type, and turn off the Store Credentials switch. Click Create.

  7. Now you see a message that REST Enabled SQL Service has been created. From the Deployment Server select list, choose the one that you just created. Click Next.

  8. Enter the values for Client ID and Client Secret and click Next. In this example, you use Basic Authentication. Therefore, enter the database user name (deployment schema) for Client ID and the corresponding password for Client Secret.
    Note: User names for Basic Authentication are converted to upper case. To include a case-sensitive user name, prefix it with exclamation mark (!).

  9. The Remote Deployment Options page appears.
    • Deploy Application: For 'As Of', you can specify a time in minutes to export an app definition from an earlier state.This option enables you to go back in time in your application, perhaps to get back a deleted object. In this example,leave it as blank.
    • Export Preferences: You include support object definitions in your export and ensure that they are installed into the deployment server automatically upon deployment. In this example, for Export Preferences > Export Supporting Object Definitions, select Yes and Install on Import Automatically.
    • Deployment Overrides: In this example, even though the application does not yet exist on the target system, turn the Overwrite Existing Application option on so that you can preserve the application ID, and enable this application for future overwrites.
      Note: Owner Override – You can use this attribute to import this application into the available schemas defined by your REST Enabled SQL connection. Workspace Override – You can select the workspace where the application should be deployed from the list.

      After making the above selections, click Deploy.

  10. You see the Remote Deployment Successful message. Your application has been successfully deployed to remote server (APEXSTAGE in this example).

  11. Log in to your remote APEX instance. Make sure that the application is available and with the same application id as in the source system. Navigate to Object Browser and verify that the DEMO_EMP table has been created and the data is available. Run the application to verify.

Overwriting Existing Application and Upgrading Database Objects

In this section, you will make some changes to your application and the database objects in the development instance (APEXDEV in this example). You will add a chart to the Employees App and add a new column to the DEMO_EMP table. You will then push these changes to the testing environment. The One-click Remote Application Deployment wizard lets you overwrite the existing application and upgrade the database objects as well.

Perform the followings steps:

  1. Log in to your development workspace. Add a column to the DEMO_EMP table and populate it with some data.  Perform the following steps:
       a) Navigate to SQL Workshop > SQL Scripts and then click Upload.
       b) Click Choose File and select demo_emp_update.sql.
       c) Enter demo_emp_update for Script Name and click Upload.
       d) Now run demo_emp_update.sql.
       e) Click Run Now. The script is run successfully.
  2. Add a chart to your application. Perform the following steps:
      a) Navigate to the App Builder and then to your application home page.
       b) Click Create Page. Then, select Chart and click Next.
       c) Accept the default Bar for Chart Type and click Next.
       d) Enter Salary By Department for Page Name.
       e) Select Breadcrumb for Breadcrumb. Click Next.
       f)  For Navigation Preference, select Create a new navigation menu entry and click Next.
       g) For Table / View Name, select DEMO_EMP (table) and click Next.
       h) For Column Mapping, select the following and click Create.
           •    Label Column: DEPTNO
           •    Value Aggregation: Sum
           •    Value Column: SAL
        i) The chart is created. Run the page. The chart should look like:

  3. Navigate to your application home page and click Supporting Objects.
  4. Under Upgrade, click Upgrade Scripts.
  5. Click Create. Then, select Create from File and click Next.
  6. Enter demo_emp_update for Name and click Next.
  7. Click Choose File and select demo_emp_update.sql from your working directory. Click Create Script.
  8. Enter the following code for Query to Detect Existing Supporting Objects. Then, click Apply Changes.
    SELECT OBJECT_NAME FROM SYS.USER_OBJECTS WHERE OBJECT_NAME = 'DEMO_EMP'

  9. Navigate to the current active application home page and click Export / Import.
  10. Select Remote Deployment and click Next.
  11. From the Deployment Server select list, select the deployment server that you earlier created. In this example, select APEXSTAGE.
  12. Enter the Client ID and Client Secret. Click Next.
  13. For Export Preferences > Export Supporting Object Definitions, select Yes and Install on Import Automatically.
  14. The application should be overwritten on the target system. For Deployment Overrides > Overwrite Existing Application: Turn On.
  15. Click Deploy. The application has been successfully deployed to the remote server.
  16. Log in to your deployment workspace. 
    Navigate to SQL Workshop > Object Browser. Click DEMO_EMP. Notice that the COUNTRY column is available. 
    Click Data to review.
  17. Navigate to App Builder. Run the Employees App application.
    Click Salary By Department in the left navigation menu. The chart is displayed.

In this blog post, you are introduced to the One-click Remote Application Deployment feature in Oracle APEX 20.1. Using One-click Remote Application Deployment, you can deploy an application along with supporting object definitions to another workspace or to remote Oracle APEX instances. You Access the Remote Deployment option from the Export/Import option in App Builder. You learned how to override an existing application deployment and upgrade the database objects in the remote instance.

Additional Information

Application Express (APEX) is the low-code application development platform which is included in every Oracle Database and is also provided with every Oracle Database Cloud Service, including Oracle Autonomous Database and the Oracle Cloud Free Tier. APEX, combined with the Oracle Database, provides a fully integrated environment to build, deploy, maintain and monitor data-driven business applications that look great on mobile and desktop services. To learn more about Oracle Application Express, visit apex.oracle.com. To learn more about Oracle Autonomous Database, visit oracle.com/autonomous.