Idea Summary

Currently, Oracle APEX SQL Scripts execute only under the workspace parsing schema, and there is no built-in option to select or switch the schema context while compiling objects.

In many enterprise Oracle APEX environments, applications follow a multi-schema architecture, where database objects are distributed across schemas. For example, one schema may contain tables and views, while another schema contains packages, procedures, and business logic.

When deploying scripts exported from another environment (DEV/UAT/PROD) using the SQL Scripts feature, objects belonging to different schemas may fail to compile due to schema context limitations. This results in errors such as “object does not exist”, even though the objects are present in another schema within the same workspace.

Currently, developers must manually split scripts or rewrite them with schema prefixes, which makes deployment more complex and error-prone.

Adding the ability to execute or compile scripts at a selected schema level within SQL Scripts would significantly improve the usability of APEX for multi-schema deployments.

Use Case

1. Multi-Schema Application Architecture
   Many APEX applications separate objects across schemas, for example:

   • COMMON_UAT – tables and views
   • HR_UAT – packages, procedures, and functions

   Deployment scripts may contain objects for multiple schemas.

2. Environment Migration
   When migrating database objects between environments, exported scripts often contain objects belonging to multiple schemas, causing compilation issues in SQL Scripts.

3. Cross-Schema Dependencies
   Packages compiled in one schema may depend on tables or objects in another schema, which cannot be resolved properly when the script runs under a single parsing schema.

4. Enterprise Development and DevOps
   Teams using APEX for enterprise applications need a simpler way to deploy multi-schema database objects directly within the SQL Scripts interface.

Preferred Solution (Optional)

Introduce a schema selection option in the SQL Scripts execution interface, allowing developers to choose the target schema before compiling or executing scripts.

Alternatively, allow schema context switching within SQL Scripts, for example:

ALTER SESSION SET CURRENT_SCHEMA = COMMON_UAT;
-- create tables

ALTER SESSION SET CURRENT_SCHEMA = HR_UAT;
-- compile packages

This enhancement would simplify multi-schema object deployment and compilation within Oracle APEX and reduce manual script modifications.