How do I HIDE/DISPLAY Fields using a function

 Introduction
In Oracle APEX, dynamically hiding or displaying fields based on business logic enhances the user experience by showing only relevant information. Using functions to control the visibility of fields allows developers to apply complex conditions and reusable logic that determine when a field should be visible or hidden. This approach helps create cleaner, more intuitive forms and pages that adapt to user inputs or application state.

 In Oracle APEX, hiding or displaying fields dynamically using a function enables you to apply complex business rules and reusable logic for controlling field visibility. This technique uses PL/SQL functions that return Boolean values (TRUE or FALSE) to determine whether a field should be shown or hidden on a page.

Here is a detailed step-by-step guide on how to hide or display fields using a function in Oracle APEX:

Step 1: Create the PL/SQL Function

• Define a PL/SQL function that returns a Boolean (TRUE or FALSE), which encapsulates the logic deciding whether a field should be visible.

• This function can use any conditions such as user roles, page item values, session state, or database queries.

Example function in a package or as an inline function:

FUNCTION show_field RETURN BOOLEAN IS
BEGIN
  -- Example logic: show the field only if P1_STATUS = 'APPROVED'
  IF :P1_STATUS = 'APPROVED' THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END;

Step 2: Add the Field (Page Item) to Your Page

• In your Oracle APEX page, add or select the field (page item) you want to show or hide dynamically.

Step 3: Configure the Server-side Condition

• In the Page Designer, locate the field’s Server-side Condition section.

• Choose Type as PL/SQL Function Returning Boolean.

• In the PL/SQL Function Body, call your function or write the logic directly.

For example:

RETURN show_field;

Or inline:

BEGIN
  RETURN :P1_STATUS = 'APPROVED';
END;

• When the function returns TRUE, the field will be rendered (visible); when FALSE, the field is hidden (not rendered at all).

Step 4: Use Dynamic Actions for Client-side Interactivity (Optional)

• If you want to toggle field visibility dynamically after the page loads without refreshing the page, use Dynamic Actions.

• Create a Dynamic Action based on a page item’s change event.

• Add True Actions to Show the field, and False Actions to Hide the field.

• Use JavaScript expressions or PL/SQL AJAX callbacks to evaluate your function logic and trigger the Dynamic Action accordingly.

Step 5: Testing and Validation

• Run the page and test various scenarios that affect your function’s logic.

• Ensure that the field appears only when your function returns TRUE and hides otherwise.

• Check for proper behavior with different user roles, input values, or other business conditions.

Additional Notes

• Server-side conditions prevent the field from being rendered, improving performance and security.

• Client-side hiding via Dynamic Actions only hides the field visually but keeps it in the DOM.

• Combining both approaches provides flexibility for different use cases.

Using PL/SQL functions to control field visibility in Oracle APEX offers a powerful and flexible way to implement complex business rules for user interfaces. This method ensures that fields appear only when appropriate, improving usability and security. By mastering server-side conditions with functions and complementing them with client-side dynamic actions, developers can build highly responsive and maintainable APEX applications.

Hiding and Displaying Fields Using a Function in Oracle APEX

Hiding and displaying fields dynamically in APEX is useful for improving user experience by showing only relevant fields based on user actions or system conditions. This can be achieved using JavaScript functions, PL/SQL, or Dynamic Actions.

Method 1: Using a JavaScript Function (Client-Side Approach)

This approach allows you to control visibility instantly without requiring a page refresh.

Steps to Implement JavaScript for Hiding/Showing Fields

1. Create a JavaScript Function

• Go to Page Attributes > Execute when Page Loads

• Add the following JavaScript function

function toggleField(fieldName, show) {

    var field = $('#' + fieldName);

    if (show) {

        field.show();

    } else {

        field.hide();

    }

}

2. Call the Function When the Page Loads

toggleField('P1_FIELD_NAME', false); // Hides the field initially

3. Use a Dynamic Action to Call the Function

• Event: Change

• Selection Type: Item(s)

• Item: P1_CONTROL_FIELD

• Action: Execute JavaScript Code

• Code:

if ($v('P1_CONTROL_FIELD') === 'Show') {

    toggleField('P1_FIELD_NAME', true);

} else {

    toggleField('P1_FIELD_NAME', false);

}

Method 2: Using PL/SQL to Control Visibility on Page Load

If the visibility is based on data conditions, use PL/SQL.

Steps to Hide/Show Fields Using PL/SQL

1. Create a Hidden Page Item

• Example: P1_FIELD_VISIBILITY

2. Set the Value Using a PL/SQL Computation

CASE 

    WHEN :APP_USER = 'ADMIN' THEN 'SHOW'

    ELSE 'HIDE'

END;

3. Use a Dynamic Action to Hide/Show the Field

• True Action: Show P1_FIELD_NAME when P1_FIELD_VISIBILITY = SHOW

• False Action: Hide P1_FIELD_NAME when P1_FIELD_VISIBILITY = HIDE

Method 3: Using a Dynamic Action (No JavaScript or PL/SQL Needed)

1. Create a Dynamic Action

• Event: Change

• Item: P1_CONTROL_FIELD

2. Add a True Action

• Action: Show

• Item: P1_FIELD_NAME

• Condition: Item = Value

• Value: Show

3. Add a False Action

• Action: Hide

• Item: P1_FIELD_NAME

• Condition: Item != Value

Best Practices

• Use JavaScript for real-time UI updates.

• Use Dynamic Actions for no-code solutions.

• Use PL/SQL if visibility depends on data conditions from the database.

Hiding and displaying fields in Oracle APEX can be done efficiently using JavaScript functions, Dynamic Actions, or PL/SQL computations, depending on the complexity of the requirement.

EXAMPLE:

This is an example of how to use a function for displaying and hiding items of a page.

The function

The following function returns a number (the count) that represents if the user is in a table with a match of the OKTA id and the Location id.

create or replace function  IS_NAME_IN_ROLE_AND_LOCATION (OKTA1 NUMBER, LOC NUMBER)

return NUMBER

as

returnedCount NUMBER;

BEGIN

SELECT COUNT(*) INTO returnedCount

 FROM

    LOCATIONS  

    JOIN  WKSP_XXDIIPAULTEST.SITE_MANAGEMENT_ROLES_TO_LOCATIONS

    ON  WKSP_XXDIIPAULTEST.SITE_MANAGEMENT_ROLES_TO_LOCATIONS.LOCATION = LOCATIONS.LOCATION_IDENTIFIER

    JOIN WKSP_XXDIIPAULTEST.OKTA 

    ON WKSP_XXDIIPAULTEST.OKTA.ID = WKSP_XXDIIPAULTEST.SITE_MANAGEMENT_ROLES_TO_LOCATIONS.OKTA

      where LOCATION_IDENTIFIER = LOC  AND OKTA.ID = OKTA1 ;

RETURN returnedCount;

END;

/

Setting up the page

We are going to set up various elements in the page.

1. A database table that displays some data.

1. Name: TestPageDataTable

2. A textbox that  will be used to save the value from the function and will be used for referencing the showing/hiding of elements.

1. Name: P9_HIDDEN

3. Two button that will be used for displaying how items can be shown or hidden.

1. Name: Button New1

2. Name: Button New2

Displaying data in the table

This is just a simple query used to display data on the table.

SELECT

    LOCATION_IDENTIFIER,

    LOCATION_NAME,

    LOCATION_CODE,

    OKTA,

    OKTA.ID,

    OKTA.DISPLAY_NAME

FROM

    LOCATIONS  

    JOIN  WKSP_XXDIIPAULTEST.SITE_MANAGEMENT_ROLES_TO_LOCATIONS

    ON  WKSP_XXDIIPAULTEST.SITE_MANAGEMENT_ROLES_TO_LOCATIONS.LOCATION = LOCATIONS.LOCATION_IDENTIFIER

    JOIN WKSP_XXDIIPAULTEST.OKTA 

    ON WKSP_XXDIIPAULTEST.OKTA.ID = WKSP_XXDIIPAULTEST.SITE_MANAGEMENT_ROLES_TO_LOCATIONS.OKTA

    where LOCATION_IDENTIFIER = 875 

AND OKTA.ID =1803;

Page ONLOAD Event

Here we are going to create an event that will run when the paged is initially loaded and will do two things

1. Set a value in the P9_HIDDEN textbox.

2. Disable the New2 button.

