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