Implement the "APEX Util" Button

The APEX Util button retrieves and displays the session state values stored using apex_util.get_session_state().

Implementation Steps

1. Set the Button Action to Defined by Dynamic Action.

2. Create a Dynamic Action with the following attributes: 

• Event: Click

• Action: Execute PL/SQL Code

• PL/SQL Code:

DECLARE

    v_fname VARCHAR2(100);

    v_lname VARCHAR2(100);

BEGIN

    v_fname := apex_util.get_session_state('P1_FNAME');

    v_lname := apex_util.get_session_state('P1_LNAME');

    apex_debug.info('Stored First Name: ' || v_fname);

    apex_debug.info('Stored Last Name: ' || v_lname);

END;

This logs the stored values in the APEX debug console for troubleshooting and validation.

This tutorial demonstrated different ways to interact with text fields and buttons in Oracle APEX. The buttons use various techniques, including session state management, page submission, dynamic SQL execution, JavaScript manipulation, and APEX utilities.

Each method serves a specific purpose:

• Clear Session: Clears values from session state.

• Submit: Submits the page.

• APEX Submit: Stores values in session state before submission.

• Dynamic SQL: Executes SQL dynamically.

• Dynamic JS: Modifies text fields with JavaScript.

• APEX Util: Retrieves stored session values for debugging.

By combining these methods, you can create highly interactive and dynamic APEX applications.

EXAMPLE:

In this application we have two text fields and 6 buttons:

• Text fields

• FName

• LName

• Buttons

• Clear Session

• Submit

• Apex Submit

• Dynamic SQl

• Dynamic JS

• Apex Util

ClearSessions Button

• Create button named “Clear Session”

• Create a Dynamic Action

• On the true Branch

• Create a second True Branch

Via Submit Button

Via APEX Submit

Via Dynamic SQL

• Create a Dynamic Action

• In the true branch

• In the Processing tab

• With the following values

Via Dynamic JavaScript

• At the page level

• Create a dynamic action

• In the True branch

Via App Util

Viewing the session values

At the bottom of your page select Session > View Session variables

In this example the session variables are EMPTY, but had we had any data, it would display under Item Values.

After the session is cleared

Implementing a "Dynamic JS" button

 In Oracle APEX, implementing a "Dynamic JS" button means creating a button that, when clicked, executes custom JavaScript code dynamically on the client side. This is useful to add interactivity, manipulate page elements, or call APIs without requiring a page submit.

What is a "Dynamic JS" Button?

A button that triggers JavaScript code dynamically on the page, often via a Dynamic Action that runs JavaScript on click. This allows you to perform client-side logic instantly, like showing/hiding regions, updating items, calling REST services via AJAX, or manipulating the DOM.

How to Implement a "Dynamic JS" Button in Oracle APEX

1. Create the Button

• Open your APEX application and navigate to the page.

• In Page Designer, create a new button (e.g., BTN_DYNAMIC_JS).

• Set its Action to Defined by Dynamic Action (so it doesn’t submit the page).

2. Create a Dynamic Action for the Button

• Select the button BTN_DYNAMIC_JS.

• Create a new Dynamic Action with Event = Click.

• Add a True Action with Action = Execute JavaScript Code.

3. Write Your JavaScript Code

In the Execute JavaScript Code section, enter the JavaScript you want to run when the button is clicked. For example:

// Example: Show an alert with a message
alert('Dynamic JavaScript Button Clicked!');

// Example: Hide a region with Static ID "my_region"
$('#my_region').hide();

// Example: Set a page item value dynamically
$s('P1_MY_ITEM', 'New Value');

// Example: Trigger a refresh on a region
apex.region('my_region').refresh();

4. Optional: Use Page Items and Dynamic Data

You can access and modify page items dynamically with JavaScript:

• Get item value: var val = $v('P1_MY_ITEM');

• Set item value: $s('P1_MY_ITEM', 'New Value');

5. Additional Example

The Dynamic JS button updates the text fields dynamically using JavaScript.

Implementation Steps

1. Set the Button Action to Defined by Dynamic Action.

2. Create a Dynamic Action with the following attributes: 

• Event: Click

• Action: Execute JavaScript Code

• JavaScript Code:

$s("P1_FNAME", "John");

$s("P1_LNAME", "Doe");

This script sets the values of the text fields to "John" and "Doe" when the button is clicked.

6. Test the Button

Run the page, click the button, and see your JavaScript code in action.

Why Use a Dynamic JS Button?

• Instant UI updates without server round trips.

• Customize page behavior dynamically.

• Integrate third-party libraries or APIs.

• Enhance user experience with animations, validations, or dynamic content.

Important Considerations

