19 Using and Managing a Cloud Code Repository with Autonomous Database

Autonomous Database provides routines to manage and store files in Cloud Code (Git) Repositories. The supported Cloud Code Repositories are: GitHub, AWS CodeCommit, and Azure Repos.

About Cloud Code Repositories with Autonomous Database

The DBMS_CLOUD_REPO package provides a single interface for accessing a Cloud Code Repository from Autonomous Database.

The supported Cloud Code Repositories provide the following features:

  • Git Version Control System: Git is software for tracking changes in any set of files, usually used for coordinating work among programmers collaboratively developing source code during software development. Its goals include speed, data integrity, and support for distributed, non-linear workflows.

  • Git Repository: A Git repository is a virtual storage of your project. It allows you to save versions of your code, which you can access when needed.

The DBMS_CLOUD_REPO APIs use a repository handle (REPO object). The repository handle is an opaque JSON object that represents a Cloud Code Repository of a specific cloud provider. A REPO object can be passed to different DBMS_CLOUD_REPO APIs. This opaque object ensures that DBMS_CLOUD_REPO procedures and functions are multicloud compatible; you do not have to change your code when you migrate from one Cloud Code Repository provider to another Cloud Code Repository.

Autonomous Database provides the following to help you work with Cloud Code Repositories:

Initialize a Cloud Code Repository

The DBMS_CLOUD_REPO initialization routines initialize a Cloud Code Repository. After you obtain a Cloud Code Repository handle, you use the handle to access the Cloud Code Repository.

To initialize a Cloud Code Repository:

  1. Create a credential to access the Cloud Code Repository.

    See CREATE_CREDENTIAL Procedure for information on creating credentials.

  2. Depending on the repository, GitHub, Azure Repos, or AWS CodeCommit, call DBMS_CLOUD_REPO.INIT_REPO with the parameters for the particular repository to obtain a repository handle.

    The following examples provide samples for each supported Cloud Code Repository.

    • GitHub Initialization:

      DEFINE repo_name='test_repo';
      DEFINE cred_name='GITHUB_CRED';
      VAR repo clob
      BEGIN
        :repo := DBMS_CLOUD_REPO.INIT_REPO(
                      params => JSON_OBJECT('provider' value 'github',
                                            'repo_name' value '&repo_name',
                                            'credential_name' value '&cred_name',
                                            'owner'           value '<myuser>')
                );
      END;
      /
      
    • Azure Repos Initialization:

      DEFINE repo_name='test_repo';
      DEFINE cred_name='AZURE_REPO_CRED';
      VAR repo clob
      BEGIN
        :repo := DBMS_CLOUD_REPO.INIT_REPO(
                      params => JSON_OBJECT('provider' value 'azure',
                                            'repo_name' value '&repo_name',
                                            'credential_name' value '&cred_name',
                                            'organization' value '<myorg>',
                                            'project' value '<myproject>')
                );
      END;
      /
    • AWS CodeCommit Initialization:

      DEFINE repo_name='test_repo';
      DEFINE cred_name='AWS_REPO_CRED';
      VAR repo clob
      BEGIN
        :repo := DBMS_CLOUD_REPO.INIT_REPO(
                      params => JSON_OBJECT('provider' value 'aws',
                                            'repo_name' value '&repo_name',
                                            'credential_name' value '&cred_name',
                                            'region' value 'us-east-1')
                );
      END;
      /

See DBMS_CLOUD_REPO Initialization Operations for details on the initialization functions.

Create and Manage a Cloud Code Repository

The DBMS_CLOUD_REPO management routines allow you to manage a Cloud Code Repository by creating, listing, updating, or deleting a repository.

