How Do I Add Validations and Error Message

 Validations and Error Messages in Oracle APEX

Validations in Oracle APEX ensure that user inputs meet specific criteria before processing the data. If a validation fails, an error message is displayed to the user, preventing incorrect or incomplete data from being saved.

You can use validations to check data a user enters before processing. Once you create a validation and the associated error message, you can associate it with a specific item. You can choose to have validation error messages display inline (that is, on the page where the validation is performed) or on a separate error page.

Creating an inline error message involves these steps:

• Step 1 - Create a validation and specify error message text. 

• Step 2 - Associate the validation with a specific item.

This tutorial covers the different types of validations available in APEX, how to create them, and how to customize error messages.

Types of Validations in APEX

There are several types of validations in APEX, each suited for different scenarios.

1. Item-Level Validations

These validations apply to individual page items, such as text fields, number fields, or select lists.

Examples include:

• Checking if a field is empty

• Validating email format

• Ensuring a number is within a specific range

2. Row-Level Validations

These validations apply to entire rows when using an Interactive Grid or Forms that insert or update database records.

Examples include:

• Ensuring a record is unique

• Checking if a value already exists in the database

3. Processing-Level Validations

These validations apply when submitting data through a process, such as an INSERT or UPDATE statement.

Examples include:

• Ensuring business rules are followed

• Validating multiple fields together

Creating Validations in APEX

Step 1: Adding a Required Field Validation

1. Open Page Designer in APEX.

2. Select the page item (e.g., P1_EMAIL).

3. Under the Validation section, click Create.

4. Choose Not Null as the validation type.

5. Enter a custom error message, such as:

"Email address cannot be empty. Please enter an email."

6. Click OK, then Save and Run the Page.

If the field is left empty and the user tries to submit the form, the error message will be displayed.

Step 2: Validating an Email Format

1. Create a new validation on the P1_EMAIL item.

2. Select Regular Expression as the validation type.

3. Use the following regex pattern to validate email format:

^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$

4. Enter an error message such as:

"Invalid email format. Please enter a valid email address."

5. Click Save and Run the Page, then test by entering an incorrect email format.

Step 3: Checking If a Value Already Exists in the Database

To prevent duplicate records, create a validation that checks if a value already exists in the database.

1. Open Page Designer and select the item, such as P1_USERNAME.

2. Under the Validation section, click Create.

3. Select PL/SQL Function Returning Boolean.

4. Enter the following PL/SQL code:

DECLARE

    v_count NUMBER;

BEGIN

    SELECT COUNT(*) INTO v_count 

    FROM USERS 

    WHERE USERNAME = :P1_USERNAME;

    RETURN v_count = 0;

END;

5. Enter an error message, such as:

"This username is already taken. Please choose another."

6. Click Save and Run the Page, then test by entering a duplicate username.

Customizing Error Messages

Step 1: Using a Friendly Error Display

1. Open Shared Components → Messages.

2. Click Create and enter an error code, such as ERR_DUPLICATE_USERNAME.

3. Enter a custom error message, such as:

"This username is already registered. Try another name."

4. Modify your validation to return this error code instead of a static message.

Step 2: Using JavaScript to Display Error Messages

To show a custom error message dynamically using JavaScript, follow these steps:

1. Add a Dynamic Action triggered on button click.

2. Set the action to Execute JavaScript Code.

3. Enter the following code to display an alert if a field is empty:

if (!$('#P1_USERNAME').val()) {

    apex.message.alert('Please enter a username before submitting.');

}

4. Click Save and Run the Page, then test by clicking the button without entering a username.

Best Practices for Using Validations in APEX

• Use Not Null validations for required fields to ensure they are always filled.

• Apply Regular Expressions for format validation, such as emails or phone numbers.

• Use PL/SQL Validations for database checks, such as ensuring uniqueness.

• Display friendly error messages to guide users instead of generic error codes.

• Use JavaScript for real-time feedback before form submission.

Validations in Oracle APEX help enforce data integrity and improve user experience by ensuring inputs meet specific requirements. By combining item-level, row-level, and process-level validations, along with custom error messages, you can create a more reliable and user-friendly application.

How Do I Add Checkbox Lists in a Report in Oracle APEX

 

Introduction

