Oracle APEX

https://docs.oracle.com/en/database/oracle/apex/24.2/
Admin
Oracle APEX is a low-code development platform that enables you to build stunning, scalable, secure...
Tokens:1,490,137
Snippets:16,725
Trust Score:9.7
Update:2 months ago
Show doc for...
Tokens:
Raw
### Complete Installation Script Example

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeadm/installing-an-application.html

A full example script demonstrating how to set multiple installation context parameters before running the application SQL file.

```sql
REM First setup the installation context in the target environment
begin
    apex_application_install.set_workspace('TARGET_WORKSPACE');
    apex_application_install.set_application_id(5678);
    apex_application_install.generate_offset();
    apex_application_install.set_schema('HRAPP_PROD');
    apex_application_install.set_application_alias('hrapp');
end;
/
REM Then install the application by running its exported SQL script
REM For "split" export run @f1234/install.sql 
REM or @f1234/install_component.sql instead
@f1234.sql
```

--------------------------------

### Sample Custom Installation File Structure

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/htmdb/splitting-export-files-into-separate-scripts.html

This example shows the typical structure of a custom installation file when splitting exports into separate scripts. It includes environment setup and teardown scripts along with individual page or component scripts.

```sql
@@application/set_environment.sql
@@application/pages/page_00001.sql
@@application/pages/page_00023.sql
@@application/pages/page_00095.sql
@@application/end_environment.sq
```

--------------------------------

### Install APEX Application Example

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/INSTALL-Procedure.html

Example demonstrating how to fetch an application from a remote URL and install it with a new ID and component ID offsets in a specified workspace.

```PL/SQL
DECLARE
    l_source apex_t_export_files;
    l_info   apex_application_install.t_file_info;
BEGIN
    l_source := apex_t_export_files (
                    apex_t_export_file (
                         name     => 'f100.sql',
                         contents => apex_web_service.make_rest_request (
                                          p_url         => 'https://www.example.com/apps/f100.sql',
                                          p_http_method => 'GET' )));

    apex_util.set_workspace('EXAMPLE');
    apex_application_install.generate_application_id;
    apex_application_install.generate_offset;
    apex_application_install.install (
         p_source => l_source );
END;
```

--------------------------------

### APEX Installer Action Examples

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/htmdb/correlating-apex-sessions-to-database-sessions.html

Provides examples of values seen for the action session attribute during APEX installation or upgrade.

```sql
'upgrade'
```

```sql
'recompiling'
```

--------------------------------

### Example: Fetch and Print Install Information

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/GET_INFO-Function.html

This PL/SQL example demonstrates how to fetch an application from a remote URL using apex_web_service.make_rest_request and then use the GET_INFO function to retrieve and print its installation details.

```PL/SQL
DECLARE
    l_source apex_t_export_files;
    l_info   apex_application_install.t_file_info;
BEGIN
    l_source := apex_t_export_files (
                    apex_t_export_file (
                        name     => 'f100.sql',
                        contents => apex_web_service.make_rest_request (
                                        p_url         => 'https://www.example.com/apps/f100.sql',
                                        p_http_method => 'GET' )));
    l_info   := apex_application_install.get_info (
                    p_source      => l_source );
    sys.dbms_output.put_line (apex_string.format (
         p_message => q'!Type ................. %0
                      !Workspace ............ %1
                      !Version .............. %2
                      !App ID ............... %3
                      !App Name ............. %4
                      !Alias ................ %5
                      !Owner ................ %6
                      !Build Status ......... %7
                      !Has Install Script ... %8
                      !App ID Usage ......... %9
                      !App Alias Usage ...... %10!',
       p0        => l_info.file_type,
       p1        => l_info.workspace_id,
       p2        => l_info.version,
       p3        => l_info.app_id,
       p4        => l_info.app_name,
       p5        => l_info.app_alias,
       p6        => l_info.app_owner,
       p7        => l_info.build_status,
       p8        => apex_debug.tochar(l_info.has_install_script),
       p9        => l_info.app_id_usage,
       p10       => l_info.app_alias_usage,
       p_prefix  => '!' ));
END;
```

