Say Goodbye to Boring Usernames and Build a Complete Login Profile in Oracle APEX with Pure PL/SQL

bySajjad -
0
APEX PL/SQL UI/UX

Build a Professional User Profile in Oracle APEX Using PL/SQL

Display profile image, user name, email address and a custom logout button — using only PL/SQL.

📅 July 2026 👤 Sajjad Hanifa ⏱ ~9 min read 🏢 S&H Software Solutions

Hi everyone, welcome back to another Oracle APEX tutorial. Today I'll show you how I created a fully dynamic User Profile component using only PL/SQL in Oracle APEX.

This component displays the logged-in user's profile image, full name, email address, online status, and a custom logout button — all in a clean and professional layout. Many Oracle APEX applications only display the username after login. While that works, it doesn't provide the polished user experience expected from modern business applications. A custom profile component gives your application a much more professional appearance while making important user information immediately accessible.

The best part is that this entire solution is built using PL/SQL only. No JavaScript framework, Dynamic Actions, or external plugins are required. Once completed, the component can be reused throughout the entire application by placing the PL/SQL code either inside an Oracle Package or on Page 0.

Below is the final result:

Custom User Profile Component in Oracle APEX
Figure 1 — Custom User Profile Component

The profile section displays:

Profile Picture
Full Name
Email Address
Active Status Indicator
Custom Logout Button

Everything is generated dynamically based on the currently logged-in user.

💡
Note: This entire component runs on a single PL/SQL block — no JavaScript, no Dynamic Actions, no plugins. It can be reused across the whole application from a Package or from Page 0.

Why Build a Custom User Profile?

Oracle APEX provides an excellent authentication system, but most enterprise applications require more than simply displaying the logged-in username. Adding a custom profile component improves both the appearance and usability of the application.

Users can immediately identify the account they are logged into without opening another page or menu. Instead of seeing only text, they are presented with a profile picture, their full name, email address, and a clearly visible logout button.

Another advantage is consistency. Because this component is reusable, it can be displayed across every page of the application without duplicating any code. Small UI improvements like this make an application feel much more modern and professional.

Before We Start

Before implementing this solution, make sure you already have:

Oracle APEX installed
Authentication configured
A USERS table
User profile images stored as BLOB
MIME type stored for every image
APP_USER authentication enabled
⚠️
Tip: Keep profile images around 10 KB. The profile section loads on almost every page, so keeping images small helps loading speed and overall application performance.

How the Solution Works

The entire profile component is generated inside one PL/SQL block. The process is straightforward:

Retrieve the logged-in user
Load the user's profile image
Convert the image into Base64
Generate a default avatar if no image exists
Determine the user's active status
Generate a custom logout URL
Build the HTML dynamically
Return the completed HTML to Oracle APEX

Now let's build everything step by step.

Step 01
1
Retrieve the Logged-in User Information
APP_USER · USERS table

The first task is identifying the currently logged-in user. Oracle APEX stores the authenticated username inside the built-in variable :APP_USER. This value is used to retrieve the correct record from the USERS table — User ID, Full Name, Email Address, Active Status, Profile Image, and MIME Type — all in a single query.

sql — PL/SQL 
SELECT
    USER_ID,
    USER_FIRST_NAME || ' ' || USER_LAST_NAME,
    USER_EMAIL,
    USER_ACTIVE_YN,
    USER_PROFILE_BLOB,
    USER_PROFILE_MIME
INTO
    l_user_id,
    l_user_name,
    l_email,
    l_active_yn,
    l_blob,
    l_mime
FROM USERS
WHERE lower(USER_EMAIL) = lower(:APP_USER);

Notice that the comparison uses LOWER(USER_EMAIL) = LOWER(:APP_USER). Using LOWER() ensures the comparison remains case-insensitive, preventing login issues caused by uppercase or lowercase characters in email addresses.

Step 02
2
Convert the Profile Image
APEX_WEB_SERVICE.BLOB2CLOBBASE64

The profile picture is stored inside the database as a BLOB. Browsers cannot display BLOB data directly, so Oracle APEX provides a built-in function that converts binary data into Base64.

sql — PL/SQL 
l_base64     := APEX_WEB_SERVICE.BLOB2CLOBBASE64(p_blob => l_blob);
l_avatar_src := 'data:' || NVL(l_mime, 'image/png') || ';base64,' || l_base64;

The function reads the BLOB and converts it into a Base64 string that can be used directly inside an HTML image tag. This removes the need for external image files or download procedures — the image is rendered immediately by the browser while remaining completely managed inside PL/SQL.

Step 03
3
Generate a Default Avatar
Inline SVG fallback

