Table of Contents

Introduction

Days when a single programmer could develop an entire system are long gone. Most non-trivial applications are too complex and large to be developed by a single person. Collaboration between members of a development team is therefore a necessity. This is the reason why various code management solutions have become so popular. This approach to collaboration is of course also true for database developers. This is why Oracle Database has introduced a functionality to easily use popular cloud code repositories such as GitHub.
Implemented by the package DBMS_CLOUD_REPO it’s out-of-the-box available in Autonomous Databases – 23ai or 19c. For 23.7 ODA or Exadata releases you can easily install it with the following steps described here. For other database releases such as 19c or 21c you can install the dbms_cloud package family using the MOS note How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1)

Usage

To use this functionality we need to meet the following requirements:

  • an account in one of the following cloud code repository systems: GitHub, AWS Code Commit or Azure Repo
  • an Oracle database with DBMS_CLOUD_REPO pre-installed on ADB or installed (see links above)

Although in this article we will describe how to use GitHub, the whole procedure looks similar for other supported repo systems as well, and – in general – consists of the following steps:

1. To use a repository directly from a database, we need to get authentication credentials. In case of GitHub it is a pair consisting of a username and an authentication token. The procedure of generating the token is described in GitHub documentation and can be found here. It is very important to remember about appropriate level of privileges assigned to the token, like access to all repositories, metadata etc. Otherwise we will not be able to perform some additional operations.

2. In the next step we need to create a credential object. To do this, we need to use DBMS_CLOUD package in ADB or DBMS_CREDENTIAL in a non-autonomous database.

begin
  dbms_cloud.create_credential(
    credential_name => 'GITHUB_CREDENTIAL',
    username => 'witold.swierzy@oracle.com',
    password => '<access token>'
  );
end;
/

Of course we have to create a credential object only once – it can be used multiple times.

3. Now we can use the code repository. For example it is possible to download a sql script containing a PL/SQL programm and immediately execute it in the database:

declare
  l_repo_handle clob; -- CLOB object will contain a JSON document 
                      -- containing all the details required to connect to a GitHub account
begin
  -- below code initializes the connection to a GitHub account and repository
  l_repo_handle := dbms_cloud_repo.init_github_repo(
    credential_name => 'GITHUB_CREDENTIAL', 
    owner => 'witold-swierzy',                -- GitHub username 
    repo_name => 'sql_horizontal_aggregation' -- name of the repository
  );
  dbms_cloud_repo.install_file(      -- download and execute a SQL script
    repo => l_repo_handle,           -- CLOB connection descriptor
    file_path => 'json_arr.sql',     -- name of the file we want to download and execute
    stop_on_error => true            -- stop in case of any error 
  );
end;
/

The script json_arr.sql contains definition of a json_arr_vc aggregation function, so after its succesful installation we can start to use it immediately:

SQL> select department_id, json_arr_vc(last_name)
  2  from employees
  3* group by department_id;

   DEPARTMENT_ID JSON_ARR_VC(LAST_NAME)                          
________________ _______________________________________________ 
              10 ["Whalen"]                                      
              20 ["Hartstein","Be Charge"]                       
              50 ["Mourgos","Vargas","Matos","Davies","Rajs"]    
              60 ["Hunold","Lorentz","Ernst"]                    
              80 ["Zlotkey","Taylor","Abel"]                     
              90 ["King","De Haan","Kochhar"]                    
             110 ["Higgins","Gietz"]                             
                 ["Grant"]

4. DBMS_CLOUD_REPO package allows also for other actions, like, for example: creating a repository, pushing and commiting changes – in general – it offers partial to full functionality of git command line tool, but implemented in the form of a PL/SQL API.

Below example presents how to create the new repository plsql_demos and add/upload geocode procedure to it.

declare
    repo_handle clob;
begin
    -- first, we need to initalize the repository in the database
    -- the repository has not been created in our GitHub yet
    repo_handle:=dbms_cloud_repo.init_github_repo(
                                    credential_name => 'GITHUB_CREDENTIAL',    
                                    repo_name       => 'plsql_demos', -- new repository name
                                    owner           => 'witold-swierzy');

    -- below call creates the repository in our GitHub account                                            
    dbms_cloud_repo.create_repository(
                        repo        => repo_handle,
                        description => 'My test repo',
                        private     => false);
    ...

5.  Now we can push and commit the code of a procedure into the repository we created previously:

    ... 
    dbms_cloud_repo.export_object(
                       repo           => repo_handle,
                       file_path      => 'spatial/geocode.sql',
                       object_type    => 'PROCEDURE',
                       object_schema  => user,
                       object_name    => 'GEOCODE',
                       branch_name    => 'main',
                       commit_details => json_object('message' value 'geocoding wrapper procedure',
                                                     'author'  value 'Witold Swierzy',
                                                     'email'   value 'witold.swierzy@oracle.com' ),
                       append         => false );
end;
/

Summary

DBMS_CLOUD_REPO provides an easy and comfortable way of using cloud code repositories at a database level. It supports multiple platforms providing ability to choose this one, which is accepted as official solution inside of an organization. Thanks to this functionality modern development of data-centric applications is significantly easier allowing for collaboration of developers and providing new ways of protecting the code and its consistency in a multi-user environment.

Additional Resources