This article explores two integration options between the Oracle Database and Oracle Digital Assistant (ODA) to manage Dynamic Entities.

It was actually written during COVID but not published and I have not seen much changes in the APIs, so I figured why not take it off my backlog and re-post it here.

Two Oracle Database package options are covered, APEX_WEB_SERVICE and DBMS_CLOUD, targeted to Oracle APEX and PL/SQL developers for customer database applications.

Example / Code snippets are provided but are not explained in depth (line-by-line). It is assumed the reader has some APEX or PLSQL experience and seeking to integrate with Oracle Digital Assistant using Oracle APEX/Database features.

The outbound integration goal is to update ODA dynamic entities as the data is managed within the database application. i.e. Insert, Update, Delete operations

In brief Dynamic Entities are value list which are managed through the ODA Rest APIs. They are similar to a key-value pair list having a value with/without synonyms. As of the writing of this document, ODA version 21.06 is the current release.

This article does not delve into ODA Entity types or Dynamic Entities, those topics are covered in depth by previously release TechExchange articles. It is suggested that the Dynamic Entities in Oracle Digital Assistant, by Frank Nimphius & Chris Kutler from November 2019 be reviewed prior to this article as it provides core information regarding Dynamic Entities and the required Rest APIs.

Architecture

The architecture is simple with only two components, the Oracle Database (DB) and Oracle Digital Assistant (ODA).

This can also be represented as:

The database DML will be translated to actions supported by the ODA API

The DML operation can be implemented at the database level or within the APEX or custom DB application.

Oracle Rest API integration(consumer) Packages: APEX vs. Database Cloud APIs

The Oracle database provides different options to consume Restful services. As stated this article will cover the APEX_WEB_SERVICE and DBMS_CLOUD packages. The APEX_EXEC package also provides options for external service integration combined with Web Source definitions but not included in the comparison.

The APEX_WEB_SERVICE, compare to DBMS_CLOUD package, provide similar implementation steps and practices with fewer required object definitions. Each has a set of functions allow you to make HTTP requests, obtain and save results.

DBMS_CLOUD has an additional set of functions supporting the following cloud services:

• Oracle Cloud Infrastructure

• Amazon Web Services (AWS)

• Azure Cloud

• Oracle Cloud Infrastructure Classic

To the database developer selecting a package may be a clear choice and the obvious decision point is the type of application to be developed. i.e. APEX package for APEX applications and DBMS_CLOUD for Autonomous DB applications.

To consume the ODA Rest APIs, each package requires credentials to be established (created) for access management. The credentials will be passed to the Rest Request to authenticate and authorize each call, referenced by its static ID, established when creating the credential.

Brief note on Oracle Cloud Infrastructure (OCI) Credentials

As ODA APIs are protected resources under Oracle Cloud Infrastructure (OCI), follow the OCI documentation under the Developer Resources -> Setup and Prerequisites -> Required Keys and OCIDs

Record these details

• User OCID

• User API Private Key

• User API Public Key fingerprint

• Tenant OCID

The OCI credential will be used via a Web Credential in the case of APEX_WEB_SERVICE or as a database cloud credential in the case of DBMS_CLOUD. The process of creating the credentials are unique and are not shared.

Although APEX has added support to reference a database credential for a web credential, I have not fully tested it yet.

About the APEX_WEB_SERVICE Package

The APEX_WEB_SERVICE API enables you to integrate other systems with Application Express by allowing you to interact with Web services anywhere you can use PL/SQL in your application.

The main advantage here is that this package is fully integrated into the APEX environment and can be used in combination with application & session variables, and accessible from any part of the application. The package is available to use out-of-the-box with no additional role assignments required.

Credentials are created via the application workspace, shared components as Web Credentials and stored at the workspace-level and therefore are visible in all applications in the same workspace. These credentials are not stored in the all_credentials table for the database instance.

Native support for OCI credentials has been available since 19c.

