APEX: Which PL/SQL APIs to use in APEX for OCI REST API Integration projects running on Autonomous DB?

There’s nothing like having options and Oracle certainly likes to give you that. Everyone 🫶🏾LOVES🫶🏾 choice right?

I’ve listed 3 in the subtitle but there are others like UTL_HTTP, but TK always 🫶🏾 things in 3, so that’s what I’m sticking with.

About This Post

Today, I am trying to get an opinion on when I should use which DB API when building OCI Integration projects.

I’ll attempt to draft the options, my considerations, and if there are any advantages that I’ve seen by using them. This is not a “Best Practice Guide” nor a must follow recommendation.

Make your decision based on what is right for your project.

What 3 Options and Why These 3?

The 3 Options I am comparing are the APEX Web Service Make REST Request, the Autonomous Database version of DBMS_CLOUD Send Request, and the Oracle Cloud Infrastructure (OCI) PL/SQL SDK.

Each will provide a means to manage API credentials and support the OCI account type and REST APIs.

The Oracle database also provides the UTL_HTTP package which is likely the underlying package for those listed above. I am not evaluating this one as it is not specific to REST and I would either need to manage my credentials myself or use the attached Oracle Wallet to store them. Autonomous Database’s restrictions would require additional preparation and steps that are abstracted by the 3 options I am comparing.

Any Differences to Note?

The First two can be used for different APIs and use URLs to connect with the target API service, while the OCI PL/SQL SDK abstracts the need for URLs and accepts tenancy specific details and provides a function for each of the OCI services APIs & methods.

The APEX Packages can be used any where that APEX has been deployed, where both of the other options are pre-installed in all Autonomous Database Serverless instances.

It should also be noted that both DBMS_CLOUD and the PL/SQL SDK have limited target service support, but APEX can be used with any REST API.

Advantage to APEX if you need deployment and API flexibility!

These two also use custom database types for response objects where as APEX uses generic CLOB or BLOB that can be parsed as normal JSON (If that is the returned document type).

Each provide functions to operate on the response type, and once you’ve adopted one method this will be a small difference in the end.

Service Endpoints

APEX’s MAKE_REST_REQUEST and DBMS_CLOUD’s SEND_REQUEST require the target URL or Service Endpoint. The Service Endpoint is specific to each OCI Region and Service.

OCI Service Endpoints include version numbering as well, though I have yet to notice this change for the infrastructure services, it is possible it could be updated in the future.

A point here for the PL/SQL SDK which defaults to the tenancy & region of the Autonomous database instance while allowing for overrides. No need to worry about Service Endpoint updates as the team will update the API functions for Endpoint changes.

A Note on Credentials

Each option provides credential management that maps to an OCI infrastructure account. The account is configured with policies that grant access to the target services and granted API access.

APEX uses Workspace Web Credentials associated with an individual user account.

Resource Principals can be enabled for the database instance and is not mapped to a individual user account are supported by both the DBMS_CLOUD and PL/SQL APIs.

The Resource Principal replaces the need to manage Service Accounts or providing individual accounts elevated privileges.

In either situation multiple APEX end users and developers are impersonating a single account and auditing at the session level is required.

DBMS_CLOUD REST API Supported Cloud Services

The DBMS_CLOUD REST API functions allow you to make HTTP requests using DBMS_CLOUD.SEND_REQUEST and obtain and save results. These functions provide a generic API that lets you call any REST API with the following supported cloud services:

• Oracle Cloud Infrastructure

• Amazon Web Services (AWS)

• Azure Cloud ^Foot 3

• Oracle Cloud Infrastructure Classic

• GitHub Repository

Notable DBMS_CLOUD Limitations

DBMS_CLOUD support for HTTP methods does not include the PATCH method, commonly used for Update operations.

Another consideration for DBMS_CLOUD it seems 🤔 and in a previous release I have tried to use the static value ‘PATCH’ without success and have not tried since.

OCI PL/SQL SDK is specific to OCI Services

The list is way to long to include here but it is fairly comprehensive, adheres to OCI security practices and enables you to write PL/SQL code to manage Oracle Cloud Infrastructure resources.

Comprehensive indeed but functions and types are specific to each service and each one matches the its REST API counter part 1:1.

A Typical Example: PutObject to OCI Object Storage

The main function calls are all similar.

APEX

apex_web_service.make_rest_request

DECLARE
    l_request_url        varchar2(32767);
    l_request_filename    varchar2(500);
    l_mime_type         varchar2(200);
    l_enc_filename      varchar2(4000);
    l_request_object    blob;
    l_response            clob;