--------------------------------

### Example Installation of APEX Full Development Environment

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/htmig/installing-apex-into-different-pdbs.html

This is an example of running apexins.sql with specific tablespace and image directory values.

```sql
@apexins.sql SYSAUX SYSAUX TEMP /i/

```

--------------------------------

### Example: Install Application and Keep Sessions

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/SET_KEEP_SESSIONS-Procedure.html

This example demonstrates how to install an application and preserve its session state using SET_KEEP_SESSIONS. Ensure the workspace is set correctly before calling the procedure.

```PL/SQL
BEGIN
  apex_application_install.set_workspace(p_workspace => 'FRED_PROD');
  apex_application_install.set_keep_sessions(p_keep_sessions => true);
END;
/
@f100.sql
```

--------------------------------

### Example Installation of APEX Runtime Environment

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/htmig/installing-apex-into-different-pdbs.html

This is an example of running apxrtins.sql with specific tablespace and image directory values.

```sql
@apxrtins.sql SYSAUX SYSAUX TEMP /i/

```

--------------------------------

### Example: Install APEX Full Development Environment

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/htmig/installing-apex-into-different-pdbs.html

Example of running apexins.sql with specific tablespace and image directory values. Ensure the virtual image directory is set to '/i/' for future upgrades.

```SQLcl
@apexins.sql SYSAUX SYSAUX TEMP /i/
```

--------------------------------

### Example: Install APEX Runtime Environment

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/htmig/installing-apex-into-different-pdbs.html

Example of running apxrtins.sql with specific tablespace and image directory values. Ensure the virtual image directory is set to '/i/' for future upgrades.

```SQLcl
@apxrtins.sql SYSAUX SYSAUX TEMP /i/
```

--------------------------------

### Get Application Build Status Example

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX_APPLICATION_ADMIN.GET_BUILD_STATUS-Function.html

This PL/SQL example demonstrates how to call the GET_BUILD_STATUS function to retrieve the build status for a given application ID.

```sql
DECLARE
    l_application_build_status apex_application_admin.t_build_status;
BEGIN
    l_application_build_status := apex_application_admin.get_build_status (
                                      p_application_id => 100 );
END;
```

--------------------------------

### Get Supporting Object Scripts for an Application

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/GET_SUPPORTING_OBJECT_SCRIPT-Function.html

This example demonstrates how to set the workspace ID and then retrieve install, upgrade, and deinstall scripts for a specific Oracle APEX application using APEX_UTIL.GET_SUPPORTING_OBJECT_SCRIPT.

```sql
declare
    l_install_script   clob;
    l_upgrade_script   clob;
    l_deinstall_script clob;
begin
    apex_util.set_workspace( p_workspace => 'FRED');

    l_install_script := apex_util.get_supporting_object_script( p_application_id => 100,
 p_script_type => apex_util.c_install_script );
    l_upgrade_script := apex_util.get_supporting_object_script( p_application_id => 100,
 p_script_type => apex_util.c_upgrade_script );
    l_deinstall_script := apex_util.get_supporting_object_script( p_application_id => 100,
 p_script_type => apex_util.c_deinstall_script );
end;
```

--------------------------------

### Using KEYVAL_NUM to Retrieve Package Variable Value

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/KEYVAL_NUM-Function.html

This example demonstrates how to use the KEYVAL_NUM function to get the current value of the apex_utilities.g_val_num package variable. No specific setup is required beyond standard PL/SQL block structure.

```plsql
DECLARE
    VAL NUMBER;
BEGIN
    VAL := APEX_UTIL.KEYVAL_NUM;
END;
```

--------------------------------

### Get Region ID by Static ID Example

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX_REGION.GET_ID-Function-Signature-1.html

This example demonstrates how to get the ID of a region using its static ID, application ID, and page ID. Note that the p_dom_static_id is not unique and can raise a 'too_many_rows' error.

```PLSQL
DECLARE
    l_region_id apex_application_page_regions.region_id%type;
BEGIN
    ...
    l_region_id := apex_region.get_id(
                       p_application_id => 100,
                       p_page_id        => 1,
                       p_dom_static_id  => 'my_apex_region' );
    ...
END;
```

