DB views on App export

When exporting an application in Oracle APEX, the export file does not include the database views or other database objects like tables, procedures, or functions. The exported application file contains:

1. Application Metadata: This includes all the definitions for pages, regions, items, dynamic actions, processes, and shared components.

2. Application Logic: Such as PL/SQL code used in processes, validations, and computations.

3. UI Definitions: Themes, templates, and CSS used in the application.

4. Settings and Configurations: Application-level settings like security, globalization, and authentication schemes.

Why Doesn't It Contain Views?

Views, tables, or other schema objects reside in the database schema and are not part of the APEX application export. This separation is intentional because:

• APEX operates on a declarative model, where the application is a layer on top of the database schema.

• Database objects (e.g., views, tables) are typically managed separately using SQL scripts or tools like SQL Developer or Data Pump.

To Include Views and Other Schema Objects

If you want to include database views when migrating an application, you should:

1. Export Schema Objects Separately: Use SQL Developer or Oracle Data Pump to export the schema, which includes views, tables, and other objects.

2. Create Installation Scripts: Write SQL scripts for creating or altering views, and include them as part of your deployment package.

3. Incorporate Scripts in APEX: You can attach these scripts to your APEX application export under the "Supporting Objects" section. When importing the application, these scripts will run to create or update the necessary schema objects.

---

So what to do?

Here’s a detailed guide on exporting database views and integrating them into an Oracle APEX application export:

Step 1: Export the Database Views

To migrate database views along with your APEX application, follow these steps to export the views:

Option 1: Using SQL Developer

1. Connect to Your Database:

   • Open SQL Developer and connect to the schema where your views are defined.

2. Export the Views:

   • Navigate to the "Connections" tab and expand your schema.

   • Right-click the "Views" node and select "Export DDL".

   • Configure the export options (e.g., file path, include dependent objects).

   • Save the script containing the CREATE VIEW statements.

Option 2: Using SQL Command

You can manually query the database to generate the DDL for views:

SELECT DBMS_METADATA.GET_DDL('VIEW', view_name, 'SCHEMA_NAME') AS ddl
FROM user_views;

This query retrieves the CREATE VIEW statements. Save these statements into a script file, e.g., create_views.sql.

Step 2: Prepare the APEX Application Export

Export your APEX application from the APEX Builder:

1. Log in to Oracle APEX.

2. Navigate to the Application Builder.

3. Select the application you want to export.

4. Click on Export/Import and choose Export.

5. Download the exported SQL file of the application.

Step 3: Integrate Views into the APEX Export

You can include the view definitions as part of the APEX export using the Supporting Objects feature. Here's how:

Method 1: Attach SQL Scripts

1. Open your APEX application in the Application Builder.

2. Go to Supporting Objects in the Shared Components section.

3. Add a new Script:

   • Name: Create Views

   • Upload your create_views.sql file containing the CREATE VIEW statements.

4. Configure the script to run Before Application Import.

Method 2: Add SQL Directly

If you don’t want to use an external file:

1. Go to Supporting Objects.

2. Create a new script and paste the CREATE VIEW statements directly into the SQL editor.

Step 4: Import and Deploy

When you import the APEX application into a new workspace or environment:

1. The Supporting Objects installation wizard will prompt you to run the scripts.

2. Ensure the views are successfully created in the target database schema before running the application.

Important Notes

1. Dependency Management: Ensure any referenced objects (e.g., tables) exist in the target schema before creating the views.

2. Granting Privileges: If your views rely on database privileges, ensure those permissions are granted in the target schema.

3. Version Control: Keep separate versioned files for APEX exports and database object scripts for better maintainability.

4. Testing the Process: Test the entire deployment process in a staging environment before applying it in production.