Rest Call With Authorization Domain In Pl Sql

We’ll cover how to make authenticated REST API calls directly from PL/SQL, using both UTL_HTTP and Oracle APEX’s apex_web_service. The focus is on the most common authorization schemes: Basic Authentication, Bearer tokens, API keys, and a high‑level view of OAuth2.0.

1. The Two Main Workhorses for HTTP in PL/SQL

Both can set the Authorization header – the key to authenticated REST calls.

Prerequisite: Create an ACL to allow outbound network access to the target domain.

sql
BEGIN
DBMS_NETWORK_ACL_ADMIN.append_host_ace(
host => ‘api.example.com’,
lower_port => 443,
upper_port => 443,
ace => xs$ace_type(privilege_list => xs$name_list(‘http’),
principal_name => ‘YOUR_SCHEMA’,
principal_type => xs_acl.ptype_db));
END;
/

Replace YOUR_SCHEMA with your actual Oracle schema name.

2. Basic Authentication (username:password)

Using UTL_HTTP

sql
DECLARE
req utl_http.req;
resp utl_http.resp;
url VARCHAR2(32767) := ‘https://api.example.com/v1/resource‘;
username VARCHAR2(100) := ‘myuser’;
password VARCHAR2(100) := ‘mypass’;
buffer VARCHAR2(32767);
BEGIN
— Base64-encode username:password
UTL_HTTP.set_header(req, ‘Authorization’,
‘Basic ‘ || UTL_RAW.cast_to_varchar2(
UTL_ENCODE.base64_encode(
UTL_RAW.cast_to_raw(username || ‘:’ || password))));

req := UTL_HTTP.begin_request(url, ‘GET’);
UTL_HTTP.set_header(req, ‘Content-Type’, ‘application/json’);

resp := UTL_HTTP.get_response(req);
BEGIN
LOOP
UTL_HTTP.read_text(resp, buffer);
DBMS_OUTPUT.put_line(buffer);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN NULL;
END;
UTL_HTTP.end_response(resp);
END;
/

A simpler helper – you can use the built-in authentication parameter:
sql
UTL_HTTP.set_authentication(req, username, password, ‘Basic’);

Using apex_web_service

sql
DECLARE
l_response CLOB;
BEGIN
l_response := apex_web_service.make_rest_request(
p_url => ‘https://api.example.com/v1/resource‘,
p_http_method => ‘GET’,
p_username => ‘myuser’,
p_password => ‘mypass’
);
DBMS_OUTPUT.put_line(l_response);
END;

3. Bearer Token Authorization (most common for modern APIs)

Set the Authorization header to Bearer .

UTL_HTTP

