Information, tips, tricks and sample code for Big Data Warehousing in an autonomous, cloud-driven world

Autonomous Database adds PL/SQL to its arsenal of OCI native SDKs

Nilay Panchal
Senior Product Manager

Earlier this year I introduced our new functionality in Autonomous Database on Shared infrastructure (ADB-S) which made it possible to call most cloud REST APIs by simply running scripts right in your database, without the need to stand up your own server. We now went the extra mile and built a familiar, native OCI PL/SQL SDK over the previous generic DBMS_CLOUD.SEND_REQUEST procedures. This adds PL/SQL to the long list of native SDKs for Oracle Cloud Infrastructure (OCI).

This SDK enables a user to call PL/SQL procedures, as you would in your database scripts, that invoke any OCI REST API. This is powerful as it allows you to consolidate parts of data pipeline and business logic, including creating and managing object storage data, spinning up virtual machines and autonomous databases, invoking serverless Oracle Functions, managing your data streams and everything in between!

Let's jump into our SQL Developer Web worksheet and have a look at what this looks like when put into action. In my examples below, we will create a bucket in the object store that can hold data, and later call an Oracle Function, all using simple PL/SQL procedures.

If you don't already have an Autonomous Database created, follow Lab 1 in our quickstart tutorial. It only takes a few minutes!


Create a native OCI credential

For authorized secure calls to OCI REST APIs, we must first create a native OCI credential using a public and private key. If you are unfamiliar with creating credentials, follow my colleague Can's easy guide to creating a secure access OCI credential. Here, I create an OCI native credential "SDK_CRED".

    credential_name => 'SDK_CRED',
    user_ocid       => 'ocid1.user.oc1....',
    tenancy_ocid    => 'ocid1.tenancy.oc1....',
    private_key     => 'MIIE.....',
    fingerprint     => 'f2:db:d9:18:a4:aa:fc:83:f4:f6:6c:39:96:16:aa:27'


Call the relevant DBMS_CLOUD_OCI_*   PL/SQL SDK package for OCI Object Storage

When using this SDK over OCI REST APIs, a general guideline for your block of code will be:

  1. Identify the named SDK package that relates to the OCI resource on which you want to perform a REST API call
  2. Declare a response object as defined in the package, and it's corresponding response body object with type as specified in the response object type definition.
  3. Declare and set any request object and its parameters, if one needs to be sent along with your intended SDK function call ("bucketdetails" needs to be set, in our example below)
  4. Call the relevant SDK function for the action you want to perform, passing in values for the required parameters. You will pass in any request objects created in Step 3 over here.
  5. Set the response object to retrieve the response from your SDK function call. Query this response object for the status and response information from your function call.

With this, we proceed to use types and functions in the DBMS_CLOUD_OCI_OBS_OBJECT_STORAGE package from the PL/SQL SDK. In the script below, we will create a new bucket in our object store named "examplebucketfordata" in the Toronto region.

If you are unfamiliar with storing data in the cloud: A bucket is a container that can store your small or large data files (analogous to a folder on your computer where you store files). Read more about cloud object storage and buckets here.

Refer to the screens below for information on where to find your necessary resource details.


set serveroutput on
  l_type_status  PLS_INTEGER;
  resp_body      dbms_cloud_oci_obs_object_storage_bucket_t;
  response       dbms_cloud_oci_obs_object_storage_create_bucket_response_t;
  bucket_details dbms_cloud_oci_obs_object_storage_create_bucket_details_t;
  l_json_obj     json_object_t;
  l_keys         json_key_list;
  bucket_details := dbms_cloud_oci_obs_object_storage_create_bucket_details_t();
  bucket_details.name := 'examplebucketfordata'; 
  bucket_details.compartment_id := 'ocid1.compartment.oc1...';
  --Note the use of the native SDK function create_bucket
  response := dbms_cloud_oci_obs_object_storage.create_bucket(
                namespace_name => 'adwc4pm',
                opc_client_request_id => 'xxxxxxxxx',
                create_bucket_details => bucket_details,
                credential_name => 'SDK_CRED',
                region => 'ca-toronto-1');
  resp_body := response.response_body;
  -- Response Headers
  dbms_output.put_line('Headers: ' || CHR(10) ||'------------');
  l_json_obj := response.headers;
  l_keys := l_json_obj.get_keys;
  for i IN 1..l_keys.count loop
  end loop;
  -- Response status code
  dbms_output.put_line('Status Code: ' || CHR(10) || '------------' || CHR(10) ||      response.status_code);


  • You can find your compartment OCID by sliding out the left pane menu and going to Identity -> Compartments -> Your compartment


I have simplified the output below however, notice that you receive a complete response header and body, as is expected in a REST API call, which you can use to navigate for status codes, resource location and other necessary information for your application's logic. 


After running this script and getting a success response code (200), we now have a newly created bucket in your object store! You can now proceed to write similar scripts with the DBMS_CLOUD package to upload data and manage your files in your bucket.



Conclusion: Revisiting our friendly ol' Oracle Function example

As we conclude, let us rewrite our previous Oracle Function example, in which we had used the generic DBMS_CLOUD.SEND_REQUEST for our REST API call, to invoke a simple Oracle Function that prints "Hello <Name>". We use the DBMS_CLOUD_OCI_FNC_FUNCTIONS_INVOKE package and provide our Oracle Function "fndemo" OCID, which can be found on your Function's console page, alongside its invoke endpoint.

While the generic function is still of good use to call other cloud platform REST APIs, notice that the new SDK uses native PL/SQL objects for parameters and responses, improving your code quality and reducing the margin for error. This is great example of how things are constantly evolving toward becoming simpler in the world of Autonomous!

  resp_body      blob;
  response       dbms_cloud_oci_fnc_functions_invoke_invoke_function_response_t;   
  l_json_obj     json_object_t;
  l_keys         json_key_list;
    -- Note the use of the native SDK function invoke_function
  response := dbms_cloud_oci_fnc_functions_invoke.invoke_function(
                function_id => 'ocid1.tenancy.oc2...',
                invoke_function_body => UTL_RAW.cast_to_raw('Nilay'),
                credential_name => 'SDK_CRED',
                region => 'ca-toronto-1');
  resp_body := response.response_body;
  -- Response status code
  dbms_output.put_line('Status Code: ' || CHR(10) || '------------' || CHR(10) ||       response.status_code);


Like what I write? Follow me on the Twitter! 🐦


Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.