• JavaScript runs on the client browser; keep security in mind.

• Use jQuery selectors carefully to avoid affecting unintended elements.

• For server-side logic, use AJAX callbacks or page submits instead.

• Debug JavaScript using browser developer tools (Console tab).

If you want, I can help you write JavaScript examples for specific use cases like refreshing regions, calling REST APIs, or toggling page elements dynamically. Just ask!

Implementing a Dynamic SQL Button

 In Oracle APEX, implementing a "Dynamic SQL" button allows you to run SQL statements dynamically at runtime based on input or logic. This is especially useful when building flexible interfaces such as report builders, advanced filters, or admin tools. Below is a detailed explanation on how to implement a button that executes dynamic SQL.

Objective:

Create a button in Oracle APEX that executes Dynamic SQL when clicked, based on user input or page item values.

Steps to Implement a "Dynamic SQL" Button

1. Create the Button

• Go to the page in APEX.

• In the Page Designer, under Buttons, click + and create a new button, name it: BTN_DYNAMIC_SQL.

• Set Action to Defined by Dynamic Action (not Submit).

2. Create a Textarea Item for SQL Input (Optional)

If you want to let users input SQL statements:

• Add a Textarea item, e.g. P1_SQL_INPUT.

3. Create a Dynamic Action on Button Click

• In the Page Designer, right-click on the BTN_DYNAMIC_SQL and select Create Dynamic Action.

• Event: Click

• Action: Execute PL/SQL Code

4. PL/SQL Code to Execute Dynamic SQL

Here’s a sample code block:

DECLARE
    v_sql VARCHAR2(4000);
BEGIN
    v_sql := :P1_SQL_INPUT; -- Or hardcode: 'UPDATE emp SET sal = sal * 1.1 WHERE deptno = 10';
    EXECUTE IMMEDIATE v_sql;
EXCEPTION
    WHEN OTHERS THEN
        APEX_ERROR.ADD_ERROR (
            p_message => 'Error executing SQL: ' || SQLERRM,
            p_display_location => apex_error.c_inline_with_field_and_notif
        );
END;

5. Settings in Dynamic Action