The following blog post: Better File Storage in Oracle Cloud provides a guide on created the OCI credentials (Identity setup step 5 and the Web Credentials for Oracle Cloud Infrastructure) and a great example of using the APEX_WEB_SERVICE package.

The static ID can be provided, or the default value can be referenced in the API calls.

The APEX_WEB_SERVICE provides the MAKE_REST_REQUEST function to invoke a RESTful style Web service supplying either name value pairs, a character-based payload or a binary payload and returning the response in a CLOB.

Headers are set using the G_REQUEST_HEADERS before the process that invokes the Web service.

Making a Rest Request with APEX_WEB_SERVICE.MAKE_REST_REQUEST

Making the Rest request is a simple process once the credentials have been established. The request will return a CLOB object with the full response and all request and response cookies and headers can be set and accessed through arrays {name,value} pairs.

Making Rest Requests sample template

DECLARE
    l_ODA_BASE_URL varchar2(4000) := <The base url to the rest APIs>;
    l_OCI_WEB_CREDENTIALS varchar2(40) := <The static ID of the credential>;
    -- variable to store the response
    l_response clob; 

BEGIN
    -- set any required headers for the request (suggested minimum required)
    apex_web_service.g_request_headers.delete();
    apex_web_service.g_request_headers(1).name := 'Content-Type';
    apex_web_service.g_request_headers(1).value := 'application/json';

    -- making the rest request, the response is stored in the clob variable
    l_response := apex_web_service.make_rest_request(
        p_url => G_ODA_BASE_URL || 'skills',
        p_http_method => 'GET',
        p_credential_static_id => :G_OCI_WEB_CREDENTIALS
    );
    -- response received is json and needs to be parsed
    dbms_output.put_line(l_response || CHR(10));
    dbms_output.put_line('*******' || CHR(10));
END;

About the DBMS_Cloud Package

(docRef: DB19c)

In the On Premise database the DBMS_CLOUD package is not pre-installed with Oracle Database. You need to manually install the DBMS_CLOUD package and also configure users or roles to use this package.

Once installed it provides a comprehensive support for working with data in Object Storage starting with Oracle Database 19c and later releases.

(docRef: ADB)

In the Autonomous Database documentation, it states that the interface support is not limited to Oracle Cloud nor Object Storage only but that it provides database routines for working with cloud resources. This article uses the Autonomous Transaction Database (ATP) to implement the Restful calls.

To run DBMS_CLOUD subprograms with a user other than ADMIN you need to grant EXECUTE privileges to that user.

GRANT EXECUTE ON DBMS_CLOUD TO adb_user;

Credentials are created via the CREATE_CREDENTIAL Procedure and stored in the all_credentials table for the database instance.

Native support for OCI credentials has been available since the release of the autonomous DB with this procedure. This procedure is overloaded to support different authentication types supported by the cloud vendor.

DBMS_CLOUD.CREATE_CREDENTIAL (
    credential_name IN VARCHAR2,
    user_ocid       IN VARCHAR2,
    tenancy_ocid    IN VARCHAR2,
    private_key     IN VARCHAR2,
    fingerprint     IN VARCHAR2);

The credential_name parameter will define the static ID for the credential that will be referenced for API calls.

The DBMS_CLOUD package SEND_REQUEST Function and Procedure provides a workflow for sending a cloud REST API request with arguments and the function returns a response code and payload returning the response in a DBMS_CLOUD_TYPES.resp object type.

Headers are passed along with call to the Web service as a parameter to the function. In the testing conducted the DBMS_CLOUD.SEND_REQUEST require a SHA256 encoded hash of the request body to be sent in the header along with the request.

Making a Rest Request with DBMS_CLOUD.SEND_REQUEST

Making the Rest request is a simple process once the credentials have been established. The request will return a CLOB object with the full response and all request and response cookies and headers can be set and accessed through arrays {name,value} pairs.

Making Rest Requests sample template

DECLARE
    l_ODA_BASE_URL varchar2(4000) := <The base url to the rest APIs>;
    l_OCI_WEB_CREDENTIALS varchar2(40) := <The static ID of the credential>;
    -- variable to store the response
    resp DBMS_CLOUD_TYPES.resp;