1. Add the On Load event

2. Next, add two actions to a “TRUE” action

3. Set the P9_HIDDEN textbox to save the value from the function.

Calling the Function from within APEX

The function can be called using the following steps

4. In the Identification area set the “Action” to “Set Value”.

5. In the Settings area

1. Set Type to “PL/SQL Function Body”

2. In the PL/SQL Function Body place the following code:

DECLARE

   L_RETURN_VALUE NUMBER;

   L_OKTA1 NUMBER := 1803;

   L_LOC NUMBER := 875;

BEGIN

   L_RETURN_VALUE := WKSP_XXDIIPAULTEST.IS_NAME_IN_ROLE_AND_LOCATION(

      OKTA1 => L_OKTA1,

      LOC => L_LOC

   );

   RETURN L_RETURN_VALUE;

END;

6. In the Affected Elements select the item/textbox that will hold the data from the function

7. Compile and save and run. Notice that the Hidden” text box now displays the value from the function. Notice that the “New2” button is disabled.

8. To disable the New2 button – Add a second action to the true event.

9. Set the “Identification” to “Disable

10. Set the Client-Side Condition This means that the button will be disabled anytime that  P9_HIDDEN has a value greater than 0. In the case of our function, it always returns a value > 0. That makes the button always disabled.

BONUS

You can also hide/display the item (button) by using the Server-side conditions

Will display when Item is not ZERO

Displays the button because p9_HIDDEN has a value of 1

 

Conclusion
Hiding and displaying fields using functions in Oracle APEX provides a powerful way to customize the user interface dynamically. By leveraging PL/SQL functions or expressions as conditions for field visibility, developers can implement flexible and maintainable solutions that respond to changing requirements. Mastering this technique is key to building sophisticated and user-friendly APEX applications.

How do I Set a value to True/false using a checkbox on a form

Introduction
Using checkboxes to set true or false values on forms is a common requirement in Oracle APEX applications. Checkboxes provide a simple way for users to indicate binary choices, such as yes/no, active/inactive, or enabled/disabled. Understanding how to capture checkbox states and store them as Boolean values in your database or application logic is essential for building effective and user-friendly forms.

 In Oracle APEX, setting a value to true or false using a checkbox on a form involves configuring the checkbox item so that when checked it stores a specific value (commonly 'Y', '1', or 'TRUE'), and when unchecked it stores another value (such as 'N', '0', or 'FALSE'). This setup lets you easily capture Boolean-type data from users through the form and save it in your database or use it in application logic.

Here’s a detailed explanation on how to do this:

Step 1: Create the Checkbox Item

• In your Oracle APEX page, add a new item of type Checkbox.

• Give it a meaningful name, e.g., P1_IS_ACTIVE.

Step 2: Configure the Checkbox Settings

• Set Display As to Checkbox.

• Define the List of Values (LOV) for the checkbox. For Boolean values, you typically set:

  • Static LOV with one entry, for example:

    Y, Yes
    

  • This means when the checkbox is checked, the value 'Y' is submitted.

• Set the Value When Checked property to 'Y' (or your preferred true value).

• Set the Value When Unchecked property to 'N' (or your preferred false value).

Step 3: Bind the Checkbox to a Database Column

• If the form is based on a table or view, ensure the page item P1_IS_ACTIVE is mapped to the corresponding Boolean or CHAR(1) column in the table, such as IS_ACTIVE.

• When the form is submitted, Oracle APEX will save 'Y' if the checkbox is checked and 'N' if unchecked.

Step 4: Handling the Checkbox in PL/SQL Processes

• When processing form data in PL/SQL, you can check the value of the checkbox item like this:

IF :P1_IS_ACTIVE = 'Y' THEN
   -- Logic for true case
ELSE
   -- Logic for false case
END IF;

• This lets you control logic based on the checkbox state.

Step 5: Displaying Checkbox Values

• When loading data into the form, set the checkbox item value to 'Y' to show it as checked, or 'N' (or null) to show it as unchecked.

• Oracle APEX automatically renders the checkbox accordingly.

Step 6: Testing

• Run your page and test the checkbox by checking and unchecking it.

• Submit the form and verify that the correct true/false value is saved to the database.

• Use debugging or logging if necessary to confirm the stored values.

