The DBMS_CLOUD_REPO package provides for use of
and management of cloud hosted code repositories from Oracle Database. Supported cloud code
repositories include GitHub, AWS CodeCommit , and Azure Repos.
DBMS_CLOUD_REPO Overview The DBMS_CLOUD_REPO package provides easy access to files in Cloud Code (Git) Repositories, including: GitHub, AWS CodeCommit, and Azure Repos.
DBMS_CLOUD_REPO Subprogram Groups The DBMS_CLOUD_REPO package subprograms can be grouped into four categories: Initialization Operations, Repository Management Operations, File Operations, and SQL Install Operations.
The DBMS_CLOUD_REPO package provides easy access
to files in Cloud Code (Git) Repositories, including: GitHub, AWS CodeCommit, and
Azure Repos.
This package is a single interface for access to Multicloud
Code repositories and allows you to upload SQL files to Git
repositories or install SQL scripts directly from Cloud Code
Repositories. This package also allows you to use a Cloud Code
Repository to manage code versions for SQL scripts and to install or
patch application code from Git repositories.
Concepts
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.
Architecture
DBMS_CLOUD_REPO package provides four
feature areas:
Repository Initialization with
Generic Cloud Code Repository Handle
Initialize a GitHub Code
Repository
Initialize an AWS CodeCommit Code
Repository
Initialize an Azure Repos Code
Repository
Repository Management Operations
Create a repository
Update a repository
List repositories
Delete a repository
Repository File Management
Operations
Upload a file to Code Repository
from Oracle Database.
Download a file from Code
Repository to Oracle Database.
Delete files from Code
Repository.
List files from Code
Repository.
SQL Install Operations
Export Database object metadata
DDL to repository.
Install SQL statements from a file
in the Code Repository in Oracle Database.
The DBMS_CLOUD_REPO package defines record types
and a generic JSON object type repo.
REPO JSON
Object
A DBMS_CLOUD_REPOREPO is an opaque JSON object to represent 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
any code when you migrate from one Cloud Code Repository provider to another Cloud Code Repository.
The DBMS_CLOUD_REPO package subprograms can be
grouped into four categories: Initialization Operations, Repository Management Operations,
File Operations, and SQL Install Operations.
This procedure updates a Cloud Code repository identified by the repo handle argument. The procedure supports updating the name, description, or private visibility status, as supported by the Cloud Code repository.
The function downloads the contents of a file from the Cloud Code repository. The procedure allows you to download the contents of a file from the Cloud Code repository and save the file in a directory.
This function downloads a file from Cloud Code repository. Optionally, file content can be accessed from either a specific branch, tag or commit name. By default, the file is accessed from the default repository branch.
This procedure uploads a file to the Cloud Code repository identified by the repo handle argument. The procedure is overloaded to support either uploading a file from a directory object or uploading the contents from a CLOB to the repository file.
CREATE_BRANCH Procedure This procedure creates a branch in the Cloud Code Repository identified by the repo handle argument.
CREATE_REPOSITORY Procedure This procedure creates a Cloud Code Repository identified by the repo handle argument.
DELETE_BRANCH Procedure This procedure deletes a branch in the Cloud Code repository identified by the repo handle argument.
DELETE_FILE Procedure This procedure deletes a file from the Cloud Code repository identified by the repo handle argument.
DELETE_REPOSITORY Procedure This procedure deletes the Cloud Code Repository identified by the repo handle argument.
EXPORT_OBJECT Procedure This procedure uploads the DDL metadata of a database object to the Cloud Code repository identified by the repo handle argument. This procedure is an easy way to upload the metadata definition of a database object in single step.
EXPORT_SCHEMA Procedure This procedure exports metadata of all objects in a schema to the Cloud Code Repository branch identified by the repo handle argument.
GET_FILE Procedure and Function The function downloads the contents of a file from the Cloud Code repository. The procedure allows you to download the contents of a file from the Cloud Code repository and save the file in a directory.
INIT_AWS_REPO Function This function initializes an AWS repository handle and returns an opaque type.
INIT_AZURE_REPO Function This function initializes an Azure repository handle and returns an opaque type. This function is only supported for Azure cloud provider.
INIT_GITHUB_REPO Function This function initializes a GitHub repository handle and returns an opaque type.
INIT_REPO Function This function initializes a Cloud Code Repository handle and returns an opaque JSON object. This function is a generic interface to accept a JSON document, and avoids having to change code, you only need to change a JSON document, when moving a code repository from one Cloud Code repository to another Cloud Code repository.
INSTALL_FILE Procedure This procedure installs SQL statements from a file in the Cloud Code repository identified by the repo handle argument.
INSTALL_SQL Procedure This procedure installs SQL statements from a buffer given as input.
LIST_BRANCHES Function This function lists branches in the Cloud Code Repository branch identified by the repo handle argument.
LIST_COMMITS Function This function lists commits in the Cloud Code Repository branch identified by the repo handle argument.
LIST_FILES Function This function downloads a file from Cloud Code repository. Optionally, file content can be accessed from either a specific branch, tag or commit name. By default, the file is accessed from the default repository branch. The results include the file names and additional metadata about the files.
LIST_REPOSITORIES Function This function lists all Cloud Code Repositories identified by the repo handle argument. If the repo handle has a repository name specified, the function does not restrict the listing to the specified repository name; it lists all repositories accessible to the user.
MERGE_BRANCH Procedure This procedure merges a repository branch into another specified branch in the Cloud Code Repository identified by the repo handle argument. The MERGE_BRANCH procedure is currently not supported in Azure.
PUT_FILE Procedure This procedure uploads a file to the Cloud Code repository identified by the repo handle argument. The procedure is overloaded to support either uploading a file from a directory object or uploading the contents from a BLOB to the repository file.
UPDATE_REPOSITORY Procedure This procedure updates a Cloud Code repository identified by the repo handle argument. UPDATE_REPOSITORY supports updating the name, description, or private visibility status, as supported by the Cloud Code repository.
This procedure creates a branch in the Cloud Code Repository identified by the repo handle argument.
Syntax
PROCEDURE DBMS_CLOUD_REPO.CREATE_BRANCH(
repo IN CLOB,
branch_name IN VARCHAR2,
parent_branch_name IN VARCHAR2 DEFAULT NULL,
parent_commit_id IN VARCHAR2 DEFAULT NULL
);
Parameters
Parameter
Description
repo
Specifies the repository handle.
This parameter is mandatory and supported for all cloud providers.
branch_name
Specifies the repository branch name.
This parameter is mandatory and supported for all cloud providers.
parent_branch_name
Creates the new branch using the head commit of the specified parent branch.
This parameter is supported for all cloud providers.
If you do not supply a parent_branch_name value, the parent_branch_name is set to main.
parent_commit_id
Creates the new branch using the specified repository commit.
This parameter is supported for all cloud providers.
If you do not supply a parent_commit_id value, the parent_commit_id is set to a NULL value.
Example
Note
To create a branch in a Cloud Code repository, you must specify the parent branch or the parent commit id.
This procedure deletes a file from the Cloud Code
repository identified by the repo handle argument.
Syntax
PROCEDURE DBMS_CLOUD_REPO.DELETE_FILE(
repo IN CLOB,
file_path IN VARCHAR2,
branch_name IN VARCHAR2 DEFAULT NULL,
commit_details IN CLOB DEFAULT NULL
);
Parameters
Parameter
Description
repo
Specifies the repository handle.
file_path
File path to delete file in the repository.
branch_name
Delete file from a specific branch.
commit_details
Commit Details as a JSON document
{"message":
"Commit message", "author": {"name": "Committing user name",
"email": "Email of committing user" } }
This procedure uploads the DDL metadata of a database
object to the Cloud Code repository identified by the repo handle argument.
This procedure is an easy way to upload the metadata definition of a database object in
single step.
Syntax
PROCEDURE DBMS_CLOUD_REPO.EXPORT_OBJECT(
repo IN CLOB,
file_path IN VARCHAR2,
object_type IN VARCHAR2,
object_name IN VARCHAR2 DEFAULT NULL,
object_schema IN VARCHAR2 DEFAULT NULL,
branch_name IN VARCHAR2 DEFAULT NULL,
commit_details IN CLOB DEFAULT NULL,
append IN BOOLEAN DEFAULT FALSE
);
Parameters
Parameter
Description
repo
Specifies the repository handle.
file_path
File path to upload object metadata in the repository.
Commit Details as a JSON document:{"message": "Commit
message", "author": {"name": "Committing user name",
"email": "Email of committing user" } }
append
Append metadata DDL to existing file.
Usage Note
For customized control on the object DDL, you can use
DBMS_METADATA.GET_DDL along with DBMS_CLOUD_REPO.PUT_FILE. In order to get metadata
definition of the object, the current user must be privileged to retrieve the object
metadata. See DBMS_METADATA for the security
requirements of the package.
This procedure exports metadata of all objects in a schema to the Cloud Code Repository branch identified by the repo handle argument.
Syntax
PROCEDURE DBMS_CLOUD_REPO.EXPORT_SCHEMA(
repo IN CLOB,
file_path IN VARCHAR2,
schema_name IN VARCHAR2,
filter_list IN CLOB DEFAULT NULL,
branch_name IN VARCHAR2 DEFAULT NULL,
commit_details IN CLOB DEFAULT NULL
);
Parameters
Parameter
Description
repo
Specifies the repository handle.
This parameter is mandatory and supported for all cloud providers.
file_path
Specifies the name of the schema file to upload to the repo.
This parameter is mandatory and supported for all cloud providers.
schema_name
Specifies the name of the schema for which a DDL script is to be uploaded to the Cloud Code Repository branch.
This parameter is mandatory and supported for all cloud providers.
filter_list
Specifies the CLOB of the JSON array that defines the filter conditions to include or exclude the objects whose metadata needs to be exported.
This parameter is supported for all cloud providers.
JSON parameters for filter_list are:
match_type: Specifies the type of filter to be applied to the object types or object names.
Valid match_type values are:
in/not_in
like/not_like
equal/not_equal
type: Specifies the type of object by which to filter.
name: Specifies the name of the object by which to filter.
branch_name
Specifies the repository branch name.
This parameter is supported for all cloud providers.
If you do not supply a branch_name value, the branch_name is set to the default repository branch.
commit_details
Commit Details as a JSON document
{"message": "Commit message", "author": {"name": "Committing user name", "email": "Email of committing user" } }
This parameter is supported for all cloud providers.
If you do not supply a commit_details value, the commit_details is set to the default commit message that includes the information about the current database session user and database name performing the commit.
The function downloads the contents of a file from the
Cloud Code repository. The procedure allows you to download the contents of a file from the
Cloud Code repository and save the file in a directory.
Syntax
FUNCTION DBMS_CLOUD_REPO.GET_FILE(
repo IN CLOB,
file_path IN VARCHAR2,
branch_name IN VARCHAR2 DEFAULT NULL,
tag_name IN VARCHAR2 DEFAULT NULL,
commit_name IN VARCHAR2 DEFAULT NULL
) RETURN CLOB;
PROCEDURE DBMS_CLOUD_REPO.GET_FILE(
repo IN CLOB,
file_path IN VARCHAR2,
directory_name IN VARCHAR2,
target_file_name IN VARCHAR2 DEFAULT NULL,
branch_name IN VARCHAR2 DEFAULT NULL,
tag_name IN VARCHAR2 DEFAULT NULL,
commit_name IN VARCHAR2 DEFAULT NULL
);
This function initializes an Azure repository handle
and returns an opaque type. This function is only supported for Azure cloud
provider.
Syntax
FUNCTION DBMS_CLOUD_REPO.INIT_AZURE_REPO(
credential_name IN VARCHAR2,
repo_name IN VARCHAR2,
organization IN VARCHAR2,
project IN VARCHAR2
) RETURN repo;
Parameters
Parameter
Description
credential_name
Credential object specifying Azure, with a Username and Personal
Access Token (PAT).
This function initializes a Cloud Code Repository
handle and returns an opaque JSON object. This function is a generic interface to accept a
JSON document, and avoids having to change code, you only need to change a JSON document,
when moving a code repository from one Cloud Code repository to another Cloud Code
repository.
Syntax
FUNCTION DBMS_CLOUD_REPO.INIT_REPO(
params IN CLOB)
RETURN CLOB;
Parameters
JSON Parameter
Description
provider
Cloud code repository provider from
the following:
This parameter is only applicable for Azure cloud
provider.
project
Azure Team Project
DBMS_CLOUD_REPO_PARAM_PROJECT
This parameter is only applicable for Azure cloud
provider
Example
BEGIN
:repo := DBMS_CLOUD_REPO.INIT_REPO(
params => JSON_OBJECT('credential_name' value 'mycred',
'repo_name' value 'myrepo',
'repo_owner' value 'foo')
);
END;
/
This procedure installs SQL statements from a file in
the Cloud Code repository identified by the repo handle
argument.
Syntax
PROCEDURE DBMS_CLOUD_REPO.INSTALL_FILE(
repo IN CLOB,
file_path IN VARCHAR2,
branch_name IN VARCHAR2 DEFAULT NULL,
tag_name IN VARCHAR2 DEFAULT NULL,
commit_name IN VARCHAR2 DEFAULT NULL,
stop_on_error IN BOOLEAN DEFAULT TRUE
);
Parameters
Parameter
Description
repo
Specifies the repository handle.
file_path
File path in the repository.
branch_name
Branch to install file from a specific
branch.
tag_name
Tag to install file from a specific Tag.
commit_name
Commit ID to install file from a specific
commit.
stop_on_error
Stop executing the SQL statements on first
error.
Usage Notes
You can install SQL statements containing nested SQL from a Cloud Code repository file using the following:
@: includes a SQL file with a relative path to the ROOT of the repository.
@@: includes a SQL file with a path relative to the current file.
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.
This function lists commits in the Cloud Code Repository branch identified by the repo handle argument.
Syntax
FUNCTION DBMS_CLOUD_REPO.LIST_COMMITS(
repo IN CLOB,
branch_name IN VARCHAR2 DEFAULT NULL,
file_path IN VARCHAR2 DEFAULT NULL,
commit_id IN VARCHAR2 DEFAULT NULL
) RETURN list_commit_ret_tab PIPELINED PARALLEL_ENABLE;
Parameters
Parameter
Description
repo
Specifies the repository handle.
This parameter is mandatory and supported for all cloud providers.
branch_name
List commits from a specific branch.
This parameter is supported for all cloud providers.
If you do not supply a branch_name value, the branch_name is set to main.
file_path
List files under the specified subfolder path in the repository.
This parameter is only supported for Git and Azure cloud providers.
If you do not supply a file_path value, the file_path is set to a NULL value.
commit_id
List files starting from the specified sha/id
This parameter is supported for all cloud providers.
If you do not supply a commit_id value, the commit_id is set to a NULL value.
Example
SELECT name FROM DBMS_CLOUD_REPO.LIST_COMMITS(repo => l_repo);
Example
SELECT name FROM DBMS_CLOUD_REPO.LIST_COMMITS (
repo => l_repo,
commit_id => '66dd2b23b74cd0afabd11af66c6aa9c550540ba6',
file_path => 'sub_dir/test11.sql'
);
Usage Notes
This is a pipelined table function with a return type as list_commit_ret_tab.
DBMS_CLOUD_REPO.LIST_COMMITS returns the column: commit_id.
This function downloads a file from Cloud Code
repository. Optionally, file content can be accessed from either a specific branch, tag or
commit name. By default, the file is accessed from the default repository branch. The
results include the file names and additional metadata about the files.
Syntax
FUNCTION DBMS_CLOUD_REPO.LIST_FILES(
repo IN CLOB,
path IN VARCHAR2 DEFAULT NULL,
branch_name IN VARCHAR2 DEFAULT NULL,
tag_name IN VARCHAR2 DEFAULT NULL,
commit_id IN VARCHAR2 DEFAULT NULL
) RETURN list_file_ret_tab PIPELINED PARALLEL_ENABLE;
Parameters
Parameter
Description
repo
Specifies the repository handle.
path
List files under the specified subfolder path in the
repository.
branch_name
List files from a specific branch.
tag_name
List files from a specific Tag.
commit_name
List files from a specific commit.
Usage Notes
This is a pipelined table function with return type as
list_file_ret_tab.
DBMS_CLOUD_REPO.LIST_FILES returns the
columns: id, name, url,
and bytes.
Example
SELECT name FROM DBMS_CLOUD_REPO.LIST_FILES(repo => l_repo);
NAME
-------------------------
test3.sql
This
function lists all Cloud Code Repositories identified by the
repo handle argument. If the repo handle has a
repository name specified, the function does not restrict the listing to the specified
repository name; it lists all repositories accessible to the user.
Syntax
FUNCTION DBMS_CLOUD_REPO.LIST_REPOSITORIES(
repo IN CLOB
) RETURN list_repo_ret_tab PIPELINED PARALLEL_ENABLE;
Parameters
Parameter
Description
repo
Specifies the repository handle. This parameter is
supported by all cloud providers.
Usage Notes
This is a pipelined table function with return type as
list_repo_ret_tab.
DBMS_CLOUD_REPO.LIST_REPOSITORIES returns the
columns: id, name, owner,
description, private,
url, bytes, created,
and last_modified.
Example
SELECT name description FROM DBMS_CLOUD_REPO.LIST_REPOSITORIES(:repo);
NAME DESCRIPTION
--------------------- ---------------
TestRepo1 My test repo
This procedure merges a repository branch into another specified branch in the Cloud Code Repository identified by the repo handle argument. The MERGE_BRANCH procedure is currently not supported in Azure.
Syntax
PROCEDURE DBMS_CLOUD_REPO.MERGE_BRANCH (
repo IN CLOB,
branch_name IN VARCHAR2,
parent_branch_name IN VARCHAR2 DEFAULT NULL,
commit_details IN CLOB DEFAULT NULL
);
Parameters
Parameter
Description
repo
Specifies the repository handle.
This parameter is mandatory and supported for GITHUB and AWS cloud providers.
branch_name
Specifies the Git branch name to merge.
This parameter is mandatory and supported for all cloud providers.
target_branch_name
Specifies the target branch name to merge into.
This parameter is mandatory and supported for all cloud providers.
commit_details
Commit Details as a JSON document
{"message": "Commit message", "author": {"name": "Committing user name", "email": "Email of committing user" } }
If you do not supply a commit_detailsvalue, the commit_details is set to the default commit message that includes the information about current database session user and database name performing the commit.
This procedure uploads a file to the Cloud Code
repository identified by the repo handle argument. The procedure is
overloaded to support either uploading a file from a directory object or uploading the
contents from a BLOB to the repository file.
Syntax
PROCEDURE DBMS_CLOUD_REPO.PUT_FILE(
repo IN CLOB,
file_path IN VARCHAR2,
contents IN BLOB,
branch_name IN VARCHAR2 DEFAULT NULL,
commit_details IN CLOB DEFAULT NULL
);
PROCEDURE DBMS_CLOUD_REPO.PUT_FILE(
repo IN CLOB,
file_path IN VARCHAR2,
directory_name IN VARCHAR2,
source_file_name IN VARCHAR2 DEFAULT NULL,
branch_name IN VARCHAR2 DEFAULT NULL,
commit_details IN CLOB DEFAULT NULL
);
Parameters
Parameter
Description
repo
Specifies the repository handle.
file_path
File path to upload file in the repository.
contents
BLOB containing the file contents.
directory_name
Directory object name containing the file name.
source_file_name
Source file name to upload to repository.
branch_name
Put file to a specific branch.
commit_details
Commit Details as a JSON document:
{"message":
"Commit message", "author": {"name": "Committing user name",
"email": "Email of committing user" } }
Example
BEGIN
DBMS_CLOUD_REPO.PUT_FILE(
repo => l_repo,
);
END;
/
This procedure updates a Cloud Code repository
identified by the repo handle argument. UPDATE_REPOSITORY supports updating
the name, description, or private visibility status, as supported by the Cloud Code
repository.
Syntax
PROCEDURE DBMS_CLOUD_REPO.UPDATE_REPOSITORY(
repo IN OUT CLOB,
new_name IN VARCHAR2 DEFAULT NULL,
description IN CLOB DEFAULT NULL,
private IN BOOLEAN DEFAULT NULL
);
Parameters
Parameter
Description
repo
Specifies the repository handle.
This parameter is
supported for all cloud providers.
new_name
New name for repository.
This parameter is
supported for all cloud providers.
description
A short text description for the repository.
This
parameter is supported for GITHUB and AWS cloud
providers.
private
Repository is private and only accessible with valid
credentials.
This parameter is supported for the GITHUB cloud provider.