Checkbox lists are a powerful feature in Oracle APEX reports that allow users to select multiple rows for actions like batch processing, approval, or deletion. By integrating checkbox lists into your Interactive or Classic Reports, you can enhance user interactivity and streamline workflows that require multi-record input. This blog post explains how to add checkbox lists to a report, manage the selected values, and trigger server-side processes based on user selections.

How Do I Add Checkbox Lists in a Report in Oracle APEX

Purpose
Checkbox lists in Oracle APEX reports allow users to select multiple rows of data for batch actions like updates, deletions, or approvals. This is useful in admin dashboards, workflow management, or any screen where multi-row interaction is required.

Step-by-Step Guide: Adding Checkbox Lists to an Interactive Report

Step 1: Create an Interactive Report Page
Start by creating a new Interactive Report page or use an existing one. The report should display the data you want users to select from.

Step 2: Add a Checkbox Column to the Report
Go to Page Designer, select the report region, and add a new column to represent the checkbox. For example, you might use a hidden primary key column like EMPLOYEE_ID.
Click on the column → Under "Appearance" set the column type to "Plain Text" → Under "HTML Expression", enter the following:

<input type="checkbox" name="f01" value="#EMPLOYEE_ID#">

This creates a checkbox for each row, assigning the EMPLOYEE_ID as the value.

Step 3: Add a Button to Submit Selection
Create a button (e.g., "Submit Selected") on the same page. Set the button action to "Submit Page".

Step 4: Create a Process to Handle Selected Values
In Page Designer, under Processing, create a new PL/SQL Process that runs when the page is submitted. Use the following example code:

FOR i IN 1 .. APEX_APPLICATION.G_F01.COUNT LOOP
  -- You can use APEX_APPLICATION.G_F01(i) to access each selected value
  UPDATE employees
  SET status = 'Approved'
  WHERE employee_id = APEX_APPLICATION.G_F01(i);
END LOOP;

This code loops through the selected EMPLOYEE_IDs and applies an action to each one.

Step 5: Optional - Display a Confirmation or Success Message
Add a Success Message in the process or under "Messages" to confirm the update has occurred.

Step 6: Test the Page
Run the page. Select multiple checkboxes, click the submit button, and verify that the selected rows were processed accordingly.

Notes and Best Practices
The checkbox input name should be "f01", "f02", etc., and must match the G_F01 reference in the process.
Use unique identifiers (like primary keys) as checkbox values.
Always validate and sanitize inputs in backend processes.
Use hidden columns if needed to avoid exposing sensitive IDs in the visible report.
Add JavaScript or Dynamic Actions if you need more control over selection behavior.

Summary
Adding checkbox lists to an APEX report allows multi-row user interaction, improving the flexibility and efficiency of your application. By using HTML expressions and APEX_APPLICATION collections, you can easily manage selected rows and execute bulk operations with minimal code.

Conclusion

Incorporating checkbox lists into your Oracle APEX reports improves usability by enabling users to interact with multiple records efficiently. Whether used for mass updates, approvals, or data tagging, checkbox functionality can significantly boost productivity in your applications. With the right setup, checkbox lists provide both flexibility and control, helping you build responsive, user-centered solutions.

Example

Step 1 -  Add a region

Step 2 – Add a region Item

• Give it a name and make it a checkboxgroup

• In the List Of Value area  

• Select Type: SQL Query

• In the SQL Query field select a select query

Step 3- review your page, it should look something like the following example

Step 4- make the checkboxes be grouped into manageable columns. Here we are using the number 6.

• Settings > Number of columns: 6

Here is what it looks like with the change:

Step 5 – Create a new Region

• Identification > Name: Employees

• Identification > Type: Interactive Report

• Source > Table > OEHR_EMPLOYEES

• Source >  Where Clause:*Notice that there is no need for the word “WHERE’*

InStr(':' || :P22_DEPARTMENTS || ':',   ':' || DEPARTMENT_ID || ':' ) > 0 

AND DEPARTMENT 

• Source > Page items To Submit – Place the name of the checkbox

Step 6- Create a Dynamic Action for the checkbox group\

• In the true section 

• Identification > Action: Refresh

• Affected Elements > Section Type: Region

• Affected Elements > Region: Employees

 The final product should look something like this

How Do I Add a Dropdown List in Oracle APEX

 

Introduction

