How do I Work with a Map Report

 

Introduction
Displaying spatial data in a visual, interactive way is increasingly valuable in modern applications. In Oracle APEX, Map Reports offer a powerful and intuitive way to represent geographic information directly within your application. Whether you're tracking locations, plotting assets, or visualizing regions with business data, a Map Report provides an effective tool to enhance the user experience and bring location intelligence into your APEX apps.

Detailed Instructions: How to Work with a Map Report in Oracle APEX

To work with a Map Report in Oracle APEX, follow these steps:

1. Prepare Your Spatial Data

   • Make sure your table contains spatial data in the form of SDO_GEOMETRY or longitude/latitude pairs.

   • Example table:

     CREATE TABLE store_locations (
       id           NUMBER,
       store_name   VARCHAR2(100),
       longitude    NUMBER,
       latitude     NUMBER
     );
     

2. Create a Map Region

   • Open Page Designer, click Regions, then create a new Map region.

   • Set the Region Type to Map.

   • Choose the Data Source Type: SQL Query or Table/View.

   • If using SQL, provide a query with longitude and latitude columns.

     SELECT id,
            store_name AS name,
            latitude,
            longitude
     FROM store_locations
     

3. Configure Map Attributes

   • Under the Map Attributes, choose the map provider (Oracle Maps, OpenStreetMap, or others).

   • Set the marker layer to define what icons will be shown.

   • You can bind column values to tooltips, labels, and popup info windows for interaction.

4. Style the Map Markers

   • Go to the Appearance settings to assign marker icons based on data.

   • You can use different icons or colors based on location type, category, or status.

5. Add Interactivity

   • Use Dynamic Actions to respond to user clicks on map markers.

   • For example, when a user clicks a marker, you can redirect them to a details page or open a modal.

6. Test and Adjust Zoom

   • Customize the map’s initial zoom level and center point to best fit your data coverage.

7. Use RESTful Data Sources (Optional)

   • APEX also allows loading map data from REST APIs if your location data resides in external systems.

 

The first thing that you need to make a map work is a way to map your points of interest into the map. This can be done using one of three categories:

Geospatial Data Types in Oracle APEX

Oracle APEX supports multiple geospatial data formats, each suited for different use cases in mapping and spatial analysis.

1. SDO_GEOMETRY (Oracle Spatial Object Type)

• SDO_GEOMETRY is a specialized Oracle Object Type used by Oracle Spatial to store and manipulate geographic data.

• It includes details such as the coordinate system, geometry type, dimensions, and spatial data points stored in an array.

• Ideal for map layers in APEX and advanced spatial operations.

• Utilized by the SDO_UTIL and SDO_SAM packages for: 

• Calculating distances between points.

• Performing spatial analysis and geometric transformations.

• Supports all geometry types, including points, lines, and polygons.

2. GeoJSON (Geospatial JSON Format)

• GeoJSON is a widely used JSON-based format for representing geographic data.

• Follows the GeoJSON standard, making it easy to read and exchange between systems.

• Oracle supports storing and indexing GeoJSON data.

• Particularly useful in JavaScript-based applications and dynamic map interactions.

• Supports all geometry types, including points, lines, and polygons.

3. Longitude/Latitude (2D Coordinates)

• The simplest and most common way to represent locations on a map.

• Uses two numeric values: 

• Longitude (X-axis) – Horizontal position.

• Latitude (Y-axis) – Vertical position.

• Familiar to most users and widely available in public datasets and APIs.

• Suitable for basic mapping needs and integration with mapping tools like Google Maps.

Each of these geospatial data types serves a unique purpose in Oracle APEX, from simple mapping to complex spatial analytics. 

EXAMPLE:

In this case we will use Longitude/Latitude.

Step 1 – Create a map page

Select table

Select map style

Save and Browse

Conclusion
Working with a Map Report in Oracle APEX allows you to visually represent data in ways that tables and charts cannot. With support for spatial queries, dynamic interactions, and beautiful visual styling, Map Reports help users explore, analyze, and act on geographic data with ease. Whether you're plotting store locations, delivery zones, or event venues, integrating a Map Report into your application elevates both functionality and user experience.

How do I Get the User name Using PL/sql Expression

 Introduction
In Oracle APEX, knowing the currently logged-in user is essential for customizing the user experience, enforcing security, or logging actions. Whether you're tracking who made changes to a record or filtering data based on the user, you’ll often need to retrieve the username using PL/SQL. Oracle APEX provides simple ways to access this information using built-in functions that work well within PL/SQL expressions.

Detailed Instructions: How to Get the Username Using PL/SQL in Oracle APEX

To retrieve the current user's username in a PL/SQL expression, use the :APP_USER bind variable or the V function.

