Working with REST APIs Using PL/SQL

-

 Introduction

Modern enterprise systems rarely operate in isolation. Databases are increasingly required to integrate with external services such as payment gateways, messaging platforms, cloud services, and microservices. One of the most common integration methods is through REST APIs.

Oracle Database provides native support for RESTful communication using PL/SQL through packages such as UTL_HTTP and APEX_WEB_SERVICE.

This allows developers to call REST endpoints directly from PL/SQL, eliminating the need for external middleware in many scenarios.

This article explores how to consume and integrate REST APIs using PL/SQL, including authentication, request handling, JSON processing, error handling, and best practices.

Why Use REST APIs in PL/SQL?

Integrating REST APIs directly into PL/SQL enables:

  • Real-time external system communication

  • Data enrichment inside the database

  • Automation of business workflows

  • Lightweight integration without middleware

  • Event-driven database applications

Common use cases include:

  • Payment processing

  • SMS/email notifications

  • Address validation services

  • Currency conversion APIs

  • Cloud service integration

Core Packages for REST Integration

1. UTL_HTTP

UTL_HTTP is the primary low-level package used for HTTP calls.

Capabilities:

  • GET, POST, PUT, DELETE requests

  • Header manipulation

  • SSL/TLS communication

2. APEX_WEB_SERVICE

APEX_WEB_SERVICE provides a higher-level API:

  • Simplified REST calls

  • Built-in JSON handling

  • Easier authentication handling

Making a Simple REST GET Call

Example using UTL_HTTP

DECLARE
   l_request  UTL_HTTP.req;
   l_response UTL_HTTP.resp;
   l_buffer   VARCHAR2(32767);
BEGIN
   l_request := UTL_HTTP.begin_request('https://api.example.com/employees');

   l_response := UTL_HTTP.get_response(l_request);

   LOOP
      UTL_HTTP.read_line(l_response, l_buffer, TRUE);
      DBMS_OUTPUT.put_line(l_buffer);
   END LOOP;

   UTL_HTTP.end_response(l_response);
EXCEPTION
   WHEN UTL_HTTP.end_of_body THEN
      UTL_HTTP.end_response(l_response);
END;
/

Making a POST Request with JSON

DECLARE
   l_req   UTL_HTTP.req;
   l_resp  UTL_HTTP.resp;
   l_body  CLOB := '{"name":"John","salary":5000}';
BEGIN
   l_req := UTL_HTTP.begin_request(
      'https://api.example.com/employees',
      'POST',
      'HTTP/1.1'
   );

   UTL_HTTP.set_header(l_req, 'Content-Type', 'application/json');
   UTL_HTTP.set_header(l_req, 'Content-Length', LENGTH(l_body));

   UTL_HTTP.write_text(l_req, l_body);

   l_resp := UTL_HTTP.get_response(l_req);

   UTL_HTTP.end_response(l_resp);
END;
/

Working with JSON Responses

Oracle provides native JSON support through:

  • JSON_OBJECT_T

  • JSON_TABLE

  • JSON_VALUE

Example Parsing API Response

DECLARE
   l_json CLOB := '{"emp_id":101,"name":"Alice"}';
   l_obj  JSON_OBJECT_T;
BEGIN
   l_obj := JSON_OBJECT_T.parse(l_json);

   DBMS_OUTPUT.put_line(l_obj.get_string('name'));
END;
/

Using APEX_WEB_SERVICE (Simplified Approach)

DECLARE
   l_response CLOB;
BEGIN
   l_response := APEX_WEB_SERVICE.make_rest_request(
      p_url         => 'https://api.example.com/employees',
      p_http_method => 'GET'
   );

   DBMS_OUTPUT.put_line(l_response);
END;
/

Benefits:

  • Less boilerplate code

  • Easier authentication handling

  • Better JSON support

Handling Authentication

1. Basic Authentication

UTL_HTTP.set_authentication(
   l_req,
   'username',
   'password'
);

2. Bearer Token Authentication

UTL_HTTP.set_header(
   l_req,
   'Authorization',
   'Bearer ' || l_token
);

3. API Key Authentication

UTL_HTTP.set_header(
   l_req,
   'x-api-key',
   'your-api-key'
);

SSL and Wallet Configuration

For HTTPS endpoints, Oracle requires wallet configuration.

Example:

BEGIN
   UTL_HTTP.set_wallet('file:/u01/app/wallet', 'wallet_password');
END;
/

This enables secure TLS communication.

Error Handling in REST Calls

REST calls can fail due to:

  • Network issues

  • Authentication failure

  • Invalid response formats

  • Timeouts

Example Handling

BEGIN
   -- API call
EXCEPTION
   WHEN UTL_HTTP.request_failed THEN
      DBMS_OUTPUT.put_line('Request failed');
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line(SQLERRM);
END;
/

Timeouts and Performance Tuning

Set timeout values to avoid hanging sessions:

UTL_HTTP.set_transfer_timeout(30);

Best practices:

  • Always define timeouts

  • Avoid long-running synchronous calls

  • Use batch processing for bulk APIs

Real-World Integration Example

Scenario: Currency Conversion API

  1. PL/SQL calls external API

  2. Retrieves exchange rate

  3. Updates transaction table

DECLARE
   l_response CLOB;
BEGIN
   l_response := APEX_WEB_SERVICE.make_rest_request(
      p_url => 'https://api.exchangerate.host/latest?base=USD'
   );

   -- parse and update logic here
END;
/

Best Practices for REST in PL/SQL

1. Use APEX_WEB_SERVICE for Simplicity

Prefer high-level APIs unless fine control is required.

2. Always Handle Errors

Never assume API success.

3. Use JSON Instead of XML

JSON is lighter and better supported.

4. Avoid Blocking Calls in Critical Transactions

Do not depend on external APIs for core transaction commits.

5. Cache External Responses

Reduce unnecessary API calls.

6. Secure Credentials

Do not hardcode passwords or API keys.

Security Considerations

  • Use HTTPS for all external calls

  • Store credentials in secure wallets

  • Validate API responses before processing

  • Avoid exposing internal database structure in responses

  • Restrict UTL_HTTP execution privileges

Performance Considerations

REST calls introduce network latency.

Recommendations:

  • Use asynchronous processing (job queues)

  • Batch API requests where possible

  • Cache frequent responses

  • Avoid calling APIs inside row-level triggers

Common Pitfalls

1. Calling APIs inside loops

This causes performance bottlenecks.

2. Ignoring timeouts

Leads to session hangs.

3. Poor error handling

Makes debugging difficult.

4. Missing SSL configuration

Causes connection failures.

When to Use PL/SQL for REST Integration

Best suited for:

  • Lightweight integrations

  • Internal APIs

  • Event-driven database workflows

  • Data enrichment during processing

Not ideal for:

  • High-volume API orchestration

  • Complex microservice communication

  • Long-running workflows

Conclusion

Integrating REST APIs with PL/SQL enables Oracle databases to participate directly in modern distributed architectures. With packages like UTL_HTTP and APEX_WEB_SERVICE, developers can build powerful integrations without external middleware.

However, careful attention must be given to security, performance, error handling, and transaction design. When used appropriately, REST integration transforms the database into an active participant in enterprise ecosystems rather than a passive data store.

Comments

Post a Comment

Popular posts from this blog

Oracle APEX Development Tips I Wish I Knew Earlier

-
  When I started working with Oracle APEX, I assumed that building applications was mostly about creating pages, writing SQL queries, and adding a few processes. After working on enterprise applications involving approvals, reporting, integrations, document generation, and workflow automation, I realized that successful Oracle APEX development is less about knowing every feature and more about understanding how to design applications that remain maintainable over time. Looking back, there are several lessons I wish someone had shared with me earlier. These lessons would have saved me countless hours of debugging, redesigning, and maintaining applications. 1. Spend More Time Designing the Database When I first started developing applications, I focused heavily on the user interface. The reality is that a well-designed database solves many problems before they reach the application layer. Poor table structures eventually lead to: Complex SQL queries Slow reports Difficult maintenance...
Read more

The Evolution of Software Engineering: From Writing Code to Solving Business Problems

-
Software engineering is often misunderstood. When people hear the term "software engineer," they usually imagine someone sitting in front of multiple screens, typing code for hours. While coding is certainly a fundamental part of the profession, my experience has taught me that software engineering is far more complex and rewarding than simply writing lines of code. Over the years, the role of software engineers has evolved significantly. We are no longer just developers who implement requirements. We are problem solvers, architects, communicators, collaborators, and, increasingly, strategic contributors to business success. This evolution has transformed how software is built and how engineers create value within organizations. My Early Understanding of Software Engineering Like many aspiring developers, I initially believed that mastering programming languages would be the key to becoming a successful software engineer. I spent countless hours learning syntax, algorithms, d...
Read more

Advanced PL/SQL Performance Tuning: Processing Millions of Records Efficiently

-
 Introduction One of the most common challenges faced by Oracle developers is processing large volumes of data efficiently. Applications that perform well with thousands of records may struggle when dealing with millions of rows. Poorly optimized PL/SQL code can lead to excessive context switches, increased CPU utilization, and long execution times. In this article, I will discuss practical techniques for improving PL/SQL performance using BULK COLLECT, FORALL, proper indexing, and efficient exception handling. Understanding the Performance Problem Many developers use cursor loops similar to the following: FOR rec IN ( SELECT employee_id, salary FROM employees ) LOOP UPDATE employees SET salary = salary * 1.10 WHERE employee_id = rec.employee_id; END LOOP; Although this approach is easy to understand, it performs a SQL-to-PL/SQL context switch for every row processed. When processing millions of records, this becomes a major performance bottleneck. The Cost ...
Read more