BEGIN
    -- set any required headers for the request (suggested minimum required)
    apex_web_service.g_request_headers.delete();
    resp := DBMS_CLOUD.send_request
    (
        credential_name => G_OCI_WEB_CREDENTIALS,
        uri =>  G_ODA_BASE_URL || 'skills/',
        method => DBMS_CLOUD.METHOD_GET
    );

    dbms_output.put_line(DBMS_CLOUD.get_response_text(resp)|| CHR(10));
    dbms_output.put_line('*******'|| CHR(10));
END;

ODA Rest Call Sequence

Software life-cycle of ODA applications and working with updated IDs

The starting point as shown is to retrieve the skill's ID and then the relevant dynamic entity ID(s) within the skill. Management of the IDs will need to be addressed as they will change, so hardcoding ID values is not suggested at all.

Situations that can change IDs

Getting Skill and Dynamic Entity IDs in APEX applications

I recommend using REST Data Source Synchronisation to store skill(s) and dynamic entities in local tables. This does not need to be run often as skills and dynamic entities tend to be fairly static.

Getting Skill and Dynamic Entity IDs manually

Your use case may require more control than the REST Data Source component. For those case integrating using once the response is in hand, it will need to be parsed.

The follow will demonstrate how to work with response using SQL/JSON native support.

DECLARE

    l_response clob;
    l_curr_oda_skill_id varchar2(2000);
    l_curr_oda_dynamic_entity_id varchar2(2000);
    l_curr_oda_dynamic_entity_name varchar2(2000);

    -- parsing json
    l_paths apex_t_varchar2;
    l_curr_version number := 0;
    l_arr_count number;

