HOW DO I Automate Export Using SQL Command in ORACLE APEX

Automating the export of Oracle APEX applications is essential for maintaining up-to-date backups, supporting continuous integration, and simplifying deployment workflows. While manual export through the APEX web interface works for occasional backups, automation using SQL commands or command-line tools provides efficiency and consistency for development teams. Oracle APEX includes the APEXExport Java utility, which allows you to export applications from the command line using SQL or shell scripts. This blog explains how to automate your APEX application export process using SQL commands and command-line tools step-by-step.

How to Automate Export Using SQL Command in Oracle APEX

1. Set Up Your Environment
   Ensure you have Java installed on your machine, as the APEXExport utility requires it. The utility comes bundled with the Oracle APEX installation in the utilities directory.

2. Locate the APEXExport Utility
   Find the APEXExport.jar file inside the APEX installation folder on your database server or client machine.

3. Prepare Database Connection Details
   Have your database connection string, schema username, password, and application ID ready.

4. Run the Export Command
   Use the following command to export your application:

   java -jar APEXExport.jar -db <host>:<port>:<service_name> -user <username> -password <password> -applicationid <APP_ID> -skipExportDate
   

   • Replace <host>, <port>, <service_name>, <username>, <password>, and <APP_ID> with your details.

   • The -skipExportDate option removes the export date from the output file for easier version control.

5. Schedule Automation

   • Use OS schedulers such as cron on Linux or Task Scheduler on Windows to run this command automatically at desired intervals (daily, weekly).

   • Direct the output to a designated backup directory with timestamped filenames for organization.

6. Incorporate Into CI/CD Pipelines
   Integrate this command in your build or deployment scripts to export and store application definitions during development or release cycles.

Best Practices for Automating APEX Exports

• Protect your credentials by using environment variables or encrypted files instead of plain text passwords in scripts.

• Keep your export files under version control to track changes and rollback when necessary.

• Test your scheduled exports and restore processes regularly to verify backup integrity.

• Use the -skipExportDate option for consistent file content across exports.

• Automate clean-up of old exports to manage storage space efficiently.

• Combine export automation with import automation for full deployment automation.

 EXAMPLE: For scheduling backups via SQL, use:

sql

Copy

BEGIN

    APEX_EXPORT.GET_APPLICATION (

        p_application_id => 100, 

        p_filename       => 'app_100_backup.sql'

    );

END;

This can be automated using Oracle Scheduler.

Oracle APEX Documentation Links

• Using APEXExport Utility

• Oracle APEX Application Lifecycle Management

• Scheduling OS Jobs

Conclusion

Automating the export of Oracle APEX applications using SQL commands and the APEXExport utility streamlines backup and deployment workflows. By scheduling regular exports, integrating with CI/CD pipelines, and following security best practices, developers can ensure their applications are consistently backed up and ready for migration or recovery. This automation reduces manual effort, minimizes errors, and supports robust application lifecycle management in Oracle APEX environments.

 

HOW DO I Export an APEX Application Manually in ORACLE APEX

Exporting an Oracle APEX application manually is a straightforward and essential task for developers who want to create backups, move applications between environments, or share their work. Oracle APEX provides an easy-to-use interface that allows you to export the full application definition, including pages, components, and supporting objects, into a single SQL script file. This manual export process ensures you can safely preserve your application and deploy it elsewhere whenever needed. In this blog, we explain the step-by-step procedure for manually exporting your APEX application.How to Export an APEX Application Manually

1. Log in to Oracle APEX
   Access your APEX workspace through the web interface using your credentials.

2. Navigate to App Builder
   Click on the App Builder icon to list your applications.

3. Select Your Application
   Click the application you want to export to open its management page.

4. Open Export / Import Menu
   In the left sidebar, click Export / Import.

5. Choose Export
   Select the Export option to start the export wizard.

6. Set Export Options

   • File Format: Choose between SQL (default) or JSON format.

   • Include Supporting Objects: Enable this if your app uses custom tables, packages, or seed data you want to export along with the application.

   • Skip Application Export Date: Optional, to make the export file reusable without changing export timestamps.

7. Export the Application
   Click Export to generate the SQL file. The browser will download the .sql file automatically.

8. Save and Store Safely
   Keep the exported file in a secure location, ideally under version control.

Best Practices for Manual Export

• Export regularly, especially before making major changes or upgrades.

• Use descriptive file names that include application ID, version, and date.

• Always include supporting objects unless you are sure they exist in the target environment.

• Test importing the exported file in a development workspace to verify completeness.

• Use version control systems like Git to track changes to exported files.

• Document your export and import procedures for team consistency.

 Notes:

1. Go to APEX App Builder and open your application.

2. Navigate to Utilities > Export/Import > Export.

3. Choose Export Application Definition.

4. Select Export Supporting Objects (if applicable).

5. Click Export, then download and store the .sql file securely.

 For scheduling backups via SQL, use:

BEGIN

    APEX_EXPORT.GET_APPLICATION (

        p_application_id => 100, 

        p_filename       => 'app_100_backup.sql'

    );

END;

This can be automated using Oracle Scheduler.

 Backing up the Entire APEX Workspace

