Wednesday, February 2, 2022

DBMS_CLOUD Package

DBMS_CLOUD Package, Oracle Database Exam Prep, Oracle Database Career, Oracle Database Skills, Oracle Database Jobs

This article describes the basic usage of the DBMS_CLOUD package. The DBMS_CLOUD package was introduced in Autonomous Database to provide a simple way to interact with an object store from the database. It can be used in on-prem installations for versions 19c and 21c. It works equally well with AWS S3 buckets or Oracle Cloud Object Storage buckets.

◉ Prerequisites


Most of the examples in this article use an on-prem installation of the DBMS_CLOUD package, but some functionality only seems to work properly on the Autonomous Database. We'll highlight when one of these restrictions is in place.

The DBMS_CLOUD package is present by default on the Autonomous Database. It is not installed in Oracle 19c or 21c installations, so it has to be installed manually. The installation is described in this MOS note.


There is an example of this installation here.


We need an object store bucket for some of the examples. This could be an Oracle Cloud Object Store bucket, or an AWS S3 bucket. The following article describes how to create an Oracle Cloud Object Store bucket.


◉ Setup


We create a test user. We make sure the user can create credentials, and give it access to the DBMS_CLOUD package.

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

--drop user testuser1 cascade;
create user testuser1 identified by testuser1 quota unlimited on users;
grant connect, resource to testuser1;

grant create credential to testuser1;
grant execute on dbms_cloud to testuser1;

We need a local directory object to interact with files on the database file server. We give both the test user and C##CLOUD$SERVICE user access to this directory.

create or replace directory tmp_files_dir as '/tmp/files';
grant read, write on directory tmp_files_dir to testuser1, C##CLOUD$SERVICE;

The external table functionality requires access to a directory object called DATA_PUMP_DIR, so create it in the PDB and grant read/write access to the test user.

alter session set "_oracle_script"=TRUE;
create or replace directory data_pump_dir as '/u01/app/oracle/admin/cdb1/dpdump/';
alter session set "_oracle_script"=FALSE;
grant read, write on directory data_pump_dir to testuser1;

Connect to the test user and create the following table.

conn testuser1/testuser1@//localhost:1521/pdb1

create table emp (
  empno    number(4,0), 
  ename    varchar2(10 byte), 
  job      varchar2(9 byte), 
  mgr      number(4,0), 
  hiredate date, 
  sal      number(7,2), 
  comm     number(7,2), 
  deptno   number(2,0), 
  constraint pk_emp primary key (empno)
);
  
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,10);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7839,'KING','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7844,'TURNER','SALESMAN',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7902,'FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10);
commit;

All SQL operations in this article will be performed from the test user, unless otherwise stated.

conn testuser1/testuser1@//localhost:1521/pdb1

◉ Object Store URIs


We use an object store URI for many of the examples in this article. For Oracle Cloud the URI can take either of these forms.

https://swiftobjectstorage.{region}.oraclecloud.com/v1/{namespace}/{bucket}/{objectname}

https://objectstorage.{region}.oraclecloud.com/n/{namespace}/b/{bucket}/o/{objectname}

The documentation typically uses the "swiftobjectstorage" URI, but both work. For the remainder of the article we will use the "swiftobjectstorage" URI.

The AWS S3 and Azure blob storage URIs typically look like this.

AWS S3: https://s3-{region}.amazonaws.com/{bucket}/{objectname}
Azure Blog Storage: https://{account}.blob.core.windows.net/{container}/{objectname}

A number of the procedures and functions support wildcards in the URI definitions.

- * - A wildcard for multiple characters.
- ? - A wildcard for a single character.

◉ Object Store Credentials


The DBMS_CLOUD package contains a copy of the procedures from the DBMS_CREDENTIAL package. The two packages can be used interchangeably, as they do the same thing.


Create a credential for your object store using the CREATE_CREDENTIAL procedure. For an Oracle object storage bucket we use our Oracle Cloud email and the Auth Token we generated.

begin
  dbms_cloud.create_credential (
    credential_name => 'obj_store_cred',
    username        => 'me@example.com',
    password        => '{my-Auth-Token}'
  ) ;
end;
/

For AWS buckets we use our AWS access key and secret access key.

begin
  dbms_cloud.create_credential (
    credential_name => 'obj_store_cred',
    username        => 'my AWS access key',
    password        => 'my AWS secret access key'
  );