BEGIN
    -- A Sample JSON response
    l_response := '{"items":[{"id":"D7C33583-9C85-4D60-B90A-2746A5D16715","name":"ATP_ORDS_SAMPLE","version":"1.1","status":"DRAFT","platformVersion":"21.04","createdOn":"2021-05-11T13:34:11.894Z","fromSkillStore":false,"multilingualMode":"NATIVE","primaryLanguageTag":"en","nativeLanguageTags":["en"]},{"id":"B110F314-8C6F-489A-A9DC-82F25A59C020","name":"ATP_ORDS_SAMPLE","version":"1.0","status":"DRAFT","platformVersion":"20.06","createdOn":"2021-05-11T13:33:08.032Z","fromSkillStore":false}],"count":2,"offset":0,"limit":100,"hasMore":false,"links":[{"rel":"self","href":"https://oda-<myserver>-da2.data.digitalassistant.oci.oraclecloud.com/api/v1/skills?name=ATP_ORDS_SAMPLE"},{"rel":"canonical","href":"https://oda-<myserver>-da2.data.digitalassistant.oci.oraclecloud.com/api/v1/skills?name=ATP_ORDS_SAMPLE"}]}';

    dbms_output.put_line(l_response || CHR(10));
    dbms_output.put_line('*******' || CHR(10));

    -- Using JSON_Table to extract Skill ID and version for the latest version of the skill
    select id,version into l_curr_oda_skill_id, l_curr_version from (  
        select id,name,version from json_table(l_response, '$.items[*]'
        columns(
            id      varchar2(2000) path '$.id',
            name    varchar2(2000) path '$.name',
            version varchar2(2000) path '$.version'
            ) 
        ) jt
        order by version desc
    ) where rownum = 1; 

    /**
    *********************************************
    An Example using the APEX_JSON.PARSE function
    *********************************************
    apex_json.parse(l_response);
    l_arr_count := apex_json.get_count(p_path => 'items');
    dbms_output.put_line(l_arr_count);

    for i in 1 .. l_arr_count loop
        if l_curr_version < to_number(apex_json.get_varchar2(p_path => 'items[%d].version', p0 => i)) then
            l_curr_oda_skill_id := apex_json.get_varchar2(p_path =>'items[%d].id', p0 => i);
            l_curr_version := to_number(apex_json.get_varchar2(p_path => 'items[%d].version', p0 => i));
        end if;
    end loop;
    **/

    dbms_output.put_line('Highest skill id version is: ' || l_curr_oda_skill_id || 'version: ' || l_curr_version || CHR(10)); 
    dbms_output.put_line('*******' || CHR(10));

    -- Use the Skill ID to List the Dynamic Entities of the Skill
    -- Required variables => G_ODA_BASE_URL, G_OCI_WEB_CREDENTIALS
    l_response := apex_web_service.make_rest_request(
        p_url => G_ODA_BASE_URL || 'bots/' || l_curr_oda_skill_id ||'/dynamicEntities', 
        p_http_method => 'GET',
        p_credential_static_id => G_OCI_WEB_CREDENTIALS
    );

    dbms_output.put_line(l_response || CHR(10));
    dbms_output.put_line('*******' || CHR(10));

    -- Get the ID for the Dynamic Entity by its name passed in as a parameter => DYNAMIC_ENTITY_NAME
    select id, name  into l_curr_oda_dynamic_entity_id, l_curr_oda_dynamic_entity_name
    from json_table(l_response, '$.items[*]'
        columns(
            id      varchar2(2000) path '$.id',
            name    varchar2(2000) path '$.name'
            ) 
        ) jt
    where upper(name) = upper(:DYNAMIC_ENTITY_NAME ) ; 

    /**
    *********************************************
    An Example using the APEX_JSON.PARSE function
    *********************************************
    apex_json.parse( l_response); 
    l_paths := apex_json.find_paths_like (
        p_return_path => 'items[%]',
        p_subpath => '.name',
        p_value => :DYNAMIC_ENTITY_NAME 
    );

    for i in 1 .. l_paths.count loop
        l_curr_oda_dynamic_entity_id :=  apex_json.get_varchar2(p_path => l_paths(i) || '.id');
        l_curr_oda_dynamic_entity_name := apex_json.get_varchar2(p_path => l_paths(i) || '.name');
        dbms_output.put_line(l_curr_oda_dynamic_entity_name || CHR(10));
    end loop;

    dbms_output.put_line('Dynamic Entity id  is: ' || l_curr_oda_dynamic_entity_id);
    dbms_output.put_line('*******' || CHR(10));
    **/

    dbms_output.put_line('Dynamic Entity id  is: ' || l_curr_oda_dynamic_entity_id);
    dbms_output.put_line('*******' || CHR(10));

end;

Implementing ODA Dynamic Entity Rest APIs

The Dynamic Entities will be a 1:1 map to a table limited to the main identifying attribute required by the skill to assist in intent and resolving entities. From the data side each value in the list of values can equate to a single field in a tuple or column in a row.

ODA uses the value and a list of synonyms through its Natural Language Processor (NLP) to extract the entity values from the user's responses.

The data as a json_object from tables will be used in the Push Data to Request call.

Code Samples: Building Push Data Request JSON Body

DECLARE
    -- var to store json body raw text
    l_json_text varchar2(4000);
    -- cursor used to loop and groubp countries in region
    cursor c1 is select distinct (subregion) as region from countries order by subregion asc;