BEGIN
    -- Get the content details from apex_application_temp_files
    select blob_content,filename, mime_type into l_request_object,l_request_filename, l_mime_type from apex_application_temp_files
    where name = :P401_DOCUMENT;

    -- handle those pesky characters
    l_enc_filename := replace(apex_util.url_encode(:P401_DOC_NAME),'+','%20');    

    -- Setup the service endpoint 
    l_request_url := :G_OBJECT_STORAGE_BASE_URL || :G_TENANT_NAMESPACE || '/b/' || :G_TENANT_BUCKET ||'/o/data/' || :P401_CUSTOMER_NAME || '/' || l_enc_filename;

    apex_web_service.g_request_headers(1).name := 'Content-Type';
    apex_web_service.g_request_headers(1).value := l_mime_type;

    l_response := apex_web_service.make_rest_request(
        p_url => l_request_url,
        p_http_method => 'PUT',
        p_body_blob => l_request_object,
        p_credential_static_id => :G_TENANT_CREDENTIAL
    );

DBMS_CLOUD

DBMS_CLOUD provides functions for OCI Object Storage. Two examples are provided for reference.

SEND_REQUEST

DECLARE
    object_uri varchar2(32767);
    my_blob_data BLOB;
    l_resp DBMS_CLOUD_TYPES.resp;
    l_resp_json JSON_OBJECT_T;

BEGIN 
  -- Use a bind variable, application or page item over hardcoding
  object_uri := 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/my_new_file';

  l_resp := DBMS_CLOUD.send_request(
    credential_name    => 'NATIVE_CRED_OCI',
    uri                => object_uri,
    method             => DBMS_CLOUD.METHOD_PUT,
    body               => my_blob_data
  );
END;

PUT_OBJECT

DECLARE
  object_uri varchar2(32767);
  my_blob_data BLOB;
BEGIN 
  -- Use a bind variable, application or page item over hardcoding
  object_uri := 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/my_new_file';

  DBMS_CLOUD.PUT_OBJECT(
     credential_name => 'OBJ_STORE_CRED',
     object_uri => object_uri,
     contents => my_blob_data)); 
END;

OCI PL/SQL SDK

The SDK uses a simple, yet long, naming convention covering each service API method. Review the examples to get started.

dbms_cloud_oci_obs_object_storage.put_object

DECLARE
  my_blob_data   blob;
  response       dbms_cloud_oci_obs_object_storage_put_object_response_t;
BEGIN
  -- No hard coding of endpoints, override is supported
  response := dbms_cloud_oci_obs_object_storage.put_object(
     namespace_name => 'namespace-string',
     bucket_name => 'bucketname',
     object_name => 'objectname',
     put_object_body => my_blob_data,
     credential_name => 'OCI_KEY_CRED', -- Optional
     region => 'region-identifier' -- Optional
  );
END;

A Note on Response Types

Each of these requests uses a different response type. APEX uses a native database type while DBMS_CLOUD and the PL/SQL SDK use custom defined types.

Headers

APEX web service response headers are accessible through the globals that a populated if the Web service response included any HTTP headers and saved in the g_headers global. The global variables are parsed as an array.

The DBMS_CLOUD type DBMS_CLOUD_TYPES.resp provides the GET_RESPONSE_HEADERS Function that returns the HTTP response headers as JSON data in a JSON object (JSON_OBJECT_T).

DECLARE
    l_resp            DBMS_CLOUD_TYPES.resp;
    l_resp_headers    JSON_OBJECT_T;
BEGIN
    -- call not shown
    l_resp_headers := DBMS_CLOUD.GET_RESPONSE_HEADERS(
        resp => l_resp);
END;

Each of the OCI PL/SQL functions has specific return types, so refer to the documentation for guidance. The standard response type will include a headers and status_code field. The headers is aJOSN object (JSON_OBJECT_T), same as DBMS_CLOUD.

DECLARE
    response       dbms_cloud_oci_obs_object_storage_put_object_response_t;
    response_headers json_object_t;
BEGIN
    -- call not shown
    response_headers := response.headers;
END;

Response Body

When a response body is returned by a request, it is parsed based on the type as well.

DBMS_CLOUD GET_RESPONSE_RAW and GET_RESPONSE_TEXT are provided to retrieve BLOB or CLOB data respectively.

Piotr and I use this in our APEX DevOps console based on Piotr’s APEX Service can DevOps too! DBMS_CLOUD on Autonomous blog post.

The responses from the PL/SQL SDK are, as to be expected, specific, with fields that map to the APIs Body structure. For our example with OCI Object Storage, the response_body is a BLOB type.

To see this in action review the Sample Document Generator App that I covered in my last post. The plugin uses that PL/SQL SDK and works with the response types in the process.

Conclusion

If the app needs to be deployed anywhere Oracle is deployed, then this is a simple decision as the APEX APIs will be the same regardless of deployment model. APEX also offers the most flexibility as it is not limited to specific service endpoints.

For Autonomous deployments that are OCI centric, with tight OCI service endpoint integration then OCI PL/SQL API has its advantages. The PL/SQL variant offers a nice abstraction layer and defaulting for the tenancy, region and endpoints when compared to both APEX and DBMS_CLOUD.

DBMS_CLOUD has a specific deployment that is only available to Autonomous databases. Although the package can be deployed to non-Autonomous database instances, it has a limited set of functions as well as supported service endpoints.

If the use case is OCI centric on Autonomous then any choice is reasonable. I have my preferences and I’m sure with experience you will have yours as well.