How to Call REST APIs from PL/SQL Using APEX_WEB_SERVICE

APEX_WEB_SERVICE is Oracle APEX's HTTP client. It is built on UTL_HTTP but adds the instance's proxy and wallet settings, Web Credentials, and OAuth, so calling a REST or SOAP service from PL/SQL takes one function call and no secrets in your code. REST Data Sources are the declarative alternative; APEX_WEB_SERVICE is for one-off calls and for services that do not fit a data source. Three companion packages round it out: APEX_CREDENTIAL stores secrets in the workspace, APEX_JWT signs and validates JSON Web Tokens, and APEX_HTTP sends files to the browser.

This guide covers all four with tested examples and their real output, including two behaviors in APEX 26.1 that differ from the documentation.

Quick Reference

Before You Start: Network Access

The database needs a network ACL that lets APEX's schema, APEX_260100 in 26.1, reach each host, and HTTPS needs a wallet with the root certificates, configured in the instance settings or passed with p_wallet_path. Most "it works in Postman but not in APEX" problems come down to one of these two.

How to Run These Examples

The examples ran in Oracle APEX 26.1 against three services: an ORDS REST module on the sample schema that returns orders, httpbin.org, which echoes back whatever request it receives, and a public SOAP calculator. Replace the ORDS URL with an endpoint of your own. Most examples need an APEX session of application 200, created with APEX_SESSION.CREATE_SESSION as shown in the guide to creating APEX sessions and managing session state from PL/SQL. Run them as your workspace schema with server output switched on. The output under each example is exactly what the database printed.

Calling REST Services: APEX_WEB_SERVICE

MAKE_REST_REQUEST and MAKE_REST_REQUEST_B

Sends an HTTP request and returns the response body, as a CLOB, or as a BLOB from MAKE_REST_REQUEST_B for binary content. p_http_method is GET, POST, PUT, PATCH, DELETE, or HEAD. The body is p_body for text or p_body_blob for binary, and p_parm_name and p_parm_value send name/value pairs as a form. Authentication comes from p_username and p_password (Basic), or better from a Web Credential in p_credential_static_id, with p_token_url and p_oauth_scope for OAuth. p_transfer_timeout is in seconds, 180 by default.

Syntax:

apex_web_service.make_rest_request[_b](p_url in varchar2, p_http_method in varchar2,
    p_username in varchar2 default null, p_password in varchar2 default null, p_scheme in varchar2 default 'Basic',
    p_proxy_override in varchar2 default null, p_transfer_timeout in number default 180,
    p_body in clob default empty_clob(), p_body_blob in blob default empty_blob(),
    p_parm_name in apex_application_global.vc_arr2 default empty_vc_arr,
    p_parm_value in apex_application_global.vc_arr2 default empty_vc_arr,
    p_wallet_path in varchar2 default null, p_wallet_pwd in varchar2 default null, p_https_host in varchar2 default null,
    p_credential_static_id in varchar2 default null, p_token_url in varchar2 default null,
    p_oauth_scope in varchar2 default null) return clob | blob

After each call, package variables describe the response: g_status_code and g_reason_phrase, g_headers (a table of name and value records), and g_response_cookies. An HTTP error status is not an exception, so always check g_status_code.

This example needs a session of application 200, page 1, and calls an ORDS module that returns orders as JSON.

Example:

declare
    l_response clob;
begin
    -- the ORBIT schema's ORDS module orbit.sales, on the local ORDS
    l_response := apex_web_service.make_rest_request(
                      p_url         => 'http://host.docker.internal:8080/ords/orbit/sales/orders/',
                      p_http_method => 'GET');

    dbms_output.put_line('status: ' || apex_web_service.g_status_code || ' ' || apex_web_service.g_reason_phrase);
    for i in 1 .. apex_web_service.g_headers.count loop
        if apex_web_service.g_headers(i).name = 'Content-Type' then
            dbms_output.put_line('type:   ' || apex_web_service.g_headers(i).value);
        end if;
    end loop;

    for r in (select * from json_table(l_response, '$.items[*]'
                                       columns (order_number, status, order_total number))
               fetch first 3 rows only) loop
        dbms_output.put_line(r.order_number || ' ' || rpad(r.status, 10) || r.order_total);
    end loop;
end;
/

Output:

status: 200 OK
type:   application/json
ORD-12283 SHIPPED   463.21
ORD-12280 CANCELLED 69.98
ORD-12279 APPROVED  1329.95

JSON_TABLE turned the response straight into rows. Publishing such a module is covered in the guide to publishing REST services with ORDS and Oracle APEX, and reading JSON in PL/SQL in the guide to parsing and generating JSON with APEX_JSON.