sql
DECLARE
req utl_http.req;
resp utl_http.resp;
token VARCHAR2(1000) := ‘eyJhbGciOi…’; — your token
BEGIN
req := UTL_HTTP.begin_request(‘https://api.example.com/v2/data‘, ‘GET’);
UTL_HTTP.set_header(req, ‘Authorization’, ‘Bearer ‘ || token);
UTL_HTTP.set_header(req, ‘Accept’, ‘application/json’);

resp := UTL_HTTP.get_response(req);
— process response …
END;

apex_web_service (since APEX 5.0)

sql
DECLARE
l_response CLOB;
BEGIN
apex_web_service.g_request_headers(1).name := ‘Authorization’;
apex_web_service.g_request_headers(1).value := ‘Bearer ‘ || ‘eyJhbGciOi…’;

l_response := apex_web_service.make_rest_request(
p_url => ‘https://api.example.com/v2/data‘,
p_http_method => ‘GET’
);
END;

For older APEX versions you can use the overloaded p_parm_name/p_parm_value arrays in make_rest_request.

4. API Key Authorization

Many services use a custom header (e.g., x-api-key) or include the key in the query string. We’ll show the header method.

UTL_HTTP:
sql
UTL_HTTP.set_header(req, ‘x-api-key’, ‘your-api-key-value’);

apex_web_service:
sql
apex_web_service.g_request_headers(1).name := ‘x-api-key’;
apex_web_service.g_request_headers(1).value := ‘your-api-key-value’;

If the key is passed as a query parameter, simply append it to the URL:
?api_key=.... Ensure the URL does not exceed Oracle’s limits and that you URL‑encode special characters with UTL_URL.escape.

5. OAuth 2.0 – Client Credentials Grant (Machine‑to‑Machine)

Most server‑side PL/SQL calls that need OAuth2 use the Client Credentials flow. It requires a two‑step process:

Obtain an access token from the token endpoint.
Use that token as a Bearer token in subsequent API calls.

Here’s a complete procedure to fetch a token with client credentials:

sql
CREATE OR REPLACE FUNCTION get_oauth_token(
p_token_url IN VARCHAR2,
p_client_id IN VARCHAR2,
p_client_secret IN VARCHAR2,
p_scope IN VARCHAR2 DEFAULT NULL
) RETURN VARCHAR2
IS
req utl_http.req;
resp utl_http.resp;
post_data VARCHAR2(32767);
token VARCHAR2(32767);
json_obj JSON_OBJECT_T; — requires Oracle 12.2+
BEGIN
— Build the form-encoded body
post_data := ‘grant_type=client_credentials’;
IF p_scope IS NOT NULL THEN
post_data := post_data || ‘&scope=’ || UTL_URL.escape(p_scope);
END IF;

— Encode the Authorization header for Basic (client_id:client_secret)
UTL_HTTP.set_authentication(req, p_client_id, p_client_secret, ‘Basic’);

req := UTL_HTTP.begin_request(p_token_url, ‘POST’);
UTL_HTTP.set_header(req, ‘Content-Type’, ‘application/x-www-form-urlencoded’);
UTL_HTTP.set_header(req, ‘Content-Length’, LENGTH(post_data));

— Write the body
UTL_HTTP.write_text(req, post_data);

resp := UTL_HTTP.get_response(req);

— Read the JSON response containing access_token
DBMS_LOB.createtemporary(token, TRUE);
UTL_HTTP.read_text(resp, token);

— Parse JSON (Oracle 12.2+)
json_obj := JSON_OBJECT_T.parse(token);
RETURN json_obj.get_string(‘access_token’);
EXCEPTION
WHEN OTHERS THEN
UTL_HTTP.end_response(resp);
RAISE;
END;
/

You would then call:
sql
DECLARE
v_token VARCHAR2(4000);
BEGIN
v_token := get_oauth_token(
p_token_url => ‘https://auth.example.com/oauth/token‘,
p_client_id => ‘my_client_id’,
p_client_secret => ‘my_client_secret’,
p_scope => ‘read write’
);
— Store v_token securely (e.g., in a package variable or a table)
END;

Important: Tokens are short‑lived. In production, cache the token and refresh it before expiry. A common pattern is to store it in a package‑level variable with a timestamp.

6. Handling SSL / HTTPS (TLS)

Most REST APIs require HTTPS. Oracle Database needs access to the appropriate wallet containing trusted root certificates.

Simplest: Use apex_web_service – it automatically uses the Oracle Wallet configured with APEX or a default system wallet.
With UTL_HTTP: You must create an Oracle Wallet and set the wallet path in the session or database.

sql
— Set wallet for the current session
UTL_HTTP.set_wallet(‘file:/path/to/wallet’, ‘wallet_password’);

Alternatively, some DBAs prefer to use the system’s root certificates by pointing to a PEM file. Always consult your security policy.

7. Putting It All Together – A Real‑World Template

sql
CREATE OR REPLACE PROCEDURE call_secure_api(
p_api_base IN VARCHAR2,
p_endpoint IN VARCHAR2,
p_token IN VARCHAR2, — Bearer token
p_body IN CLOB DEFAULT NULL,
p_method IN VARCHAR2 DEFAULT ‘GET’
) IS
req utl_http.req;
resp utl_http.resp;
l_url VARCHAR2(32767);
l_body CLOB;
BEGIN
l_url := p_api_base || p_endpoint;

req := UTL_HTTP.begin_request(l_url, p_method);

— Standard headers
UTL_HTTP.set_header(req, ‘Content-Type’, ‘application/json’);
UTL_HTTP.set_header(req, ‘Accept’, ‘application/json’);

— Authorization
IF p_token IS NOT NULL THEN
UTL_HTTP.set_header(req, ‘Authorization’, ‘Bearer ‘ || p_token);
END IF;

— Body for POST/PUT/PATCH
IF p_body IS NOT NULL AND p_method IN (‘POST’,’PUT’,’PATCH’) THEN
UTL_HTTP.set_header(req, ‘Content-Length’, DBMS_LOB.getlength(p_body));
UTL_HTTP.write_text(req, p_body);
END IF;

resp := UTL_HTTP.get_response(req);

DBMS_OUTPUT.put_line(‘HTTP Status: ‘ || resp.status_code);

BEGIN
LOOP
UTL_HTTP.read_text(resp, l_body);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN NULL;
END;

UTL_HTTP.end_response(resp);

— Here you could parse l_body and/or store it
— …
END call_secure_api;

8. Security Considerations

Never hard‑code secrets – Use Oracle Secure Application Roles, DBMS_CRYPTO, or an external vault. Consider using Oracle Wallets to store credentials.
ACL strictly restrict outbound hosts – don’t grant connect to *.
Avoid passing tokens in the URL (query string) – headers are safer from logging.
Check expiration – implement a token refresh mechanism before every call if needed.
Error handling – inspect HTTP status codes and handle 401/403 by refreshing tokens or raising meaningful exceptions.

With these building blocks you can connect PL/SQL to nearly any REST API that requires authorization. The choice between UTL_HTTP and apex_web_service is usually one of convenience versus environment constraints; both will reliably set the necessary headers to authenticate at the specified domain.