Globalization

Introduction
Using globalization in Oracle APEX allows developers to create applications that support multiple languages, regional formats, and cultural preferences. This feature enables your application to adapt dynamically based on the user’s language, number format, date format, and currency settings. Whether you're building an app for a local team or a global audience, APEX provides the tools to deliver a personalized, localized experience without duplicating logic or layouts.

 Globalization in Oracle APEX enables your application to support multiple languages, regional formats, and culturally specific settings like dates, numbers, and currencies. This ensures that users from different parts of the world can interact with the application in a way that feels natural and intuitive to them. Globalization is built into the Oracle APEX platform, making it possible to translate labels, messages, and content, as well as adapt formats dynamically based on user preferences or browser settings.

To begin using globalization, go to your APEX application and navigate to Shared Components > Globalization Attributes. Here, you can configure several key settings:

• Primary Language: This is the default language of your application (e.g., en for English).

• Application Language Derived From: Choose how the language is determined. Common options include:

  • User Preference: Stored in session state or retrieved from user profile

  • Browser Language: Automatically uses the browser’s default language

  • Application Item or Substitution String: You can set it dynamically with logic

You should also define the Application Language Item (e.g., P0_LANGUAGE), which can be used to let the user manually select a language. For this, create a language selector (e.g., a drop-down on the login or home page) that sets the P0_LANGUAGE item and triggers a redirect or page reload.

Next, enable Text Messages under Shared Components > Text Messages. This feature lets you define translatable content in key-value pairs. For example:

• Name: GREETING

• Language: en

• Text: Welcome

Then for Spanish:

• Name: GREETING

• Language: es

• Text: Bienvenido

You can reference this message anywhere in your app using the syntax:

#GREETING#

Oracle APEX will automatically display the correct version based on the current language setting.

For page and region-level translations, go to Shared Components > Language > Translate Application. Create a new language (e.g., es for Spanish), then export the XLIFF file. This file contains all translatable strings from your app (labels, button text, headings, etc.). Translate the content in the XLIFF file and re-import it to apply the translated strings. You can repeat this process for each additional language you want to support.

APEX also handles locale-specific formatting for numbers, dates, and currencies automatically. This is controlled by the Application Primary Language and the NLS settings in your Oracle database session. If needed, you can override these settings using initialization code in a Before Header process or in the Globalization Attributes section.

Example for setting NLS preferences dynamically:

BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = ''DD-MM-YYYY''';
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '',.''';
END;

To provide the best user experience, always test your application with different language settings, especially for layouts, currency formats, and message displays. Also, remember that some layouts may shift depending on the length or orientation of translated strings.

Globalization in Oracle APEX lets you build one application that feels local to users around the world. With careful planning, use of text messages, translations, and NLS formatting, your application can dynamically adapt to any audience without needing multiple versions of the same app. This makes your development process more efficient and your users more satisfied.

 How to Implement Globalization in Oracle APEX

Globalization in Oracle APEX allows applications to support multiple languages, date formats, number formats, and cultural settings. This is essential for applications used in different regions, ensuring a smooth user experience regardless of the user's location and language preference.

This tutorial explains how to enable globalization, translate applications, and handle date and number formats in APEX.

Understanding Globalization in APEX

Oracle APEX provides built-in support for:

• Application Translations – Translate application components into multiple languages.

• Session-Based Language Switching – Change language based on user preferences.

• Locale-Based Number and Date Formatting – Automatically adjust number and date formats based on the user’s region.

• Multi-Language Data Storage – Store and retrieve data in multiple languages.

Steps to Implement Globalization in APEX

Step 1: Enable Globalization Settings

1. Open Oracle APEX and navigate to your application.

2. Go to Shared Components > Globalization Attributes.

3. Configure the following settings: 

• Primary Language – Choose the default language (e.g., English).

• Automatic Time Zone – Enable automatic detection of the user’s time zone.

• Automatic Language Detection – Allow the application to detect and apply the user's browser language.

• Date and Number Format – Set default format masks (e.g., DD-MON-YYYY for dates).

4. Click Apply Changes.

Step 2: Add Additional Languages

1. Go to Shared Components > Translations.

2. Click Create Language Mapping.

3. Select the language you want to add (e.g., French, Spanish).

4. Click Create to enable translation support for the new language.

Once a language is added, APEX will allow translation of application text and labels.

Step 3: Translate Application Components

1. Go to Shared Components > Text Messages.

2. Click Create to add a new translation key.

3. Enter: 

• Message Name – A unique identifier (e.g., WELCOME_TEXT).

• Primary Language Text – Default text in the primary language.