1. Use the :APP_USER Bind Variable

   • This is the most direct and commonly used approach inside APEX pages or PL/SQL code blocks.

   • Example:

     v_username := :APP_USER;
     

   • You can use this in processes, validations, computations, or triggers to capture the username of the logged-in session.

2. Use V('APP_USER') in SQL Queries

   • If you're writing SQL or dynamic SQL inside PL/SQL, use the V() function.

   • Example:

     SELECT * FROM AUDIT_LOG WHERE USERNAME = V('APP_USER');
     

   • This is useful for filtering data shown to the current user.

3. Insert the Username into a Table

   • Suppose you’re tracking who submitted a form:

     INSERT INTO CHANGE_LOG (ID, CHANGED_BY, CHANGE_DATE)
     VALUES (SEQ_LOG.NEXTVAL, :APP_USER, SYSDATE);
     

4. Use in Default Value for Items

   • You can set a page item default type to PL/SQL Expression and use:

     :APP_USER
     

   • This will auto-fill a username field when the page loads.

5. Use in Logging Procedures

   • For centralized logging:

     PROCEDURE log_action(p_action IN VARCHAR2) IS
     BEGIN
       INSERT INTO LOG_TABLE (username, action, log_time)
       VALUES (:APP_USER, p_action, SYSTIMESTAMP);
     END;
     

 

Getting the User Name Using PL/SQL Expression in Oracle APEX

Retrieving the currently logged-in username in Oracle APEX is essential for tracking user actions, personalizing content, and managing security. This tutorial explains how to obtain the username using PL/SQL expressions within different APEX components.

Using the APP_USER Variable

Oracle APEX automatically provides the APP_USER variable, which holds the username of the currently logged-in user. This variable can be used in different places, such as SQL queries, PL/SQL processes, and page items.

Method 1: Using PL/SQL Expression in a Page Item

1. Open Page Designer in your APEX application.

2. Create a new page item (e.g., P1_USERNAME) of type Display Only.

3. Set the Value Type to PL/SQL Expression.

4. In the PL/SQL Expression field, enter:

:APP_USER

5. Save and run the page. The page item will now display the logged-in username.

Method 2: Using APP_USER in a SQL Query

You can use APP_USER in SQL queries to filter data based on the logged-in user.

Example: Retrieve employee details for the logged-in user

SELECT employee_id, name, department

FROM employees

WHERE username = :APP_USER;

This ensures that each user sees only their own records.

Method 3: Using APP_USER in PL/SQL Process

You can use APP_USER within a PL/SQL Process to store the username in a table or log user activity.

Example: Inserting the logged-in user's activity

INSERT INTO user_logs (username, login_time, action)

VALUES (:APP_USER, SYSDATE, 'Page Accessed');

This records every instance when a user accesses a page.

Method 4: Using APP_USER in a PL/SQL Function Returning a Value

If you need to use APP_USER in a function for validation or business logic, you can define a PL/SQL function as follows:

FUNCTION get_current_user RETURN VARCHAR2 IS

BEGIN

   RETURN :APP_USER;

END get_current_user;

This function can be used in different PL/SQL components in your application.

Method 5: Displaying the Username in a Header or Footer

1. Go to Shared Components > User Interface > Breadcrumbs, Headers, or Footers.

2. Add the following PL/SQL block in the appropriate section:

Welcome, <b> &APP_USER. </b>

This dynamically displays the username in the header.

Best Practices

• Use APP_USER for user-specific filtering and logging.

• Store APP_USER in a table if you need a historical log of user activities.

• Always validate user permissions before displaying data based on APP_USER.

• Use APP_USER in combination with authorization schemes for secure access control.

The APP_USER variable in Oracle APEX makes it easy to retrieve the logged-in username using PL/SQL expressions. Whether you are storing user activity, filtering reports, or personalizing content, APP_USER ensures that your application dynamically adapts to each user's session.

EXAMPLE:

The key here is to use  the PL/SQL EXPRESSION: apex_custom_auth.get_username

In this example we will save the value into a text box and call the expression at the time that the page loads.

Step 1 – Add a field to the page

Step 2- Add a dynamic action

• In this case, in the Event> Page Load.

• Set the Action: Set Value

• Set Settings > Settings: PL/SQL Expression

• Set Settings > PL/SQL Expression: apex_custom_auth.get_username

• Set Settings > Items To Submit: P31_NEW (the name of your field)

• Set Affected Elements > Selection Type: Item(s)

• Set Affected Elements > P31_NEW (Name of your Text Field)

Here is the result. The name of the user (In this case Test2) is displayed in the box

Conclusion
Retrieving the current user's username using PL/SQL in Oracle APEX is straightforward and highly useful for personalizing and securing your application. The :APP_USER bind variable and V('APP_USER') function give you full access to session identity, allowing you to track, filter, and respond to users dynamically. Whether you're inserting audit records, limiting data access, or pre-populating fields, capturing the username with PL/SQL is a core practice every APEX developer should master.