--------------------------------

### Get File Type Example

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX_DATA_PARSER.GET_FILE_TYPE-Function.html

This example demonstrates how to use the APEX_DATA_PARSER.GET_FILE_TYPE function to determine the file type of 'test.xlsx'.

```sql
DECLARE 
   l_file_type apex_data_parser.t_file_type;
BEGIN
    l_file_type := apex_data_parser.get_file_type( 'test.xlsx' );
END;
```

--------------------------------

### Basic Installation Script Structure

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeadm/installing-an-application.html

The template for setting up the installation context before executing the application SQL script.

```sql
REM First setup the installation context in the target environment
begin
    -- Add necessary calls to apex_application_install package procedures here
end;
/
REM Then install the application by running its exported SQL script
REM For "split" export run @f1234/install.sql 
REM or @f1234/install_component.sql instead
@f1234.sql
```

--------------------------------

### Configure Instance URL and Images URL Examples

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeadm/configuring-email.html

Examples of URL formats required for system-generated emails.

```text
https://www.example.com/apex/
```

```text
https://www.example.com/i/
```

--------------------------------

### POST_LOGIN Procedure Example

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/POST_LOGIN-Procedure.html

Demonstrates how to call the POST_LOGIN procedure to authenticate a user with provided credentials.

```SQL
apex_authentication.post_login('JOE USER', 'mysecret');
```

--------------------------------

### Get Session Territory Example

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/GET_SESSION_TERRITORY-Function.html

This example demonstrates how to call the APEX_UTIL.GET_SESSION_TERRITORY function and store its return value in a local variable.

```plsql
DECLARE
    VAL VARCHAR2(30);
BEGIN
    VAL := APEX_UTIL.GET_SESSION_TERRITORY;
END;
```

--------------------------------

### Example: Creating an OAuth Client Credential

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/CREATE_CREDENTIAL-Procedure-Signature-1.html

Demonstrates how to create a credential definition for OAuth client credentials. This example also shows how to set the persistent client ID and secret for the created credential.

```PLSQL
BEGIN
   -- first set the workspace
   apex_util.set_workspace(p_workspace => 'MY_WORKSPACE');

   apex_credential.create_credential (
       p_credential_name => 'OAuth Login',
       p_credential_static_id => 'OAUTH_LOGIN',
       p_authentication_type => apex_credential.C_TYPE_OAUTH_CLIENT_CRED,
       p_scope => 'email',
       p_allowed_urls => apex_t_varchar2( 'https://tokenserver.example.com/oauth2/token', 'https://www.oracle.com' ),
       p_prompt_on_install => false,
       p_credential_comment => 'Credential for OAuth Login' );

  -- should be followed by set_persistent_credentials
  apex_credential.set_persistent_credentials (
      p_credential_static_id => 'OAUTH_LOGIN',
      p_client_id => 'dnkjq237o8832ndj98098-..',
      p_client_secret => '1278672tjksaGSDA789312..' );
END;
```

--------------------------------

### Get Session Language Example

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/GET_SESSION_LANG-Function.html

This example demonstrates how to retrieve the session language for the current user and store it in a local variable.

```plsql
DECLARE
    VAL VARCHAR2(5);
BEGIN
    VAL := APEX_UTIL.GET_SESSION_LANG;
END;
```

--------------------------------

### Configure Wallet Path Examples

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeadm/configuring-wallet-information.html

Examples of the required file system path format for Oracle Wallets on different operating systems.

```text
file:c:\WINNT\Profiles\<username>\WALLETS
```

```text
file:/home/<username>/wallets
```

--------------------------------

### Get Workspace ID Example

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/GET_WORKSPACE_ID-Function.html

This PL/SQL example demonstrates how to call the GET_WORKSPACE_ID function and store the returned workspace ID in a variable.

```plsql
DECLARE
    l_workspace_id number;
BEGIN
    l_workspace_id := apex_application_install.get_workspace_id;
END;
```

--------------------------------

### Get Application Schema Example

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/GET_SCHEMA-Function.html

