Oracle APEX app import & export

When you work on an enterprise application with a team of professionals, you should definitely use multiple environments: at least DEV, QA and PROD. That raises a tricky question: how do you move your APEX application between them, safely and repeatably?

The answer is export and import. This article covers both the manual way in the UI and the automated way with scripts. APEXLang is not part of this article!

1. How it works
2. Export & Import using UI
3. Automated Export & Import
4. Conclusions

How it works

To move your APEX application from one environment to another, you export it from the source and import it into the target. It’s always two separate actions, so this article is split into two parts: export and import.

Actually, Remote Deployment can handle that in one command, but it’s not a common approach, we’ve never used it for production.

Oracle made various efforts to make export & import efficient and smooth. I can’t tell whether they succeeded, but right now it works reliably, and that’s the most important point. Due to the APEX 26 release, I won’t explain readable export formats such as YAML, since they are no longer in use. So, right now there are two options: the standard export (.sql file) and APEXlang. Both are sets of files that satisfy a complete export and import of your application. This article explains only the standard, old-fashioned export in a .sql file.

Export

An export is a script, not a backup. An APEX application isn’t stored as a file. It lives in the APEX schema(s), a set of internal tables owned by APEX, spread across hundreds of rows: one for the application, one for each page, region, item, process, list of values and so on.

An export reads all those rows and writes them out as a SQL script, for example f100.sql. If you open it, you'll see a long sequence of PL/SQL calls to internal APEX packages, such as wwv_flow_imp. Each call creates one piece of the application. The script starts by opening an import session and ends by closing it, and everything in between is the application, in the right order.

So the file doesn’t contain the application as data. It contains the instructions to rebuild it.

The export contains the application, not your database. Tables, packages and data must be deployed separately (more on that later).

Import

An import process is just running that script. This is why importing is simple. The target APEX instance runs the script, the calls recreate the pages, regions and processes, and the application exists again.

Before the script runs, APEX prepares the context it needs: which workspace the app goes into, which schema it will run as, and which application ID it gets. In the UI you answer these questions in the Install Application dialog. In a script, you set them with apex_application_install before running the file. Either way, they are the same three decisions.

The application ID decision has a real effect. If you install with an ID that already exists, the existing application is replaced, so an import doubles as an update. If you pick a new ID, you get a second copy next to the old one. Replacing an application also ends the sessions of users who are currently logged in to it, unless you tell the installer to keep them.

Export & Import using UI

To export an application, open it in App Builder and use one of the Export buttons. There are several, and they all lead to the same Export page. The most common route is the application’s home page: select your application, click Export/Import in the toolbar, and choose Export. You can also reach it from Shared Components, where the export option is listed among the application’s tools.

On the Export page, choose the application, adjust the options for your needs, and click Export. APEX generates the file and your browser downloads it.

Keep the format as SQL for the standard .sql output. Use Custom Export for a configurable export, or just use Standard if you don’t care. For Custom Export, you have a list of important options. I recommend that you understand all of them for a proper export.

Don’t worry about breaking anything with these options. They are only switches that add or remove extra information in the export. The application itself, with all its pages, is always part of every export. These options only tailor the export to your needs.

Split into Multiple Files
Your export file can be in .sql or .zip format. A .sql file is one file that contains the whole application export. A .zip is a set of separate files organized in folders. The Split into Multiple Files switch means exporting the application as a .zip file, not as a .sql file.

I recommend a split export for big projects. A single large .sql file is hard to import reliably, and it is unreadable in Git, where one small change is buried in thousands of lines.

Do not afraid that .sql file, it’s quite understandable and readable. In git you can also track the changes in such file. The only issue you have with this .sql file is a lot’s of internal ID’s, but until you need to manage releases this is absolutely not important.

My recommendation: Leave the internal IDs alone. They are unique, and APEX uses them to build the relations between pages, regions, items and other components. If you edit them in the exported text file, the application will behave unpredictably, and the import may fail.

1.Build Status Override

You have two options: Run and Build Application and Run Applciation Only.