Best Practice: Backup Everything, Not Just the App

While application backups include pages and logic, a full workspace backup includes:

• Users & Authentication Settings

• RESTful Web Services

• Workspace-Level Configurations

How to Export an APEX Workspace

1. Go to APEX Administration.

2. Navigate to Manage Workspaces > Export Workspace.

3. Select the workspace and click Export.

4. Download the .sql file.

Backing up the Underlying Database

Best Practice: Full Database Backup

Since APEX apps rely on Oracle Database objects (tables, views, packages, etc.), always back up the APEX schema and associated database objects.

Methods to Backup the Database

1. Using Oracle Data Pump (Preferred)

2. expdp username/password@db schemas=APEX_SCHEMA dumpfile=apex_backup.dmp logfile=apex_backup.log

This method captures all APEX-related tables, PL/SQL, and configurations.

3. Using RMAN (For Full Database Backup)

4. RMAN> BACKUP DATABASE FORMAT '/backup/apex_full_%U.bak';

This ensures a full database recovery in case of failure.

Storing Backups Securely

Best Practice: Keep Backups in Multiple Locations

• On-Premise Storage: Save local copies for quick restores.

• Cloud Storage: Store backups on Oracle Cloud, AWS S3, or Google Drive.

• Version Control (GitHub, GitLab, Bitbucket): Store exported .sql backups under version control.

Example backup folder structure:

/backups/

  ├── daily/

  │   ├── app_100_backup_20240310.sql

  │   ├── db_apex_schema_20240310.dmp

  ├── weekly/

  ├── monthly/

Enabling Flashback and Undo for Data Recovery

Best Practice: Use Oracle Flashback to Restore Data

Enable Flashback Query to retrieve previous states of tables without requiring a backup restore.

SELECT * FROM USCG_DATA AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);

This can be a lifesaver for quick data recovery.

Testing Restores Regularly

Best Practice: Verify That Your Backups Work

• Regularly import backups into a test APEX instance.

• Validate that all pages, components, and database objects work correctly.

To restore an application:

1. Go to App Builder > Import.

2. Select the backup .sql file.

3. Choose Install Application.

For database recovery:

impdp username/password@db schemas=APEX_SCHEMA dumpfile=apex_backup.dmp logfile=restore.log

 

Official Oracle APEX Documentation

• Exporting and Importing Applications

• Managing Oracle APEX Applications

Conclusion

Manually exporting your Oracle APEX application is a simple but critical step in application lifecycle management. This process ensures you have a reliable backup and a portable version of your app that can be shared or migrated to different environments. By following the step-by-step method and adhering to best practices such as regular exports, including supporting objects, and version control, you can safeguard your development efforts and maintain smooth deployment workflows.

HOW DO I Backup an Oracle APEX Application in ORACLE APEX

Backing up your Oracle APEX application is one of the most important steps you can take to protect your development work. Whether you're preparing for a migration, safeguarding against data loss, or maintaining version control, exporting your application ensures that all components—pages, logic, shared components, and settings—are preserved in a single, portable file. Oracle APEX offers multiple options for exporting applications, including browser-based tools, command-line utilities, and developer tools like SQL Developer. This guide explains how to back up your APEX application efficiently and reliably.

How to Backup an Oracle APEX Application in Detail

1. Exporting from the APEX Interface (GUI)

   • Navigate to App Builder.

   • Click the application you want to back up.

   • From the side menu, click Export / Import, then Export.

   • Choose options:

     • Export Format: SQL (default) or JSON

     • Include Supporting Objects: Select this if your app depends on packages, tables, or seed data

   • Click Export to generate and download a .sql file containing the full application definition.

2. Exporting with SQL Developer

   • Open Oracle SQL Developer.

   • Connect to your APEX workspace schema.

   • In the APEX view, locate the application.

   • Right-click and select Export.

   • Save the generated .sql file locally.

3. Using the APEXExport Command-Line Tool
   Ideal for scheduled backups or automation pipelines:

   java oracle.apex.APEXExport -db <connection_string> -user <schema_user> -password <password> -applicationid <APP_ID>
   

   • The export will create a file named f<APP_ID>.sql

   • Useful for continuous integration or nightly backups.

4. Export from APEX Administration (Instance Admin Only)

   • Navigate to Administration Services → Manage Applications → Export.

   • Select individual apps or export all applications in a workspace.

   • This method allows batch export with or without supporting objects.

Best Practices for Backing Up APEX Applications

• Schedule Regular Backups
  Automate daily or weekly exports using APEXExport or database jobs.

• Use Version Control
  Store your .sql exports in Git or SVN with proper commit messages to track changes.

• Include Supporting Objects
  When exporting, include packages, procedures, and reference tables to ensure complete restoration capability.

• Use Clear Naming Conventions
  Name files with app ID, version, and date:

  f100_customer_portal_v1_2025_07_13.sql
  

• Verify Restorability
  Occasionally test imports in a development workspace to ensure your backups are working as expected.

 Example 1

Step 1: Exporting an APEX Application

1. Log into Oracle APEX and navigate to your workspace.