Binary responses come back as BLOBs. BLOB2CLOBBASE64 and CLOBBASE642BLOB convert between binary and Base64 text, for files inside JSON bodies; p_newlines set to 'N' returns a single line instead of lines of 64 characters, and p_padding controls the trailing = characters. This example also needs a session:

Example:

declare
    l_png    blob;
    l_base64 clob;
begin
    l_png := apex_web_service.make_rest_request_b(p_url => 'https://httpbin.org/image/png',
                                                  p_http_method => 'GET');
    dbms_output.put_line(apex_web_service.g_status_code || ': ' || dbms_lob.getlength(l_png) || ' bytes, starts with '
                         || utl_raw.cast_to_varchar2(dbms_lob.substr(l_png, 3, 2)));

    -- Base64, for JSON payloads and data: URLs; p_newlines => 'N' gives one line
    l_base64 := apex_web_service.blob2clobbase64(p_blob => l_png, p_newlines => 'N');
    dbms_output.put_line('base64: ' || dbms_lob.getlength(l_base64) || ' chars: ' || substr(l_base64, 1, 24) || '...');
    dbms_output.put_line('decoded: ' || dbms_lob.getlength(apex_web_service.clobbase642blob(l_base64)) || ' bytes');
end;
/

Output:

200: 8090 bytes, starts with PNG
base64: 10788 chars: iVBORw0KGgoAAAANSUhEUgAA...
decoded: 8090 bytes

SET_REQUEST_HEADERS, GET_REQUEST_HEADER, REMOVE_REQUEST_HEADER, and CLEAR_REQUEST_HEADERS

Request headers live in the package variable g_request_headers and are sent with every request in the database session until removed. SET_REQUEST_HEADERS sets up to five at once; p_reset true clears the others first, and p_skip_if_exists true keeps existing values. GET_REQUEST_HEADER reads one, REMOVE_REQUEST_HEADER removes one, and CLEAR_REQUEST_HEADERS removes them all. Clear them after a call, or the next call in the session sends them too.

Syntax:

apex_web_service.set_request_headers(p_name_01 in varchar2, p_value_01 in varchar2, ... p_name_05, p_value_05,
    p_reset in boolean default true, p_skip_if_exists in boolean default false)
apex_web_service.get_request_header(p_header_name in varchar2) return varchar2
apex_web_service.remove_request_header(p_name in varchar2)
apex_web_service.clear_request_headers

This example needs a session of application 200, page 1.

Example:

declare
    l_response clob;
begin
    apex_web_service.set_request_headers(
        p_name_01  => 'Content-Type',    p_value_01 => 'application/json',
        p_name_02  => 'X-Orbit-Client',  p_value_02 => 'api-lab',
        p_reset    => true);                          -- start from no headers
    dbms_output.put_line('header: ' || apex_web_service.get_request_header('X-Orbit-Client'));

    -- httpbin.org echoes the request it receives
    l_response := apex_web_service.make_rest_request(
                      p_url         => 'https://httpbin.org/anything/orders',
                      p_http_method => 'POST',
                      p_body        => '{"sku":"TNT-1002","qty":2}');
    dbms_output.put_line('method: ' || json_value(l_response, '$.method'));
    dbms_output.put_line('json:   ' || json_query(l_response, '$.json'));
    dbms_output.put_line('client: ' || json_value(l_response, '$.headers."X-Orbit-Client"'));
    dbms_output.put_line('ecid:   ' || case when json_value(l_response, '$.headers."Ecid-Context"') is not null
                                            then 'sent' end);

    -- request headers stay set for the next request, until removed
    apex_web_service.remove_request_header('X-Orbit-Client');
    for i in 1 .. apex_web_service.g_request_headers.count loop
        dbms_output.put_line('left:   ' || apex_web_service.g_request_headers(i).name);
    end loop;
    apex_web_service.clear_request_headers;
    dbms_output.put_line('left:   ' || apex_web_service.g_request_headers.count || ' header(s)');
end;
/

Output:

header: api-lab
method: POST
json:   {"qty":2,"sku":"TNT-1002"}
client: api-lab
ecid:   sent
left:   Content-Type
left:   ECID-Context
left:   0 header(s)

APEX adds an ECID-Context header to every request, which is why it appears in the list of leftover headers. Forgetting to clear headers is a classic source of confusing bugs, where a JSON Content-Type from one call leaks into an unrelated one.

SET_REQUEST_ECID_CONTEXT

Sets the Execution Context ID sent in the ECID-Context header, by default APEX's own for the request, so the logs of the called service can be matched to the APEX request.

This example needs a session of application 200, page 1.

Example:

declare
    l_response clob;
begin
    -- APEX sends an ECID-Context header, to trace a request across servers; set your own ID
    apex_web_service.set_request_ecid_context(p_ecid => 'ORBIT-TRACE-0042');
    l_response := apex_web_service.make_rest_request(p_url => 'https://httpbin.org/headers', p_http_method => 'GET');
    dbms_output.put_line(json_value(l_response, '$.headers."Ecid-Context"'));