• Items to Submit: Add P1_SQL_INPUT (or any item you're using for the SQL).

• Page Items to Return: None (unless your SQL uses RETURNING INTO).

6. (Optional) Show Feedback

To show confirmation, add a True Action → Show Notification after the PL/SQL block.

The Dynamic SQL button runs an SQL statement dynamically using EXECUTE IMMEDIATE.

Implementation Steps

1. Set the Button Action to Defined by Dynamic Action.

2. Create a Dynamic Action with the following attributes: 

• Event: Click

• Action: Execute PL/SQL Code

• PL/SQL Code:

DECLARE

    v_full_name VARCHAR2(100);

BEGIN

    EXECUTE IMMEDIATE 'SELECT ' || :P1_FNAME || ' || '' '' || ' || :P1_LNAME || ' FROM DUAL' INTO v_full_name;

    apex_debug.info('Full Name: ' || v_full_name);

END;

• Affected Elements:

• Selection Type: Items

• Items: P1_FNAME, P1_LNAME

This code dynamically concatenates FName and LName and logs the result using apex_debug.info.

Example Use Cases

• Admin pages that allow direct updates.

• Executing dynamic reports.

• Batch SQL execution tools.

Security Warning

Running raw user input as dynamic SQL can be dangerous and may lead to SQL injection. Ensure this is only available to trusted users and sanitize input when possible. Use bind variables where applicable.

Let me know if you want to use this button to dynamically build and execute a SELECT statement, or to refresh a region based on user input—I can help with those patterns too.

Implementing the "Submit" Button

 The "Submit" button in Oracle APEX plays a central role in processing user input and interacting with backend logic. Whether you're working with forms, interactive grids, or other data-entry components, the Submit button allows you to capture user data and trigger validations, processes, or computations. Implementing it correctly ensures a smooth and consistent experience for both the end user and the application logic. In APEX, this button is typically configured to submit the page and initiate backend PL/SQL processes, making it a key tool for handling data persistence and flow control within your application.

In Oracle APEX, implementing a "Submit" button is essential for processing user input and managing server-side logic. When a user clicks the Submit button, the page is submitted to the server, and any defined processes, validations, or branches associated with the button are executed. Here's how to create and configure a Submit button in detail:

To begin, open your APEX application and navigate to the page where you want to place the Submit button. In the Page Designer, under the Buttons section, right-click and select Create > Button. Assign a meaningful name to the button, such as SUBMIT, and choose the appropriate Position (e.g., Position 01: Region Template Button).

Set the Action property of the button to Submit Page. This setting ensures that clicking the button triggers a full page submit, allowing APEX to execute server-side logic. Next, go to the Processing section of the page and create a Process (e.g., to insert or update records). Under the Server-Side Condition, set the condition When Button Pressed = SUBMIT. This ensures that the process only runs when the Submit button is clicked.

Optionally, add Validations in the Validations section to check user input before the process is executed. These can be item-level or page-level validations, and APEX will automatically prevent the page process from running if validations fail. You can also configure Branching after the Submit process, such as redirecting the user to another page or showing a confirmation message.

By using these features, the Submit button acts as the control center for input handling, server-side logic, and user feedback. You can reuse this pattern across different pages and forms for consistent functionality throughout your application.

In Oracle APEX, APEX_UTIL.SET_SESSION_STATE is a built-in procedure used to programmatically set the value of one or more session state items (page items, application items, or other APEX variables) from PL/SQL. This is particularly useful when you want to update the value of an item without user interaction or when you're performing logic in a process or dynamic PL/SQL code.

Syntax:

APEX_UTIL.SET_SESSION_STATE (
   p_name  IN VARCHAR2,
   p_value IN VARCHAR2
);

Parameters:

• p_name – The name of the item or application item whose session state value you want to set.

• p_value – The value you want to assign to that item in session state.

Example 1: Set session state for a page item

BEGIN
   APEX_UTIL.SET_SESSION_STATE('P1_DEPTNO', '10');
END;

This sets the page item P1_DEPTNO to the value 10.

Example 2: Set session state for an application item

BEGIN
   APEX_UTIL.SET_SESSION_STATE('APP_USER_ROLE', 'ADMIN');
END;

This assigns the value 'ADMIN' to the application item APP_USER_ROLE.

Notes:

• The value set using APEX_UTIL.SET_SESSION_STATE is stored in the session and can be used by subsequent processes or PL/SQL logic.

• If the item is protected by session state protection and marked as "Restricted", the procedure may not work unless you disable the protection or adjust settings.

• This is commonly used in Before Header or After Submit processes, or in dynamic actions that execute PL/SQL.

Use Case Example in a Process:

Suppose you want to save a department number into session state for use across multiple pages:

BEGIN
   APEX_UTIL.SET_SESSION_STATE('G_DEPT_ID', :P2_DEPT_ID);
END;

This takes the value from P2_DEPT_ID and assigns it to the application-level item G_DEPT_ID.

This button submits the page and sets the session state for the text fields explicitly.

Implementation Steps

1. Set the Button Action to Defined by Dynamic Action.

2. Create a Dynamic Action with the following attributes: 

• Event: Click

• Action: Execute PL/SQL Code

• PL/SQL Code:

apex_util.set_session_state('P1_FNAME', :P1_FNAME);

apex_util.set_session_state('P1_LNAME', :P1_LNAME);

• Affected Elements:

• Selection Type: Items

• Items: P1_FNAME, P1_LNAME

This explicitly stores the values in the session state before submitting the page.

The Submit button is more than just a trigger—it’s the bridge between user interaction and application logic. By using built-in features such as conditions, validations, and page processes, developers can fine-tune what happens after submission. Whether you're saving a form, applying changes, or redirecting users, implementing the Submit button efficiently helps maintain a responsive and reliable application. As part of your APEX development toolkit, understanding how to configure and control this button will greatly improve the power and usability of your applications.

Implementing the "Clear Session" Button

 In many Oracle APEX applications, maintaining session state can improve performance and user experience. However, there are scenarios—especially in complex forms or dashboards—where users may need to reset the current session to clear out all entered or stored values. Implementing a "Clear Session" button provides a convenient way to reset page items, filters, and internal state without requiring users to log out or manually refresh every field. This small feature can significantly reduce confusion, especially when testing or working with large datasets.

In Oracle APEX, implementing a “Clear Session” button is a helpful feature that allows users to reset all session state for a page. This is especially useful in data entry forms or dashboards where users want to clear filters, item values, or undo changes without manually refreshing each element. You can easily create this functionality using a button and a built-in URL.

To begin, go to the Page Designer and create a new Button on the desired page. You can place it in a toolbar, region, or any area that makes sense contextually. Give it a meaningful name like CLEAR_SESSION.

Under the Behavior section of the button’s properties, set the Action to Redirect to URL.

In the Target URL field, use the following format:

f?p=&APP_ID.:&APP_PAGE_ID.:&SESSION.::RESET

This URL tells APEX to reload the current application and page, but to clear the session state before doing so. The ::RESET part is what performs the session state reset. It clears all values, including page items, pagination, sorting, filters, and other APEX internal states associated with the page.

If you want to clear the entire session (for example, across multiple pages), you can use:

f?p=&APP_ID.:1:&SESSION.:RESET

This version will redirect the user to page 1 (or any other page you specify), while also performing a full application session reset.

You can style the button with a CSS class like t-Button--danger if you want to highlight it as a reset or destructive action.

Optionally, if you want to provide a confirmation before executing the action, add a Dynamic Action on the button’s click event, and use a JavaScript confirmation dialog before redirecting.

By using the built-in APEX reset functionality, you don’t need to write any PL/SQL or JavaScript logic to manually clear item values. This is a lightweight, clean, and efficient solution that enhances user experience and reduces data entry errors.

Another way of doing it:

The Clear Session button resets the values of FName and LName by clearing the session state.

Implementation Steps

1. Set the Button Action to Defined by Dynamic Action.

2. Create a Dynamic Action with the following attributes: 

• Event: Click

• Action: Execute PL/SQL Code

• PL/SQL Code:

apex_util.clear_page_cache(:APP_PAGE_ID);

• Affected Elements:

• Selection Type: Items

• Items: P1_FNAME, P1_LNAME

This ensures that the values in the text fields are cleared when the button is clicked.

Adding a "Clear Session" button gives users a fresh starting point within the application, improving usability and reducing input errors. Whether you're building administrative tools, data entry forms, or dashboards, including this functionality helps keep the interface clean and responsive. By pairing the button with proper page processes or redirection logic, you ensure that users always return to a well-defined and consistent application state.

Working with Text Fields and Buttons in Oracle APEX

 In Oracle APEX, text fields and buttons are the foundation of user interaction in any application. Whether you're designing a search form, a data entry screen, or a dashboard filter, understanding how to effectively configure and combine these components is essential. Text fields allow users to input data, while buttons trigger actions like submitting forms, running processes, or navigating between pages. APEX provides a highly intuitive interface for managing both, with numerous customization options such as validations, dynamic actions, and styling. This makes it easy to build responsive and user-friendly forms without needing complex coding.

In Oracle APEX, text fields and buttons are essential page items used to collect input and trigger logic within your applications. They play a vital role in building forms, filters, and interactive reports. Here's how to work with them effectively:

Creating Text Fields
To add a text field, open a page in Page Designer, then in the layout section (usually “Content Body” or a region), right-click and select Create > Item. Choose Text Field as the item type. Assign a meaningful Name (e.g., P1_SEARCH) and set a label for user clarity. You can define settings like maximum length, default value, and alignment under the Settings and Appearance properties.

Customizing Text Field Behavior
Text fields can have dynamic behaviors using validations or dynamic actions. For example, you might validate that a user entered at least 3 characters or automatically populate it with data from a LOV (List of Values). Dynamic actions can be added to respond to changes in the field, such as showing a message, enabling other components, or triggering processes when a user types a certain value.

Creating Buttons
Buttons are added similarly by right-clicking the layout or region and choosing Create > Button. Give it a Name (e.g., SUBMIT_BTN) and choose a label. The Action property determines what the button will do—submit the page, redirect to a page, or trigger a dynamic action.

Triggering Processes with Buttons
If the button’s action is set to Submit Page, it can trigger page processes. For instance, you can create a PL/SQL process that runs only when SUBMIT_BTN is clicked. Go to the Processing tab, create a new process, and specify Server-side Condition > When Button Pressed.

Combining Buttons with Dynamic Actions
Dynamic actions allow buttons to respond to client-side interactions without submitting the page. Select the button, create a dynamic action such as Execute JavaScript Code or Set Value, and define the conditions for when the action should run (e.g., when the button is clicked). This is often used for search fields, where clicking the button filters a report region.

Styling and Appearance
You can control the look of both text fields and buttons through the Appearance and Custom Attributes properties. You can apply CSS classes, change themes, and set alignment. For consistent styling, you might also use Template Options such as button color, icon, or size.

Best Practices

• Use meaningful item names with page prefixes for easy reference (P2_CUSTOMER_NAME).

• Apply validations at both client-side (for faster feedback) and server-side (for data integrity).

• Use dynamic actions for responsive user interactions rather than unnecessary page submits.

• Keep buttons labeled clearly according to their action (e.g., “Search”, “Save”, “Reset”).

By combining these features, Oracle APEX developers can create intuitive, responsive forms and interfaces that guide the user and enforce proper input behavior, enhancing both usability and application performance.

Working with text fields and buttons in Oracle APEX opens the door to powerful and flexible application development. By learning to configure their properties, apply dynamic actions, and integrate logic, developers can create interactive experiences that respond to user input in real time. Mastering these core elements ensures that your applications are not only functional, but also efficient and user-centric.