Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
set serveroutput on
credential_name varchar2(200) := 'OCI$RESOURCE_PRINCIPAL';
region varchar2(200) := 'us-phoenix-1';
base_uri varchar2(200) := 'https://telemetry-ingestion.' || region|| '';
resp dbms_cloud_types.RESP;
-- Current PDB's OCID
-- select json_value(cloud_identity , '$.DATABASE_OCID') from v$pdbs
-- Crate Dynamic Group "klrice-testing"
-- = 'ocid1.autonomousdatabase.oc1.phx.abcxyz'
-- Policy
-- Allow dynamic-group klrice-testing to use metrics in compartment dbtools-dev
-- Flush the RP to reload policy
-- dbms_cloud_admin.disable_resource_principal;
-- dbms_cloud_admin.enable_resource_principal;
for r in (
with q as (
/* MAIN QUERY for metrics Should only need to adjust this section to adhere to this structure
Expected Columns:
NAMESPACE - The namespace of the metric : 'my_namespace'
COMPARTMENT_ID - The OCID of the compartment to post the metric : 'ocid1.compartment....'
RESOURCE_GROUP - The resource group of the metric : 'my_resource_group'
NAME - The name of the metric : 'my_metric_name'
DIMENSIONS - The dimensions of the metric in json format : '{"factioid":"this","other":"else"}'
VALUE - The numberic value of the metric : 123.45
select 'a_namespace' namespace,
'ocid1.compartment.oc1..aaaaaaaacw2ft7eu33tlaoppsu6mck7qn2wsqefuixcjhza6xhhsbnhvjorq' compartment_id,
'sample_resource_group' resource_group,
'{"resourceId":"ocid1.exampleresource.region1.phx.exampleuniqueID","appName":"myAppA"}' dimensions,
count(1) value
from v$session
group by 1,2,3,4,5
/* END MAIN QUERY for metrics */
/* format to oci metric payload */
select json_object('metricData' value json_array(
json_object('namespace' value q.namespace,
'compartmentId' value q.compartment_id,
'resourceGroup' value q.resource_group,
'name' value,
'dimensions' value q.dimensions FORMAT JSON,
'datapoints' value json_array(json_object(
'timestamp' value to_char(systimestamp, 'YYYY-MM-DD"T"HH24:mm:ss"Z"'),
'value' value q.value
)) metric
from q
) loop
resp := dbms_cloud.send_request(
credential_name => credential_name,
uri => base_uri,
headers => JSON_OBJECT('Content-Type' value 'application/json'),
body => UTL_RAW.cast_to_raw(to_char(r.metric))
end loop;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment