Getting OCI usage (consumption) with the REST API in PL/SQL

OCI REST usage API returns usage for an account. In this post we are explaining how to invoke from an autonomous database with PL/SQL code.

Method 1: DBMS_CLOUD package

First we create a credential

-- for oci rest api's we need a credential for signing key
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL (
credential_name => 'DEVCSCRED',
user_ocid => 'ocid1.user.oc1..aa...q',
tenancy_ocid => 'ocid1.tenancy.oc1..a...a',
private_key => 'MIIEv...BE=',
fingerprint => 'eb:...:71');
END;
/

Now we execute the method and put the result in JSON table:

-- example table for storing data
 CREATE TABLE metering
    (id RAW (16) NOT NULL,
     date_loaded TIMESTAMP WITH TIME ZONE,
     label varchar2(20),
     jsondata CLOB
     CONSTRAINT ensure_metering_json CHECK (jsondata IS JSON));
 set serveroutput on
 declare
     resp DBMS_CLOUD_TYPES.resp;
     region varchar2(32) := 'eu-frankfurt-1';
     endpoint varchar2(64) := 'https://usageapi.eu-frankfurt-1.oci.oraclecloud.com';
     mydata varchar2(32000);
 begin
     resp := DBMS_CLOUD.send_request(credential_name => 'DEVCSCRED',uri =>
         endpoint || '/20200107/usage',
         method => 'POST',
         body => UTL_RAW.cast_to_raw(JSON_OBJECT(
             'granularity' value 'HOURLY',
             'tenantId' value 'ocid1.tenancy.oc1..a...a',
             'timeUsageStarted' value '2020-11-18T00:00:00.000Z',
             'timeUsageEnded' value '2020-11-19T10:00:00.000Z'))
         );
     mydata :=  DBMS_CLOUD.get_response_text(resp);
     dbms_output.put_line('result: ' || mydata);
     INSERT INTO metering VALUES (SYS_GUID(), SYSTIMESTAMP, 'test', mydata );
     commit;
 end;

Finally let’s query some data:

select met.jsondata.items[1].timeUsageStarted,  met.jsondata.items[0].timeUsageEnded, met.jsondata.items[0].computedQuantity,  met.jsondata.items[0].computedAmount from metering met;

Method 2: DBMS_CLOUD_OCI_UA_USAGEAPI package

The code:

set serveroutput on
 declare
   status_code number ;
   req dbms_cloud_oci_usageapi_request_summarized_usages_details_t := dbms_cloud_oci_usageapi_request_summarized_usages_details_t();
   res dbms_cloud_oci_ua_usageapi_request_summarized_usages_response_t;
   mysummary dbms_cloud_oci_usageapi_usage_aggregation_t;
   myitems   dbms_cloud_oci_usageapi_usage_summary_tbl := dbms_cloud_oci_usageapi_usage_summary_tbl();
   myitem    dbms_cloud_oci_usageapi_usage_summary_t;
 begin
     req.tenant_id := 'ocid1.tenancy.oc1..a...a';
     req.time_usage_started := '15-12-2020-00:00:00Z'; 
     req.time_usage_ended := '16-12-2020-00:00:00Z';
     req.granularity := 'HOURLY';
   res := DBMS_CLOUD_OCI_UA_USAGEAPI.request_summarized_usages(req, null, null, null, 'eu-frankfurt-1', null, 'DEVCSCRED');
   dbms_output.put_line(res.status_code);
   --dbms_output.put_line(res.headers);
   mysummary := res.response_body;
   myitems := mysummary.items;
   FOR i IN myitems.FIRST .. myitems.LAST LOOP
dbms_output.put_line('---------');
         dbms_output.put_line('compartment_id: ' || myitems(i).compartment_id);
         dbms_output.put_line('compartment_name: ' || myitems(i).compartment_name);
         dbms_output.put_line('resource_name: ' || myitems(i).resource_name);
         dbms_output.put_line('weight: ' || myitems(i).weight);
         dbms_output.put_line('shape: ' || myitems(i).shape);
         dbms_output.put_line('sku_part_number: ' || myitems(i).sku_part_number);
         dbms_output.put_line('sku_name: ' || myitems(i).sku_name);
         dbms_output.put_line('unit: ' || myitems(i).unit);
         dbms_output.put_line('discount: ' || myitems(i).discount);
         dbms_output.put_line('list_rate: ' || myitems(i).list_rate);
         dbms_output.put_line('platform: ' || myitems(i).platform);
         dbms_output.put_line('time_usage_started: ' || myitems(i).time_usage_started);
         dbms_output.put_line('time_usage_ended: ' || myitems(i).time_usage_ended);
         dbms_output.put_line('computed_amount: ' || myitems(i).computed_amount);
         dbms_output.put_line('computed_quantity: ' || myitems(i).computed_quantity);
         dbms_output.put_line('overages_flag: ' || myitems(i).overages_flag);
         dbms_output.put_line('unit_price: ' || myitems(i).unit_price);
         dbms_output.put_line('subscription_id: ' || myitems(i).subscription_id);
         dbms_output.put_line('overage: ' || myitems(i).overage);
   END LOOP;
 end;
 /

The output:

That’s all, hope it helps! 🙂