end;
/

Information about the credential can be displayed using the USER_CREDENTIALS view.

column credential_name format a25
column username format a20

select credential_name,
       username,
       enabled
from   user_credentials
order by credential_name;

CREDENTIAL_NAME           USERNAME             ENABL
------------------------- -------------------- -----
OBJ_STORE_CRED            me@example.com       TRUE

SQL>

The DISABLE_CREDENTIAL and ENABLE_CREDENTIAL procedures disable and enable credentials respectively.

begin
  dbms_credential.disable_credential('obj_store_cred');

  dbms_credential.enable_credential('obj_store_cred');
end;/

The UPDATE_CREDENTIALS procedure allows us to edit attributes of a credential.

begin
  dbms_credential.update_credential(
    credential_name => 'obj_store_cred',
    attribute       => 'username', 
    value           => 'me@example.com');

  dbms_credential.update_credential(
    credential_name => 'obj_store_cred',
    attribute       => 'password', 
    value           => '{my-Auth-Token}');
end;
/

The DROP_CREDENTIAL procedure drops the named credential.

begin
  dbms_cloud.drop_credential(credential_name => 'obj_store_cred');
end;
/

The following examples require a valid credential.

◉ Objects and Files


There are several routines for manipulating files on the local database file system and objects in a cloud object store.

Create a file on the database server file system.

mkdir -p /tmp/files
echo "This is a test file" > /tmp/files/test1.txt

We use the PUT_OBJECT procedure to transfer a file from the directory object location to the cloud object store.

begin
  dbms_cloud.put_object (
    credential_name => 'obj_store_cred',
    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/test1.txt',
    directory_name  => 'tmp_files_dir',
    file_name       => 'test1.txt');
end;
/

There is an overload of the PUT_OBJECT procedure to transfer the contents of a BLOB to the object store.

declare
  l_file blob;
begin
  l_file := utl_raw.cast_to_raw('This is another test file');

  dbms_cloud.put_object (
    credential_name => 'obj_store_cred',
    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/test2.txt',
    contents        => l_file);
end;
/

The LIST_OBJECTS table function lists objects in the location pointed to by the object store URI.

set linesize 150
column object_name format a12
column checksum format a35
column created format a35
column last_modified format a35