This PL/SQL example demonstrates how to call the GET_SCHEMA function and store the returned application schema in a variable.

```plsql
DECLARE
    l_schema varchar2(30);
BEGIN
    l_schema := apex_application_install.get_schema;
END;
```

--------------------------------

### Wallet Parameter Examples

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeadm/configuring-wallet-information.html

Example of the WALLET_PATH parameter value format.

```text
file:/home/username/wallets
```

--------------------------------

### Example Usage of APEX_JSON.STRINGIFY

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/STRINGIFY-Function-Signature-5.html

This example demonstrates how to use the APEX_JSON.STRINGIFY function to convert an sdo_geometry point to its GeoJSON representation and print it. Ensure SDO_GEOMETRY is installed.

```PLSQL
BEGIN
  sys.htp.p(apex_json.stringify( 
                mdsys.sdo_geometry( 2001, 4326, sdo_point_type( 10, 50, null ), null, null ) ) );
END;
```

--------------------------------

### Generate Example Data for 'animal.family'

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/GET_EXAMPLE-Function.html

This example demonstrates how to use the GET_EXAMPLE function to retrieve five sample rows for the 'animal.family' friendly name.

```SQL
select * 
from apex_dg_data_gen.get_example( p_friendly_name => 'animal.family');
```

--------------------------------

### View activity.sql usage instructions

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/htmdb/viewing-debug-messages.html

Displays help information for the activity.sql script.

```sql
SQL> @activity help
```

--------------------------------

### Example: Get ALLOW_HOSTNAMES Parameter

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/GET_WORKSPACE_PARAMETER.html

This example demonstrates how to retrieve the value of the 'ALLOW_HOSTNAMES' parameter for the 'HR' workspace using the GET_WORKSPACE_PARAMETER procedure and prints the output.

```PL/SQL
BEGIN
    DBMS_OUTPUT.PUT_LINE (
APEX_INSTANCE_ADMIN.GET_WORKSPACE_PARAMETER (
            p_workspace => 'HR',
            p_parameter => 'ALLOW_HOSTNAMES' ));
END;
```

--------------------------------

### Example of APEX_AUTHENTICATION.LOGIN Usage

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/LOGIN-Procedure.html

Demonstrates how to call the LOGIN procedure to authenticate a user with provided credentials. Ensure the username and password are correct for successful authentication.

```sql
BEGIN
    apex_authentication.login (
        p_username => 'JOE USER',
        p_password => 'mysecret' );
END;

```

--------------------------------

### Import Application Blueprint Example

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/ADD_BLUEPRINT_FROM_FILE-Procedure.html

Example of using ADD_BLUEPRINT_FROM_FILE to import a blueprint from an application file. Ensure the file exists and the application ID is correct.

```plsql
DECLARE
   l_blueprint number;
BEGIN
   apex_dg_data_gen.add_blueprint_from_file
     (p_filename              => 'app/example.json',
      p_application_id        => 145,
      p_override_name         => 'My Application Blueprint',
      p_replace               => false,
      p_blueprint_id          => l_blueprint
     );
END;
```

--------------------------------

### Get User Style Preference Example

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/GET_USER_STYLE-Function.html

This example demonstrates how to use the GET_USER_STYLE function to retrieve the theme style preference for a specific user, application, and theme.

```sql
select apex_theme.get_user_style( 100, 'ADMIN', 42 ) from dual;
```

--------------------------------

### Get Application Alias Example

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX_APPLICATION_INSTALL.GET_APPLICATION_ALIAS-Function.html

This PL/SQL example demonstrates how to call the GET_APPLICATION_ALIAS function and store its return value. The application alias is limited to 255 characters.

```sql
DECLARE
    l_alias varchar2(255);
BEGIN
    l_alias := apex_application_install.get_application_alias;
END;
```

--------------------------------

### Extracting and Inspecting Application Archive

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/UNZIP-Function.html

This example demonstrates fetching an application archive from a URL, unzipping it using APEX_EXPORT.UNZIP, and then retrieving information about the contained application using APEX_APPLICATION_INSTALL.GET_INFO. Ensure the necessary packages are accessible.

```PL/SQL
DECLARE
   l_zip blob;
   l_info apex_application_install.t_file_info;
BEGIN
   l_zip := apex_web_service.make_rest_request_b (
                p_url => 'https://www.example.com/apps/f100.zip',
                p_http_method => 'GET' );
   l_info := apex_application_install.get_info (
                   p_source => apex_export.unzip (
                       p_source_zip => l_zip ) );

   sys.dbms_output.put_line (
       apex_string.format (
           p_message => q'~
                        !Type ................. %0
                        !App Name ............. %1
                        !~',
           p0 => l_info.file_type,
           p1 => l_info.app_name,
           p_prefix => '!' ));
END;
```

--------------------------------

### Example Usage of APEX_AI.GENERATE

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX_AI.GENERATE-Function-Signature-2.html

This example demonstrates how to use the APEX_AI.GENERATE function to get a response from an AI service configured with a specific static ID and a user prompt.

```plsql
DECLARE
  l_response clob;
BEGIN
  l_response := apex_ai.generate(
    p_config_static_id => 'low_code_expert',
    p_prompt           => 'What is Oracle APEX' );
END;
```

--------------------------------

### Install APEX in an Application Container

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/htmig/installing-apex-into-application-container.html

Connect to the application container and run the `apxappcon.sql` script to install APEX. The script accepts four arguments similar to `apexins.sql`, plus a fifth argument for the `APEX_PUBLIC_USER` password during installation.

```sql
ALTER SESSION SET CONTAINER = apex_approot1;

 @apxappcon.sql SYSAUX SYSAUX TEMP /i/ P@ssw0rd!
```

--------------------------------

### Export Application Source with Checksum

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/GET_APPLICATION_Function.html

This example shows the content of an install.sql file generated when exporting application source code along with its checksum. It includes references to other SQL scripts and a final checksum value.

```SQL
prompt --install
@@application/set_environment.sql
@@application/delete_application.sql
...snip...
@@application/deployment/buildoptions.sql
@@application/end_environment.sql
-- Application Checksum SH1:jpcliMUZZDVVBI1MKpyyAfPBDww=

```

--------------------------------

### Generate AI Response Example

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX_AI.GENERATE-Function-Signature-1.html

This PL/SQL example demonstrates how to call the apex_ai.generate function to get a response from a specific Generative AI service identified by 'MY_AI_SERVICE'.

```PL/SQL
DECLARE
  l_response clob;
BEGIN
  l_response := apex_ai.generate(
    p_prompt            => 'What is Oracle APEX',
    p_service_static_id => 'MY_AI_SERVICE');
END;
```

--------------------------------

### App Builder Proxy Configuration Examples

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeadm/configuring-service-level-security-settings.html

Examples of proxy server URL formats for Generative AI Services.

```text
http://www-proxy.example.com
```

```text
http://www-proxy.example.com:8080
```

--------------------------------

### Starting a Workflow with Parameters

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX_WORKFLOW.START_WORKFLOW-Function.html

Example of how to start a workflow using the START_WORKFLOW function, including setting application ID, static ID, and passing workflow parameters.

```plsql
BEGIN
    l_workflow_id := apex_workflow.start_workflow (
        p_application_id => 110,
        p_static_id      => 'REQUISITIONWORKFLOW',
        p_parameters     => apex_workflow.t_workflow_parameters(
            1 => apex_workflow.t_workflow_parameter(static_id => 'REQ_DATE', string_value   => sysdate),
            3 => apex_workflow.t_workflow_parameter(static_id => 'REQ_AMOUNT', string_value => l_req_amount),
            4 => apex_workflow.t_workflow_parameter(static_id => 'REQ_ITEM', string_value   => l_req_item),
            5 => apex_workflow.t_workflow_parameter(static_id => 'REQ_ID', string_value     => l_req_id)),
        p_debug_level => apex_debug_api.c_log_level_info );
END;
```

--------------------------------

### Get Install Script to DBMS_OUTPUT

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/GET_SUPPORTING_OBJECT_SCRIPT-Procedure.html

Sets the workspace and then retrieves the installation script for a given application, outputting it to the server output buffer. Ensure 'serveroutput' is enabled before execution.

```SQL
set serveroutput on;
begin
    apex_util.set_workspace( p_workspace => 'FRED');
    apex_util.get_supporting_object_script(
        p_application_id => 100,
        p_script_type    => apex_util.c_install_script );
end;
```

--------------------------------

### Example Wallet Path Configuration

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX_INSTANCE_ADMIN.Available-Parameter-Values.html

This snippet shows an example of a wallet path configuration. It is used to specify the location of a wallet on the file system.

```text
file:/home/<username>/wallets
```

--------------------------------

### Setting Session Credentials Example

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/SET_SESSION_CREDENTIALS-Procedure-Signature-1.html

This example demonstrates how to use the SET_SESSION_CREDENTIALS procedure to set credentials for 'Login'. This is typically used for BASIC authentication.

```PLSQL
BEGIN
    apex_credential.set_session_credentials (
        p_credential_static_id => 'Login',
        p_username             => 'scott',
        p_password             => 'tiger );
END;

```

--------------------------------

### Get Workflow Variable Value Example

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX_WORKFLOW.GET_VARIABLE_VALUE-Function.html

This example demonstrates how to use the APEX_WORKFLOW.GET_VARIABLE_VALUE function to retrieve the value of a workflow variable named 'NEW_SALARY' for a specific workflow instance.

```plsql
BEGIN
    apex_workflow.get_variable_value(
           p_instance_id => 1234,
           p_variable_static_id => 'NEW_SALARY');
END;
```

--------------------------------

### Preserve Background Executions During Installation

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/SET_KEEP_BACKGROUND_EXECS-Procedure.html

Example of how to set the workspace and preserve background executions before installing an application. This is useful when you want to ensure that scheduled background jobs are not lost during an application upgrade.

```sql
BEGIN
   apex_application_install.set_workspace(p_workspace => 'FRED_PROD');
   apex_application_install.set_keep_background_execs(p_keep_background_execs => true);
END;
/
@f100.sql
```

--------------------------------

### Install APEX Runtime Environment

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/htmig/installing-apex-into-different-pdbs.html

Execute this script to install the APEX runtime environment locally in a PDB. Provide the tablespace names for APEX platform and files, the temporary tablespace, and the virtual image directory.

```sql
@apxrtins.sql tablespace_apex tablespace_files tablespace_temp images

```

--------------------------------

### Theme File Prefix Examples

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/htmdb/editing-themes.html

Examples of how to specify a file prefix for theme files. This can be a relative path, an absolute URL, or use APEX substitution strings.

```text
/myFiles/
```

```text
https://example.com/myFiles/
```

```text
#APEX_FILES#myFiles/
```

--------------------------------

### Get Proxy Server Attribute Example

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/GET_PROXY-Function.html

This PL/SQL example demonstrates how to use the APEX_APPLICATION_INSTALL.GET_PROXY function to retrieve the proxy server attribute. The attribute value must not exceed 255 characters.

```sql
DECLARE
    l_proxy varchar2(255);
BEGIN
    l_proxy := apex_application_install.get_proxy;
END;
```

--------------------------------

### Create Table, Insert Metadata, and Create Spatial Index

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/INSERT_GEOM_METADATA-Procedure.html

This example demonstrates creating a table, inserting spatial metadata using APEX_SPATIAL.INSERT_GEOM_METADATA, and then creating a spatial index.

```SQL
create table cities (
    city_id   number primary key,
    city_name varchar2(30),
    shape     mdsys.sdo_geometry )
/
begin
    apex_spatial.insert_geom_metadata (
        p_table_name   => 'CITIES',
        p_column_name  => 'SHAPE',
        p_diminfo     => SDO_DIM_ARRAY (
            SDO_DIM_ELEMENT('X', -180, 180, 1),
            SDO_DIM_ELEMENT('Y',  -90,  90, 1) ),
        p_srid        => apex_spatial.c_wgs_84 );
end;
/
   create index cities_idx_shape on cities(shape) indextype is mdsys.spatial_index
/

```

--------------------------------

### Get start of day

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aexjs/apex.date.html

Returns a Date object representing the beginning of the day for the provided date.

```javascript
var dayStartDate = apex.date.startOfDay( myDate );
// output: "2021-JUN-29 24:00:00" (as date object)
```

--------------------------------

### Example of Adding a Menu Entry

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX_EXTENSION.ADD_MENU_ENTRY-Procedure.html

Demonstrates how to add an extension menu link to a specific workspace using the ADD_MENU_ENTRY procedure. Requires the APEX_ADMINISTRATOR_ROLE.

```sql
BEGIN
    apex_extension.add_menu_entry(
        p_label       => 'Example',
        p_url         => 'https://example.com'
        p_description => 'This is an example'
        p_workspace   => ' MY_WORKSPACE' );
END;
```

--------------------------------

### Install APEX Full Development Environment

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/htmig/installing-apex-into-different-pdbs.html

Execute this script to install the full APEX development environment locally in a PDB. Provide the tablespace names for APEX platform and files, the temporary tablespace, and the virtual image directory.

```sql
@apexins.sql tablespace_apex tablespace_files tablespace_temp images

```

--------------------------------

### Retrieve Scheduler Job Name Example

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX_AUTOMATION_GET_SCHEDULER_JOB_NAME-Function.html

This example demonstrates how to use the APEX_AUTOMATION.GET_SCHEDULER_JOB_NAME function to get the scheduler job name for an automation. It requires specifying the application ID and the static ID of the automation.

```PL/SQL
BEGIN
    dbms_output.put_line(
    apex_automation.get_scheduler_job_name(
         p_application_id => 152,  
         p_static_id      => 'my_emp_table_automation' ) );

    -- ==> APEX$AUTOMATION_2167837869128719
END;
```

--------------------------------

### Generate PDF Document Example

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX_PRINT.GENERATE_DOCUMENT-Function-Signature-2.html

This example demonstrates how to generate a PDF document using a report query and layout defined in an APEX application, then downloads it.

```PL/SQL
DECLARE
    l_document blob;
BEGIN

    l_document :=
        apex_print.generate_document (
            p_application_id             => 100,
            p_report_query_static_id     => 'MY_REPORT_QUERY',
            p_report_layout_static_id    => 'MY_REPORT_LAYOUT',
            p_output_type                => apex_print.c_output_pdf );

    apex_http.download(
        p_blob           => l_document,
        p_content_type   => 'application/pdf',
        p_filename       => 'my-report.pdf' );

END;
```

--------------------------------

### Get Application Status Example

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX_APPLICATION_ADMIN.GET_APPLICATION_STATUS-Function.html

This example demonstrates how to retrieve the status of application ID 100. It sets the workspace, calls the function, and then checks if the application is available for editing using a predefined constant.

```plsql
DECLARE
  l_app_status apex_application_admin.t_app_status;
BEGIN
  apex_util.set_workspace('YOUR_WORKSPACE_NAME');
  l_app_status := apex_application_admin.get_application_status (
                      p_application_id => 100 );
  IF l_app_status = apex_application_admin.c_app_available_with_edit_link THEN
    dbms_output.put_line(l_app_status);
    -- your custom code here...
  END IF;
END;
```

--------------------------------

### Example Usage of CREATE_SESSION Procedure

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/CREATE_SESSION-Procedure.html

This example demonstrates how to create a session for a specific user and application, and then prints the application ID and session ID. This procedure is not supported in SQL Workshop tools.

```sql
begin
    apex_session.create_session (
    p_app_id   => 100,
    p_page_id  => 1,
    p_username => 'EXAMPLE_USER' );
    sys.dbms_output.put_line (
   'App is '||v('APP_ID')||', session is '||v('APP_SESSION'));
 end;
```

--------------------------------

### Get APEX Instance URL with Script Name

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/HOST_URL-Function.html

This example demonstrates how to use the HOST_URL function with the 'SCRIPT' option to get the URL including the script name. This is useful for constructing full application URLs.

