EXERCISING WITH API REST CALLS AND JSON DATA IN AN AUTONOMOUS DATABASE BY DEVELOPING A PROCEDURE SCHEDULED TO BE EXECUTED DAILY THAT COLLECTS CONSUMPTION DATA IN A TABLE FOR REPORTING OR OTHER PURPOSES


In this post we depict a solution developed completely in the database, you can find the code here.

Following a short explanation of the different parts of the solution.

PREREQS

Execute as admin:

GRANT EXECUTE ON DBMS_CLOUD TO <youruserschema>;

DATABASE TABLE

In this case we have opted for a classic table because what we finally pursue is to evaluate some data and numeric values

CREATE TABLE MYOCICOSTS(     "STARTTIME" TIMESTAMP ,  "ENDTIME" TIMESTAMP,  "PRICEPEROCPU" NUMBER(6,4),  "OFICIALOCPUQUANTITY" NUMBER(5,1),  "AMOUNT" NUMBER(8,4),  "REMARKS" VARCHAR2(160 BYTE) COLLATE "USING_NLS_COMP",   PRIMARY KEY ("STARTTIME")    )  TABLESPACE "DATA" ;

Then we try to drop the credential in case it already exist…

execute DBMS_CLOUD.DROP_CREDENTIAL (credential_name => 'OCICOSTSCRED');

LOGIC

The logic is implemented in a PL/SQL package. In the header you must provide your own data corresponding to your tenancy, user and the like in order to be able to execute API REST calls with the proper credentials. Notice the days_back variable, because it is important in order to define the window to gather data because data may not be ready for the latest moments of our lives…

CREATE OR REPLACE PACKAGE oci_cloud_cost AS 
   procedure getcosts; 
   endpoint varchar2(128) := 'https://usageapi.eu-frankfurt-1.oci.oraclecloud.com';
   method varchar2(64) := '/20200107/usage';
   -- put valid cfedentials here
   userocid varchar2(128) :=  'ocid1.user.oc1..aa...q';
   tenancyocid varchar2(128) := 'ocid1.tenancy.oc1..a...a';
   privatekey varchar2(4000) := 'MIIE...TBE=';
   fingerprint varchar2(64) := 'e...1';
   days_back number := 3; -- number of days before today to get data
end oci_cloud_cost;

In the body is the logic of the solution: create credential, define the start and end date, call the REST endpoint, parse JSON array result into pieces, get data form each item and insert data into table.

-- the logic is get the data from midnight the days we configure in days_baxk before today and midnight 1 day before
CREATE OR REPLACE PACKAGE BODY oci_cloud_cost AS 
procedure getcosts is
        resp DBMS_CLOUD_TYPES.resp;
        mydata varchar2(32000);
        po_obj        JSON_OBJECT_T;
        li_arr        JSON_ARRAY_T;
        li_item       JSON_ELEMENT_T;
        li_obj        JSON_OBJECT_T;
        timeUsageStarted timestamp;
        timeUsageEnded timestamp;
        computedQuantity number;
        computedAmount number;
        comienzo varchar2(64);
        fin varchar2(64);
    begin
        -- creating the credential if it doesn't exist
        begin
            DBMS_CLOUD.CREATE_CREDENTIAL (credential_name => 'OCICOSTSCRED',
                user_ocid => userocid, tenancy_ocid => tenancyocid, private_key => privatekey, fingerprint => fingerprint);
            exception
                when others then
                    null;
        end;
        -- the day before yesterday
        comienzo := to_char(sysdate - days_back , 'YYYY') || '-' || to_char(sysdate - days_back , 'MM') || '-' || to_char(sysdate - days_back , 'DD') || 'T00:00:00.000Z';
        -- yesterday
        fin := to_char(sysdate - days_back + 1, 'YYYY') || '-' || to_char(sysdate - days_back + 1, 'MM') || '-' || to_char(sysdate - days_back + 1, 'DD') || 'T00:00:00.000Z';
        --dbms_output.put_line('comienzo: ' || comienzo);
        --dbms_output.put_line('fin: ' || fin);
        -- rest call
        resp := DBMS_CLOUD.send_request(credential_name => 'OCICOSTSCRED',uri =>
            endpoint || method , method => 'POST',
            body => UTL_RAW.cast_to_raw(JSON_OBJECT(
                'granularity' value 'HOURLY',
                'tenantId' value tenancyocid,
                'timeUsageStarted' value comienzo,
                'timeUsageEnded' value fin)) );
        -- get json result
        mydata :=  DBMS_CLOUD.get_response_text(resp);
        --dbms_output.put_line('result: ' || mydata);
        po_obj := JSON_OBJECT_T.parse(mydata);
        li_arr := po_obj.get_Array('items');
        -- iterate items in json
        FOR i IN 0 .. li_arr.get_size - 1 LOOP
            li_obj := JSON_OBJECT_T(li_arr.get(i));
            timeUsageStarted := li_obj.get_Timestamp('timeUsageStarted');
            timeUsageEnded := li_obj.get_Timestamp('timeUsageEnded');
            computedQuantity := li_obj.get_Number('computedQuantity');
            computedAmount := li_obj.get_Number('computedAmount');
            --dbms_output.put_line(timeUsageStarted || ' ' || timeUsageEnded || ' ' || computedQuantity || ' ' || computedAmount);
            begin
                -- insert values in table
                insert into MYOCICOSTS values ( timeUsageStarted, timeUsageEnded , 0.2101, computedQuantity, computedAmount, null );
                commit;
            exception
                when others then
                    -- duplicates exist in json data
                    insert into MYOCICOSTS values ( systimestamp, systimestamp , 0, 0, 0, 'duplicate' );
            end;
        END LOOP;
    exception
        when others then
            raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
    end;
end oci_cloud_cost;
/

SCHEDULING THE JOB

We create a daily job that executes the procedure created in the package, the we enable it and finally we query the scheduler view to see if everything is ok

-- drop the schedule if it already exist
BEGIN
    DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'OCICOSTS');
END;
/
-- create daily schedule
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'OCICOSTS',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'ADMIN.OCI_CLOUD_COST.GETCOSTS',
   repeat_interval    =>  'FREQ=DAILY;INTERVAL=1',
   auto_drop          =>   FALSE,
   comments           =>  'my oci costs');
END;
/
-- enable checdule
BEGIN
    DBMS_SCHEDULER.ENABLE('OCICOSTS');
END;
/
-- check whether the schedule is ok or not
SELECT
    JOB_NAME,
    ENABLED,
    RUN_COUNT,
    REPEAT_INTERVAL,
    STATE,
    MAX_FAILURES,
    LAST_START_DATE,
    LAST_RUN_DURATION
FROM
    DBA_SCHEDULER_JOBS
WHERE
    JOB_NAME = 'OCICOSTS'
/

TESTING IT ALL

Final bytes are for testing the stuff. Clean the table, execute the procedure and see if data appears in it.

--
-- test the procedure, some data should appear in the table
--
-- cleansing ...
delete from myocicosts;
/
commit;
/
-- test
begin
     oci_cloud_cost.getcosts;
end;
/
--query
select * from myocicosts where remarks is null order by starttime asc
/

That¡s all, hope it helps! 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.