2. Go to App Builder and select the application you want to back up.

3. Click on Utilities > Export/Import > Export.

4. Under Export options: 

• Export Application Definition: Select this to back up the entire application.

• Export Supporting Objects: Choose this if you also need related files.

• Include Application Export Compatibility Mode (choose the latest APEX version).

5. Click Export and download the .sql file. 

• This file contains all components of your application and can be used to restore or move it to another instance.

Step 2: Importing a Backup to Restore an APEX Application

If you need to restore your application from a backup:

1. Go to App Builder > Import.

2. Select the previously exported .sql file.

3. Click Next, then select Install Application.

4. Choose Replace existing application if updating an app or Install as new application to create a separate copy.

Click Install to restore your application.

Oracle APEX Documentation Links

• Exporting and Importing Applications

• APEXExport Command-Line Utility

• Managing APEX Applications

Conclusion

Backing up your Oracle APEX application is quick, effective, and essential for responsible development. Whether you're working in a local workspace or managing multiple environments, having consistent and restorable backups gives you peace of mind. Use the built-in APEX export options or command-line tools depending on your workflow, and follow best practices like naming conventions and version control. With a reliable backup process in place, you’ll protect your app—and your time—from unexpected issues or data loss.

 

HOW DO I Add Facet Searches in ORACLE APEX

Facet searches in Oracle APEX provide an intuitive and interactive way for users to filter data across multiple dimensions without writing any SQL or form input. Whether you're building a catalog, directory, product list, or data dashboard, facet searches help users drill down to the information they want with just a few clicks. Oracle APEX makes it easy to add facets to reports, maps, or cards—enhancing usability while keeping performance optimized. This blog post walks you through how to add facet searches to your APEX pages, step-by-step.Step-by-Step: How to Add Facet Searches in Oracle APEX

1. Create or Identify a Report Region

   Start with a table or SQL query that serves as the data source for your report. You can use Classic Reports, Interactive Reports, Cards, or Maps. Make sure the table or view has multiple columns that could be used as filter dimensions, like status, category, region, date_created, etc.

2. Create a Faceted Search Page (Optional)

   To add facets to a new page:

   • Go to App Builder → Create → Page.

   • Choose Faceted Search from the report options.

   • Select your table or write a SQL query as the data source.

   • Choose your report type: Classic Report, Cards, or Map.

   This automatically creates a region for facets on the left and the results on the right.

3. Add Facets to an Existing Page

   If you want to enhance an existing page:

   • Open your page in Page Designer.

   • Add a new Faceted Search region (under Regions).

   • Set the Source Type to your table or SQL query.

   • Under the Facets node, click ➕ to add a new facet.

4. Choose Facet Types

   Oracle APEX supports different types of facets:

   • Checklist – for categories, tags, or statuses

   • Range – for numeric or date columns

   • Search – for keyword filtering

   • Select List – for dropdown-style facets

   • Group By – for grouping and summarizing filters

   Example: Add a checklist facet for status, a range facet for salary, and a date range for hire_date.

5. Map Facets to Columns

   For each facet:

   • Set the Column to match the dataset (e.g., department_id, region).

   • Optionally add display text, order by, or null display values.

   • You can also define custom LOVs if the column values are not descriptive enough.

6. Test and Refine

   • Run the page and interact with the facets.

   • Verify that selecting a facet updates the report dynamically.

   • You can combine multiple facets—APEX handles AND/OR logic automatically.

   • Add Show Count to help users understand how many results match.

Best Practices for Faceted Search in APEX

• Keep facet categories meaningful and limited to 5–10 options where possible.

• Use indexing on frequently filtered columns for performance.

• Avoid redundant facets that confuse users (e.g., category and subcategory when one is sufficient).

• Use Null Display Value to handle missing data gracefully.

• Disable unused facets when no data matches using Hide if No Values.

• Use cards or maps for better visual feedback when working with images or geo-data.

 

Step 1.  Add the facet search on the left hand side, in the Search region.

Step 2.  Add the type as “Checkbox Group” and  Give the label a name for the Region.

Step 3. List of value type: “Distinct Values”

Step 4. Set the Database Column that we want to use for searching.

Results:

Adding another Facet Search

Lets add another faceted search for States where the earthquake occurred. For this we will use the “PROPERTIES_NET” field from the database table.

Step 1. Add the facet

Step 2. Change the Label to “State

Step 3. Ste the List of Values to “distinct Values”

Step 4.  Set the Source to the column “PROPERTIES_NET”

Step 5. Save and Run

Results:

Official Oracle APEX Documentation

• Faceted Search Pages in APEX

• Oracle APEX Faceted Search Concepts

• APEX Facet Types and Settings

Conclusion

Adding facet searches in Oracle APEX improves your application's interactivity and empowers users to explore data on their own terms. With just a few clicks, you can create dynamic filters that work across multiple columns, support keyword searching, and integrate seamlessly with reports, cards, or maps. Whether you’re building dashboards, catalogs, or GIS apps, APEX’s Faceted Search region offers a clean and scalable way to improve UX without heavy development. Use it early in your design process to turn raw data into smart, discoverable content.