Run and Build Application is the default behavior, the same as what you see in App Builder: the app can be run and edited. If you choose Run Application Only, the app can be run but not modified. Your workspace administrator can change this setting later, without a re-import or re-install.

2. Supporting Object Definitions

Includes the install scripts you defined under Supporting Objects. Choose Yes if the app should be able to install its own database objects. If your schema changes are managed by a separate tool, choose No.

Supporting objects are here:

3. Public Reports

Includes saved interactive reports that users made public.

4. Private Reports

Includes users’ personal saved reports. Keep it at No for deployments, because personal filters from DEV shouldn’t reach PROD.

5. Report Subscriptions

Includes email subscriptions on reports. Keep it at No, or QA will start emailing real people.

6. Translations

Includes translated versions of the app. Turn it on if the app is multilingual.

7. Debugging

Exports the application with debugging on or off. It’s better to avoid using this flag for production exports.

8. Original ID’s

What are IDs? Every component in an APEX app (page, region, item, process, list of values and so on) has a unique internal number. APEX uses these numbers to link components together. A region belongs to a page, an item belongs to a region, and a process points to a page. Each of those links is stored as an ID. They’re internal, so you never see them in Page Designer.

How are they used in an export? The export file refers to components by these numbers. On import, APEX normally adds an offset to them, so the imported components get new IDs and can’t clash with anything already in the target workspace. That’s why the same app can be installed next to a copy of itself.

What does Export with Original IDs do? It exports the IDs as they are in the source, without that offset. As a result, two exports of the same app contain the same numbers, so Git shows only real changes. Without it, the numbers can differ between exports, and the diff fills with noise.

When to use it: turn it on for exports that go into Git, together with the split export. Leave it off for one-off copies, where you want a fresh set of IDs and no chance of a clash.

9. Owner Override

Owner Override is the setting that decides which database schema owns the application on the target. Its official name is the parsing schema. This is the schema the app runs as when it executes SQL and PL/SQL, and every query, table and package the app uses is resolved in this schema.

It matters because schema names often differ between environments. Your app might run as HR_DEV in DEV, HR_QA in QA and HR in PROD. The export file remembers the schema from the source. Owner Override tells APEX to use a different one instead, so you don't have to edit the file.

10. Audit Information

Includes audit information such as Changed By or/and Changed On in export. Usually not used.

11. As of

Exports the app as it was N minutes ago, using database flashback. It’s useful for recovering from a bad change, and it depends on how much undo data your database retains.

Don’t rely on this feature. Use proper application backups instead. It works through database flashback, which depends on the Undo data your database still holds, and that could cover an hour or only couple of minutes. You never know precisely.

12. Workflow Instances

Specify whether to include Workflow instances in your application export. Available options include. On includes Workflow instances in the application export. Instances with active state will be set to suspended in the export.

13. Task Instances

Includes Task instances in the application export.

Get Yurii Slobodianiuk’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

That’s everything you need to know about exporting an application from the UI. Import is simpler. Select the file you want to import, make sure the correct File Type is selected, and click Next.

Next, you’ll see the following screen.

Validate all the information here. Specify the correct Parsing Schema and Build Status, and pay attention to the Install As Application value. There are three options:

• Auto Assign New Application ID creates a new application and assigns a new ID. It’s the safest option when you just need to import the new app.
• Reuse Application ID From Export File reuses the application ID from the import file. It’s mostly used for continuous application updates, when the application is already installed and you need to update it.
• Change Application ID imports the app with a manually defined application ID. Double-check the ID you enter, because you may overwrite another application. Also, you can’t use IDs from the range 3000–8999, as it’s reserved for APEX.

Automated Export & Import

APEX offers three ways to automate export and import: SQLcl, the PL/SQL package APEX_EXPORT, and the APEXExport Java utility. I strongly recommend using only one of them, and that one should be SQLcl.

Don’t mix different export and import tools in the same process. If you export with one utility and import with another, you may end up with different options and settings on each side, and the application can behave differently from what you tested. Pick one tool and use it everywhere.

SQLcl is Oracle’s modern command line for the database. It has a built-in apex command, so there is nothing extra to install. This manual takes you from a first export to a repeatable script.

