Oracle APEX on ATP: Real-World Integration with OCI Services

-

 

🧭 Introduction

Oracle APEX on Autonomous Transaction Processing (ATP) is a powerful low-code platform that enables rapid application development, directly on Oracle Cloud. But its real strength is unlocked when integrated with other OCI services such as Object Storage, Functions, Monitoring, and Email Delivery.

In this blog, we’ll walk through real-world use cases and demonstrate how APEX applications can seamlessly connect, consume, and extend OCI services to build modern cloud-native solutions.


🧩 Why Integrate APEX with OCI Services?

FeatureBenefit
🧠 Native cloud access       Direct interaction with OCI APIs from APEX
⚙️ Serverless computeExtend APEX logic using Oracle Functions
📦 Object StorageStore & retrieve documents, images, reports
📈 Monitoring & AlertsApp-level observability & automation
✉️ Email DeliverySend user notifications or alerts

🔧 Prerequisites

  1. ATP instance with APEX enabled

  2. Access to OCI tenancy with appropriate permissions

  3. Authenticated access using OCI REST APIs

    • Auth via Resource Principal (preferred on ATP)

    • Or using API signing keys (if external access is needed)





🧪 Use Case 1: Upload & Access Files in OCI Object Storage

🔹 Step 1: Create a Bucket in OCI


oci os bucket create --name apex-bucket --compartment-id <compartment_ocid>

🔹 Step 2: Use PL/SQL to Upload Files

Create a PL/SQL Web Source Module in APEX using OCI Object Storage REST API:


declare

  l_blob BLOB := :P1_FILE;

  l_resp CLOB;

begin

  apex_web_service.g_request_headers(1).name := 'Authorization';

  apex_web_service.g_request_headers(1).value := 'Bearer ' || get_auth_token();


  l_resp := apex_web_service.make_rest_request(

    p_url => 'https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/apex-bucket/o/myfile.jpg',

    p_http_method => 'PUT',

    p_body_blob => l_blob,

    p_wallet_path => 'file:/wallet',

    p_content_type => 'image/jpeg'

  );


  :P1_RESPONSE := l_resp;

end;

📌 Use DBMS_CLOUD or DBMS_CREDENTIAL for secure credential handling.




🔃 Use Case 2: Triggering Oracle Functions from APEX

🔹 Step 1: Deploy a Function

Use Fn Project or OCI Console:


fn init --runtime python apex-notifier
fn deploy --app apex-utils

🔹 Step 2: Call Function via REST from APEX

Use APEX_WEB_SERVICE.MAKE_REST_REQUEST:

declare
  l_response clob;
begin
  l_response := apex_web_service.make_rest_request(
    p_url => 'https://<invoke-url>',
    p_http_method => 'POST',
    p_body => '{ "user_id": ' || :P1_USER_ID || ' }',
    p_content_type => 'application/json'
  );
  :P1_OUTPUT := l_response;
end;

✅ Example Use Cases:

  • Sending SMS/Email via function

  • Backend image processing

  • Serverless workflow triggers

📡 Use Case 3: Monitoring APEX App Events via OCI Monitoring

🔹 Step 1: Enable Custom Metrics in Your PL/SQL

Push custom metrics from APEX:

declare

  l_response clob;

begin

  l_response := apex_web_service.make_rest_request(

    p_url => 'https://telemetry-ingestion.<region>.oraclecloud.com/20180401/metrics',

    p_http_method => 'POST',

    p_body => '{

      "namespace": "apex_metrics",

      "compartmentId": "<compartment_ocid>",

      "name": "UserLogins",

      "dimensions": {

         "app": "HelpDesk"

      },

      "datapoints": [

         {

            "timestamp": "' || to_char(systimestamp, 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"') || '",

            "value": 1

         }

      ]

    }',

    p_content_type => 'application/json'

  );

end;

📊 Then build alarms in OCI Monitoring Dashboard for:

  • Login spikes

  • Failed transactions

  • Custom thresholds

📧 Use Case 4: Send Emails via OCI Email Delivery

🔹 Step 1: Set Up Email Delivery in OCI

  • Create approved sender email

  • Generate SMTP credentials

🔹 Step 2: Use APEX_MAIL with SMTP

begin
  apex_mail.send(
    p_to => 'user@example.com',
    p_from => 'no-reply@yourdomain.com',
    p_subj => 'Welcome to Our App',
    p_body => 'Hello, your account has been activated!'
  );
  apex_mail.push_queue;
end;
📌 Configure SMTP settings in APEX instance setup.

🔐 Authentication: Resource Principals (ATP-Only)

If using ATP, you can use resource principal authentication with DBMS_CLOUD.SEND_REQUEST.

BEGIN

  DBMS_CLOUD.SEND_REQUEST(

    credential_name => 'OCI_RESOURCE_PRINCIPAL',

    uri => 'https://objectstorage.<region>.oraclecloud.com/n/...'

  );

END;

✅ Recommended for secure, managed integration directly from ATP without API keys.


💡 Bonus: Use Oracle AI Services

Combine APEX forms with:

  • OCI Vision (image classification)

  • OCI Speech (transcription)

  • OCI Language (text sentiment, NER)

Invoke via REST API using pre-authenticated headers from within PL/SQL.

📌 Summary

OCI Service            APEX Use Case
Object Storage                          File upload/download
FunctionsBusiness logic extension
MonitoringCustom metrics & alerts
Email DeliveryNotifications & password resets
AI ServicesIntelligent app features

🧭 Final Thoughts

Oracle APEX + ATP + OCI = a low-code cloud-native development powerhouse. With just PL/SQL and a few clicks, you can tap into enterprise-grade services and build modern, secure, and intelligent apps without needing to manage infrastructure or deploy code.

📎 Resources







Comments

Post a Comment

Popular posts from this blog

Introduction to Oracle Vector Search – Concepts, Requirements & Use Cases

-
Image
  Title: Unlocking the Power of Oracle Vector Search: Concepts, Requirements, and Real-World Use Cases Introduction: In today’s AI-driven world, traditional keyword-based search techniques are no longer sufficient for applications like image recognition, recommendation engines, or natural language search. This is where vector search steps in — enabling machines to find semantically similar data even when exact keywords don't match. Oracle has embraced this next-gen capability with native vector search support in Oracle Database 23ai , making it possible to run efficient similarity searches — even on BLOB-based image embeddings . This blog kicks off our 5-part series where we explore Oracle Vector Search , especially focusing on handling images as BLOBs and converting them into meaningful vector embeddings for AI-powered search. What is Vector Search? Vector search, also known as nearest neighbor search , enables finding items based on similarity in a high-dimensional space....
Read more

Setting Up Monitoring and Alerts in OCI for Your Resources

-
Image
  Oracle Cloud Infrastructure (OCI) provides powerful Monitoring and Alarms features to help you gain visibility into the health and performance of your cloud resources. In this blog post, we’ll walk through the step-by-step process of setting up monitoring and alerts for CPU, Memory, and other important metrics using OCI’s native tools. ✅ Whether you’re managing Compute Instances, Autonomous Databases, Load Balancers, or other services, OCI Monitoring helps you stay ahead of performance issues by automatically collecting and analyzing metrics. 🎯 What We Will Cover Understanding OCI Monitoring and Metrics Enabling Monitoring for Resources Setting Up Alarms (CPU, Memory, etc.) Viewing Metrics in the OCI Console Notifications Using OCI Notifications Service Bonus: Monitoring Using CLI & SDK (Optional for automation)   🧠 1. What is OCI Monitoring? OCI Monitoring allows you to collect, view, and analyze metrics for your cloud resources in near real-...
Read more
-
Image
  🐚 How to Use Oracle Cloud Shell Like a Pro Introduction Oracle Cloud Shell is a browser-based terminal pre-authenticated with your Oracle Cloud account. It comes with a rich set of tools to help you manage and automate your OCI resources—no setup needed. In this blog, we’ll walk through everything from launching Cloud Shell to using advanced tools like oci-cli , Git, and Terraform—all directly from your browser. 🧰 What Is Oracle Cloud Shell? Web-based shell environment Persistent 5GB storage in $HOME/cloudshell Pre-installed tools : OCI CLI Git Terraform Kubectl Helm Python, Java, Node.js Text editors (nano, vi) Auto-authenticated : No need to configure your API keys. 🟢 How to Launch Oracle Cloud Shell Log in to Oracle Cloud Console . Click the Cloud Shell icon (top-right corner, looks like >_ ). The shell will open in the lower panel—ready to use. ⚙️ Exploring the Environment Code : # Check current user whoami # List home d...
Read more