Oracle APEX (Rest Integration Code Snippets)

Source: Dev.to

(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

---

(C) 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

A. Document Processing Use Cases

Automated PDF Extraction & Classification

Perfect for: HR onboarding, invoices, receipts, forms
OCI Services: Object Storage, AI Document Understanding, Functions
APEX Role: Upload UI + results viewer

Flow

1. User uploads document in APEX.
2. Document is stored in OCI Object Storage.
3. AI Document Understanding extracts entities.
4. APEX displays structured results (date, amount, vendor, etc.).

Image Recognition Inside APEX

Perfect for: Field inspections, asset management
OCI Services: Vision AI

Flow

1. Upload photo → send to OCI Vision.
2. Vision returns labels, anomalies.
3. APEX triggers workflow or flags issues.

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

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

1. APEX sends user message to OCI Generative AI.
2. Receive structured response.
3. Store chat history.
4. Display in a chatbot‑style UI.

Smart Ticket Categorization

Perfect for: IT service desk, municipal complaints
OCI Services: AI Language (sentiment, classification)

Flow

1. APEX form → AI Language → auto‑assign priority/team.

C. Dashboards and Analytics

Real‑Time Operational Dashboard

Perfect for: Logistics, finance, retail
OCI Services: Streaming (Kafka‑like), API Gateway

Flow

1. Data streams into the DB.
2. APEX refreshes dashboard with live polling.

Executive KPI Dashboards

Perfect for: Management reporting
OCI Services: Oracle Analytics Cloud (optional), ADW

Flow

1. APEX consumes curated ADW models.
2. Interactive charts, drill‑downs.

Audit & Security Dashboard

Perfect for: Compliance, security teams
OCI Services: Data Safe, Logging, Monitoring

Flow

1. APEX queries Data Safe logs.
2. Show risky users, anomalies, audit alerts.