Step 0: What you need

• SQLcl installed, with Java available on the machine. Check it with
  sql -V.
• A database connection to the environment. Connect as the schema that owns the application (the parsing schema), or for workspace exports as a user with the APEX_ADMINISTRATOR_ROLE.
• The application ID you want to export.

You can also ask SQLcl for built-in help at any time:

help apex export-application
help apex export-application examples

Step 1: Connect

sql hr_dev/password@dev-host:1521/DEVPDB

Or start without a connection and connect later, which keeps the password out of your shell history:

sql /nolog
SQL> connect hr_dev@dev-host:1521/DEVPDB

SQLcl then asks for the password.

Autonomous Databases and connection with wallet also supported, check official manual for SQlcl for more info.

Step 2: Choose your export format

Since APEX 26.1 you can export in two formats, and you choose with -exptype:

• SQL, the classic PL/SQL script.
• APEXLANG, the readable .apx source files.

Step 3: Export as SQL

The simplest command:

apex export -applicationid 100

SQLcl writes f100.sql to the current directory. That's a full backup of the application, and it's already importable.

For a team project you want a better export:

apex export -applicationid 100 -split -expOriginalIds -skipExportDate

What each flag does:

• -split writes one file per page and component into a folder instead of one huge .sql file. Reviews and Git diffs become readable.
• -expOriginalIds keeps internal component IDs stable between exports, so diffs show only real changes.
• -skipExportDate removes the export timestamp. Without it, Git sees a change in every file on every export.

Add extra content only when you need it:

apex export -applicationid 100 -split -expOriginalIds -skipExportDate -expComments -expTranslations -expSupportingObjects Y

You can also export the whole workspace, meaning users, groups and workspace files:

apex export -workspaceid 1234567890

Use -dir to choose where the files land, for example -dir ./release/app100.

What you get

With -split, you'll see a folder named after the application, with an install.sql inside and subfolders for pages, shared components and so on. Commit the whole folder to Git.

Step 4: Import the SQL export

The export is a normal SQL script, so importing means running it. First tell APEX where the app should go. This is the scripted version of the Install Application dialog.

Connect to the target environment, then run:

begin
  apex_application_install.set_workspace('MY_WORKSPACE');
  apex_application_install.set_application_id(100);
  apex_application_install.set_schema('HR');
  apex_application_install.set_application_alias('MYAPP');
  apex_application_install.set_auto_install_sup_obj(true);
end;
/

sql hr_dev/password@dev-host:1521/DEVPDB
SQL> @f100.sql

For a split export, go into the folder and run @install.sql instead.

The steps in plain words:

1. set_workspace chooses the workspace.
2. set_application_id chooses the ID. Use the same ID you have in DEV, and remember it can’t be in the reserved range 3000 to 8999.
3. set_schema chooses the parsing schema, which is often different in each environment.
4. set_application_alias avoids alias conflicts.
5. set_auto_install_sup_obj installs supporting objects without asking.

If the ID already exists, the application is replaced, so this is also your update procedure.

Enterprise rules of thumb

• Use Run Application Only in PROD. Nobody can change the app there by accident. Also try application locking.
• Deploy database changes separately and first. The APEX export doesn’t carry your tables, so deploy DB objects and data before the app import.
• Keep environment-specific values out of the app. Use application settings or substitution strings that you set per environment after the import.
• Use a dedicated deployment account with the minimum required privileges, not a developer’s login.
• Rehearse the import in QA. If it fails there, PROD never sees it.

Conclusion

Export and import are the foundation of working with multiple APEX environments. An export is a script that rebuilds your application, and an import runs that script in another workspace. Once you understand that, the options in the UI stop being mysterious.

The UI is the right place to start. It’s the best way to learn what each option does, take a quick backup, or move an application once. And remember that the export carries your application and not your database.

When releases become regular, automation is the natural next step. SQLcl does the same export and import from a script, which removes manual mistakes and lets you run the process from a pipeline. Pick one tool, and stay with it.

Thank you for reading!