Additional Tips

• For Boolean database columns of type NUMBER(1), you can use '1' and '0' as values instead of 'Y' and 'N'.

• Always be consistent with the values you use to represent true and false across your application.

• You can use checkbox groups for multiple Boolean fields but for single true/false, a single checkbox is simplest.

Setting a value to true or false using a checkbox on a form in Oracle APEX is straightforward once you configure the checkbox’s checked and unchecked values properly. This method provides a clean and intuitive way for users to input binary choices, while ensuring the data is stored consistently in your backend. Proper setup and testing help maintain data integrity and improve the overall user experience in your applications.

Step 1 – Add a checkbox and a textbox to a page

Step 2 – In the checkbox, create a dynamic action

Step 3 – set the following values

Step 4 – In the TRUE action set the following values

Step 5 _ in the FALSE action set the following values

And, your all done.

Conclusion
Setting true or false values using checkboxes in Oracle APEX enables clear and intuitive data input for binary options. By properly configuring checkboxes and handling their values in your processes, you ensure accurate data capture and streamlined user interaction. Mastering this technique contributes to building robust, responsive forms that meet real-world business needs.

 

How do I Hide & display controls via variables

Introduction
Hiding and displaying controls dynamically based on variable values is a fundamental technique in Oracle APEX for creating interactive and user-friendly applications. By controlling the visibility of page items, regions, or buttons through variables, you can tailor the user interface to different scenarios, roles, or user inputs. This approach improves usability by showing only relevant information and reducing clutter, making applications more efficient and intuitive.

 In Oracle APEX, hiding and displaying controls (such as page items, buttons, or regions) based on variable values is commonly done using page item values or application-level variables combined with conditions and dynamic actions. This approach lets you create interactive pages where UI elements appear or disappear according to user input, application state, or other logic.

Here is a detailed explanation of how to hide and display controls via variables in Oracle APEX:

Step 1: Identify the Controls and Variables

Decide which controls (page items, buttons, or regions) you want to show or hide. Also, determine the variables that will control their visibility. Variables typically are page items (e.g., P1_SHOW_DETAILS) or application items.

Step 2: Use Server-side Conditions

For controls like regions or items, Oracle APEX provides built-in server-side conditions that you can apply in the property editor:

• In the control’s properties, find the Server-side Condition section.

• Choose a condition type such as Value of Item / Column = Expression.

• Specify the controlling page item (your variable) and the expected value.

For example, to show a region only when P1_SHOW_DETAILS = 'Y':

• Set Server-side Condition Type: Value of Item / Column = Expression

• Item: P1_SHOW_DETAILS

• Expression 1: Y

This way, the region will be rendered only if the variable has the matching value.

Step 3: Use Dynamic Actions for Client-side Show/Hide

For real-time interactivity without page reload, use Dynamic Actions:

1. Create a Dynamic Action on the controlling variable (usually a page item) with an event like Change or Click.

2. Add True Actions to Show or Hide specific controls.

3. Optionally, add False Actions to reverse the behavior.

Example:

• Event: Change on P1_TOGGLE_BUTTON

• True Action: Show region P1_DETAILS_REGION

• False Action: Hide region P1_DETAILS_REGION

Step 4: Using JavaScript and Variables

You can also use JavaScript to control visibility based on variables or page item values:

if ($v("P1_SHOW_DETAILS") === "Y") {
  $("#P1_DETAILS_REGION").show();
} else {
  $("#P1_DETAILS_REGION").hide();
}

This can be executed on page load or on item change using Dynamic Actions with Execute JavaScript Code.

Step 5: Combining Server and Client Logic

Use server-side conditions to prevent unnecessary rendering of controls when hidden, and client-side Dynamic Actions to toggle visibility dynamically after the page loads. This approach optimizes performance and user experience.Step 6: Testing and Debugging

• Test different values of controlling variables to ensure controls show/hide as expected.

• Use browser developer tools or APEX debug mode to inspect item values and event triggers.

• Adjust conditions and dynamic actions to cover all intended scenarios.

Hiding and displaying controls via variables in Oracle APEX is a versatile method to build dynamic, user-responsive applications. By combining server-side conditions, client-side Dynamic Actions, and optional JavaScript, developers can finely tune the interface to show only relevant content based on user input or application logic. Mastering these techniques improves application usability, reduces clutter, and delivers a polished user experience.

 