select *
from   dbms_cloud.list_objects(
        credential_name => 'obj_store_cred',
        location_uri    => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket');

OBJECT_NAME       BYTES CHECKSUM                            CREATED                             LAST_MODIFIED
------------ ---------- ----------------------------------- ----------------------------------- -----------------------------------
test1.txt            20 5dd39cab1c53c2c77cd352983f9641e1                                        11-SEP-21 08.45.42.779000 AM +00:00
test2.txt            25 d0914057907f9d04dd9e68b1c1e180f0                                        11-SEP-21 08.45.54.148000 AM +00:00

SQL>

We use the GET_METADATA function to return information about a specific object.

select dbms_cloud.get_metadata(
         credential_name => 'obj_store_cred',
         object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/test2.txt') as metadata
from dual;

METADATA
--------------------------------------------------------------------------------
{"Content-Length":25}

SQL>

We use the GET_OBJECT procedure to transfer an object from the cloud object store to the directory object location.

begin
  dbms_cloud.get_object (
    credential_name => 'obj_store_cred',
    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/test2.txt',
    directory_name  => 'tmp_files_dir',
    file_name       => 'test2.txt');
end;
/

There is a GET_OBJECT function to transfer an object from the cloud object store to a BLOB.

declare
  l_file blob;
begin
  l_file := dbms_cloud.get_object (
    credential_name => 'obj_store_cred',
    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/test2.txt');
end;
/

The DELETE_OBJECT procedure deletes objects from the cloud object store.

begin
  dbms_cloud.delete_object(
    credential_name => 'obj_store_cred',
    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/test1.txt');

  dbms_cloud.delete_object(
    credential_name => 'obj_store_cred',
    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/test2.txt');
end;
/

The DELETE_FILE procedure deletes files from directory object location.

begin
  dbms_cloud.delete_file(
    directory_name => 'tmp_files_dir',
    file_name      => 'test1.txt');

  dbms_cloud.delete_file(
    directory_name => 'tmp_files_dir',
    file_name      => 'test2.txt');
end;
/

The LIST_FILES table function lists the files in the location pointed to by the specified Oracle directory object. The documentation says it's only supported for directory objects mapping to Oracle File System (OFS) or Database File System (DBFS) file systems, so we can't use it for a regular file system. It does work in the locations provided on the Autonomous Database.

select *
from   dbms_cloud.list_files(directory_name => 'data_pump_dir');

◉ External Tables


This section describes the creation of external tables based on files in a cloud object store.

    ◉ CREATE_EXTERNAL_TABLE

We create a file called "emp.dat" with the following contents and place it into our object store. It is a pipe-delimited file with no headers.

7369|"SMITH"|"CLERK"|7902|17-DEC-80|800||20
7499|"ALLEN"|"SALESMAN"|7698|20-FEB-81|1600|300|30
7521|"WARD"|"SALESMAN"|7698|22-FEB-81|1250|500|30
7566|"JONES"|"MANAGER"|7839|02-APR-81|2975||20
7654|"MARTIN"|"SALESMAN"|7698|28-SEP-81|1250|1400|30
7698|"BLAKE"|"MANAGER"|7839|01-MAY-81|2850||30
7782|"CLARK"|"MANAGER"|7839|09-JUN-81|2450||10
7788|"SCOTT"|"ANALYST"|7566|19-APR-87|3000||20
7839|"KING"|"PRESIDENT"||17-NOV-81|5000||10
7844|"TURNER"|"SALESMAN"|7698|08-SEP-81|1500|0|30
7876|"ADAMS"|"CLERK"|7788|23-MAY-87|1100||20
7900|"JAMES"|"CLERK"|7698|03-DEC-81|950||30
7902|"FORD"|"ANALYST"|7566|03-DEC-81|3000||20
7934|"MILLER"|"CLERK"|7782|23-JAN-82|1300||10

The CREATE_EXTERNAL_TABLE procedure creates an external table called EMP_EXT based on a file in a cloud object store.

--drop table emp_ext;

begin
  dbms_cloud.create_external_table(
    table_name      => 'emp_ext',
    credential_name => 'obj_store_cred',
    file_uri_list   => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/emp.dat',
    column_list     => 'empno     number(4),
                        ename     varchar2(10),
                        job       varchar2(9),
                        mgr       number(4),
                        hiredate  date,
                        sal       number(7,2),
                        comm      number(7,2),
                        deptno    number(2)',
    format          => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true')
 );
end;
/

We query the external table, which reads the data from the cloud object store.

select * from emp_ext;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                         20
      7499 ALLEN      SALESMAN     7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN     7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER       7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN     7698 28-SEP-81       1250       1400       30
      7698 BLAKE      MANAGER      7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER       7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT         17-NOV-81            5000                    10
      7844 TURNER     SALESMAN     7698 08-SEP-81       1500           0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL>

The FORMAT parameter allows us to tailor the load process to to suit the data file contents. The full list of format options can be found here. The following example works with a CSV file.

We create a file called "emp.csv" with the following contents and place it into our object store. It is a CSV file with a header row.

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,17-DEC-80,800,,20
7499,"ALLEN","SALESMAN",7698,20-FEB-81,1600,300,30
7521,"WARD","SALESMAN",7698,22-FEB-81,1250,500,30
7566,"JONES","MANAGER",7839,02-APR-81,2975,,20
7654,"MARTIN","SALESMAN",7698,28-SEP-81,1250,1400,30
7698,"BLAKE","MANAGER",7839,01-MAY-81,2850,,30
7782,"CLARK","MANAGER",7839,09-JUN-81,2450,,10
7788,"SCOTT","ANALYST",7566,19-APR-87,3000,,20
7839,"KING","PRESIDENT",,17-NOV-81,5000,,10
7844,"TURNER","SALESMAN",7698,08-SEP-81,1500,0,30
7876,"ADAMS","CLERK",7788,23-MAY-87,1100,,20
7900,"JAMES","CLERK",7698,03-DEC-81,950,,30
7902,"FORD","ANALYST",7566,03-DEC-81,3000,,20
7934,"MILLER","CLERK",7782,23-JAN-82,1300,,10

The CREATE_EXTERNAL_TABLE procedure creates an external table called EMP_CSV_EXT based on a file in a cloud object store.

--drop table emp_csv_ext;

begin
  dbms_cloud.create_external_table(
    table_name      => 'emp_csv_ext',
    credential_name => 'obj_store_cred',
    file_uri_list   => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/emp.csv',
    column_list     => 'empno     number(4),
                        ename     varchar2(10),
                        job       varchar2(9),
                        mgr       number(4),
                        hiredate  date,
                        sal       number(7,2),
                        comm      number(7,2),
                        deptno    number(2)',
    format          => json_object('type' value 'csv', 'skipheaders' value '1')
 );
end;
/

We query the external table, which reads the data from the cloud object store.

select * from emp_csv_ext;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80         800                           20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                      20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400       30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87        3000                     20
      7839 KING       PRESIDENT            17-NOV-81          5000                      10
      7844 TURNER     SALESMAN        7698 08-SEP-81     1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                      20
      7900 JAMES      CLERK           7698 03-DEC-81        950                        30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                     20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                      10

14 rows selected.

SQL>

The VALIDATE_EXTERNAL_TABLE procedure allows us to check the validity of an external table.

begin
  dbms_cloud.validate_external_table('emp_csv_ext');
end;
/

    ◉ CREATE_EXTERNAL_PART_TABLE

Create four CSV files using the following queries.

set markup csv on quote on
set trimspool on linesize 1000 feedback off pagesize 0

spool /tmp/files/gbr1.txt
select 'GBR',
       object_id,
       owner,
       object_name
from   all_objects
where  object_id <= 2000
and    rownum <= 1000;
spool off

spool /tmp/files/gbr2.txt
select 'GBR',
       object_id,
       owner,
       object_name
from   all_objects
where  object_id BETWEEN 2000 AND 3999
and    rownum <= 1000;
spool off

spool /tmp/files/ire1.txt
select 'IRE',
       object_id,
       owner,
       object_name
from   all_objects
where  object_id <= 2000
and    rownum <= 1000;
spool off

spool /tmp/files/ire2.txt
select 'IRE',
       object_id,
       owner,
       object_name
from   all_objects
where  object_id BETWEEN 2000 AND 3999
and    rownum <= 1000;
spool off

set markup csv off
set trimspool on linesize 1000 feedback off pagesize 14

You may need to clean up the start and end of the files a little before uploading them. Copy the files to the object store.

begin
  dbms_cloud.put_object (
    credential_name => 'obj_store_cred',
    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr1.txt',
    directory_name  => 'tmp_files_dir',
    file_name       => 'gbr1.txt');
end;
/

begin
  dbms_cloud.put_object (
    credential_name => 'obj_store_cred',
    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr2.txt',
    directory_name  => 'tmp_files_dir',
    file_name       => 'gbr2.txt');
end;
/

begin
  dbms_cloud.put_object (
    credential_name => 'obj_store_cred',
    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire1.txt',
    directory_name  => 'tmp_files_dir',
    file_name       => 'ire1.txt');
end;
/

begin
  dbms_cloud.put_object (
    credential_name => 'obj_store_cred',
    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire2.txt',
    directory_name  => 'tmp_files_dir',
    file_name       => 'ire2.txt');
end;
/

The CREATE_EXTERNAL_PART_TABLE procedure creates an external partitioned table called COUNTRY_PART_TAB_EXT based on a files in a cloud object store.

--drop table country_part_tab_ext;

begin
  dbms_cloud.create_external_part_table(
    table_name      => 'country_part_tab_ext',
    credential_name => 'obj_store_cred',
    format          => json_object('type' value 'csv', 'skipheaders' value '1'),
    column_list     => 'country_code  varchar2(3),
                        object_id     number,
                        owner         varchar2(128),
                        object_name   varchar2(128)',
    partitioning_clause => 'partition by list (country_code) (
                              partition part_gbr values (''GBR'') location (
                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr1.txt'',
                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr2.txt''
                              ),
                              partition part_ire values (''IRE'') location (
                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire1.txt'',
                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire2.txt''
                              )
                            )'
  );
end;
/

We query the external table, which reads the data from the cloud object store.

select country_code, count(*) as amount
from   country_part_tab_ext
group by country_code
order by country_code;

COU     AMOUNT
--- ----------
GBR       2000
IRE       2000

SQL>

The VALIDATE_EXTERNAL_PART_TABLE procedure allows us to check the validity of an external partitioned table.

begin
  dbms_cloud.validate_external_part_table('country_part_tab_ext');
end;
/

    ◉ CREATE_HYBRID_PART_TABLE

The CREATE_HYBRID_PART_TABLE procedure creates an external hybrid partitioned table called COUNTRY_HYBRID_PART_TAB_EXT based on a files in a cloud object store.

--drop table country_hybrid_part_tab_ext;

begin
  dbms_cloud.create_hybrid_part_table(
    table_name      => 'country_hybrid_part_tab_ext',
    credential_name => 'obj_store_cred',
    format          => json_object('type' value 'csv', 'skipheaders' value '1'),
    column_list     => 'country_code  varchar2(3),
                        object_id     number,
                        owner         varchar2(128),
                        object_name   varchar2(128)',
    partitioning_clause => 'partition by list (country_code) (
                              partition part_gbr values (''GBR'') external location (
                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr1.txt'',
                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr2.txt''
                              ),
                              partition part_ire values (''IRE'') external location (
                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire1.txt'',
                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire2.txt''
                              ),
                              partition part_usa values (''USA'')
                            )'
  );
end;
/

We insert a row into the regular partition.

insert into country_hybrid_part_tab_ext values ('USA', 123, 'banana', 'banana');
commit;

We query the external table, which reads the data from the cloud object store.

select country_code, count(*) as amount
from   country_hybrid_part_tab_ext
group by country_code
order by country_code;

COU     AMOUNT
--- ----------
GBR       2000
IRE       2000
USA          1

SQL>

The VALIDATE_HYBRID_PART_TABLE procedure allows us to check the validity of an external hybrid partitioned table.

begin
  dbms_cloud.validate_hybrid_part_table('country_hybrid_part_tab_ext');
end;
/

◉ Copy Data


The COPY_DATA procedure allows us to copy data from a cloud object store into an existing table.


We create a file called "emp.csv" with the following contents and place it into our object store. It is a CSV file with a header row.

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,17-DEC-80,800,,20
7499,"ALLEN","SALESMAN",7698,20-FEB-81,1600,300,30
7521,"WARD","SALESMAN",7698,22-FEB-81,1250,500,30
7566,"JONES","MANAGER",7839,02-APR-81,2975,,20
7654,"MARTIN","SALESMAN",7698,28-SEP-81,1250,1400,30
7698,"BLAKE","MANAGER",7839,01-MAY-81,2850,,30
7782,"CLARK","MANAGER",7839,09-JUN-81,2450,,10
7788,"SCOTT","ANALYST",7566,19-APR-87,3000,,20
7839,"KING","PRESIDENT",,17-NOV-81,5000,,10
7844,"TURNER","SALESMAN",7698,08-SEP-81,1500,0,30
7876,"ADAMS","CLERK",7788,23-MAY-87,1100,,20
7900,"JAMES","CLERK",7698,03-DEC-81,950,,30
7902,"FORD","ANALYST",7566,03-DEC-81,3000,,20
7934,"MILLER","CLERK",7782,23-JAN-82,1300,,10

We truncate the local EMP table and reload the data from the cloud object store using the COPY_DATA procedure.

truncate table emp;

begin
  dbms_cloud.copy_data(
    table_name      => 'emp',
    credential_name => 'obj_store_cred',
    file_uri_list   => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/emp.csv',
    format          => json_object('type' value 'csv', 'skipheaders' value '1')
  );
end;
/

We query the EMP table, and we can see the data has been loaded.

select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK               7902 17-DEC-80        800                        20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                       20
      7654 MARTIN     SALESMAN        7698 28-SEP-81      1250       1400       30
      7698 BLAKE      MANAGER         7839 01-MAY-81      2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87        3000                      20
      7839 KING       PRESIDENT            17-NOV-81           5000                     10
      7844 TURNER     SALESMAN        7698 08-SEP-81    1500          0         30
      7876 ADAMS      CLERK            7788 23-MAY-87       1100                     20
      7900 JAMES      CLERK             7698 03-DEC-81        950                       30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                      20
      7934 MILLER     CLERK           7782 23-JAN-82         1300                    10

14 rows selected.

SQL>

Similar to the external table examples, the FORMAT parameter allows us to tailor the load process to to suit the data file contents.

◉ Export Data


The EXPORT_DATA procedure takes the data generated by a query and exports it to a cloud object store in the requested format. This doesn't seem to work on the on-prem version of the DBMS_CLOUD package, but does work on Autonomous Database.

begin
  dbms_cloud.export_data (
    credential_name => 'obj_store_cred',
    file_uri_list   => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/emp.json',
    query           => 'select * from emp',
    format          => '{"type" : "JSON"}'
  );
end;
/

begin
  dbms_cloud.export_data (
    credential_name => 'obj_store_cred',
    file_uri_list   => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/emp.csv',
    query           => 'select * from emp',
    format          => '{"type" : "CSV"}'
  );
end;
/

◉ SODA Collections


Oracle databases can be used as a document store using Simple Oracle Document Access (SODA). You can read about SODA here.

We create a new collection called "TestCollection1".

set serveroutput on

declare
  l_collection  soda_collection_t;
begin
  l_collection := dbms_soda.create_collection('TestCollection1');

  if l_collection is not null then
    dbms_output.put_line('Collection ID : ' || l_collection.get_name());
  else
    dbms_output.put_line('Collection does not exist.');  
  end if;
end;
/
Collection ID : TestCollection1

PL/SQL procedure successfully completed.

SQL>

Create a file called "fruit.json" with the following contents and upload it to your cloud object store.

{"fruit": "banana"}

The COPY_COLLECTION procedure loads the data from our cloud object store into the collection.

begin
  dbms_cloud.copy_collection(
    collection_name => 'TestCollection1',
    credential_name => 'obj_store_cred',
    file_uri_list   => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/fruit.json',
    format          => json_object('unpackarrays' value 'true')
  );
end;
/

We can see the data in the collection using the following query.

select json_document
from   "TestCollection1";

JSON_DOCUMENT
--------------------------------------------------------------------------------
{"fruit":"banana"}

SQL>

◉ Delete Operations


Many of the DBMS_CLOUD operations produce additional files (log files, bad files, temp files etc.). These need to be cleaned up once an operation is complete. Several of the procedures shown above have overloads that return an operation ID value, which can be used with the DELETE_OPERATION procedure to clean up the extra files. Alternatively the operations for the current session can be displayed using the USER_LOAD_OPERATIONS view.

SQL> desc user_load_operations
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                    NOT NULL     NUMBER
 TYPE                                              NOT NULL     VARCHAR2(128)
 SID                                                  NOT NULL     NUMBER
 SERIAL#                                         NOT NULL    NUMBER
 START_TIME                                                         TIMESTAMP(6) WITH TIME ZONE
 UPDATE_TIME                                                    TIMESTAMP(6) WITH TIME ZONE
 STATUS                                                                 VARCHAR2(9)
 OWNER_NAME                                                   VARCHAR2(128)
 TABLE_NAME                                                     VARCHAR2(128)
 PARTITION_NAME                                             VARCHAR2(128)
 SUBPARTITION_NAME                                     VARCHAR2(128)
 FILE_URI_LIST                                                    VARCHAR2(4000)
 ROWS_LOADED                                                 NUMBER
 LOGFILE_TABLE                                                VARCHAR2(128)
 BADFILE_TABLE                                                VARCHAR2(128)
 TEMPEXT_TABLE                                               VARCHAR2(128)

SQL>

We return the operations for the current session using the USER_LOAD_OPERATIONS view.

column type format a10

select id, type
from   user_load_operations
order by 1;

        ID TYPE
---------- ----------
         1 COPY
        11 COPY

SQL>

The DELETE_OPERATION procedure allows us to clear up the additional files associated with a specific operation.

begin
  dbms_cloud.delete_operation(1);
end;
/

select id, type
from   user_load_operations
order by 1;

        ID TYPE
---------- ----------
        11 COPY

SQL>

The DELETE_ALL_OPERATIONS procedure allows us to clean up the additional files for all operations, or those for specific types of operations if we specify the type value.

-- Delete only COPY operations.
begin
  dbms_cloud.delete_all_operations('COPY');
end;
/

-- Delete all operations.
begin
  dbms_cloud.delete_all_operations;
end;
/

Source: oracle-base.com

Related Posts

0 comments:

Post a Comment