Complete Guide to APEX_HTTP Package in Oracle APEX

When developing enterprise systems based on Oracle APEX, you may often need to communicate with external services (such as REST APIs, SOAP services, or webhooks). In such cases, Oracle APEX provides the APEX_HTTP package — a powerful and flexible API that enables you to send HTTP requests directly from within the Oracle database.

This article is a comprehensive, step-by-step introduction to the APEX_HTTP package, its functions and procedures, along with real-world examples to help you understand and use it effectively.

---

What is APEX_HTTP?

APEX_HTTP is one of Oracle APEX's internal APIs that enables sending and receiving HTTP requests from PL/SQL. It allows you to interact with any external system that supports the HTTP/HTTPS protocol — directly from your APEX app or Oracle database logic.

Common Use Cases:

• Sending data to a web service (e.g., creating a new order, user authentication, sending SMS)

• Retrieving data from external APIs (e.g., exchange rates, weather information, user profiles)

• Connecting to APIs that require authentication or API tokens

In simple terms, APEX_HTTP is the bridge that lets your database "talk" to the outside world.

---

Key Functions and Procedures of APEX_HTTP

Let’s explore the most important functions and procedures of the APEX_HTTP package, with explanations and code examples for better clarity.

---

1. request(p_url, p_http_method, ...)

This is the main function to send an HTTP request to a specified URL.

Key Parameters:

---

Example: Simple GET Request

DECLARE
   l_response CLOB;
BEGIN
   l_response := apex_http.request(
      p_url         => 'https://api.apilayer.com/exchangerates_data/latest?base=USD',
      p_http_method => 'GET'
   );

   dbms_output.put_line('Response: ' || l_response);
END;

---

2. add_header(p_name, p_value)

Adds custom HTTP headers to the request. This should be called before invoking the request function.

Example:

BEGIN
   apex_http.add_header('Content-Type', 'application/json');
   apex_http.add_header('apikey', 'your-api-key');
END;

---

3. get_response

Returns the HTTP response status (e.g., 200 OK, 404 Not Found).

Example:

DECLARE
   l_status VARCHAR2(100);
BEGIN
   apex_http.request('https://example.com');
   l_status := apex_http.get_response;
   dbms_output.put_line('Status: ' || l_status);
END;

---

4. get_header(p_name)

Reads a specific header from the response, such as Content-Type or Location.

Example:

DECLARE
   l_content_type VARCHAR2(100);
BEGIN
   apex_http.request('https://example.com');
   l_content_type := apex_http.get_header('Content-Type');
   dbms_output.put_line('Content-Type: ' || l_content_type);
END;

---

5. get_cookies

Retrieves a list of cookies returned by the server — useful for session-based authentication scenarios.

Example:

DECLARE
   l_cookie apex_t_varchar2;
BEGIN
   apex_http.request('https://example.com');
   l_cookie := apex_http.get_cookies;

   FOR i IN 1 .. l_cookie.COUNT LOOP
      dbms_output.put_line('Cookie: ' || l_cookie(i));
   END LOOP;
END;

---

6. reset

Resets the internal state of the APEX_HTTP package. This is essential before sending a new request to avoid conflicts with previous headers or session settings.

---

Full Example: Sending a POST Request with JSON Payload

Suppose you want to send user information (name and email) using a POST request with JSON content:

DECLARE
   l_response  CLOB;
   l_json_body CLOB := '{"name":"Mahdi","email":"mahdi@example.com"}';
BEGIN
   -- Reset the package state
   apex_http.reset;

   -- Add required headers
   apex_http.add_header('Content-Type', 'application/json');
   apex_http.add_header('Authorization', 'Bearer your-token-here');

   -- Send the request
   l_response := apex_http.request(
      p_url         => 'https://api.example.com/users',
      p_http_method => 'POST',
      p_body        => l_json_body
   );

   -- Output the response
   dbms_output.put_line('Response: ' || l_response);
END;

---

Testing with Public Services like httpbin.org

For initial testing and debugging, use freely available services like https://httpbin.org:

DECLARE
   l_response CLOB;
BEGIN
   apex_http.reset;
   apex_http.add_header('Custom-Header', 'TestValue');

   l_response := apex_http.request(
      p_url         => 'https://httpbin.org/get',
      p_http_method => 'GET'
   );

   dbms_output.put_line(l_response);
END;

---

Best Practices and Pro Tips

• If you're connecting to an HTTPS URL, Oracle requires an Oracle Wallet to handle SSL/TLS. Always set the p_wallet_path accordingly.

• Don’t forget proper error handling (EXCEPTION) in real-world implementations.

• To parse JSON responses, use Oracle’s built-in json_value or json_table functions.

• Use tools like Postman to test your target API before implementing it in PL/SQL.

• While APEX_WEB_SERVICE provides higher-level REST/SOAP utilities, APEX_HTTP gives you fine-grained control.

---

Summary Table

---

Final Thoughts

If you’re developing Oracle APEX applications that require external integrations (e.g., payment gateways, government APIs, SMS services), mastering APEX_HTTP is a vital skill.

---

Author: Mahdi Ahmadi
Entrepreneur & Enterprise Solutions Architect using Oracle APEX