end;
/

Output:

ORBIT-TRACE-0042

Forms, Cookies, and CLEAR_REQUEST_COOKIES

p_parm_name and p_parm_value send a form, but in 26.1 only when the Content-Type header application/x-www-form-urlencoded is set; without it the body is sent empty. Cookies to send go into g_request_cookies, a utl_http.cookie_table, and each needs domain and path set or the request fails. CLEAR_REQUEST_COOKIES empties it.

This example needs a session of application 200, page 1.

Example:

declare
    l_response clob;
begin
    -- p_parm_name/p_parm_value: sent as an HTML form; the Content-Type header is needed
    apex_web_service.set_request_headers('Content-Type', 'application/x-www-form-urlencoded');
    l_response := apex_web_service.make_rest_request(
                      p_url         => 'https://httpbin.org/post',
                      p_http_method => 'POST',
                      p_parm_name   => apex_string.string_to_table('sku:qty'),
                      p_parm_value  => apex_string.string_to_table('TNT-1002:2'));
    dbms_output.put_line('form: ' || json_query(l_response, '$.form'));
    dbms_output.put_line('type: ' || json_value(l_response, '$.headers."Content-Type"'));

    apex_web_service.clear_request_headers;

    -- a cookie for the next request (domain and path are required);
    -- the cookies of a response arrive in g_response_cookies
    apex_web_service.g_request_cookies(1).name   := 'orbit_region';
    apex_web_service.g_request_cookies(1).value  := 'west';
    apex_web_service.g_request_cookies(1).domain := 'httpbin.org';
    apex_web_service.g_request_cookies(1).path   := '/';
    l_response := apex_web_service.make_rest_request(p_url => 'https://httpbin.org/cookies',
                                                     p_http_method => 'GET');
    dbms_output.put_line('cookies: ' || json_query(l_response, '$.cookies'));
    apex_web_service.clear_request_cookies;
    dbms_output.put_line('request cookies now: ' || apex_web_service.g_request_cookies.count);
end;
/

Output:

form: {"qty":"2","sku":"TNT-1002"}
type: application/x-www-form-urlencoded
cookies: {"orbit_region":"west"}
request cookies now: 0

The empty-form behavior is the first 26.1 difference: the documentation does not mention the header requirement, and without it the service simply receives nothing.

APPEND_TO_MULTIPART and GENERATE_REQUEST_BODY

Build a multipart/form-data body, the kind an HTML form with a file field sends. APPEND_TO_MULTIPART adds a part, either a field or a file with p_filename and p_content_type, with a CLOB or BLOB body. GENERATE_REQUEST_BODY returns the whole body as a BLOB and sets the Content-Type request header with the boundary.

Syntax:

apex_web_service.append_to_multipart(p_multipart in out nocopy t_multipart_parts, p_name in varchar2,
    p_filename in varchar2 default null, p_content_type in varchar2 default 'application/octet-stream',
    p_body in clob | p_body_blob in blob)
apex_web_service.generate_request_body(p_multipart in t_multipart_parts, p_to_charset in varchar2 default null) return blob

This example needs a session of application 200, page 1.

Example:

declare
    l_parts    apex_web_service.t_multipart_parts;
    l_body     blob;
    l_response clob;
begin
    apex_web_service.append_to_multipart(p_multipart => l_parts, p_name => 'orderNumber', p_body => 'ORD-10042');
    apex_web_service.append_to_multipart(p_multipart => l_parts, p_name => 'invoice',
                                         p_filename  => 'ORD-10042.csv', p_content_type => 'text/csv',
                                         p_body_blob => apex_util.clob_to_blob('sku,qty' || chr(10) || 'TNT-1002,2'));
    l_body := apex_web_service.generate_request_body(p_multipart => l_parts);

    -- generate_request_body sets the Content-Type header with the boundary
    l_response := apex_web_service.make_rest_request(p_url => 'https://httpbin.org/post',
                                                     p_http_method => 'POST', p_body_blob => l_body);
    dbms_output.put_line('type:  ' || regexp_substr(json_value(l_response, '$.headers."Content-Type"'), '^[^;]+'));
    dbms_output.put_line('form:  ' || json_query(l_response, '$.form'));
    dbms_output.put_line('files: ' || json_query(l_response, '$.files'));
    apex_web_service.clear_request_headers;
end;
/

Output:

type:  multipart/form-data
form:  {"orderNumber":"ORD-10042"}
files: {"invoice":"sku,qty\nTNT-1002,2"}

This replaces the hand-built boundaries of older code such as posting multipart form data with UTL_HTTP.

SOAP Services