Not every user uploads a profile picture, and displaying a broken image or an empty placeholder doesn't look professional. To solve this, the code checks whether a profile image exists. If an image is available, it's converted to Base64 and displayed. If not, a default SVG avatar is generated automatically.

sql — PL/SQL 
IF l_blob IS NOT NULL AND DBMS_LOB.GETLENGTH(l_blob) > 0 THEN
    l_base64     := APEX_WEB_SERVICE.BLOB2CLOBBASE64(p_blob => l_blob);
    l_avatar_src := 'data:' || NVL(l_mime, 'image/png') || ';base64,' || l_base64;
ELSE
    l_avatar_src := 'data:image/svg+xml;utf8,' ||
        '<svg xmlns="http://www.w3.org/2000/svg" width="36" height="36">' ||
        '<rect width="36" height="36" rx="18" fill="%23a855f7"/>' ||
        '<text x="18" y="23" font-size="14" fill="white" text-anchor="middle">' ||
        UPPER(SUBSTR(l_user_name,1,1)) || '</text></svg>';
END IF;

Instead of a generic icon, the avatar uses the first letter of the user's name — John → J, Michael → M, Sara → S. This small feature gives every user a personalized profile icon even without uploading a picture, and since SVG images are lightweight, they also help keep the application fast.

Step 04
4
Display the User Status
Active / inactive indicator

The application checks whether the user account is active. If active, a green status indicator is displayed; otherwise a gray indicator is shown. This colored circle appears on the bottom-right corner of the profile picture — a small UI element that gives users instant visual feedback.

sql — PL/SQL 
l_status_col := CASE WHEN l_active_yn = 'YES' THEN '#22c55e' ELSE '#9ca3af' END;
Step 05
5
Create a Custom Logout Button
APEX_PAGE.GET_URL

Oracle APEX already provides a logout mechanism, but here we create our own custom logout button. The logout URL is generated using the built-in Oracle APEX function APEX_PAGE.GET_URL, keeping the implementation fully compatible with Oracle APEX authentication.

sql — PL/SQL 
l_logout_url := APEX_PAGE.GET_URL(
                    p_page         => 9999,
                    p_clear_cache  => '9999',
                    p_items        => 'P9999_LOGOUT_YN',
                    p_values       => 'YES'
                 );

Instead of a simple text link, the logout action is presented as a circular button with an SVG logout icon, styled to match the application's overall design. Users can log out with a single click, without opening additional menus.

Step 06
6
Build the HTML Layout
Flexbox · Base64 image · Logout SVG

After all required information has been collected, the final step is generating the HTML that will be displayed inside Oracle APEX. Everything is combined into one reusable component, built with Flexbox so it stays aligned regardless of screen size.

The profile image is rendered as a circle using border-radius:50% with a purple border matching the app's color scheme. The status indicator overlaps the picture using absolute positioning — just like many modern messaging apps. To keep long names or emails from breaking the layout, the component uses overflow:hidden, white-space:nowrap and text-overflow:ellipsis. The logout button sits on the right, using a modern SVG icon with a subtle hover effect.

sql — PL/SQL 
l_html :=
    '<div style="display:flex;align-items:center;gap:8px;padding:4px 6px;max-width:250px;">' ||
        '<div style="position:relative;flex-shrink:0;">' ||
            '<img src="' || l_avatar_src || '"' ||
                 ' alt="' || APEX_ESCAPE.HTML(l_user_name) || '"' ||
                 ' style="width:36px;height:36px;border-radius:50%;object-fit:cover;' ||
                        'border:2px solid #a855f7;" />' ||
            '<span style="position:absolute;bottom:-1px;right:-1px;width:10px;height:10px;' ||
                        'border-radius:50%;background:' || l_status_col || ';' ||
                        'border:2px solid #fff;box-shadow:0 0 4px ' || l_status_col || ';"></span>' ||
        '</div>' ||
        '<div style="display:flex;flex-direction:column;line-height:1.2;overflow:hidden;">' ||
            '<span style="font-size:13px;font-weight:600;color:#111827;' ||
                        'white-space:nowrap;overflow:hidden;text-overflow:ellipsis;">' ||
                APEX_ESCAPE.HTML(l_user_name) ||
            '</span>' ||
            '<span style="font-size:11px;color:#6b7280;' ||
                        'white-space:nowrap;overflow:hidden;text-overflow:ellipsis;">' ||
                APEX_ESCAPE.HTML(l_email) ||
            '</span>' ||
        '</div>' ||
        '<a href="' || l_logout_url || '" title="Logout"' ||
           ' style="flex-shrink:0;display:flex;align-items:center;justify-content:center;' ||
                  'width:32px;height:32px;border-radius:50%;background:#FAECE7;color:#993C1D;' ||
                  'text-decoration:none;margin-left:4px;transition:filter 0.15s;"' ||
           ' onmouseover="this.style.filter=''brightness(0.95)''"' ||
           ' onmouseout="this.style.filter=''none''">' ||
            '<svg xmlns="http://www.w3.org/2000/svg" width="16" height="16" viewBox="0 0 24 24" ' ||
                 'fill="none" stroke="currentColor" stroke-width="2" stroke-linecap="round" stroke-linejoin="round">' ||
                '<path d="M9 21H5a2 2 0 0 1-2-2V5a2 2 0 0 1 2-2h4"></path>' ||
                '<polyline points="16 17 21 12 16 7"></polyline>' ||
                '<line x1="21" y1="12" x2="9" y2="12"></line>' ||
            '</svg>' ||
        '</a>' ||
    '</div>';