• Translated Text – The translated version.

4. Save and repeat for other application messages.

For UI elements (buttons, labels, region titles):

1. Go to Shared Components > Translation Repository.

2. Export the XLIFF file, which contains all translatable application components.

3. Translate the text in an external editor.

4. Import the translated file back into APEX.

Step 4: Implement Language Switching

To allow users to switch languages dynamically:

1. Create a Select List (LOV) with available languages.

2. Populate the LOV using this SQL:

SELECT language_name, language_code FROM apex_application_translations;

3. Create a Dynamic Action on the select list to update the session language:

BEGIN

    APEX_UTIL.SET_SESSION_LANG(:P_LANG);

END;

4. Refresh the page to apply the language change.

Step 5: Manage Date and Number Formatting

APEX automatically adjusts date and number formats based on the user’s session language. However, you can override these settings manually:

1. Go to Shared Components > Globalization Attributes.

2. Set the desired date format mask (e.g., DD/MM/YYYY).

3. Set decimal and thousand separators based on regional preferences.

To ensure proper formatting in SQL queries, use:

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=FRENCH') FROM dual;

Best Practices for Globalization in APEX

• Use text messages instead of hardcoded strings to make translation easier.

• Enable automatic language detection for a seamless user experience.

• Test with different languages and regions to ensure consistency.

• Use multi-language data storage to allow users to input text in various languages.

• Apply proper format masks to ensure correct date and number display for each locale.

With these steps, you can successfully implement globalization in your APEX application, making it accessible to a worldwide audience.

Conclusion
Implementing globalization in Oracle APEX helps ensure your application is accessible, user-friendly, and relevant across different languages and regions. With built-in support for language translation, locale-sensitive formatting, and user-driven preferences, APEX gives you the flexibility to serve a global user base while maintaining a single, unified application structure.

 

HOW DO I Users and Groups of Users

 

Introduction
In Oracle APEX, managing users and groups of users is a critical part of building secure, role-based applications. By organizing users into logical groups—such as Administrators, Managers, or Employees—you can assign permissions and access rights more efficiently. This structure allows developers to control what each group can see or do within the application, simplifying user management and strengthening overall security.

In Oracle APEX, managing users and groups of users allows you to define access and behavior across your application in a structured and secure way. This is typically implemented using a user management table and a group or role assignment table that lets you associate one or more users with specific roles or permission levels.

To begin, you should first define a user table in your schema. For example:

CREATE TABLE app_users (
  user_id     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  username    VARCHAR2(100) NOT NULL UNIQUE,
  email       VARCHAR2(200),
  status      VARCHAR2(20),
  created_on  DATE DEFAULT SYSDATE
);

Next, create a table to define groups or roles:

CREATE TABLE user_groups (
  group_id    NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  group_name  VARCHAR2(100) NOT NULL UNIQUE
);

Then, define a mapping table that links users to groups. This allows a single user to belong to multiple groups if needed:

CREATE TABLE user_group_membership (
  user_id   NUMBER NOT NULL,
  group_id  NUMBER NOT NULL,
  PRIMARY KEY (user_id, group_id),
  FOREIGN KEY (user_id) REFERENCES app_users(user_id),
  FOREIGN KEY (group_id) REFERENCES user_groups(group_id)
);

Once the structure is in place, insert your users and groups. For example:

INSERT INTO app_users (username, email, status) VALUES ('ADMIN1', 'admin@example.com', 'ACTIVE');
INSERT INTO user_groups (group_name) VALUES ('ADMIN');
INSERT INTO user_groups (group_name) VALUES ('MANAGER');

-- Associate user with ADMIN group
INSERT INTO user_group_membership (user_id, group_id)
SELECT u.user_id, g.group_id
FROM app_users u, user_groups g
WHERE u.username = 'ADMIN1' AND g.group_name = 'ADMIN';

In APEX, you can use this structure to enforce group-based access control. Start by creating Authorization Schemes under Shared Components. For example, to restrict access to users in the “ADMIN” group:

• Create a new authorization scheme

• Set the type to PL/SQL Function Returning Boolean

• Use a block like this:

RETURN EXISTS (
  SELECT 1 
  FROM app_users u
  JOIN user_group_membership ugm ON u.user_id = ugm.user_id
  JOIN user_groups g ON ugm.group_id = g.group_id
  WHERE u.username = :APP_USER
    AND g.group_name = 'ADMIN'
);

Apply this scheme to pages, buttons, or processes by setting the Authorization Scheme property in the Security section of the component.

You can also use a Before Header process to store the user’s group(s) into session state:

DECLARE
  v_group VARCHAR2(100);
