Oracle Application Express

Application Express in Oracle Database 12c

Oracle Application Express Release 4.2.0.00.08 is installed by default in Oracle Database 12c. One of the major new capabilities introduced in Oracle Database 12c is Multitenant Architecture. [Please review the Multitenant Architecture on OTN to learn about this new architecture]

When you install Oracle Database 12c Multitenant Architecture, Oracle Application Express is installed into the multitenant container database by default. A single multitenant container database can have up to 252 pluggable databases (PDBs). The container database root (CDB$ROOT) includes an APEX_040200 schema which holds all of the common objects required for Application Express, including the Application Express engine packages, processes, functions and views. Therefore, there is only a single instance of these common objects across the whole database installation. The Application Express common objects, stored in the multitenant container database, are accessed from each PDB using meta-data links. Each PDB also includes an APEX_040200 schema which holds the Application Express meta-data tables for that specific PDB. These local tables ensure that the Application Express application definitions are maintained independently in each PDB. Each PDB must have a Web listener defined and is managed individually using the standard Oracle Application Express Instance Administration.

In the standard configuration, the version of Application Express installed can be updated for the multitenant container database and all pluggable databases using a single script. All pluggable databases *must* run the same version of Application Express. However, Application Express is unique amongst Oracle database options in that it can be removed from the multitenant container database and re-installed locally into a single pluggable database. This flexibility allows a single Oracle Database 12c Multitenant Architecture to run different versions of Oracle Application Express in each PDB. Once uninstalled from the multitenant container database you also have the option to reinstall Application Express into the container database. This option will enable you to re-install a runtime only configuration of Application Express into the multiteant container database.

Collateral

The following collateral is specific to Oracle Application Express 4.2 in Oracle Database 12c:

  • Deploying and Developing Oracle Application Express with Oracle Database 12c White Paper
    This white paper outlines the different ways in which Oracle Application Express can be installed into Oracle Database 12c Multitenant. This paper also covers some of the key new Oracle Database 12c features that are important for Application Express developers to learn about and how they are handled within Oracle Application Express.

  • Application Express in Oracle Database 12c Multitenant Architecture Presentation    [PDF]
    This presentation is provided to allow you to build your own presentation or utilize the PDF to simply review an overview of the new architecture and other new Oracle Database 12c features.
  • Application Express Release 4.2 Documentation for Oracle Database 12c
    Although Application Express 4.2 can be installed into any Oracle Database 10.2.0.4 and above, for Oracle Database 12c we have a dedicated documentation library. The majority of the Oracle Database 12c specific documentation is within the Application Express Installation Guide. Within this guide we have added a dedicated Oracle Database 12c chapter, Utilizing the Multitenant Architecture. This chapter outlines the various installation options together with scenarios relating to plugging in PDBs based on how Application Express is installed within both the source and target multitenant container databases.
  • Jason Straub's Blog
    As the primary developer on the Application Express Development Team responsible for developing Oracle Application Express 4.2 scripts for container databases and built-in capabilities related to Oracle Database 12c features, his blog contains posts related to utilizing Oracle Application Express in the new multitenant architecture.

Oracle Database 12c New Features for Application Express Developers