RETURN l_html;
✅
Result: A compact, professional profile widget — avatar, name, email, live status dot, and logout button — generated entirely from PL/SQL.

Where Should You Store This PL/SQL Code?

One of the questions I often get is where this code should be placed. There are two recommended approaches depending on your project structure.

Option 1 — Oracle Package

If you plan to reuse this component across multiple applications or modules, storing the PL/SQL inside an Oracle Package is the best option. It keeps your code organized, reusable, and much easier to maintain — whenever you need to make changes, you only have to update the package instead of modifying multiple pages. This approach is especially useful for large enterprise applications with several developers on the same project.

Option 2 — Oracle APEX Page 0

If the User Profile component should be visible on every page, placing the code on Page 0 is the easiest solution. Since Page 0 is shared across the entire application, every page automatically displays the profile component without duplicating any code. This is the approach used in this implementation and works well for most Oracle APEX projects.

Performance Tips

Although the component is relatively small, a few best practices will help keep your application fast and responsive.

Keep Profile Images Small

The profile image is displayed every time a page is loaded, so it's recommended to keep the image size around 10 KB. Smaller images reduce page load times and improve the overall user experience.

Store Only Required Data

Retrieve only the columns required for displaying the profile component. Avoid selecting unnecessary data from the database, as this can increase query execution time.

Use Base64 Only When Needed

The profile image is converted to Base64 only when a valid BLOB exists. If no image is available, the application automatically generates an SVG avatar instead of attempting an unnecessary conversion.

Escape Dynamic Values

Whenever user information is displayed in HTML, always escape dynamic values. This implementation uses APEX_ESCAPE.HTML(...) to safely display the user's name and email address, which helps prevent HTML injection and keeps the generated markup safe.

Complete PL/SQL Source Code

Below is the complete PL/SQL source code used in this implementation. Copy it into your Oracle Package, or into a PL/SQL Dynamic Content region on Page 0, depending on how you want to organize your application.

sql — Complete PL/SQL Source 
DECLARE
    l_user_name   VARCHAR2(511);
    l_email       VARCHAR2(320);
    l_active_yn   VARCHAR2(4);
    l_user_id     NUMBER;
    l_blob        BLOB;
    l_mime        VARCHAR2(255);
    l_base64      CLOB;
    l_avatar_src  CLOB;
    l_status_col  VARCHAR2(20);
    l_logout_url  VARCHAR2(4000);
    l_html        CLOB;