BEGIN
  SELECT g.group_name
  INTO v_group
  FROM app_users u
  JOIN user_group_membership ugm ON u.user_id = ugm.user_id
  JOIN user_groups g ON ugm.group_id = g.group_id
  WHERE u.username = :APP_USER
  FETCH FIRST ROW ONLY;

  :APP_USER_GROUP := v_group;
END;

This allows you to easily reference :APP_USER_GROUP in conditions or dynamic actions.

Using users and groups of users in Oracle APEX gives you a flexible, scalable way to define access rights and customize user experience. It’s essential for maintaining clean, role-based application design, especially as your user base grows and requires differentiated access to data and features.

Step 1- Users and User Groups are created from the application level by selecting the man+wrench icon .

Step 2- Add Users name information

Step 3- add account privileges

Step 4 – Set up the initial password

Step 5- add any Group assignments needed.

Completed

 

Conclusion
Effectively managing users and groups of users in Oracle APEX ensures that your application remains secure, scalable, and easy to maintain. Group-based access control lets you apply consistent rules to many users at once, reduces redundancy, and supports a cleaner, more flexible security model. With the right structure in place, you can confidently deliver personalized and protected experiences to every user group in your application. 

HOW DO I USE Roles and User Roles Assignments

 Introduction
Using Roles and User Role Assignments in Oracle APEX allows developers to control access to application components based on predefined user roles. By assigning roles such as ADMIN, MANAGER, or VIEWER to individual users, you can tailor the application experience, enforce security, and ensure that users only see or interact with content relevant to their responsibilities. This approach is a core part of building scalable, multi-user applications that support varying levels of access and functionality.

 Using roles and user role assignments in Oracle APEX allows you to control access to specific pages, regions, buttons, or actions based on a user’s assigned role. This is especially useful when building multi-user applications where different types of users—such as administrators, managers, and staff—need to see or interact with different parts of the application. Assigning roles and checking them through authorization schemes ensures only the right people see and use the right features.

To begin, you need a way to assign roles to users. This is typically done through a table in your application schema. For example:

CREATE TABLE app_users (
  username     VARCHAR2(100),
  user_role    VARCHAR2(50)
);

You populate this table with your application’s users and their roles. For example:

INSERT INTO app_users (username, user_role)
VALUES ('ADMIN01', 'ADMIN');

INSERT INTO app_users (username, user_role)
VALUES ('MGR01', 'MANAGER');

INSERT INTO app_users (username, user_role)
VALUES ('USR01', 'USER');

Next, create an Authorization Scheme in APEX. Go to Shared Components > Authorization Schemes, and click Create. Choose From Scratch, and use the PL/SQL Function Returning Boolean type. Give the scheme a name like “Admin Role” and enter code similar to this:

RETURN EXISTS (
  SELECT 1 
  FROM app_users 
  WHERE username = :APP_USER 
    AND user_role = 'ADMIN'
);

You can repeat this for each role type you want to manage—MANAGER, USER, etc.—by adjusting the user_role condition in the query.

To use the authorization scheme, go to Page Designer, and apply the scheme to the page, region, button, or process that should only be visible or accessible to users with that role. For example, to restrict a page to only admins:

• Open the page in Page Designer

• In the Security section, set Authorization Scheme to “Admin Role”

You can also use the same scheme for conditional display of items or buttons:

• Click on a region or button

• Under Security, set Authorization Scheme to “Admin Role”

When the user logs in, APEX sets the :APP_USER variable, which is used by the authorization scheme to check their role. If the scheme returns TRUE, the user can access the protected element. If it returns FALSE, access is denied or the component is hidden, depending on context.

Optionally, you can load the user’s role into a session state variable for easier reference throughout the app. Use a Before Header process on the home page:

DECLARE
  v_role VARCHAR2(50);
BEGIN
  SELECT user_role INTO v_role
  FROM app_users
  WHERE username = :APP_USER;

  :APP_USER_ROLE := v_role;
END;

Then, you can refer to :APP_USER_ROLE in conditions or PL/SQL expressions to apply logic throughout the app.

Using roles and user role assignments allows you to scale your application with structured access control. It keeps your application secure and gives each user the exact level of access they need, based on their responsibilities. It is one of the most flexible and essential tools in Oracle APEX security design.

Access Control requires that developers define application users and roles. Since roles are applied to users, you must create roles before adding users. You can assign users to one or more roles.

To control access to application pages and components, you need to create an Authorization Scheme.

Roles and users defined for applications can be reviewed using the following views:
APEX_APPL_ACL_USERS
APEX_APPL_ACL_USER_ROLES
APEX_APPL_ACL_ROLES