First, obtain a Cloud Code Repository handle to provide access a repository. See Initialize a Cloud Code Repository for details.

  1. To create a repository:
    VAR repo clob
    BEGIN
      DBMS_CLOUD_REPO.CREATE_REPOSITORY(
            repo => :repo,
            description => 'test repo'
      );
    END;
    /
  2. To update a repository:
    
    VAR repo clob
    DEFINE repo_name='test_repo';
    BEGIN
      DBMS_CLOUD_REPO.UPDATE_REPOSITORY(
            repo => :repo,
            new_name => '&repo_name' || '_new'
      );
    END;
    /
  3. To list repositories:
    col id format a30
    col name format a10
    col description format a15
    select id, name, bytes, private, description from
      DBMS_CLOUD_REPO.LIST_REPOSITORIES(:repo);
    
  4. To delete a repository:
    VAR repo clob
    BEGIN
      DBMS_CLOUD_REPO.DELETE_REPOSITORY(
            repo => :repo
      );
    END;
    /

Use File Operations with a Cloud Code Repository

The DBMS_CLOUD_REPO file operations allow you to create, get, list, update, or delete files in a Cloud Code Repository.

Obtain a Cloud Code Repository handle before using the file operations. See Initialize a Cloud Code Repository for details.

You also need to create a repository before you work with files. See Create and Manage a Cloud Code Repository for details.

  1. To get a file:
    
    SELECT DBMS_CLOUD_REPO.GET_FILE(repo => :repo, file_path => 'test1.sql')
    
  2. To create a file:
    VAR repo clob
    BEGIN
      DBMS_CLOUD_REPO.PUT_FILE(
            repo => :repo,
            file_path => 'test1.sql',
            contents => UTL_RAW.cast_to_raw('create table t1 (x varchar2(30))' || CHR(10) || '/')
      );
    END;
    /
  3. To update a file:
    VAR repo clob
    BEGIN
      DBMS_CLOUD_REPO.PUT_FILE(
            repo => :repo,
            file_path => 'test1.sql',
            contents => UTL_RAW.cast_to_raw('create table t2 (x varchar2(30))' || CHR(10) || '/')
      );
    END;
    /
  4. To list files:
    SELECT id, name, bytes, url FROM DBMS_CLOUD_REPO.LIST_FILES(repo => :repo);
    
  5. To delete a file:
    VAR repo clob
    BEGIN
      DBMS_CLOUD_REPO.DELETE_FILE(
            repo => :repo,
            file_path => 'test1.sql'
      );
    END;
    /

See DBMS_CLOUD_REPO File Operations for more information.

Use SQL Install Operations with a Cloud Code Repository

The DBMS_CLOUD_REPO SQL Install operations allow you to store and download SQL scripts from a Cloud Code Repository.

Obtain a Cloud Code Repository handle before using the SQL Install operations. See Initialize a Cloud Code Repository for details.

You also need to create a repository before you work with SQL Install operations. See Create and Manage a Cloud Code Repository for details.

The scripts are intended as schema install scripts and not as generic SQL scripts:

  • Scripts cannot contain SQL*Plus client specific commands.
  • Scripts cannot contain bind variables or parameterized scripts.
  • SQL statements must be terminated with a slash on a new line (/).
  • Scripts can contain DDL, DML PLSQL statements, but direct SELECT statements are not supported. Using SELECT within a PL/SQL block is supported.

Any SQL statement that can be run using EXECUTE IMMEDIATE will work if it does not contain bind variables or defines.

  1. To upload DDL metadata to a Cloud Code Repository:
    VAR repo clob
    BEGIN
      DBMS_CLOUD_REPO.EXPORT_OBJECT(
            repo => :repo,
            object_type => 'PACKAGE',
            object_name => 'MYPACK',
            file_path   => 'mypack.sql'
      );
    END;
    /
  2. To install SQL statements from a file:
    VAR repo clob
    BEGIN
      DBMS_CLOUD_REPO.INSTALL_FILE(
            repo => :repo,
            file_path     => 'test3.sql',
            stop_on_error => FALSE
      );
    END;
    /
  3. To install SQL statements from a buffer::
    VAR repo clob
    BEGIN
      DBMS_CLOUD_REPO.INSTALL_SQL(
            repo => :repo,
            content   => 'create table t1 (x varchar2(30))' || CHR(10) || '/',
            stop_on_error => FALSE
      );
    END;
    /

See DBMS_CLOUD_REPO SQL Install Operations for more information.