How do I Move data from left panel to right panel

Introduction
In Oracle APEX, creating a user interface that allows users to move data from a left panel to a right panel—commonly known as a dual list or shuttle component—is a practical way to handle multi-selection and assignment tasks. Whether you're building a form to assign roles to users or selecting multiple items from a list, implementing this feature improves usability and keeps the page clean and interactive. Oracle APEX provides built-in components and dynamic actions to make this functionality simple to implement.

 How to Move Data from Left Panel to Right Panel in Oracle APEX

To create a dual-panel interface where users can move data from the left list to the right:

1. Create a Shuttle Item

   • Go to your APEX Page Designer.

   • Add a new Item to the page.

   • Set the Type to Shuttle.

   • Under the List of Values section, define a LOV Query that retrieves values for the left panel.

     SELECT ROLE_NAME d, ROLE_ID r FROM APP_ROLES ORDER BY ROLE_NAME
     

   • This query should return the display label (d) and return value (r).

2. Define Save Logic

   • The shuttle item stores the selected values (those in the right panel) as a colon-separated string.

   • To process these values in PL/SQL (e.g., to insert into a table), use a loop:

     FOR i IN 1 .. apex_util.string_to_table(:P1_SHUTTLE_ITEM).COUNT LOOP
       INSERT INTO USER_ROLES (USER_ID, ROLE_ID)
       VALUES (:P1_USER_ID, apex_util.string_to_table(:P1_SHUTTLE_ITEM)(i));
     END LOOP;
     

3. Optional: Filter the Left Panel Values

   • If you want to exclude values already selected or assigned, modify your LOV query to filter out those already in the right panel.

4. Customize with Dynamic Actions (Optional)

   • Add Dynamic Actions to refresh the shuttle or handle additional logic when selections change.

   • You can also use apex_item.select_list_from_query_xxx functions in classic reports if you prefer building a fully custom interface.

5. Styling and Usability

   • Shuttle items support template options, so you can change width, label positioning, and spacing.

   • Add instructions above the shuttle so users understand how to interact with it.

      

 Select Master Detail

Select the table

Select the kind of report

Name the page

Identify the table that you want to work with

This is the result:

Conclusion
Moving data from a left panel to a right panel in Oracle APEX is a powerful way to handle multiple selections in a clean, user-friendly format. The shuttle item simplifies this process, allowing developers to implement it with minimal code while still supporting flexible logic and customization. With proper LOVs and PL/SQL handling, this component makes it easy to manage assignments, roles, and any other selection-based tasks in your APEX applications.

How do I Display icons as part of tab headers

Introduction
In Oracle APEX, enhancing tab headers with icons can improve the visual appeal of your application and help users identify tab content more quickly. Icons add clarity, especially when tabs represent distinct functions or data sets. By incorporating simple HTML or template settings, developers can easily display icons next to or above tab labels, creating a more engaging and intuitive user experience.

 How to Display Icons in Tab Headers in Oracle APEX

To add icons to tab headers in Oracle APEX, follow these steps:

1. Use Region Display Selector Tabs

   • Ensure you’re using a page layout that supports tabs through the Region Display Selector.

   • Create multiple regions and set their Display Selector attribute to Yes.

2. Enable HTML in Region Titles

   • Go to each region’s Title attribute.

   • Insert HTML code that includes the icon. For example:

     <span class="fa fa-home"></span> Home
     

   • You can use any supported icon library like Font Awesome, Material Icons, or APEX-native icons.

3. Using APEX Native Icons

   • Oracle APEX comes with its own icon classes. Example:

     <span class="t-Icon fa fa-chart-bar"></span> Reports
     

   • Use the t-Icon class to match APEX themes and styling.

4. Adjust with Template Options (Optional)

   • Under the region’s Template Options, explore if your theme supports icon positioning or alignment.

   • Some Universal Theme templates allow you to assign icon classes without embedding HTML directly.

5. Style with CSS (If Needed)

   • For custom spacing or alignment, add CSS rules using Inline CSS in the page or via the Inline CSS section in Shared Components > Themes > Custom CSS.

     .t-RegionDisplaySelector .fa {
       margin-right: 5px;
     }
     

6. Test Your Tabs

   • Run the application to ensure that icons appear properly next to the tab headers.

   • Make adjustments to alignment or size if needed. 

Displaying Icons as Part of Tab Headers in Oracle APEX

Using icons in tab headers enhances the visual appeal and usability of an Oracle APEX application. By incorporating icons, users can quickly identify sections based on visual cues rather than just text. This tutorial explains how to add icons to tab headers using different methods, including static HTML, CSS, and JavaScript.

Step 1: Creating the Tab Structure

1. Open your APEX application and navigate to the page where you want to add tabs.