BEGIN

    -- Teams JSON_BODY text
    select json_object(
        'add' value json_arrayagg(
            json_object(
                'canonicalName' value name, 
                'synonyms' value json_array(project),
                'nativeLanguageTag' value 'en'
                )
            ) returning varchar2
        ) teams into l_json_text
    from teams;

    /* Response
      {"add":[{"canonicalName":"Oracle","synonyms":["Oracle OCI Hackathon"],"nativeLanguageTag":"en"}]}
    */

    -- Participants JSON_BODY text
    select json_object(
        'add' value json_arrayagg(
            json_object(
                'canonicalName' value first_name || ' ' || last_name,
                'synonyms' value json_array(
                    email_address || ':' || mobile_number),
                    'nativeLanguageTag' value 'en'
                    )
                ) returning varchar2
            ) participants into l_json_text
    from participants;

    /* Response
      {"add":[{"canonicalName":"Martine Richmann","synonyms":["info@integralstrategy.ch:"],"nativeLanguageTag":"en"},{"canonicalName":"Sydney Nurse","synonyms":["sydney.nurse@oracle.com:+41790000"],"nativeLanguageTag":"en"},{"canonicalName":"Jason Dowe","synonyms":["jason.dowe@orle.com:+61410000"],"nativeLanguageTag":"en"},{"canonicalName":"Lyn Fall","synonyms":["lyn.fall@oracle.com:+62421111"],"nativeLanguageTag":"en"},{"canonicalName":"Priscila Tieg","synonyms":["priscila.tieg@oracle.com:+353 833333"],"nativeLanguageTag":"en"}]}
    */

    -- Countries JSON_BODY text
    for i in c1 loop
        select json_object(
            'add' value json_arrayagg(
                json_object(
                    'canonicalName' value nvl(subregion,'Other'), 
                    'synonyms' value json_array( 
                        LISTAGG(name, ':') WITHIN GROUP (ORDER BY name asc)
                    ), 
                    'nativeLanguageTag' value 'en')
                ) returning varchar2
            ) regions into l_json_text
        FROM countries where nvl(subregion,'Other') = nvl(i.region,'Other') GROUP BY nvl(subregion,'Other') ;
    end loop;

    /* Response
      -- not all records shown for loop
        {"add":[{"canonicalName":"Central America","synonyms":["Belize:Costa Rica:El Salvador:Guatemala:Honduras:Mexico:Nicaragua:Panama"],"nativeLanguageTag":"en"}]}
    */

END;

Performing REST Calls to Update ODA with APEX_WEB_SERVICE.MAKE_REST_REQUEST

Boiler plate variable declaration

DECLARE
    -- json body object as request data
    l_json_body json_object_t;
    -- APEX_WEB_SERVICE.MAKE_REST_REQUEST response
    l_response clob;
    -- var to store json body raw text
    l_json_text varchar2(4000);
    -- var to store the current opened Push Request
    l_push_request_id varchar2(4000);
    -- var to store the ODA server instance Rest base URL
    G_ODA_BASE_URL varchar2(4000) := 'https://oda-<instance>.data.digitalassistant.oci.oraclecloud.com/api/v1/';
    -- var to store the Web Credential Static ID
    G_OCI_WEB_CREDENTIALS varchar2(40) := '<YOUR_WEB_CREDENTIAL_STATIC_ID';

BEGIN
  -- setting HTTP headers before the REST API call
    apex_web_service.g_request_headers.delete();
    apex_web_service.g_request_headers(1).name := 'Content-Type';
    apex_web_service.g_request_headers(1).value := 'application/json';

Retrieving Dynamic Entities

    -- GET DYNAMIC ENTITIES
    dbms_output.put_line('*******' || CHR(10));
    dbms_output.put_line(' GET DYNAMIC ENTITIES : GET' || CHR(10));

    l_response := apex_web_service.make_rest_request(
        p_url => G_ODA_BASE_URL || 'bots/' || :P20001_SKILL_ID ||'/dynamicEntities', -- :G_ODA_BASE_URL 
        p_http_method => 'GET',
        p_credential_static_id => G_OCI_WEB_CREDENTIALS
    );

    dbms_output.put_line(l_response || CHR(10));
    dbms_output.put_line('*******' || CHR(10));