```plsql
declare
    l_host_url      varchar2(4000);
    l_url           varchar2(4000);
    l_application   varchar2(30) := 'f?p=100:1';
    l_email_body    varchar2(32000);
begin
    l_host_url := apex_util.host_url('SCRIPT');
    l_url := l_host_url||l_application;
    l_email_body := 'The URL to the application is: '||l_url;
end;
```

--------------------------------

### Create Human Task Example

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX_HUMAN_TASK.CREATE_TASK-Function.html

This example demonstrates creating a requisition item and then a human task for its approval using APEX_HUMAN_TASK.CREATE_TASK. It shows how to populate task parameters and set initiator options.

```plsql
DECLARE
   l_req_id     number;
   l_req_item   varchar2(100) := 'Some requisition item requiring approval';
   l_req_amount number := 2499.42;
   l_task_id    number;
BEGIN
    insert into requisitions(created_by, creator_emailid, item, item_amount, item_category)
    values (:emp_uid, :emp_email, l_req_item, l_req_amount, 'Equipment')
    returning id into l_req_id;
    commit;

    l_task_id := apex_approval.create_task(
        p_application_id => 110,
        p_task_def_static_id => 'REQAPPROVALS',
        p_subject => 'Requisition ' || l_req_id || ': ' || l_req_item || ' for ' || l_req_amount,
        p_initiator => :emp_uid,
        p_initiator_can_complete => true,
        p_parameters => apex_approval.t_task_parameters(
            1 => apex_approval.t_task_parameter(static_id => 'REQ_DATE', string_value => sysdate),
            3 => apex_approval.t_task_parameter(static_id => 'REQ_AMOUNT', string_value => l_req_amount),
            4 => apex_approval.t_task_parameter(static_id => 'REQ_ITEM', string_value => l_req_item),
            5 => apex_approval.t_task_parameter(static_id => 'REQ_ID', string_value => l_req_id)
        ),
        p_detail_pk => l_req_id
    );
END;
```

--------------------------------

### Example: Using FREE_OUTPUT with CLOB Output

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/FREE_OUTPUT-Procedure.html

This example demonstrates initializing CLOB output, generating JSON, printing it, and then freeing the CLOB resources using FREE_OUTPUT.

```sql
BEGIN
    apex_json.initialize_clob_output;

    apex_json.open_object;
    apex_json.write('hello', 'world');
    apex_json.close_object;

    dbms_output.put_line(apex_json.get_clob_output);

    apex_json.free_output;
END;
```

--------------------------------

### Get Contacts Function Example

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/TABLE_TO_STRING-Function.html

Example of creating a function that uses TABLE_TO_STRING to return a comma-delimited string of contact names for a given customer ID. This function is deprecated and JOIN or JOIN_CLOB should be used instead.

```plsql
create or replace function get_contacts (
    p_cust_id  in  number )
    return varchar2
is
    l_vc_arr2   apex_application_global.vc_arr2;
    l_contacts  varchar2(32000);

BEGIN

    select contact_name
        bulk collect
        into l_vc_arr2
        from contacts
    where cust_id = p_cust_id
        order by contact_name;

    l_contacts :=  apex_util.table_to_string (
                       p_table => l_vc_arr2,
                       p_string => ', ');

   return l_contacts;

END get_contacts;
```

--------------------------------

### Get Application ID Example

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/GET_APPLICATION_ID-Function.html

This PL/SQL example demonstrates how to call the GET_APPLICATION_ID function and store the returned application ID in a local variable. This is useful for programmatic access to the current application's ID.

```plsql
DECLARE
    l_id number;
BEGIN
    l_id := apex_application_install.get_application_id;
END;
```

--------------------------------

### GET_EXAMPLE Function

Source: https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX_DG_DATA_GEN.html

Retrieves an example related to data generation. Requires an APEX session.

```APIDOC
## GET_EXAMPLE Function

### Description
Retrieves an example, likely of generated data or a blueprint configuration. This function is part of the APEX_DG_DATA_GEN package and requires an active APEX session.

### Signature
`APEX_DG_DATA_GEN.GET_EXAMPLE`

### Parameters
This function does not explicitly list parameters in the provided documentation. Refer to APEX_DG_DATA_GEN documentation for details on any implicit parameters or required session context.
```
Tokens:
Raw