2. In Page Designer, create a Static Content Region to act as the tab container.

3. Inside this region, create a List, Buttons, or Links to act as tab headers.

4. Assign Static IDs to these tabs, such as tab_1, tab_2, etc.

Example static HTML for tab headers:

<ul class="custom-tabs">

  <li><a href="javascript:void(0);" onclick="showTab(1)"><i class="fa fa-home"></i> Home</a></li>

  <li><a href="javascript:void(0);" onclick="showTab(2)"><i class="fa fa-user"></i> Profile</a></li>

  <li><a href="javascript:void(0);" onclick="showTab(3)"><i class="fa fa-cog"></i> Settings</a></li>

</ul>

Each <i> tag represents an icon using Font Awesome (which is included in APEX).

Step 2: Creating the Tab Content Regions

1. In Page Designer, create multiple Static Content Regions for tab content.

2. Assign Static IDs to each region (tab_region_1, tab_region_2, etc.).

3. Ensure all tab regions are within the same Parent Region for organization.

Example tab content:

<div id="tab_region_1">Welcome to the Home tab</div>

<div id="tab_region_2" style="display:none;">This is the Profile section</div>

<div id="tab_region_3" style="display:none;">Settings go here</div>

Step 3: Adding JavaScript for Tab Switching

Add this JavaScript in Page Designer > Execute When Page Loads to handle tab switching:

function showTab(tabNumber) {

    // Hide all tab regions

    $("[id^=tab_region_]").hide();

    // Show the selected region

    $("#tab_region_" + tabNumber).show();

    // Update active tab styling

    $(".custom-tabs li").removeClass("active");

    $(".custom-tabs li:nth-child(" + tabNumber + ")").addClass("active");

}

This function hides all tab content areas and displays only the selected one.

Step 4: Styling the Tabs with CSS

To ensure a visually appealing tab design with icons, add this CSS in Shared Components > CSS:

.custom-tabs {

    list-style: none;

    padding: 0;

    margin: 0;

    display: flex;

    border-bottom: 2px solid #ccc;

}

.custom-tabs li {

    padding: 10px 20px;

    cursor: pointer;

    background: #f1f1f1;

    margin-right: 5px;

    display: flex;

    align-items: center;

}

.custom-tabs li i {

    margin-right: 8px;

}

.custom-tabs li.active {

    background: #0077cc;

    color: white;

    font-weight: bold;

}

This ensures that:

• Icons appear before the text.

• The active tab is highlighted.

• Tabs are visually appealing and consistent.

Step 5: Using APEX Lists for Dynamic Tabs

Instead of manually coding the tab list, you can create a List component in Shared Components and reference it dynamically.

1. Go to Shared Components > Lists and create a new List.

2. Add list entries with the following format:

SELECT 

    CASE 

        WHEN list_name = 'Home' THEN '<i class="fa fa-home"></i> Home'

        WHEN list_name = 'Profile' THEN '<i class="fa fa-user"></i> Profile'

        WHEN list_name = 'Settings' THEN '<i class="fa fa-cog"></i> Settings'

    END AS list_label,

    list_target

FROM your_table;

3. Use this list as the source for a List Region.

4. Modify the List Template to include icons.

Alternative: Using Dynamic Actions Instead of JavaScript

1. Create a Dynamic Action triggered by clicking a tab.

2. Add an action to Hide all tab regions.

3. Add another action to Show the corresponding tab region.

4. Optionally, update the styling by adding a Set Style action.

This approach avoids custom JavaScript but requires manual setup for each tab.

Best Practices

• Use icons relevant to the tab content for better usability.

• Ensure icons and text are aligned properly by adjusting padding and margins.

• If using Font Awesome, make sure APEX includes the correct CSS.

• For larger applications, use Dynamic Actions instead of custom JavaScript for better maintainability.

• Test on different devices to ensure icons display correctly on mobile.

Adding icons to tab headers in APEX improves navigation and visual appeal. By using HTML, CSS, and JavaScript (or Dynamic Actions), tabs can be styled and function seamlessly, enhancing the user experience. Whether using static HTML, lists, or Dynamic Actions, icons provide a visually intuitive way to organize content.

EXAMPLE:

Step 1 - Navigate to : Icons - Universal Theme (oracle.com)

Step 2- Select the desired Icon

Step 3- Copy the icon “span” code

<span class="fa fa-calendar-day" aria-hidden="true"></span>

Step 4- place the code in the Name field of the region

It should look something like this tab 1:

 

Conclusion
Adding icons to tab headers in Oracle APEX helps make your application more visually descriptive and easier to use. Whether you're highlighting dashboards, reports, or settings, icons provide quick visual cues that support usability and branding. With just a few HTML or CSS tweaks, you can turn plain tabs into polished, user-friendly navigation tools within your APEX interface.