In additional to the changes made to Oracle Application Express 4.2 to work within the new Oracle Database 12c Multitenant Architecture, additional changes have also been made to accommodate some other Oracle Database 12c new features. The new features which may be important to Oracle Application Express developers in Oracle Database 12c include:

  • VARCHAR2 32K (Extended Data Type)
    The byte / character limit for VARCHAR2 columns can be extended from 4000 to 32,767 by changing the MAX_STRING_SIZE database parameter to EXTENDED.
    Changing to an extended data type will not affect Application Express applications. You can specify various item types, such as text and textarea, with a maximum size of up to 32,767 within the Application Express Builder. If you are utilizing Application Express collections to store page items, and also using extended data types, then you should run $ORACLE_HOME/core/collection_member_resize.sql to update the VARCHAR2 columns from 4000 to 32,767 characters.
  • DEFAULT | DEFAULT ON NULL Table Clause
    The DEFAULT and DEFAULT ON NULL clause can be specified within CREATE / ALTER TABLE statements. These parameters allow you to specify a value to be assigned to the column if a subsequent INSERT statement omits a value for the column, or the value evaluates to NULL. The value can include any SQL function or sequence pseudo columns.
    Utilizing the DEFAULT | DEFAULT ON NULL clause on a table has no impact on Application Express applications or within the Application Express Builder.
  • IDENTITY Table Clause
    The IDENTITY clause can be specified within CREATE / ALTER TABLE statements. Use this clause to assign an increasing or decreasing integer from a sequence generator for each subsequent INSERT statement. This can be utilized instead of needing to specify a separate database sequence, and related table triggers or other processes, to retrieve an integer value. You can specify ALWAYS {the default setting}, DEFAULT, or DEFAULT ON NULL parameters within this clause. The identity_options clause can be used to extend the IDENTITY clause, and specify the same parameters as used in the CREATE SEQUENCE statement.
    Utilizing the IDENTITY clause on a table *can* have an impact on Application Express applications. An error will be raised by the database if an IDENTITY column, with ALWAYS parameter specified, is included in an INSERT or UPDATE statement. The Application Express Builder has been enhanced to allow the specification of the identity clause within the Create Table Wizard. The Create Form wizard will generate a "Display Only" item for an identity column with the ALWAYS parameter.
  • INVISIBLE ColumnClause
    The INVISIBLE clause can be specified within CREATE / ALTER TABLE statements. This clause is used to define user-specified hidden columns. For example a developer may want to add an additional column PERFORMANCE_RATING to the EMP table that is not shown to other developers when they query the table structure. The COLUMN_ID for invisible columns within the data dictionary views is NULL.
    Utilizing the INVISIBLE clause on a column has no impact on Application Express applications. The Application Express Builder has been enhanced to not display invisible columns within such areas as the Object Browser, Query Builder, and so forth. The Create Form / Report wizards will not include any invisible columns in the form regions or report source generated. Post-generation, developers can enhance a form to add a new item with Source Type of "Database Column" for invisible columns. Developers can also manually include an invisible column in report region source.
  • SELECT "ROW_LIMITING" Clause
    The ROW_LIMITING clause has a number of available settings. Use this clause to limit the rows returned by a query. This top-N reporting capability should be utilized in combination with an ORDER BY clause to ensure a deterministic result set. Available settings include OFFSET, FETCH, FIRST | NEXT, rowcount | percent PERCENT, ROW | ROWS, ONLY | WITH TIES.
    Utilizing the ROW_LIMITING clause, for example within a report region source, has no impact on Application Express applications or within the Application Express Builder.
  • Data Redaction
    Oracle Data Redaction is the ability to redact data, typically sensitive data, in real time. Using redaction database profiles, data returned from a query can be masked for low-privileged users or applications at runtime (query-execution time). Available redaction policies include full redaction, partial redaction, regular expressions, random redactions, and no redaction.
    Implementing Data Redaction policies *can* have an impact on Application Express applications. Certain users may have query results, such as in reports or in SQL Workshop, redacted based on policies implemented. These policies will have no impact within the Application Express Builder. The 'expression' parameter within the DBMS_REDACT.ADD_POLICY and DBMS_REDACT.ALTER_POLICY statements can utilize the V and NV functions - This feature was incorporated specifically so that redaction policies can be utilized by and associated with Application Express user identification.

Known Issues

Oracle Database 12c was released on June 25, 2013.

This page was last updated on 8-Jul-2013.

  • 16946990 - UNABLE TO INSTALL APEX IN LOCAL PDB 12C DATABASE. THROWS ERROR
    When you install standard Oracle Database 12c Multitenant, Application Express is installed into the multitenant container database root (CDB$ROOT) by default. If you remove Application Express from the container database and attempt to install Application Express locally into a PDB, you will immediately encounter the error "ORA-65050: Common DDLs only allowed in CDB$ROOT". Additionally, using apxremov_con.sql will leave some references to public synonyms. However, this will not prevent you from re-installing Application Express back into the container database.
    Solution: No workaround currently available. A patch is expected soon at which time we expect a database patchset exception to be made available on My Oracle Support.
  • N/A - MISSING CONTAINER SCRIPTS FOR APPLICATION EXPRESS 4.2.2.00.11
    With the original download for Application Express 4.2.2 delivered via OTN on April 26, 2013, the container "_con.sql" database scripts were not included. You are able to uninstall Application Express by utilizing the apxremov_con.sql that is distributed in the $ORACLE_HOME/apex directory. However, the required apexins_con.sql and apxrtins_con.sql scripts to reinstall Application Express 4.2.2 into the multitenant container database were not originally within the zip files for Application Express 4.2.2.
    Solution: Download Oracle Application Express 4.2.3 from the OTN Application Express Download page and utilize the container scripts provided in the revised distribution.