DBA Hub

📋Steps in this guide1/13

DBMS_CLOUD Package

This article describes the basic usage of the DBMS_CLOUD package.

oracle 21cconfigurationintermediate
by OracleDba
69 views
1

Prerequisites

Most of the examples in this article use an on-prem installation of the 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 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. - How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1) 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. - Oracle Cloud Infrastructure (OCI) : Create an Object Storage Bucket
2

Setup

We create a test user. We make sure the user can create credentials, and give it access to the package. We need a local directory object to interact with files on the database file server. We give both the test user and user access to this directory. 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. Connect to the test user and create the following table. All SQL operations in this article will be performed from the test user, unless otherwise stated.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
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;
-- Ignore any failures of below commands.
grant execute on dbms_cloud_pipeline to testuser1;
grant execute on dbms_cloud_repo to testuser1;
grant execute on dbms_cloud_notification to testuser1;
grant select on sys.v_$px_session to testuser1;

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

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;

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;

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

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. 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. A number of the procedures and functions support wildcards in the URI definitions. - * - A wildcard for multiple characters. - ? - A wildcard for a single character.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
https://swiftobjectstorage.{region}.oraclecloud.com/v1/{namespace}/{bucket}/{objectname}

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

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

Object Store Credentials

The package contains a copy of the procedures from the package. The two packages can be used interchangeably, as they do the same thing. Create a credential for your object store using the procedure. For an Oracle object storage bucket we use our Oracle Cloud email and the Auth Key we generated, or the API key approach.. For AWS buckets we use our AWS access key and secret access key. Information about the credential can be displayed using the view. The and procedures disable and enable credentials respectively. The procedure allows us to edit attributes of a credential. The procedure drops the named credential. The following examples require a valid credential.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
-- Auth key
begin
  dbms_cloud.create_credential (
    credential_name => 'obj_store_cred',
    username        => 'OracleIdentityCloudService/[email protected]',
    password        => '{my-Auth-Key-value}'
  ) ;
end;
/


-- API Key
begin
  dbms_credential.create_credential(
    credential_name => 'obj_store_cred',
    user_ocid       => 'ocid1.user.oc1..aaaaaaaaveuky????',
    tenancy_ocid    => 'ocid1.tenancy.oc1..aaaaaaaaey????',
    private_key     => '-----BEGIN PRIVATE KEY-----
????
-----END PRIVATE KEY-----
OCI_API_KEY',
    fingerprint     => 'b9:1d:51:69:3f:50:9b:a2:4b:??:??:??:??:??:??:??');
end;
/

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

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            [email protected]       TRUE

SQL>

begin
  dbms_credential.disable_credential('obj_store_cred');

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

begin
  dbms_credential.update_credential(
    credential_name => 'obj_store_cred',
    attribute       => 'username', 
    value           => '[email protected]');

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

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

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. We use the procedure to transfer a file from the directory object location to the cloud object store. There is an overload of the procedure to transfer the contents of a BLOB to the object store. The table function lists objects in the location pointed to by the object store URI. We use the function to return information about a specific object. We use the procedure to transfer an object from the cloud object store to the directory object location. There is a function to transfer an object from the cloud object store to a BLOB. The procedure deletes objects from the cloud object store. The procedure deletes files from directory object location. The 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
mkdir -p /tmp/files
echo "This is a test file" > /tmp/files/test1.txt

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

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

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>

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>

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

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

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

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

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

External Tables

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

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. The procedure creates an external table called based on a file in a cloud object store. We query the external table, which reads the data from the cloud object store. The 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. The procedure creates an external table called based on a file in a cloud object store. We query the external table, which reads the data from the cloud object store. The procedure allows us to check the validity of an external table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
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

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

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>

"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

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

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>

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

CREATE_EXTERNAL_PART_TABLE

Create four CSV files using the following queries. 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. The procedure creates an external partitioned table called based on a files in a cloud object store. We query the external table, which reads the data from the cloud object store. The procedure allows us to check the validity of an external partitioned table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
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

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

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

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>

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

CREATE_HYBRID_PART_TABLE

The procedure creates an external hybrid partitioned table called based on a files in a cloud object store. We insert a row into the regular partition. We query the external table, which reads the data from the cloud object store. The procedure allows us to check the validity of an external hybrid partitioned table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
--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;
/

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

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>

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

Copy Data

The 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. We truncate the local table and reload the data from the cloud object store using the procedure. We query the table, and we can see the data has been loaded. Similar to the external table examples, the parameter allows us to tailor the load process to to suit the data file contents.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
"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

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

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>
11

Export Data

The 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 package, but does work on Autonomous Database.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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;
/
12

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". Create a file called "fruit.json" with the following contents and upload it to your cloud object store. The procedure loads the data from our cloud object store into the collection. We can see the data in the collection using the following query.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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>

{"fruit": "banana"}

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

select json_document
from   "TestCollection1";

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

SQL>
13

Delete Operations

Many of the 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 procedure to clean up the extra files. Alternatively the operations for the current session can be displayed using the view. We return the operations for the current session using the view. The procedure allows us to clear up the additional files associated with a specific operation. The 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. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
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>

column type format a10

select id, type
from   user_load_operations
order by 1;

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

SQL>

begin
  dbms_cloud.delete_operation(1);
end;
/


select id, type
from   user_load_operations
order by 1;

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

SQL>

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

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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!