MAKE_REQUEST

Sends a SOAP envelope, p_envelope, with the SOAPAction p_action and returns the response as XMLTYPE. As a procedure, it stores the response in the collection named by p_collection_name instead, for a page to process. p_version is '1.1', the default, or '1.2'. Authentication is by user name and password or by Web Credential.

Syntax:

apex_web_service.make_request(p_url in varchar2, p_action in varchar2 default null, p_version in varchar2 default '1.1',
    [p_collection_name in varchar2,] p_envelope in clob, p_username in varchar2 default null,
    p_password in varchar2 default null | p_credential_static_id in varchar2, p_token_url in varchar2 default null, ...)
  [return sys.xmltype]

PARSE_XML, PARSE_XML_CLOB, PARSE_RESPONSE, and PARSE_RESPONSE_CLOB

Extract a value from a SOAP response with an XPath expression: PARSE_XML and PARSE_XML_CLOB from an XMLTYPE, and PARSE_RESPONSE and PARSE_RESPONSE_CLOB from a response stored in a collection. p_ns declares the namespaces the XPath uses.

Syntax:

apex_web_service.parse_xml[_clob](p_xml in sys.xmltype, p_xpath in varchar2, p_ns in varchar2 default null) return varchar2 | clob
apex_web_service.parse_response[_clob](p_collection_name in varchar2, p_xpath in varchar2, p_ns in varchar2 default null) return varchar2 | clob

This example needs a session of application 200, page 1, and calls a public SOAP calculator service.

Example:

declare
    l_envelope clob := '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body>'
                    || '<Add xmlns="http://tempuri.org/"><intA>40</intA><intB>2</intB></Add>'
                    || '</soap:Body></soap:Envelope>';
    l_xml      xmltype;
begin
    -- a public SOAP 1.1 calculator service
    l_xml := apex_web_service.make_request(
                 p_url      => 'http://www.dneonline.com/calculator.asmx',
                 p_action   => 'http://tempuri.org/Add',
                 p_envelope => l_envelope);
    dbms_output.put_line('Add: ' || apex_web_service.parse_xml(
                                        p_xml   => l_xml,
                                        p_xpath => '//AddResult/text()',
                                        p_ns    => 'xmlns="http://tempuri.org/"'));
    dbms_output.put_line('Body: ' || apex_web_service.parse_xml_clob(l_xml, '//soap:Body/*',
                                        'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'));

    -- the procedure stores the response in a collection instead
    apex_web_service.make_request(
        p_url             => 'http://www.dneonline.com/calculator.asmx',
        p_action          => 'http://tempuri.org/Multiply',
        p_collection_name => 'SOAP_RESPONSE',
        p_envelope        => replace(replace(l_envelope, '<Add ', '<Multiply '), '</Add>', '</Multiply>'));
    dbms_output.put_line('Multiply: ' || apex_web_service.parse_response(
                                             p_collection_name => 'SOAP_RESPONSE',
                                             p_xpath           => '//MultiplyResult/text()',
                                             p_ns              => 'xmlns="http://tempuri.org/"'));
end;
/

Output:

Add: 42
Body: <AddResponse xmlns="http://tempuri.org/">
  <AddResult>42</AddResult>
</AddResponse>

Multiply: 80

The namespace declaration in p_ns is what makes the XPath match; leaving it out is the most common reason PARSE_XML returns null.

OAuth Tokens

OAUTH_AUTHENTICATE_CREDENTIAL, OAUTH_GET_LAST_TOKEN, and OAUTH_SET_TOKEN

OAUTH_AUTHENTICATE_CREDENTIAL requests an access token through the OAuth2 client credentials flow from p_token_url, using a Web Credential's client ID and secret; the token goes into g_oauth_token, with token and expires. A MAKE_REST_REQUEST with p_credential_static_id and p_token_url does the same by itself and reuses the token until it expires, so most code never calls this directly. OAUTH_GET_LAST_TOKEN returns the token, or null once it has expired, and OAUTH_SET_TOKEN sets a token obtained some other way. OAUTH_AUTHENTICATE, which took the client ID and secret as parameters, is deprecated.

Syntax:

apex_web_service.oauth_authenticate_credential(p_token_url in varchar2, p_credential_static_id in varchar2,
    p_proxy_override in varchar2 default null, p_transfer_timeout in number default 180, ..., p_scope in varchar2 default null)
apex_web_service.oauth_get_last_token return varchar2
apex_web_service.oauth_set_token(p_token in varchar2, p_expires in date default null)

This example needs a session of application 200, page 1.

Example:

declare
    l_response clob;