Hiding & Displaying Controls via Variables in Oracle APEX

Hiding and displaying form elements dynamically in APEX can improve user experience by showing only relevant controls based on user input or system conditions. This can be done using Dynamic Actions, JavaScript, and PL/SQL.

Method 1: Using a Dynamic Action (Recommended Approach)

Steps:

1. Create a Page Item Variable

• Example: Create a Select List item called P1_SHOW_HIDE_CONTROL with values like Yes / No.

2. Create a Dynamic Action

• Event: Change

• Selection Type: Item(s)

• Item: P1_SHOW_HIDE_CONTROL

3. Add a True Action (Show Control)

• Action: Show

• Selection Type: Item(s)

• Item: P1_CONTROL_TO_TOGGLE

• Client-side Condition: Item = Value

• Condition Value: Yes

4. Add a False Action (Hide Control)

• Action: Hide

• Selection Type: Item(s)

• Item: P1_CONTROL_TO_TOGGLE

• Client-side Condition: Item != Value

• Condition Value: Yes

Result:

• If P1_SHOW_HIDE_CONTROL is set to Yes, the control appears.

• If P1_SHOW_HIDE_CONTROL is set to No, the control is hidden.

Method 2: Using JavaScript

If you prefer using JavaScript instead of a Dynamic Action, you can use the following script.

1. Create a JavaScript Function in Page Attributes > Execute when Page Loads

function toggleControl() {

    var value = $v('P1_SHOW_HIDE_CONTROL');

    if (value === 'Yes') {

        $('#P1_CONTROL_TO_TOGGLE').show();

    } else {

        $('#P1_CONTROL_TO_TOGGLE').hide();

    }

}

2. Call the Function on Page Load

toggleControl();

3. Create a Dynamic Action on Item Change

• Event: Change

• Item: P1_SHOW_HIDE_CONTROL

• Action: Execute JavaScript Code

• Code:

toggleControl();

Result:

• The field automatically hides or shows based on the dropdown selection.

Method 3: Using PL/SQL to Control Display on Page Load

If you need to determine visibility based on a database condition:

1. Create a Computation for a Hidden Item

• Example: P1_CONTROL_VISIBILITY

• Type: PL/SQL Expression

• Code:

CASE 

    WHEN :APP_USER = 'ADMIN' THEN 'Y'

    ELSE 'N'

END;

2. Use a Dynamic Action to Show or Hide Based on P1_CONTROL_VISIBILITY

• True Action: Show P1_CONTROL_TO_TOGGLE when P1_CONTROL_VISIBILITY = Y.

• False Action: Hide P1_CONTROL_TO_TOGGLE when P1_CONTROL_VISIBILITY = N.

Best Practices

• Use Dynamic Actions for performance-friendly UI changes.

• Use JavaScript for immediate client-side interactions.

• Use PL/SQL for data-driven decisions at the server level.

Hiding and displaying controls dynamically in Oracle APEX can be achieved using Dynamic Actions, JavaScript, and PL/SQL logic. The best method depends on whether the logic is based on user input, database conditions, or real-time interactions.

EXAMPLE:

Step 1 – Place the following code in the HTML Header section of the Page

Use this code:

<script type="text/javascript">

function hideRegion(){

$x_Hide('region1');

}

function showRegion(){

$x_Show('region1');

}

</script>

Step 2 – Create a Region 

Step 3 – In Identification

      

       Step 4- In Advanced give the Static Id the name of region1 (lower case)

Step 5 – Create another region and add two buttons

Step 6 – Name one button Show Region

Step 7 – Change behavior  to “Redirect to URL” and add the following code:

javascript:showRegion();

Step 8 – create a new button called “Hide Region”

Step 9- Change behavior  to “Redirect to URL” and add the following code:

javascript:hideRegion();

Results: SHOW

Results: HIDE

Conclusion
Using variables to hide and display controls in Oracle APEX allows developers to build adaptive applications that respond to user actions and context. This dynamic visibility management enhances the user experience by ensuring that users see only what they need at any given time. Mastering this technique is essential for creating polished, responsive, and professional Oracle APEX applications.