Oracle Cloud Infrastructure Documentation

Manage and Store Files in 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.

Parent topic: Develop

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;
/

See DBMS_CLOUD_REPO Repository Management Operations for more information.

Create and Manage Branches in a Cloud Code Repository

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

To perform Cloud Code Repository branch management operations you must first:

  1. To create a branch in a Cloud Code Repository:
    BEGIN
  DBMS_CLOUD_REPO.CREATE_BRANCH (
    repo            => l_repo,
    branch_name     => 'test_branch',
    parent_branch   => 'main'
    );
END;
/
  2. To delete a branch in a Cloud Code Repository:
    BEGIN
  DBMS_CLOUD_REPO.DELETE_BRANCH (
      repo        => l_repo,
      branch_name => 'test_branch'
  );
END;
/
  3. To merge a branch into another branch in a Cloud Code Repository:
    BEGIN
  DBMS_CLOUD_REPO.MERGE_BRANCH (
    repo               => l_repo,
    branch_name        => 'test_branch',
    target_branch_name => 'main'
    );
END;
/
  4. To list branches in a Cloud Code Repository:
    
SELECT * FROM DBMS_CLOUD_REPO.LIST_BRANCHES (repo => l_repo);
  5. You can list commits in a branch in Cloud Code Repository based on the following;

    • Repository

    • Branch

    • The file path of the branch

    • SHA/commit_id

      Note

      SHA is a checksum 40-character string composed of hexadecimal characters (0–9 and a–f).

    To list commits based on the repository:

    SELECT * FROM DBMS_CLOUD_REPO.LIST_COMMITS (
    repo => :repo);

    To list commits based on the repository, commit_id and file path of the branch:

    SELECT * FROM DBMS_CLOUD_REPO.LIST_COMMITS (
    repo        => l_repo,
    commit_id   => '66dd2b23b74cd0afabd11af66c6aa9c550540ba6',
    file_path   => 'sub_dir/test11.sql'

    To list commits based on the repository, and file path of the branch:

    SELECT * FROM DBMS_CLOUD_REPO.LIST_COMMITS (
    repo        => :repo, 
    branch_name => 'branch1');

    To list commits based on the repository, branch_name and file path of the branch:

    SELECT * FROM DBMS_CLOUD_REPO.LIST_COMMITS (
    repo         => :repo, 
    branch_name  => 'branch1',
    file_path    => 'sub_dir/test11.sql');

    To list commits based on the repository and commit_id of the branch:

    SELECT * FROM DBMS_CLOUD_REPO.LIST_COMMITS (
    repo => :repo,
    commit_id =>'66dd2b23b74cd0afabd11af66c6aa9c550540ba6');

See DBMS_CLOUD_REPO Repository Branch Management Operations for more information.

Export Schema Objects to the Cloud Code Repository Branch

The DBMS_CLOUD_REPO management routine allows you to export metadata of the objects in a schema to the Cloud Code Repository branch. You can filter your list based on the object names or object types.

To export schema metadata you must first:

Use the EXPORT_SCHEMA procedure to export metadata of the objects in your schema to a Cloud Code Repository branch: 

BEGIN
  DBMS_CLOUD_REPO.EXPORT_SCHEMA(
    repo          => l_repo,
    schema_name   => 'USER1',
    file_path     => 'myschema_ddl.sql'
    filter_list   =>
        to_clob('[    
             { "match_type":"equal",
                 "type":"table"
             },
             { "match_type":"not_equal",
                 "type":"view"
             },
             { "match_type":"in",
                 "type":"table",
                 "name": " ''EMPLOYEE_SALARY'',''EMPLOYEE_ADDRESS'' "
             },
             { "match_type":"equal",
                 "type":"sequence",
                 "name": "EMPLOYEE_RECORD_SEQ"
             },
             { "match_type":"like",
                 "type":"table",
                 "name": "%OFFICE%"
             }    
        ]'
     );
  );
END;
/

This example exports the metadata of the USER1 schema into the l_repo repository. The export includes the metadata of the tables EMPLOYEE_SALARY and EMPLOYEE_ADDRESS, and any table name that contains OFFICE. It also exports the EMPLOYEE_RECORD_SEQ sequence and excludes the views in the schema.

See EXPORT_SCHEMA Procedure for more information.

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.