begin
    -- a token obtained by other means than oauth_authenticate
    apex_web_service.oauth_set_token(p_token => 'orbit-demo-token', p_expires => sysdate + 1/24);
    dbms_output.put_line('last token: ' || apex_web_service.oauth_get_last_token);

    l_response := apex_web_service.make_rest_request(p_url => 'https://httpbin.org/bearer', p_http_method => 'GET');
    dbms_output.put_line('without header: ' || apex_web_service.g_status_code);

    -- in 26.1 the token is not added to requests by itself: send it as a header
    apex_web_service.set_request_headers('Authorization', 'Bearer ' || apex_web_service.oauth_get_last_token);
    l_response := apex_web_service.make_rest_request(p_url => 'https://httpbin.org/bearer', p_http_method => 'GET');
    dbms_output.put_line('with header:    ' || apex_web_service.g_status_code || ' ' || json_query(l_response, '$'));
    apex_web_service.clear_request_headers;
end;
/

Output:

last token: orbit-demo-token
without header: 401
with header:    200 {"authenticated":true,"token":"orbit-demo-token"}

This is the second 26.1 difference: the documentation says a token set with OAUTH_SET_TOKEN is used by the following requests, but it is not sent by itself. Add the Authorization header yourself, as the example does.

Web Credentials: APEX_CREDENTIAL

A Web Credential keeps a secret, such as a password, client secret, API key, or private key, encrypted in the workspace. Code refers to it by static ID and can never read it back, and it can be limited to certain URLs so it cannot be sent to another server. Credentials are usually created in Workspace Utilities or Shared Components; APEX_CREDENTIAL creates and changes them in code.

Changing credentials changes the workspace, which an application may only do if its Runtime API Usage includes Modify Workspace Repository. The test application's does not, so these examples run outside it after apex_util.set_workspace, and create an APEX session only for the calls that need one. Each example first drops any credential of the same name left over from an earlier run.

CREATE_CREDENTIAL, DROP_CREDENTIAL, and SET_ALLOWED_URLS

CREATE_CREDENTIAL creates a credential of a type: apex_credential.c_type_basic, c_type_oauth_client_cred, c_type_oauth_password, c_type_http_header, c_type_http_query_string, c_type_jwt, c_type_oci, c_type_key_pair, c_type_certificate_pair, c_type_signed_user_assertion, or c_type_user_assert_certificate. p_allowed_urls lists the URL prefixes it may be sent to, p_scope is the OAuth scope, p_prompt_on_install asks for the secret when the application is installed, and p_db_credential_name uses a database credential instead. DROP_CREDENTIAL deletes it. SET_ALLOWED_URLS changes the URLs and requires the current secret, so nobody can redirect a credential without knowing it.

Syntax:

apex_credential.create_credential(p_credential_name in varchar2, p_credential_static_id in varchar2,
    p_authentication_type in t_credential_type, p_scope in varchar2 default null,
    p_allowed_urls in apex_t_varchar2 default null, p_prompt_on_install in boolean default false,
    p_credential_comment in varchar2 default null, p_db_credential_name in varchar2 default null,
    p_db_credential_is_instance in boolean default false, p_named_scopes in varchar2 default null,
    p_referenced_static_id in varchar2 default null, p_oauth_token_request_type in varchar2 default null)
apex_credential.drop_credential(p_credential_static_id in varchar2)
apex_credential.set_allowed_urls(p_credential_static_id in varchar2, p_allowed_urls in apex_t_varchar2, p_client_secret in varchar2)
apex_credential.set_database_credential(p_credential_static_id in varchar2, p_db_credential_name in varchar2,
    p_db_credential_is_instance in boolean default false)

SET_PERSISTENT_CREDENTIALS and SET_SESSION_CREDENTIALS

Set a credential's secret. SET_PERSISTENT_CREDENTIALS stores it for everyone, and SET_SESSION_CREDENTIALS for the current APEX session only, overriding the stored one, for services where each user signs in with their own account. The overloads match the credential types: user name and password; client ID and secret (with the OCI p_namespace and p_fingerprint); key and value, for HTTP header and query string credentials; and, persistent only, a certificate and private key.

Syntax:

apex_credential.set_persistent_credentials | set_session_credentials(p_credential_static_id in varchar2,
    p_username in varchar2, p_password in varchar2
    | p_client_id in varchar2, p_client_secret in varchar2, p_namespace in varchar2 default null, p_fingerprint in varchar2 default null
    | p_key in varchar2, p_value in varchar2)
apex_credential.set_persistent_credentials(p_credential_static_id in varchar2, p_certificate in varchar2,
    p_private_key in varchar2, p_audience in varchar2, p_key_id in varchar2 default null)

A Basic credential, used once with the stored secret, once with a session override, and once against a URL it is not allowed for:

Example:

declare
    l_response clob;
