In addition to powerful functionality to load and manage data from various different cloud object stores, Autonomous Database (ADB) also provides a unified and simplified way to create, push to and install scripts from popular cloud-based and version control code repositories (repo), such as GitHub, Azure Repos and AWS CodeCommit.
Follow my example with GitHub below to learn how the DBMS_CLOUD_REPO PL/SQL package in Autonomous Database eliminates the need to learn any new syntax for each code repository, while also abstracting away the underlying repo making it easy to switch repos when required!
Step 1: Generate your repository’s access token
Login to your GitHub account and generate a personal access token, which will be used to provide access to your repositories from the database.
Step 2: Create a credential in ADB for access to your code repository
Navigate to your running ADB instance’s SQL Worksheet, and run the following script (fill in your own GitHub details) to create a credential for your database to use for access to your GitHub repositories.
BEGIN DBMS_CLOUD.CREATE_CREDENTIAL ( 'GITHUB_CRED', '<GitHub Email Address>', '<GitHub Personal Access Token>'); END; /
Step 3: Create a sample table named Persons
CREATE TABLE persons( person_id NUMBER GENERATED BY DEFAULT AS IDENTITY, first_name VARCHAR2(50) NOT NULL, last_name VARCHAR2(50) NOT NULL );
Step 4: Use the DBMS_CLOUD_REPO package to create a new Git Repo and export your sample table metadata
Run the following script replacing in your GitHub user’s information. This script is an example of how you may initializes the required repo handle JSON object, use it to create a new repository named “BlogRepo” and exports your newly created “Persons” table’s metadata.
DECLARE repoHandle clob; repoName clob := 'BlogRepo'; BEGIN repoHandle := DBMS_CLOUD_REPO.INIT_GITHUB_REPO( credential_name => 'GITHUB_CRED', repo_name => repoName, owner => '<GitHub Repo Owner>' ); DBMS_OUTPUT.PUT_LINE(repoHandle); DBMS_CLOUD_REPO.CREATE_REPOSITORY( repo => repoHandle, description => 'Blog example repo', private => TRUE ); DBMS_CLOUD_REPO.EXPORT_OBJECT( repo => repoHandle, object_type => 'TABLE', --Object types supported by DBMS_METADATA object_name => 'persons', file_path => 'BlogScript.sql' ); END; /
Notice that this table metadata we just exported to the repo as a script named BlogScript.sql has now been committed to and can be viewed on github.com!
Now that we have exported the Person’s table metadata object to our repository, let’s drop the table.
DROP Table persons
Step 5: Run / install your exported script on ADB directly from the code repository
Lastly, let’s run an example of the DBMS_CLOUD_REPO procedure “INSTALL_FILE” that can get any script (we will use the BlogScript.sql we just created here) from a repo and run it on our ADB instance in one single step. After running this, we can see that the Persons table has been created once again, from the script that was lying in the Git repository.
DECLARE repoHandle clob; repoName clob := 'BlogRepo'; BEGIN repoHandle := DBMS_CLOUD_REPO.INIT_GITHUB_REPO( credential_name => 'GITHUB_CRED', repo_name => 'BlogRepo', owner => '<GitHub Repo Owner>' ); DBMS_OUTPUT.PUT_LINE(repoHandle); DBMS_CLOUD_REPO.INSTALL_FILE( repo => repoHandle, file_path => 'BlogScript.sql', stop_on_error => FALSE ); END; / DESC persons
Conclusion
Above, this example accentuates the convenience brought to managing and installing scripts from your existing code repositories within Autonomous Database. Note how while the example above connects to GitHub, reusing the same code and simply updating the input parameters, would allow you to switch out the underlying repository for any of the other supported ones.
In addition to managing database files and metadata, the GitHub credentials we created above may also be used along with ADB’s DBMS_CLOUD package, to treat your GitHub repo as an object store and load data into your database using the COPY_DATA procedure using raw GitHub URLs.