Building Smart Barcode and Secure QR Code Systems in Oracle APEX with APEX_BARCODE

 

🔹 Introduction

Oracle APEX provides a powerful built-in package called APEX_BARCODE that enables developers to generate QR codes and barcodes dynamically using SQL and PL/SQL.
In this blog, we will implement two real-world use cases step by step:

  • Displaying order barcodes with status indicators
  • Generating secure QR codes for controlled report access
What is APEX_BARCODE? 

APEX_BARCODE is a standard Oracle APEX package that generates barcode and QR code images dynamically. These images can be rendered in Classic Reports, Interactive Reports, Interactive Grids, Cards Regions, PDFs, and Email templates. 

Supported barcode types: 
  • QR Code — get_qrcode_png / get_qrcode_svg 
  • Code 128 — get_code128_png 
  • EAN-8 — get_ean8_png 
Format   Return Type    Best Used For 
PNG        BLOB               PDFs, Printing, Reports 
SVG        CLOB               Dashboards, Responsive UI 

✅ Use Case 1 - Order Barcodes with Status Badges

🔸 Step 1: Create Orders Table

Navigate to:
SQL Workshop → SQL Commands

Run:

CREATE TABLE orders (

    order_id    NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    order_no    VARCHAR2(50) NOT NULL,

    order_date  DATE DEFAULT SYSDATE,

    status      VARCHAR2(20) DEFAULT 'PENDING'

                CHECK (status IN ('PENDING','SHIPPED','DELIVERED','CANCELLED')),

    customer    VARCHAR2(100)

);

🔸 Step 2: Insert Sample Data

INSERT INTO orders (order_no, order_date, status, customer)

VALUES ('ORD-2024-001', SYSDATE,     'PENDING',   'Ravi Kumar');

INSERT INTO orders (order_no, order_date, status, customer)

VALUES ('ORD-2024-002', SYSDATE - 2, 'SHIPPED',   'Priya Sharma');

INSERT INTO orders (order_no, order_date, status, customer)

VALUES ('ORD-2024-003', SYSDATE - 5, 'DELIVERED', 'Amit Verma');

INSERT INTO orders (order_no, order_date, status, customer)

VALUES ('ORD-2024-004', SYSDATE - 1, 'CANCELLED', 'Sneha Iyer');

COMMIT;

🔸 Step 3: Create APEX Page

  • Go to App Builder
  • Create → Blank Page
  • Page Name: Order Barcodes

🔸 Step 4: Add Classic Report

  • Create Region → Classic Report
  • Paste SQL:
SELECT
    order_no,
    order_date,
    customer,
    '<img src="data:image/png;base64,' ||
    apex_web_service.blob2clobbase64(
        apex_barcode.get_code128_png(
            p_value => order_no, p_scale => 1,
            p_foreground_color => '#000000',
            p_background_color => '#ffffff'
        )
    ) || '" width="150"/>' AS order_barcode,
    '<img src="data:image/png;base64,' ||
    apex_web_service.blob2clobbase64(
        apex_barcode.get_qrcode_png(
            p_value => order_no, p_scale => 4,
            p_quiet => 3,
            p_foreground_color => '#000000',
            p_background_color => '#ffffff'
        )
    ) || '" width="80"/>' AS order_qrcode
FROM orders

🔸 Step 5: Add Status Badge UI

Use CASE statement to display colored badges:

CASE status
        WHEN 'PENDING'   THEN '<span style="background:#f0ad4e;color:#fff;padding:3px 8px;border-radius:4px">PENDING</span>'
        WHEN 'SHIPPED'   THEN '<span style="background:#5bc0de;color:#fff;padding:3px 8px;border-radius:4px">SHIPPED</span>'
        WHEN 'DELIVERED' THEN '<span style="background:#5cb85c;color:#fff;padding:3px 8px;border-radius:4px">DELIVERED</span>'
        WHEN 'CANCELLED' THEN '<span style="background:#d9534f;color:#fff;padding:3px 8px;border-radius:4px">CANCELLED</span>'
    END AS status_badge,

🔸 Step 6: Configure Columns (IMPORTANT)

Set:

  • Escape Special Characters = No
    (for barcode & QRcode+ badge columns)

🔸 Step 7: Run Application

Now your report will show:

  • 📦 Order details
  • 🏷️ Status badge
  • 📊 Scannable barcode and QRcode

✅ Use Case 2 - Secure QR Code Token Access

🔸 Step 1: Create Token Table

CREATE TABLE report_access_tokens (
    token_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    token_value VARCHAR2(100),
    expiry_date DATE,
    used_flag VARCHAR2(1) DEFAULT 'N'
);

🔸 Step 2: Insert Sample Tokens

INSERT INTO report_access_tokens (token_value, expiry_date)
VALUES ('TKN-001-' || TO_CHAR(SYSDATE,'YYYYMMDD'), SYSDATE + 1);

INSERT INTO report_access_tokens (token_value, expiry_date)
VALUES ('TKN-002-' || TO_CHAR(SYSDATE,'YYYYMMDD'), SYSDATE + 2);

INSERT INTO report_access_tokens (token_value, expiry_date)
VALUES ('TKN-003-' || TO_CHAR(SYSDATE,'YYYYMMDD'), SYSDATE + 3);

COMMIT;

🔸 Step 3: Create Target Page (Secure Page)

  • Page Name: Secure Report Access
  • Add Hidden Item:
    • P10_TOKEN

🔸 Step 4: Add Token Validation Logic

Create Before Header Process

DECLARE
  l_valid NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO l_valid
  FROM report_access_tokens
  WHERE token_value = :P10_TOKEN
  AND expiry_date > SYSDATE
  AND used_flag = 'N';

  IF l_valid = 0 THEN
     RAISE_APPLICATION_ERROR(-20001,'Invalid or Expired Token');
  END IF;