begin
    -- outside the lab app: its Runtime API Usage does not allow changing the workspace
    apex_util.set_workspace('APEXBOOK');

    -- a Web Credential of the workspace, usable only for the allowed URLs
    apex_credential.create_credential(
        p_credential_name       => 'httpbin Basic',
        p_credential_static_id  => 'httpbin-basic',
        p_authentication_type   => apex_credential.c_type_basic,
        p_allowed_urls          => apex_t_varchar2('https://httpbin.org/'));
    -- stored encrypted, for all sessions
    apex_credential.set_persistent_credentials(p_credential_static_id => 'httpbin-basic',
                                               p_username => 'orbit', p_password => 'secret-1');
    commit;

    apex_session.create_session(p_app_id => 200, p_page_id => 1, p_username => 'ADMIN');
    l_response := apex_web_service.make_rest_request(
                      p_url                  => 'https://httpbin.org/basic-auth/orbit/secret-1',
                      p_http_method          => 'GET',
                      p_credential_static_id => 'httpbin-basic');
    dbms_output.put_line('persistent: ' || apex_web_service.g_status_code || ' ' || json_value(l_response, '$.user'));

    -- for this APEX session only, overriding the stored values
    apex_credential.set_session_credentials(p_credential_static_id => 'httpbin-basic',
                                            p_username => 'orbit', p_password => 'secret-2');
    l_response := apex_web_service.make_rest_request(
                      p_url                  => 'https://httpbin.org/basic-auth/orbit/secret-1',
                      p_http_method          => 'GET',
                      p_credential_static_id => 'httpbin-basic');
    dbms_output.put_line('session:    ' || apex_web_service.g_status_code);

    -- a URL outside the allowed ones
    begin
        l_response := apex_web_service.make_rest_request(p_url => 'https://postman-echo.com/basic-auth',
                          p_http_method => 'GET', p_credential_static_id => 'httpbin-basic');
    exception when others then
        dbms_output.put_line('other URL:  ' || regexp_substr(regexp_replace(sqlerrm, 'ORA-\d+: '), '[^' || chr(10) || ']+'));
    end;
    apex_session.delete_session;

    apex_util.set_workspace('APEXBOOK');
    apex_credential.drop_credential(p_credential_static_id => 'httpbin-basic');
end;
/

Output:

persistent: 200 orbit
session:    401
other URL:  APEX - Credential is not allowed to be used for this URL endpoint. - Contact your application administrator.

The session value replaced the stored one, so httpbin rejected the request with 401, and the credential was refused outright for a URL outside its allowed list. The commit after setting the persistent secret matters: a credential written in the same transaction stays locked until commit, and SET_SESSION_CREDENTIALS on it would otherwise raise a deadlock, ORA-00060.

An API key sent as an HTTP header:

Example:

declare
    l_response clob;
begin
    -- outside the lab: its Runtime API Usage does not allow changing the workspace
    apex_util.set_workspace('APEXBOOK');

    -- an API key sent as an HTTP header: the key is the header name, the value its value
    apex_credential.create_credential(
        p_credential_name      => 'httpbin API Key',
        p_credential_static_id => 'httpbin-api-key',
        p_authentication_type  => apex_credential.c_type_http_header,
        p_allowed_urls         => apex_t_varchar2('https://httpbin.org/'));
    apex_credential.set_persistent_credentials(p_credential_static_id => 'httpbin-api-key',
                                               p_key => 'X-Api-Key', p_value => 'orbit-key-123');
    apex_credential.set_allowed_urls(p_credential_static_id => 'httpbin-api-key',
                                     p_allowed_urls => apex_t_varchar2('https://httpbin.org/headers'),
                                     p_client_secret => 'orbit-key-123');   -- changing URLs needs the secret

    l_response := apex_web_service.make_rest_request(p_url => 'https://httpbin.org/headers',
                      p_http_method => 'GET', p_credential_static_id => 'httpbin-api-key');
    dbms_output.put_line('X-Api-Key: ' || json_value(l_response, '$.headers."X-Api-Key"'));

    for c in (select name, credential_type, valid_for_urls
                from apex_workspace_credentials where static_id = 'httpbin-api-key') loop
        dbms_output.put_line(c.name || ' - ' || c.credential_type || ' - ' || c.valid_for_urls);
    end loop;
    apex_credential.drop_credential('httpbin-api-key');
end;
/

Output:

X-Api-Key: orbit-key-123
httpbin API Key - HTTP Header - https://httpbin.org/headers

SET_SCOPE, SET_SESSION_TOKEN, SET_PERSISTENT_TOKEN, and CLEAR_TOKENS

For OAuth credentials: SET_SCOPE changes the scope; SET_SESSION_TOKEN and SET_PERSISTENT_TOKEN store a token obtained some other way, of type c_token_access, c_token_refresh, or c_token_id, with its expiry, for the session or for everyone; and CLEAR_TOKENS forgets the stored tokens so the next request asks for new ones.