Creating a Push Request

    -- Create Push requests
    dbms_output.put_line('*******' || CHR(10));
    dbms_output.put_line(' CREATE PUSH REQUEST : POST' || CHR(10));

    l_response := apex_web_service.make_rest_request(
        p_url => G_ODA_BASE_URL || 'bots/' || :P20001_SKILL_ID ||'/dynamicEntities/' || :P20001_DYNAMIC_ENTITY_ID || '/pushRequests',
        p_http_method => 'POST',
        p_credential_static_id => G_OCI_WEB_CREDENTIALS,
        p_body => to_clob(' ')
    );    
    --parse the response
    apex_json.parse(l_response);
    --get the Push Request ID
    l_push_request_id := apex_json.get_varchar2('id');

    dbms_output.put_line(l_push_request_id|| CHR(10));
    dbms_output.put_line('*******' || CHR(10));

Pushing Data to Push Request

    -- set the dynamic entity payload from table
    --  for challenges
    select json_object( 'add' value json_arrayagg( json_object('canonicalName' value title, 'synonyms' value json_array(trim(REGEXP_SUBSTR( title ,'(.*?)([[:space:]]|$)', 1, 2 ))),'nativeLanguageTag' value 'en')) returning varchar2) challenges into l_json_text from content where type = 'challenges';

    --set the Data to Push to the Request
    l_json_body :=  JSON_OBJECT_T(l_json_text);

    l_response := apex_web_service.make_rest_request(
        p_url => G_ODA_BASE_URL || 'bots/' || :P20001_SKILL_ID ||'/dynamicEntities/' || :P20001_DYNAMIC_ENTITY_ID || '/pushRequests/' || l_push_request_id || '/values',
        p_http_method => 'PATCH',
        p_body => l_json_body.to_clob(),
        p_credential_static_id => G_OCI_WEB_CREDENTIALS
    );

    dbms_output.put_line(l_response || CHR(10));
    dbms_output.put_line('*******' || CHR(10));

Finalising the Push Request

    -- FINALIZE PUSH REQUEST
    dbms_output.put_line('*******' || CHR(10));
    dbms_output.put_line('FINALIZE PUSH REQUEST : PUT' || CHR(10));

    l_response := apex_web_service.make_rest_request(
        p_url => G_ODA_BASE_URL || 'bots/' || :P20001_SKILL_ID ||'/dynamicEntities/' || :P20001_DYNAMIC_ENTITY_ID || '/pushRequests/' || l_push_request_id || '/DONE',
        p_http_method => 'PUT',
        p_credential_static_id => G_OCI_WEB_CREDENTIALS,
        p_body => to_clob(' ')
  );

    dbms_output.put_line(apex_web_service.g_status_code || CHR(10));
    dbms_output.put_line('*******' || CHR(10));

END;

Performing REST Calls to Update ODA with DBMS_CLOUD.SEND_REQUEST

Boiler plate variable declaration

DECLARE
    -- SHA256 Hash of Request Body
    l_body_hash VARCHAR2(4000);
    -- DBMS_CLOUD.SEND_REQUEST response
    resp DBMS_CLOUD_TYPES.resp;
    -- var to store json body raw text
    l_json_text varchar2(4000);
    -- var to store the current opened Push Request
    l_push_request_id varchar2(4000);
    -- var to store the ODA server instance Rest base URL
    G_ODA_BASE_URL varchar2(4000) := 'https://oda-<instance>.data.digitalassistant.oci.oraclecloud.com/api/v1/';
    -- var to store the Web Credential Static ID
    G_OCI_WEB_CREDENTIALS varchar2(40) := '<YOUR_WEB_CREDENTIAL_STATIC_ID';

BEGIN

Retrieving Dynamic Entities

    -- GET DYNAMIC ENTITIES
    dbms_output.put_line('*******' || CHR(10));
    dbms_output.put_line(' GET DYNAMIC ENTITIES : GET' || CHR(10));

    resp := DBMS_CLOUD.send_request
    (
        credential_name => G_OCI_WEB_CREDENTIALS,
        uri =>  G_ODA_BASE_URL || 'bots/' || :P20001_SKILL_ID ||'/dynamicEntities',
        method => DBMS_CLOUD.METHOD_GET,
        body => UTL_RAW.cast_to_raw('')
    );

    dbms_output.put_line(DBMS_CLOUD.get_response_text(resp)|| CHR(10));
    dbms_output.put_line('*******'|| CHR(10));