END;

🔸 Step 5: Mark Token as Used

Create After Header Process

UPDATE report_access_tokens

SET used_flag = 'Y'

WHERE token_value = :P10_TOKEN;

COMMIT;

🔸 Step 6: Create QR Generator Page

  • Create new page → QR Code Generator
  • Add Classic Report
  • Paste below Generate QR code 

🔸 Step 7: Generate QR Code

SELECT
    token_id,
    token_value,
    TO_CHAR(expiry_date, 'DD-MON-YYYY HH24:MI') AS expiry_date,

    -- Status Badge
    CASE used_flag
        WHEN 'N' THEN '<span style="background:#5cb85c;color:#fff;
                       padding:4px 10px;border-radius:4px;
                       font-weight:bold">ACTIVE</span>'
        WHEN 'Y' THEN '<span style="background:#d9534f;color:#fff;
                       padding:4px 10px;border-radius:4px;
                       font-weight:bold">USED</span>'
    END AS status_badge,

    -- QR Code
    '<img src="data:image/png;base64,' ||
    apex_web_service.blob2clobbase64(
        apex_barcode.get_qrcode_png(
            p_value =>
                apex_util.host_url('SCRIPT') ||
                'f?p=' || :APP_ID ||
                ':10:::NO::P10_TOKEN:' || token_value,
            p_scale            => 5,
            p_quiet            => 4,
            p_foreground_color => '#000000',
            p_background_color => '#ffffff'
        )
    ) || '" width="120"/>' AS qr_code,

    -- Barcode
    '<img src="data:image/png;base64,' ||
    apex_web_service.blob2clobbase64(
        apex_barcode.get_code128_png(
            p_value            => token_value,
            p_scale            => 1,
            p_foreground_color => '#000000',
            p_background_color => '#ffffff'
        )
    ) || '" width="180"/>' AS barcode

FROM report_access_tokens
ORDER BY created_on DESC

🔸 Step 8: Configure Columns

Set:

  • QR_CODE, BARCODE and STATUS_BADGE → Escape Special Characters = No

🔸 Step 9: Run & Test Flow

Flow:

  1. User scans QR
  2. Redirects to secure page
  3. Token validated
  4. Access granted
  5. Token marked as used

🎯 Conclusion

Using APEX_BARCODE, you can easily build:

  • Smart inventory systems
  • Secure access workflows
  • Scannable business applications

without any external plugins.


Comments

Post a Comment

Popular posts from this blog

Building Secure RESTful Services in Oracle APEX 24.2 Using ORDS, OAuth2 Client Credentials, and PL/SQL

Image
📘 Introduction Oracle APEX 24.2 provides powerful capabilities to expose database data as secure RESTful APIs using Oracle REST Data Services (ORDS). In modern enterprise architectures, APIs are essential for enabling system-to-system communication. Instead of direct database access, RESTful services allow controlled and secure data sharing. In this blog, we will walk through how to create a REST API in Oracle APEX 24.2 and secure it using privileges and OAuth authentication. 🎯 Objective The goal of this implementation is to: • Expose database data through REST APIs • Create structured API endpoints • Secure APIs using OAuth • Enable controlled external access 🧱 Prerequisites Before starting, ensure: • Oracle APEX 24.2 is installed • ORDS 20.x or later is configured • RESTful Services are enabled • A working table exists (Example: HR.EMPLOYEES) 🌐 REST API URL Structure The generated API follows this format: https://host:port/ords/schema/module/template ⚙️ Implementat...
Read more

Building a Portfolio Generator in Oracle APEX: A Step-by-Step Guide to Generate Downloadable Portfolio Documents

Image
  Introduction This tutorial demonstrates how to build a Portfolio Generator application in Oracle APEX where users can enter resume details and generate a downloadable portfolio HTML/ PDF with one click . The application collects user details, work experience, skills, and education, then dynamically generates a styled HTML portfolio that can be printed as PDF . Step- by- Step  Implementation  Guide Step 1 -  Create Database Tables First create the tables required to store resume information. We will create 5 tables . 1️⃣ Resume Master Table Stores the main profile information.   CREATE TABLE "RESUME_MASTER"     ( "RESUME_ID" NUMBER GENERATED ALWAYS AS IDENTITY, "FULL_NAME" VARCHAR2(200),  "EMAIL" VARCHAR2(200),  "PHONE" VARCHAR2(50),  "ADDRESS" VARCHAR2(500),  "DOB" DATE,  "OBJECTIVE" CLOB,  "CURRENT_COMPANY" VARCHAR2(200),  "CURRENT_ROLE" VARCHAR2(200),  "C...
Read more

Implementing WhatsApp OTP Verification in Oracle APEX Using UltraMsg API

Image
  🚀 Introduction In today’s digital applications, user verification plays a crucial role in ensuring security and trust. Traditionally, OTPs are sent via SMS — but with the widespread use of WhatsApp, sending OTPs through WhatsApp offers: Faster delivery Better user reach Improved engagement In this blog, we explore how to implement OTP verification in Oracle APEX by integrating WhatsApp messaging using an external API service. 🎯 Objective The goal of this implementation was to: ✔️ Generate OTP dynamically ✔️ Send OTP via WhatsApp ✔️ Integrate external messaging API ✔️ Enhance login/verification security ✔️ Enable real-time communication 🧩 Solution Overview This solution integrates: PL/SQL OTP generation REST API call from Oracle APEX WhatsApp messaging service This allows Oracle APEX applications to send OTP directly to users via WhatsApp instead of SMS.  🧱 Step-by-Step Approach  Step 1: Configure WhatsApp API To send OTP via WhatsApp, ...
Read more