Dropdown lists are a fundamental part of building user-friendly web applications in Oracle APEX. They allow users to select from a predefined set of options, ensuring data consistency and simplifying input. Whether you’re working with static values or pulling data from a table, adding a dropdown list (also called a select list) is quick and highly customizable. In this guide, we'll walk through how to create a dropdown list step-by-step using both static and dynamic data sources.

Purpose

A dropdown list (also known as a select list) allows users to choose a single value from a list of options. This is commonly used for selecting items like departments, statuses, or categories.

Step-by-Step Guide: Adding a Dropdown (Select List)

Step 1: Go to Page Designer

1. Open your Oracle APEX application.

2. In the App Builder, click on the page where you want to add the dropdown.

3. Click Page Designer to edit the layout.

Step 2: Create the Dropdown Item

1. In the Layout pane, select the region (or create a new one) where you want the dropdown to appear.

2. Right-click the region, and choose Create Page Item.

3. In the Type dropdown, select Select List.

4. Enter a name, such as P1_STATUS or P1_DEPT_ID.

5. Set a Label (e.g., "Select Status").

Step 3: Define the List of Values (LOV)

You must now define the list of choices that appear in the dropdown.

Option A: Static Values

1. In the Settings section for the Select List, go to List of Values.

2. Choose Type: Static Values.

3. Click Edit Static Values.

4. Add entries like this:

   Display Value	Return Value
   Active	A
   Inactive	I
   Pending	P

5. Click OK to save.

Option B: Dynamic SQL Query

1. Set Type: SQL Query.

2. Enter a SQL statement, such as:

   SELECT department_name d, department_id r
   FROM departments
   ORDER BY department_name
   

   • d is the display value (shown in dropdown)

   • r is the return value (stored in the item)

3. Optionally check Display Null Value if you want an empty default option.

Step 4: Set Default Value (Optional)

• Under the Source section, set a Default value if needed.

• You can use a static value, an SQL query, or PL/SQL.

Step 5: Save and Run

1. Click Save (or press Ctrl + S).

2. Click Run to preview your page.

3. The dropdown should now be visible and working.

Notes

• The dropdown item’s value can be referenced using :P1_STATUS or :P1_DEPT_ID in SQL or PL/SQL.

• You can use it as a filter in reports or as an input for a form.

• Dropdown items can be made dependent using Cascading LOVs if needed.

Summary

To add a dropdown list in Oracle APEX:

1. Open Page Designer.

2. Create a new Select List page item.

3. Define the List of Values using static entries or a SQL query.

4. Optionally set a default value.

5. Save and run the application to test it.

Certainly. Below is an introduction and conclusion paragraph written in plain text, using Arial font (if supported by the display environment), for a blog post titled:

Conclusion

Adding a dropdown list in Oracle APEX is a straightforward yet powerful way to enhance user interaction and control input values. Whether you're filtering reports, capturing form inputs, or driving conditional logic, the select list component integrates easily with the rest of your application. By understanding how to configure it properly, you can build more dynamic, efficient, and user-friendly pages with minimal effort.

Example

• Add a “Select One” dropdown box.

• Select the List of Values to SQL Query

• Add the SQL code

In this case we’re using the DEPT table from APEX

The query is as follows:

select 

    DEPT.DEPTNO || ' ' || DEPT.LOC as LOC   , DEPT.DNAME as DNAME

 from DEPT DEPT;

The dropdown displays as follows:

How to Pass Multiple Variables to a Second Page in Oracle APEX

 Introduction

In Oracle APEX, building responsive and interactive applications often requires transferring more than just one value between pages. Whether you're passing customer ID, region, and order status from a dashboard to a filtered report, or multiple form inputs to a processing page, Oracle APEX makes it simple to pass multiple variables during navigation. This blog post explores the various techniques available for passing multiple values—from using buttons and dynamic actions to URL parameters—ensuring your users enjoy a seamless and data-rich experience across pages.

Purpose

Passing multiple variables (parameters) from one page to another is essential in Oracle APEX when you want to transfer contextual data such as user selections, record IDs, filters, or form values between screens. Oracle APEX supports this natively using buttons, links, dynamic actions, and manual URLs.

Step-by-Step Guide: Pass Multiple Variables Between Pages

 Step 1: Prepare Page Items

On Source Page (e.g., Page 1):

Make sure you have APEX page items that hold the values you want to pass, such as:

• P1_EMP_ID

• P1_DEPT_ID

• P1_STATUS

On Target Page (e.g., Page 2):

Create matching items to receive those values:

• P2_EMP_ID

• P2_DEPT_ID

• P2_STATUS

Ensure that session state is enabled for all these items.

 Step 2: Pass Parameters via Button

1. In Page Designer, go to Page 1.

2. Add a Button (or use an existing one).

• Set its Action to: Redirect to Page in this Application

• Set Target Page to: 2 (or your target page number)

3. Scroll down to the Set Items section.

• Click Set Items or + Add Parameter.

• Add rows for each variable:

Name	Value
P2_EMP_ID	&P1_EMP_ID.
P2_DEPT_ID	&P1_DEPT_ID.
P2_STATUS	&P1_STATUS.

This tells APEX to send the current values from Page 1 to Page 2.

 Step 3: Use Parameters on Target Page

On Page 2:

• Use :P2_EMP_ID, :P2_DEPT_ID, and :P2_STATUS in SQL regions, PL/SQL logic, or dynamic actions.

Example SQL Query:

SELECT * FROM EMPLOYEES 
WHERE EMPLOYEE_ID = :P2_EMP_ID
  AND DEPARTMENT_ID = :P2_DEPT_ID
  AND STATUS = :P2_STATUS

 Alternative: Pass Parameters Using Report Links

This method is used inside Interactive Reports, Classic Reports, or Grids.

1. Open the report region on Page 1.

2. Go to the column you want to make clickable (e.g., EMPLOYEE_NAME).

3. In Column Attributes, go to the Link section:

• Link Target Page: 2

• Set Items:

  • P2_EMP_ID → #EMP_ID#

  • P2_DEPT_ID → #DEPT_ID#

  • P2_STATUS → #STATUS#

APEX will replace #COLUMN_NAME# with the actual row values when rendering the link.

Manual URL: Constructing f?p URLs for Custom Navigation

Useful for dynamic PL/SQL content, buttons, or navigation menus.

Example:

f?p=&APP_ID.:2:&SESSION.::NO::P2_EMP_ID,P2_DEPT_ID,P2_STATUS:&P1_EMP_ID.,&P1_DEPT_ID.,&P1_STATUS.

Explanation:

• &APP_ID.: Current App ID

• 2: Target page number

• &SESSION.: Session ID

• NO: Request (optional)

• P2_EMP_ID,P2_DEPT_ID,P2_STATUS: Items on the target page

• &P1_EMP_ID.,&P1_DEPT_ID.,&P1_STATUS.: Current page item values

Use this URL in:

• PL/SQL HTP.P output

• Dynamic action Redirect to URL

• Navigation menu entries

 Tips & Best Practices

•  Use session state correctly — ensure items on both pages are session-state aware.

•  Use Before Header processes on the target page to capture and act on parameters early.

•  Avoid passing sensitive data through URLs unless securely encrypted.

•  Consider resetting the session state of the target page if needed.

 Summary

Oracle APEX provides several robust methods to pass multiple variables to another page:

• Buttons with parameter mappings

• Report links with column substitutions

• Manual f?p URLs for dynamic scenarios

• Dynamic actions for custom navigation logic

This enables highly contextual, user-friendly, and functional applications with little to no code.

Example:

Lets assume that you have two pages

• Page 3 has two fields: FirstName and Last Name

• Page 4 has two fields: FirstName and Last Name

You want to pass the values from page 3 and display them in page 4

Set the two fields in Page 3 and a button to send the data.

Set the two fields in page 4 

Go back to Page 3, select the button, and set the behavior:

• Action: Redirect to Page in this Application

• Target: Page 4

In the Target builder

• Make sure that the target page is 4

• Set items Name and Value

*NOTE: The “Name” field is the name of the field in Page 4 that you want to display the data from the Value. The “Value” is the field in Page 3 with the data that you want to display in Page 4.

Conclusion

Passing multiple variables between pages in Oracle APEX is a foundational skill for any developer looking to create dynamic, context-driven applications. By leveraging built-in features like button redirects, report links, and URL construction, you can transfer data efficiently and accurately. Whether you're working with forms, reports, or custom processes, understanding how to map and retrieve multiple parameters will greatly enhance the flexibility and functionality of your applications.