Oracle OCI PL/SQL SDK


OCI PL/SQL SDK is one more of the different API’s that we have to execute a bunch of OCI features with code, in this case from inside the database. We can imagine a lot of capabilities that we can add to the logic such as send notifications, scale up/down another component in the stack, offload data to object storage, and many more…

Let’s see an example

First, we need a credential for the signing key mechanism to work properly, change the data in bold with your own credential info:

BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL (
credential_name => 'INVSTREAMPL',
user_ocid => 'ocid1.user.oc1..aaaaaa...akpa',
tenancy_ocid => 'ocid1.tenancy.oc1..aaaaaa...kcwua',
private_key => 'MIIEpAIBAAKCAQEA...60GVdeBFw==',
fingerprint => 'b7:3...2:6a');
END;

Let’s create an object storage bucket, put your own info in the code remarked in bold:

 -- ######################
 -- ## Create bucket    ##
 -- ######################
 set serveroutput on
 declare
   response_body  dbms_cloud_oci_object_storage_bucket_t;
   response       dbms_cloud_oci_obs_object_storage_create_bucket_response_t;
   bucket_details dbms_cloud_oci_object_storage_create_bucket_details_t;
   json_obj       json_object_t;
   l_keys         json_key_list;
 begin
 bucket_details :=dbms_cloud_oci_object_storage_create_bucket_details_t();
   bucket_details.name := 'bucketname';
   bucket_details.compartment_id := 'ocid1.compartment.oc1..aaaaa...swq6fa';
 response := dbms_cloud_oci_obs_object_storage.create_bucket(
                 namespace_name => 'inv...vd',
                 opc_client_request_id => 'random-request-id',
                 create_bucket_details => bucket_details,
                 credential_name => 'INVSTREAMPL',
                 region => 'eu-frankfurt-1');
 response_body := response.response_body;
 -- Response Headers
   dbms_output.put_line('Headers: ' || CHR(10) ||'------------');
   json_obj := response.headers;
   l_keys := json_obj.get_keys;
   for i IN 1..l_keys.count loop
      dbms_output.put_line(l_keys(i)||':'||json_obj.get(l_keys(i)).to_string);
   end loop;
 -- Response status code
   dbms_output.put_line('Status Code: ' || CHR(10) || '------------' || CHR(10) || response.status_code);
   dbms_output.put_line(CHR(10));
 -- Response body
   dbms_output.put_line(response_body.namespace);
   dbms_output.put_line(response_body.name);
   dbms_output.put_line(response_body.compartment_id);
   dbms_output.put_line(response_body.metadata.to_string());
   dbms_output.put_line(response_body.created_by);
   dbms_output.put_line(response_body.time_created);
   dbms_output.put_line(response_body.approximate_count);
   dbms_output.put_line(response_body.approximate_size);
   dbms_output.put_line(response_body.etag);
   dbms_output.put_line(response_body.public_access_type);
   dbms_output.put_line(response_body.storage_tier);
   dbms_output.put_line(response_body.freeform_tags.to_string());
   dbms_output.put_line(response_body.defined_tags.to_string());
   dbms_output.put_line(response_body.kms_key_id);
   dbms_output.put_line(response_body.object_lifecycle_policy_etag);
   dbms_output.put_line(response_body.id);
 end;
 /

And here is it!

Headers: 
 Content-Type:"application/json"
 location:"https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/in...vd/b/bucketname"
 etag:"13d39cc9-9a03-420f-9381-f6bdee0c5a49"
 connection:"close"
 Content-Length:"762"
 opc-client-request-id:"random-request-id"
 date:"Fri, 18 Dec 2020 16:52:46 GMT"
 opc-request-id:"fra-1:YCZXfWhmPA9Dw94qX9d4XZRN_K6q_-zFjL1YT_7yAOLHtqpWKyJun10KEqckCIyB"
 x-api-id:"native"
 access-control-allow-origin:"*"
 access-control-allow-methods:"POST,PUT,GET,HEAD,DELETE,OPTIONS"
 access-control-allow-credentials:"true"
 access-control-expose-headers:"access-control-allow-credentials,access-control-allow-methods,access-control-allow-origin,connection,content-length,content-type,date,etag,location,opc-client-info,opc-client-request-id,opc-request-id,x-api-id"
 Status Code: 
 200
 in...d
 bucketname
 ocid1.compartment.oc1..aaaaaa...6fa
 {}
 ocid1.user.oc1..aaaaaa...akpa
 18/12/20 16:52:46,119000 EUROPE/MADRID
 13d39cc9-9a03-420f-9381-f6bdee0c5a49
 NoPublicAccess
 Standard
 {}
 {}
 ocid1.bucket.oc1.eu-frankfurt-1.aaaaaaa...p3gzla
 Procedimiento PL/SQL terminado correctamente.

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.