Oracle APEX (Rest Integration Code Snippets)

(A) Calling a REST API From APEX Using APEX_WEB_SERVICE

Example: Calling OCI Functions or Any REST Endpoint

DECLARE

l_url VARCHAR2(2000) := 'https://api.example.com/process';

l_body CLOB := '{"id":123, "action":"submit"}';

l_result CLOB;

BEGIN

l_result := APEX_WEB_SERVICE.make_rest_request(

p_url => l_url,

p_http_method => 'POST',

p_body => l_body,

p_wallet_path => 'file:/u01/app/oracle/wallet',

p_wallet_pwd => 'password'

);

-- Log or parse the JSON response

dbms_output.put_line(l_result);

END;

When to use

✔ Calling serverless functions (OCI Functions)

 ✔ Integrating external APIs

 ✔ Sending data to microservices

(B) Uploading Files to OCI Object Storage

Useful for APEX apps that handle documents, images, invoices, or uploads.

DECLARE

l_url VARCHAR2(4000) := 'https://objectstorage..oraclecloud.com/n/namespace/b/bucket/o/myfile.pdf';

l_blob BLOB;

BEGIN

SELECT file_blob INTO l_blob

FROM my_docs

WHERE id = :P1_DOC_ID;

APEX_WEB_SERVICE.make_rest_request(

p_url => l_url,

p_http_method => 'PUT',

p_body_blob => l_blob,

p_wallet_path => 'file:/u01/app/oracle/wallet',

p_wallet_pwd => 'password'

);

END;

When to use

✔ Uploading resumes, invoices, PDFs

 ✔ Storing images, documents, reports

 ✔ Archiving files securely

Calling OCI AI Language or Vision Service

AI Language Example: Sentiment / Key-Phrases

DECLARE

l_body CLOB := '{

"documents": [

{"text": "The service was excellent!", "id": "1"}

]

}';

l_response CLOB;

BEGIN

l_response := apex_web_service.make_rest_request(

p_url => 'https://.../language/sentiment',

p_http_method => 'POST',

p_body => l_body,

p_wallet_path => 'file:/u01/app/oracle/wallet'

);

:P1_AI_RESULT := l_response;

END;

When to use

✔ Customer feedback analysis

 ✔ Support ticket prioritization

 ✔ Chatbot intelligence and routing

(D) Calling a REST-enabled Table or View Using ORDS

If you expose a table through ORDS, APEX can read it like any other REST endpoint.

SELECT *

FROM apex_web_service.g_response_clob;

When to use

✔ Microservice-style data sharing

 ✔ Integrating multiple APEX apps

 ✔ Exposing data to partners

Use-Case-Based Recommendations

Here are practical, real-world examples for the most common APEX + OCI integration scenarios.

A. Document Processing Use Cases

1. Automated PDF Extraction & Classification
   
   Perfec for: HR onboarding, invoices, receipts, forms
   
    OCI Services: Object Storage, AI Document Understanding, Functions
   
    APEX Role: Upload UI + results viewer
   
   Flow:
   
   User uploads document in APEX
   
   Stored in OCI Object Storage
   
   AI Document Understanding extracts entities
   
   APEX displays structured results (date, amount, vendor, etc.)




2. Image Recognition Inside APEX
   
   Perfect for: Field inspections, asset management
   
    OCI Services: Vision AI
   
   Flow:
   
   Upload photo → send to OCI Vision
   
   Vision returns labels, anomalies
   
   APEX triggers workflow or flags issues




3. Generate PDFs Automatically
   
   Perfect for: Reports, certificates, receipts
   
    OCI Services: OCI Functions + open-source libraries OR APEX built-in PDF printing
   
   B. Chatbots and Intelligent Assistants




4. LLM-Powered Chat Assistant Inside APEX
   
   Perfect for: Customer portals, HR helpdesks
   
    OCI Services: OCI Generative AI, OCI Language
   
    APEX Role: Chat UI + history log
   
   Flow:
   
   APEX sends user message to OCI Gen AI
   
   Receive structured response
   
   Store chat history
   
   Display in a chatbot-style UI




5. Smart Ticket Categorization
   
   Perfect for: IT service desk, municipal complaints
   
    OCI Services: AI Language (sentiment, classification)
   
    Flow:
   
    APEX form → AI Language → auto-assign priority/team
   
   C. Dashboards and Analytics




6. Real-Time Operational Dashboard
   
   Perfect for: Logistics, finance, retail
   
    OCI Services: Streaming (Kafka-like), API Gateway
   
    Flow:
   
    Data streams into the DB → APEX refreshes dashboard with Live Polling




7. Executive KPI Dashboards
   
   Perfect for: Management reporting
   
    OCI Services: Oracle Analytics Cloud (optional), ADW
   
    Flow:
   
    APEX consumes curated ADW models → interactive charts, drilldowns




8. Audit & Security Dashboard
   
   Perfect for: Compliance, security teams
   
    OCI Services: Data Safe, Logging, Monitoring
   
    Flow:
   
    APEX queries Data Safe logs → show risky users, anomalies, audit alerts