Syntax:

apex_credential.set_scope(p_credential_static_id in varchar2, p_scope in varchar2, p_named_scopes in varchar2 default null)
apex_credential.set_session_token | set_persistent_token(p_credential_static_id in varchar2, p_token_type in t_token_type,
    p_token_value in varchar2, p_token_expires in date, p_token_scope in varchar2 default null)
apex_credential.clear_tokens(p_credential_static_id in varchar2)

Example:

begin
    apex_util.set_workspace('APEXBOOK');
    apex_credential.create_credential(
        p_credential_name      => 'ORBIT OAuth',
        p_credential_static_id => 'orbit-oauth',
        p_authentication_type  => apex_credential.c_type_oauth_client_cred,
        p_scope                => 'orders.read');
    apex_credential.set_persistent_credentials(p_credential_static_id => 'orbit-oauth',
                                               p_client_id => 'orbit-app', p_client_secret => 'client-secret');
    apex_credential.set_scope(p_credential_static_id => 'orbit-oauth', p_scope => 'orders.read orders.write');
    for c in (select credential_type, scope from apex_workspace_credentials where static_id = 'orbit-oauth') loop
        dbms_output.put_line(c.credential_type || ', scope: ' || c.scope);
    end loop;

    -- tokens obtained by other means, used instead of asking the token URL
    apex_session.create_session(p_app_id => 200, p_page_id => 1, p_username => 'ADMIN');
    apex_credential.set_session_token(p_credential_static_id => 'orbit-oauth',
                                      p_token_type    => apex_credential.c_token_access,
                                      p_token_value   => 'session-token-1',
                                      p_token_expires => sysdate + 1/24);
    apex_credential.set_persistent_token(p_credential_static_id => 'orbit-oauth',
                                         p_token_type    => apex_credential.c_token_access,
                                         p_token_value   => 'shared-token-1',
                                         p_token_expires => sysdate + 1/24);
    apex_credential.clear_tokens(p_credential_static_id => 'orbit-oauth');   -- forget them again
    apex_session.delete_session;

    apex_util.set_workspace('APEXBOOK');
    apex_credential.drop_credential('orbit-oauth');
    dbms_output.put_line('tokens set, cleared, and the credential dropped');
end;
/

Output:

OAuth2 Client Credentials flow, scope: orders.read orders.write
tokens set, cleared, and the credential dropped

JSON Web Tokens: APEX_JWT

A JSON Web Token is a signed JSON document, made of a header, claims, and a signature, each Base64URL-encoded and joined with dots, that services exchange to prove who a caller is. APEX_JWT signs with HMAC SHA-256, known as HS256.

ENCODE

Returns a token with the registered claims iss (issuer), sub (subject), aud (audience), nbf (not before), iat (issued at, now by default), exp (seconds after iat), and jti (token ID), plus p_other_claims, extra JSON members without the braces. With p_signature_key the token is signed; without, it is an unsigned token with alg none.

Syntax:

apex_jwt.encode(p_iss in varchar2 default null, p_sub in varchar2 default null, p_aud in varchar2 default null,
    p_nbf_ts in timestamp with time zone default null, p_iat_ts in timestamp with time zone default systimestamp,
    p_exp_sec in pls_integer default null, p_jti in varchar2 default null, p_other_claims in varchar2 default null,
    p_signature_key in raw default null) return varchar2

DECODE and VALIDATE

DECODE splits a token into a t_token record with header, payload, and signature, and checks the signature against p_signature_key. VALIDATE checks the claims: that iss and aud are the expected ones and that the token has not expired and is already valid, with p_leeway_seconds to allow for clock differences. Both raise VALUE_ERROR when a check fails, and the debug log says which one.

Syntax:

apex_jwt.decode(p_value in varchar2, p_signature_key in raw default null) return t_token
apex_jwt.validate(p_token in t_token, p_iss in varchar2 default null, p_aud in varchar2 default null,
    p_leeway_seconds in pls_integer default 0)

Example:

declare
    l_key   raw(64) := utl_raw.cast_to_raw('orbit-signing-key-0123456789abcdef');
    l_jwt   varchar2(4000);
    l_token apex_jwt.t_token;

    procedure check_token(p_label varchar2, p_jwt varchar2, p_key raw) is
        l_t apex_jwt.t_token;
    begin
        l_t := apex_jwt.decode(p_value => p_jwt, p_signature_key => p_key);   -- checks the signature
        apex_jwt.validate(p_token => l_t, p_iss => 'orbit', p_aud => 'api-lab');   -- iss, aud, exp, nbf
        dbms_output.put_line(rpad(p_label, 10) || 'valid');
    exception when value_error then
        dbms_output.put_line(rpad(p_label, 10) || 'VALUE_ERROR');
    end;