Creating a Push Request

    -- Create Push requests
    dbms_output.put_line('*******' || CHR(10));
    dbms_output.put_line(' CREATE PUSH REQUEST : POST' || CHR(10));

    -- Needed to sign request
    l_body_hash := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_CRYPTO.hash(UTL_RAW.cast_to_raw(' '), DBMS_CRYPTO.HASH_SH256)));

    resp := DBMS_CLOUD.send_request
    (
        credential_name => G_OCI_WEB_CREDENTIALS,
        uri =>  G_ODA_BASE_URL || 'bots/' || :P20001_SKILL_ID ||'/dynamicEntities/' || :P20001_DYNAMIC_ENTITY_ID || '/pushRequests',
        method => DBMS_CLOUD.METHOD_POST,
        body => UTL_RAW.cast_to_raw(' '),
        headers => JSON_OBJECT('x-content-sha256' value l_body_hash,'Content-Type' value 'application/json')
    );
    --parse the response
    apex_json.parse(DBMS_CLOUD.get_response_text(resp));
    --get the Push Request ID
    l_push_request_id := apex_json.get_varchar2('id');

    dbms_output.put_line(l_push_request_id || CHR(10));
    dbms_output.put_line('*******' || CHR(10));

Pushing Data to Push Request

    -- set the dynamic entity payload from table
    --  for challenges
    select json_object( 'add' value json_arrayagg( json_object('canonicalName' value title, 'synonyms' value json_array(trim(REGEXP_SUBSTR( title ,'(.*?)([[:space:]]|$)', 1, 2 ))),'nativeLanguageTag' value 'en')) returning varchar2) challenges into l_json_text from content where type = 'challenges';

    l_body_hash := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_CRYPTO.hash(UTL_RAW.cast_to_raw(l_json_text), DBMS_CRYPTO.HASH_SH256)));

    -- PUSH DATA to PUSH REQUEST
    dbms_output.put_line('*******'|| CHR(10));
    dbms_output.put_line('PUSH DATA to REQUEST : PATCH' || CHR(10));
    resp := DBMS_CLOUD.send_request
    (
        credential_name => G_OCI_WEB_CREDENTIALS,
        uri =>  G_ODA_BASE_URL || 'bots/' || :P20001_SKILL_ID ||'/dynamicEntities/' || :P20001_DYNAMIC_ENTITY_ID || '/pushRequests/' || l_push_request_id || '/values',
        method => DBMS_CLOUD.METHOD_PATCH, -- ADB Enhancement filed TBD
        body => UTL_RAW.cast_to_raw(l_json_text),
        headers => JSON_OBJECT('x-content-sha256' value l_body_hash,'Content-Type' value 'application/json')
    );

    dbms_output.put_line(DBMS_CLOUD.get_response_text(resp)|| CHR(10));
    dbms_output.put_line('*******' || CHR(10));

Finalising the Push Request

    -- FINALIZE PUSH REQUEST
    dbms_output.put_line('*******' || CHR(10));
    dbms_output.put_line('FINALIZE PUSH REQUEST : PUT' || CHR(10));

    l_body_hash := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_CRYPTO.hash(UTL_RAW.cast_to_raw(' '), DBMS_CRYPTO.HASH_SH256)));

    resp := DBMS_CLOUD.send_request
    (
        credential_name => G_OCI_WEB_CREDENTIALS,
        uri =>  G_ODA_BASE_URL || 'bots/' || :P20001_SKILL_ID ||'/dynamicEntities/' || :P20001_DYNAMIC_ENTITY_ID || '/pushRequests/' || l_push_request_id || '/DONE',
        method => DBMS_CLOUD.METHOD_PUT,
        body => UTL_RAW.cast_to_raw(' '),
        headers => JSON_OBJECT('x-content-sha256' value l_body_hash,'Content-Type' value 'application/json')
    );

    dbms_output.put_line('*******' || CHR(10));

END;