BEGIN
    SELECT
        USER_ID,
        USER_FIRST_NAME || ' ' || USER_LAST_NAME,
        USER_EMAIL,
        USER_ACTIVE_YN,
        USER_PROFILE_BLOB,
        USER_PROFILE_MIME
    INTO
        l_user_id,
        l_user_name,
        l_email,
        l_active_yn,
        l_blob,
        l_mime
    FROM USERS
    WHERE lower(USER_EMAIL) = lower(:APP_USER);

    IF l_blob IS NOT NULL AND DBMS_LOB.GETLENGTH(l_blob) > 0 THEN
        l_base64     := APEX_WEB_SERVICE.BLOB2CLOBBASE64(p_blob => l_blob);
        l_avatar_src := 'data:' || NVL(l_mime, 'image/png') || ';base64,' || l_base64;
    ELSE
        l_avatar_src := 'data:image/svg+xml;utf8,' ||
            '<svg xmlns="http://www.w3.org/2000/svg" width="36" height="36">' ||
            '<rect width="36" height="36" rx="18" fill="%23a855f7"/>' ||
            '<text x="18" y="23" font-size="14" fill="white" text-anchor="middle">' ||
            UPPER(SUBSTR(l_user_name,1,1)) || '</text></svg>';
    END IF;

    l_status_col := CASE WHEN l_active_yn = 'YES' THEN '#22c55e' ELSE '#9ca3af' END;

    l_logout_url := APEX_PAGE.GET_URL(
                        p_page         => 9999,
                        p_clear_cache  => '9999',
                        p_items        => 'P9999_LOGOUT_YN',
                        p_values       => 'YES'
                     );

    l_html :=
        '<div style="display:flex;align-items:center;gap:8px;padding:4px 6px;max-width:250px;">' ||
            '<div style="position:relative;flex-shrink:0;">' ||
                '<img src="' || l_avatar_src || '"' ||
                     ' alt="' || APEX_ESCAPE.HTML(l_user_name) || '"' ||
                     ' style="width:36px;height:36px;border-radius:50%;object-fit:cover;' ||
                            'border:2px solid #a855f7;" />' ||
                '<span style="position:absolute;bottom:-1px;right:-1px;width:10px;height:10px;' ||
                            'border-radius:50%;background:' || l_status_col || ';' ||
                            'border:2px solid #fff;box-shadow:0 0 4px ' || l_status_col || ';"></span>' ||
            '</div>' ||
            '<div style="display:flex;flex-direction:column;line-height:1.2;overflow:hidden;">' ||
                '<span style="font-size:13px;font-weight:600;color:#111827;' ||
                            'white-space:nowrap;overflow:hidden;text-overflow:ellipsis;">' ||
                    APEX_ESCAPE.HTML(l_user_name) ||
                '</span>' ||
                '<span style="font-size:11px;color:#6b7280;' ||
                            'white-space:nowrap;overflow:hidden;text-overflow:ellipsis;">' ||
                    APEX_ESCAPE.HTML(l_email) ||
                '</span>' ||
            '</div>' ||
            '<a href="' || l_logout_url || '" title="Logout"' ||
               ' style="flex-shrink:0;display:flex;align-items:center;justify-content:center;' ||
                      'width:32px;height:32px;border-radius:50%;background:#FAECE7;color:#993C1D;' ||
                      'text-decoration:none;margin-left:4px;transition:filter 0.15s;"' ||
               ' onmouseover="this.style.filter=''brightness(0.95)''"' ||
               ' onmouseout="this.style.filter=''none''">' ||
                '<svg xmlns="http://www.w3.org/2000/svg" width="16" height="16" viewBox="0 0 24 24" ' ||
                     'fill="none" stroke="currentColor" stroke-width="2" stroke-linecap="round" stroke-linejoin="round">' ||
                    '<path d="M9 21H5a2 2 0 0 1-2-2V5a2 2 0 0 1 2-2h4"></path>' ||
                    '<polyline points="16 17 21 12 16 7"></polyline>' ||
                    '<line x1="21" y1="12" x2="9" y2="12"></line>' ||
                '</svg>' ||
            '</a>' ||
        '</div>';

    RETURN l_html;
END;

Final Result

After completing all the steps, your Oracle APEX application will display a fully dynamic and reusable User Profile component that automatically shows the profile image, full name, email address, active status indicator, and custom logout button — all based on the currently authenticated user.

Because the implementation relies entirely on Oracle APEX and PL/SQL, there is no need for external libraries or additional JavaScript frameworks. The result is a clean, lightweight, and maintainable solution that enhances both the application's appearance and the overall user experience.

Best Practices

Store the profile component in a reusable location such as a Package or Page 0
Keep profile images lightweight
Always provide a fallback avatar for users without a profile picture
Use Oracle APEX built-in functions whenever possible instead of custom workarounds
Keep the HTML structure clean and easy to maintain
Escape all dynamic values before rendering them
Test the component with different user names and email lengths to ensure the layout remains responsive

Conclusion

Building a custom User Profile component in Oracle APEX is a practical way to improve the look and usability of your application while keeping the implementation simple. In this tutorial, we used PL/SQL to retrieve the logged-in user's information, convert profile images into Base64 format, generate a default SVG avatar when no image is available, display an active status indicator, create a custom logout button, and render everything inside a reusable HTML component.

By placing the code inside an Oracle Package or on Page 0, the component can be reused throughout the application with minimal maintenance. I hope this tutorial helps you build a cleaner and more professional Oracle APEX application. Feel free to customize the layout, colors, or additional profile information to match your own project's requirements.

Happy coding! 🚀

SH
Sajjad Hanifa
Software Developer · S&H Software Solutions · Oracle APEX, n8n, Discord Automation
Oracle APEX PL/SQL User Profile APEX_ESCAPE Base64 UI/UX

 {fullWidth}

Tags

You may like these posts

Please Select Embedded Mode To Show The Comment System.*

Previous Post Next Post

نموذج الاتصال