begin
    l_jwt := apex_jwt.encode(
                 p_iss           => 'orbit',
                 p_sub           => 'ADMIN',
                 p_aud           => 'api-lab',
                 p_iat_ts        => timestamp '2026-03-14 09:30:00 +00:00',
                 p_exp_sec       => 3600,
                 p_other_claims  => '"role":' || apex_json.stringify('manager'),
                 p_signature_key => l_key);                 -- HS256
    dbms_output.put_line(l_jwt);

    l_token := apex_jwt.decode(p_value => l_jwt);          -- without a key: no signature check
    dbms_output.put_line('header:  ' || l_token.header);
    dbms_output.put_line('payload: ' || l_token.payload);

    check_token('expired', l_jwt, l_key);                  -- issued in March, valid for an hour
    l_jwt := apex_jwt.encode(p_iss => 'orbit', p_aud => 'api-lab', p_exp_sec => 3600,
                             p_signature_key => l_key);    -- issued now
    check_token('fresh', l_jwt, l_key);
    check_token('wrong key', l_jwt, utl_raw.cast_to_raw('another-key-0123456789abcdefghij'));
end;
/

Output:

eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJvcmJpdCIsInN1YiI6IkFETUlOIiwiYXVkIjoiYXBpLWxhYiIsImlhdCI6MTc3MzQ4MDYwMCwiZXhwIjoxNzczNDg0MjAwLCJyb2xlIjoibWFuYWdlciJ9.rRdTHPZ8MpyZIGxuDkPSeYTXqwN8RXvzDaCp77FS5tU
header:  {"alg":"HS256","typ":"JWT"}
payload: {"iss":"orbit","sub":"ADMIN","aud":"api-lab","iat":1773480600,"exp":1773484200,"role":"manager"}
expired   VALUE_ERROR
fresh     valid
wrong key VALUE_ERROR

The March token failed as expired, the freshly issued one passed, and the same token failed with the wrong key. Keep the signing key in a Web Credential or a protected table, never in code as the example does for demonstration.

Sending Files: APEX_HTTP

DOWNLOAD

Sends a BLOB or CLOB to the browser as the response, with the content type, the file name, and p_is_inline true to display it rather than save it. Use it in an Ajax Callback or Before Header process instead of writing headers with OWA_UTIL. It then stops the APEX engine, like APEX_DATA_EXPORT.DOWNLOAD; outside a page request, catch apex_application.e_stop_apex_engine.

Syntax:

apex_http.download(p_blob in out nocopy blob | p_clob in out nocopy clob, p_content_type in varchar2,
    p_filename in varchar2 default null, p_is_inline in boolean default false)

This example needs a session of application 200, page 1. It sets up a fake web request so the response headers can be read back in a script.

Example:

declare
    l_page htp.htbuf_arr;
    l_rows integer := 999;
    l_name owa.vc_arr;
    l_val  owa.vc_arr;
    l_text varchar2(32767);
    l_csv  clob := 'sku,qty' || chr(10) || 'TNT-1002,2';
begin
    l_name(1) := 'REQUEST_CHARSET'; l_val(1) := 'AL32UTF8';     -- a web request, as ORDS sets it up
    owa.init_cgi_env(1, l_name, l_val);
    htp.init;

    -- in an Ajax callback or a Before Header process
    begin
        apex_http.download(p_clob         => l_csv,
                           p_content_type => 'text/csv',
                           p_filename     => 'order.csv');
    exception
        when apex_application.e_stop_apex_engine then null;   -- download stops the engine
    end;

    owa.get_page(l_page, l_rows);                          -- the headers the browser receives
    for i in 1 .. l_rows loop
        l_text := l_text || l_page(i);
    end loop;
    for h in (select column_value as line from table(apex_string.split(l_text, chr(10)))
               where column_value like 'Content-%' and column_value not like 'Content-Security%') loop
        dbms_output.put_line(h.line);
    end loop;
end;
/

Output:

Content-Type:text/csv; charset=utf-8
Content-Length:18
Content-Disposition:attachment; filename="order.csv"; filename*=utf-8''order.csv

A download button wired to such a process is shown in downloading a file on button click in Oracle APEX.

Conclusion

APEX_WEB_SERVICE calls REST and SOAP services with headers, forms, multipart uploads, cookies, and binary content, and its package variables hold the response's status, headers, and cookies; check g_status_code yourself, and clear request headers after each call. In 26.1, set the form Content-Type header before sending form parameters, and add the Authorization header yourself after OAUTH_SET_TOKEN. APEX_CREDENTIAL keeps passwords, API keys, client secrets, and tokens encrypted and limited to specific URLs, for everyone or for one session. APEX_JWT signs and validates JSON Web Tokens, and APEX_HTTP sends a file to the browser in one call.