Use the following API to manage access control settings:
APEX_ACL

HOW DO I Create roles and assign the users to the roles

Conclusion
Roles and User Role Assignments in Oracle APEX provide a structured and flexible way to manage user permissions. By assigning the right roles to users and applying those roles throughout your application using authorization schemes, you maintain a secure, organized, and user-specific experience. This not only enhances the usability of your application but also ensures proper access control across all levels of functionality.

HOW DO I USE Page Level Authorization

 

Introduction
Using Page-Level Authorization in Oracle APEX allows you to control which users can access specific pages within your application based on defined rules or roles. This feature is essential for applications that serve different types of users, such as admins, managers, or standard users, and need to enforce access restrictions. By applying authorization schemes at the page level, you can ensure that sensitive data or functions are only visible to users with the appropriate privileges.

 

Page-Level Authorization in Oracle APEX is a method to control which users can access specific pages in your application. It is especially useful when different roles or user types need to see different functionality or content within the same application. By applying authorization schemes directly to pages, you ensure that only users with the proper access rights can view or interact with those pages.

To use Page-Level Authorization in Oracle APEX, first define an Authorization Scheme. Navigate to your application, then go to Shared Components > Authorization Schemes. Click Create, then choose From Scratch or From Template depending on your needs.

For example, to restrict access to users with the role "ADMIN", you might create a scheme with these settings:

• Name: Admin Only

• Scheme Type: PL/SQL Function Returning Boolean

• PL/SQL Code:

  RETURN :APP_USER IN (SELECT username FROM app_users WHERE user_role = 'ADMIN');
  

Alternatively, you can use a simpler condition like:

RETURN :APP_USER = 'ADMIN';

Once your authorization scheme is created, go to Page Designer and select the page you want to protect. In the Page Attributes, look for the Security section. Set the Authorization Scheme to the scheme you just created (e.g., “Admin Only”).

When a user attempts to access this page, Oracle APEX will evaluate the authorization scheme. If the result is TRUE, the page loads. If it returns FALSE, the user is redirected to the application's home page (or another configured page) with an access denied message.

You can also use authorization schemes at a more granular level—such as for regions, buttons, items, or processes—if you want to show or hide elements on a page rather than restrict the entire page.

To test your setup, run the application and sign in as a user with and without the required role. Try accessing the page directly through the browser’s URL bar to verify that the access restriction is working as expected.

Using Page-Level Authorization allows you to build secure, role-based access into your Oracle APEX applications. It ensures users only see the pages that are relevant to them and helps prevent unauthorized access to sensitive information or functionality.

 

Error Message

 

Conclusion
Implementing Page-Level Authorization in Oracle APEX strengthens your application’s security and supports a more tailored user experience. It helps protect restricted areas, prevents unauthorized access, and allows you to align content visibility with business roles. By combining dynamic authorization schemes with proper session and user management, you maintain control over who sees what—right where it matters most.

Social Sign-In Authentication

 

Introduction
Social Sign-In Authentication in Oracle APEX allows users to log in using their existing accounts from popular identity providers such as Google, Microsoft, or Apple. This modern authentication approach simplifies the login process by eliminating the need for separate application-specific credentials. By leveraging OAuth 2.0 and OpenID Connect standards, Social Sign-In enhances user convenience, reduces password management burdens, and supports secure, federated access across platforms.

Social Sign-In Authentication in Oracle APEX allows users to log in using their credentials from trusted third-party identity providers such as Google, Microsoft, Apple, Facebook, or any OpenID Connect-compliant service. This approach simplifies the login experience, reduces password fatigue, and improves security by delegating authentication to platforms that users already trust and manage.

To implement Social Sign-In in your APEX application, start by registering your app with the identity provider of your choice. For example, if you're using Google, go to the Google Cloud Console and create a new OAuth 2.0 client ID. For Microsoft, use the Azure Portal. When registering, you'll be asked for redirect URIs. This must match your APEX callback URL, which follows this pattern:

https://your-domain/ords/your-workspace/oauth2callback

After registration, you’ll receive a Client ID and Client Secret—store these securely.

Now, go to your APEX application, navigate to Shared Components > Authentication Schemes, and click Create. Select Based on a Preconfigured Scheme from Gallery. Choose Social Sign-In and proceed to the configuration screen.

Set the following values:

• Authentication Provider: Choose from the list (Google, Microsoft, Apple, etc.) or define a custom OpenID Connect provider.
• Client ID: Paste the client ID from your provider.
• Client Secret: Paste the corresponding client secret.
• Scope: This determines the level of access. For basic login, use openid email profile.
• User Info Endpoint: If not automatically filled, this URL is where APEX fetches user details after authentication. Each provider has a specific endpoint (e.g., https://openidconnect.googleapis.com/v1/userinfo for Google).
• Username Attribute: Choose which attribute from the provider response should be used as the APEX username. This is typically email, sub, or preferred_username.

After completing the setup, set the new authentication scheme as the Current one.

When users access your application, they’ll be redirected to the identity provider’s login screen. After successful authentication, the provider redirects them back to your app, and APEX creates a session based on the returned identity information.

You can optionally define a Post-Authentication Procedure to set up session state or roles. For example:

BEGIN

  SELECT user_role INTO :APP_ROLE

  FROM app_user_directory

  WHERE email = :APP_USER;

END;

APEX stores the authenticated user identity in the :APP_USER session variable, allowing you to apply authorizations or display personalized content.

To secure the setup, always use HTTPS, validate redirect URIs, and monitor access tokens for misuse. Most providers also allow you to configure token expiration and consent policies for better control.

Social Sign-In in APEX reduces login friction, improves user adoption, and leverages secure, widely-used identity platforms. It’s especially useful for public-facing applications or when users already maintain external accounts with supported providers. By offloading authentication to these services, your APEX application stays lighter, safer, and easier to maintain.

Social Sign-In enables authentication through third-party identity providers, including Google, Facebook, and other OpenID Connect or OAuth2-based providers. This method allows users to log in using their existing social or enterprise credentials, eliminating the need for separate usernames and passwords.

 Use Cases for Social Sign-In

Social Sign-In is ideal for applications that:

Are internet-facing and expect a large or unknown number of users from social networks.

Use enterprise authentication via a corporate OpenID Connect (OIDC) or OAuth2 identity provider, such as:

• Oracle Identity Cloud Service (IDCS)
• Microsoft Entra ID (formerly Azure AD)
• Okta
• Auth0
• Google Workspace
• Facebook, GitHub, LinkedIn, and other social platforms

Key Considerations for Social Sign-In

• User Credential Verification – Social identity providers handle user authentication, so any registered user can potentially access your application. To control access, use authorization schemes to restrict permissions.
• Registering Your Application – To integrate with an external identity provider, register your application and provide a callback URL. Oracle APEX requires the full URI for one of its predefined callback URLs to handle authentication responses.
• Enhanced Security Options – Depending on your provider, you can enforce multi-factor authentication (MFA), password policies, and access controls to secure authentication.

 How Social Sign-In Works in Oracle APEX

1. User Requests Access – The user attempts to log in to an Oracle APEX application.
2. Redirect to Identity Provider (IdP) – The user is redirected to the configured OAuth2/OpenID Connect provider (e.g., Google, Facebook, or an enterprise IdP).
3. Authentication with Provider – The user logs in using their credentials from the selected provider.
4. Identity Token Exchange – The IdP returns an authentication token (JWT or access token) containing user details.
5. User Session Establishment – Oracle APEX validates the token and starts a new session for the authenticated user.
6. Application Access – The user is granted access and can navigate the application based on authorization rules.

 

Benefits of Social Sign-In in Oracle APEX

• Seamless User Experience – Users log in with their existing social or enterprise accounts, reducing login friction.
• Stronger Security – Leverage enterprise-grade authentication, including MFA and identity federation.
• Scalability – Supports a large user base without requiring individual database accounts.
• Reduced Password Fatigue – No need for users to create and manage separate credentials for your application.

Best Practices for Implementing Social Sign-In

• Restrict Access with Authorization Schemes – Without additional security measures, any user with a valid identity provider account can access your application. Implement authorization rules to control access based on user roles.
• Use a Secure Identity Provider – Choose a trusted OAuth2/OpenID Connect provider that supports strong security policies, such as MFA, session timeout, and user activity monitoring.
• Ensure Proper Callback URL Registration – When configuring your identity provider, register the correct Oracle APEX callback URL to enable authentication responses.

Oracle recommends Social Sign-In, SAML, or OAuth2/OIDC authentication for secure and scalable authentication. These methods provide single sign-on (SSO), strong security, and seamless integration with enterprise identity systems, making them ideal for production environments.

Conclusion
Implementing Social Sign-In Authentication in Oracle APEX streamlines user access and improves the overall experience by connecting your application to trusted third-party identity providers. It reduces barriers to entry, encourages adoption, and helps maintain strong security practices without increasing administrative overhead. Whether for internal apps or public-facing services, Social Sign-In is a smart and scalable solution for modern authentication needs.