[译] DBMS_CLOUD 包

原文地址：https://oracle-base.com/articles/21c/dbms_cloud-package 原文作者：Tim Hall

本文介绍了 DBMS_CLOUD 包的基本用法。

自治数据库中引入了 DBMS_CLOUD 包，以提供一种与数据库中的对象存储进行交互的简单方法。它可用于版本 19c 和 21c 的本地安装。它同样适用于 AWS S3 存储 buckets 或 Oracle Cloud Object Storage 存储 buckets。

先决条件

本章目标：掌握本章核心知识点 前置要求：完成前序章节学习 预计时长：60 分钟

本文中的大多数示例都使用 DBMS_CLOUD 包的本地安装，但某些功能似乎只能在自治数据库上正常工作，过程中我将强调这些限制出现的地方。

DBMS_CLOUD 包默认存在于自治数据库中，它未安装在 Oracle 19c 或 21c 安装中，因此必须手动安装，此 MOS 说明中描述了安装。

How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1)

可以参考下方的安装示例：

DBMS_CLOUD : Installation on 19c and 21c On-Prem Databases

对于某些示例，我们需要一个对象存储 buckets。这可以是 Oracle Cloud Object Store 存储 buckets 或 AWS S3 存储 buckets。以下文章介绍了如何创建 Oracle Cloud Object Store 存储 buckets。

Oracle Cloud Infrastructure (OCI) : Create an Object Storage Bucket

安装

我们创建一个测试用户，确保用户可以创建凭据，并授予其访问 DBMS_CLOUD 包的权限。

SQL

1conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba
2
3--drop user testuser1 cascade;
4create user testuser1 identified by testuser1 quota unlimited on users;
5grant connect, resource to testuser1;
6
7grant create credential to testuser1;
8grant execute on dbms_cloud to testuser1;

我们需要一个本地目录对象来与数据库文件服务器上的文件进行交互，授予 test 用户和 C##CLOUD$SERVICE 用户访问此目录的权限。

SQL

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

外部表功能需要访问名为 DATA_PUMP_DIR 的目录对象，因此在 PDB 中创建它并向测试用户授予读/写访问权限。

SQL

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

连接到测试用户并创建下表：

SQL

1conn testuser1/testuser1@//localhost:1521/pdb1
2
3create table emp (
4  empno    number(4,0), 
5  ename    varchar2(10 byte), 
6  job      varchar2(9 byte), 
7  mgr      number(4,0), 
8  hiredate date, 
9  sal      number(7,2), 
10  comm     number(7,2), 
11  deptno   number(2,0), 
12  constraint pk_emp primary key (empno)
13);
14  
15insert 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);
16insert 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);
17insert 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);
18insert 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);
19insert 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);
20insert 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);
21insert 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);
22insert 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);
23insert 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);
24insert 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);
25insert 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);
26insert 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);
27insert 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);
28insert 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);
29commit;

本文中所有 SQL 操作均由测试用户执行，除非特殊说明。

SQL

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

对象存储 URI

对于本文中的许多示例，我们使用对象存储 URI。对于 Oracle Cloud，URI 可以采用这些形式之一。

SQL

1https://swiftobjectstorage.{region}.oraclecloud.com/v1/{namespace}/{bucket}/{objectname}
2
3https://objectstorage.{region}.oraclecloud.com/n/{namespace}/b/{bucket}/o/{objectname}

本文主要使用 swiftobjectstorage URI，但两者都有效。在本文的其余部分，我们将使用 swiftobjectstorage URI。

AWS S3 和 Azure blob 存储 URI 通常如下所示：

SQL

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

对象存储凭证

DBMS_CLOUD 包包含来自 DBMS_CREDENTIAL 包的过程的副本。这两个包可以互换使用，因为它们做同样的事情。

DBMS_CREDENTIAL : Persist Database and OS Credentials in Oracle Database 12c Release 1 (12.1)

使用 CREATE_CREDENTIAL 过程为您的对象存储创建凭证。对于 Oracle 对象存储桶，我们使用我们的 Oracle Cloud 电子邮件和我们生成的身份验证令牌。

SQL

1begin
2  dbms_cloud.create_credential (
3    credential_name => 'obj_store_cred',
4    username        => 'me@example.com',
5    password        => '{my-Auth-Token}'
6  ) ;
7end;
8/

对于 AWS 存储 buckets，我们使用我们的 AWS 访问密钥和秘密访问密钥。

SQL

1begin
2  dbms_cloud.create_credential (
3    credential_name => 'obj_store_cred',
4    username        => 'my AWS access key',
5    password        => 'my AWS secret access key'
6  );
7end;
8/

可以使用 USER_CREDENTIALS 视图显示有关凭证的信息。

SQL

1column credential_name format a25
2column username format a20
3
4select credential_name,
5       username,
6       enabled
7from   user_credentials
8order by credential_name;
9
10CREDENTIAL_NAME           USERNAME             ENABL
11------------------------- -------------------- -----
12OBJ_STORE_CRED            me@example.com       TRUE
13
14SQL>

DISABLE_CREDENTIAL 和 ENABLE_CREDENTIAL 过程分别禁用和启用凭据。

SQL

1begin
2  dbms_credential.disable_credential('obj_store_cred');
3
4  dbms_credential.enable_credential('obj_store_cred');
5end;/

UPDATE_CREDENTIALS 过程允许我们编辑凭证的属性。

SQL

1begin
2  dbms_credential.update_credential(
3    credential_name => 'obj_store_cred',
4    attribute       => 'username', 
5    value           => 'me@example.com');
6
7  dbms_credential.update_credential(
8    credential_name => 'obj_store_cred',
9    attribute       => 'password', 
10    value           => '{my-Auth-Token}');
11end;
12/

DROP_CREDENTIAL 过程删除命名凭据。

SQL

1begin
2  dbms_cloud.drop_credential(credential_name => 'obj_store_cred');
3end;
4/

以下示例需要有效凭据。

对象和文件

有几个例程可用于操作本地数据库文件系统上的文件和云对象存储中的对象。

在数据库服务器文件系统上创建一个文件。

Bash

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

我们使用 PUT_OBJECT 过程将文件从目录对象位置传输到云对象存储。

SQL

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

PUT_OBJECT 过程有一个重载，用于将 BLOB 的内容传输到对象存储。

SQL

1declare
2  l_file blob;
3begin
4  l_file := utl_raw.cast_to_raw('This is another test file');
5
6  dbms_cloud.put_object (
7    credential_name => 'obj_store_cred',
8    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/test2.txt',
9    contents        => l_file);
10end;
11/

LIST_OBJECTS 表函数列出对象存储 URI 指向的位置中的对象。

SQL

1set linesize 150
2column object_name format a12
3column checksum format a35
4column created format a35
5column last_modified format a35
6
7select *
8from   dbms_cloud.list_objects(
9        credential_name => 'obj_store_cred',
10        location_uri    => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket');
11
12OBJECT_NAME       BYTES CHECKSUM                            CREATED                             LAST_MODIFIED
13------------ ---------- ----------------------------------- ----------------------------------- -----------------------------------
14test1.txt            20 5dd39cab1c53c2c77cd352983f9641e1                                        11-SEP-21 08.45.42.779000 AM +00:00
15test2.txt            25 d0914057907f9d04dd9e68b1c1e180f0                                        11-SEP-21 08.45.54.148000 AM +00:00
16
17SQL>

我们使用 GET_METADATA 函数返回有关特定对象的信息。

SQL

1select dbms_cloud.get_metadata(
2         credential_name => 'obj_store_cred',
3         object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/test2.txt') as metadata
4from dual;
5
6METADATA
7--------------------------------------------------------------------------------
8{"Content-Length":25}
9
10SQL>

我们使用 GET_OBJECT 过程将对象从云对象存储传输到目录对象位置。

SQL

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

有一个 GET_OBJECT 函数可以将对象从云对象存储传输到 BLOB。

SQL

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

DELETE_OBJECT 过程从云对象存储中删除对象。

SQL

1begin
2  dbms_cloud.delete_object(
3    credential_name => 'obj_store_cred',
4    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/test1.txt');
5
6  dbms_cloud.delete_object(
7    credential_name => 'obj_store_cred',
8    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/test2.txt');
9end;
10/

DELETE_FILE 过程从目录对象位置删除文件。

SQL

1begin
2  dbms_cloud.delete_file(
3    directory_name => 'tmp_files_dir',
4    file_name      => 'test1.txt');
5
6  dbms_cloud.delete_file(
7    directory_name => 'tmp_files_dir',
8    file_name      => 'test2.txt');
9end;
10/

LIST_FILES 表函数列出指定 Oracle 目录对象指向的位置中的文件。文档说它只支持映射到 Oracle 文件系统 (OFS) 或数据库文件系统 (DBFS) 文件系统的目录对象，所以我们不能将它用于常规文件系统。它确实在自治数据库上提供的位置工作。

SQL

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

外部表

本节介绍基于云对象存储中的文件创建外部表。

CREATE_EXTERNAL_TABLE

我们使用以下内容创建一个名为“emp.dat”的文件并将其放入我们的对象存储中。它是一个没有标题的管道分隔文件。

SQL

17369|"SMITH"|"CLERK"|7902|17-DEC-80|800||20
27499|"ALLEN"|"SALESMAN"|7698|20-FEB-81|1600|300|30
37521|"WARD"|"SALESMAN"|7698|22-FEB-81|1250|500|30
47566|"JONES"|"MANAGER"|7839|02-APR-81|2975||20
57654|"MARTIN"|"SALESMAN"|7698|28-SEP-81|1250|1400|30
67698|"BLAKE"|"MANAGER"|7839|01-MAY-81|2850||30
77782|"CLARK"|"MANAGER"|7839|09-JUN-81|2450||10
87788|"SCOTT"|"ANALYST"|7566|19-APR-87|3000||20
97839|"KING"|"PRESIDENT"||17-NOV-81|5000||10
107844|"TURNER"|"SALESMAN"|7698|08-SEP-81|1500|0|30
117876|"ADAMS"|"CLERK"|7788|23-MAY-87|1100||20
127900|"JAMES"|"CLERK"|7698|03-DEC-81|950||30
137902|"FORD"|"ANALYST"|7566|03-DEC-81|3000||20
147934|"MILLER"|"CLERK"|7782|23-JAN-82|1300||10

CREATE_EXTERNAL_TABLE 过程基于云对象存储中的文件创建名为 EMP_EXT 的外部表。

SQL

1--drop table emp_ext;
2
3begin
4  dbms_cloud.create_external_table(
5    table_name      => 'emp_ext',
6    credential_name => 'obj_store_cred',
7    file_uri_list   => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/emp.dat',
8    column_list     => 'empno     number(4),
9                        ename     varchar2(10),
10                        job       varchar2(9),
11                        mgr       number(4),
12                        hiredate  date,
13                        sal       number(7,2),
14                        comm      number(7,2),
15                        deptno    number(2)',
16    format          => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true')
17 );
18end;
19/

我们查询外部表，它从云对象存储中读取数据。

SQL

1select * from emp_ext;
2
3     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
4---------- ---------- --------- ---------- --------- ---------- ---------- ----------
5      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
6      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
7      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
8      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
9      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
10      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
11      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
12      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
13      7839 KING       PRESIDENT            17-NOV-81       5000                    10
14      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
15      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
16      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
17      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
18      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
19
2014 rows selected.
21
22SQL>

FORMAT 参数允许我们调整加载过程以适应数据文件内容。可以在此处找到格式选项的完整列表，以下示例适用于 CSV 文件。

我们使用以下内容创建一个名为“emp.csv”的文件并将其放入我们的对象存储中。它是一个带有标题行的 CSV 文件。

SQL

1"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
27369,"SMITH","CLERK",7902,17-DEC-80,800,,20
37499,"ALLEN","SALESMAN",7698,20-FEB-81,1600,300,30
47521,"WARD","SALESMAN",7698,22-FEB-81,1250,500,30
57566,"JONES","MANAGER",7839,02-APR-81,2975,,20
67654,"MARTIN","SALESMAN",7698,28-SEP-81,1250,1400,30
77698,"BLAKE","MANAGER",7839,01-MAY-81,2850,,30
87782,"CLARK","MANAGER",7839,09-JUN-81,2450,,10
97788,"SCOTT","ANALYST",7566,19-APR-87,3000,,20
107839,"KING","PRESIDENT",,17-NOV-81,5000,,10
117844,"TURNER","SALESMAN",7698,08-SEP-81,1500,0,30
127876,"ADAMS","CLERK",7788,23-MAY-87,1100,,20
137900,"JAMES","CLERK",7698,03-DEC-81,950,,30
147902,"FORD","ANALYST",7566,03-DEC-81,3000,,20
157934,"MILLER","CLERK",7782,23-JAN-82,1300,,10

CREATE_EXTERNAL_TABLE 过程基于云对象存储中的文件创建名为 EMP_CSV_EXT 的外部表。

SQL

1--drop table emp_csv_ext;
2
3begin
4  dbms_cloud.create_external_table(
5    table_name      => 'emp_csv_ext',
6    credential_name => 'obj_store_cred',
7    file_uri_list   => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/emp.csv',
8    column_list     => 'empno     number(4),
9                        ename     varchar2(10),
10                        job       varchar2(9),
11                        mgr       number(4),
12                        hiredate  date,
13                        sal       number(7,2),
14                        comm      number(7,2),
15                        deptno    number(2)',
16    format          => json_object('type' value 'csv', 'skipheaders' value '1')
17 );
18end;
19/

我们查询外部表，它从云对象存储中读取数据。

SQL

1select * from emp_csv_ext;
2
3     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
4---------- ---------- --------- ---------- --------- ---------- ---------- ----------
5      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
6      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
7      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
8      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
9      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
10      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
11      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
12      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
13      7839 KING       PRESIDENT            17-NOV-81       5000                    10
14      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
15      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
16      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
17      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
18      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
19
2014 rows selected.
21
22SQL>

VALIDATE_EXTERNAL_TABLE 过程允许我们检查外部表的有效性。

SQL

1begin
2  dbms_cloud.validate_external_table('emp_csv_ext');
3end;
4/

CREATE_EXTERNAL_PART_TABLE

使用以下查询创建四个 CSV 文件：

SQL

1set markup csv on quote on
2set trimspool on linesize 1000 feedback off pagesize 0
3
4spool /tmp/files/gbr1.txt
5select 'GBR',
6       object_id,
7       owner,
8       object_name
9from   all_objects
10where  object_id <= 2000
11and    rownum <= 1000;
12spool off
13
14spool /tmp/files/gbr2.txt
15select 'GBR',
16       object_id,
17       owner,
18       object_name
19from   all_objects
20where  object_id BETWEEN 2000 AND 3999
21and    rownum <= 1000;
22spool off
23
24spool /tmp/files/ire1.txt
25select 'IRE',
26       object_id,
27       owner,
28       object_name
29from   all_objects
30where  object_id <= 2000
31and    rownum <= 1000;
32spool off
33
34spool /tmp/files/ire2.txt
35select 'IRE',
36       object_id,
37       owner,
38       object_name
39from   all_objects
40where  object_id BETWEEN 2000 AND 3999
41and    rownum <= 1000;
42spool off
43
44set markup csv off
45set trimspool on linesize 1000 feedback off pagesize 14

在上传文件之前，您可能需要稍微清理文件的开头和结尾。将文件复制到对象存储：

SQL

1begin
2  dbms_cloud.put_object (
3    credential_name => 'obj_store_cred',
4    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr1.txt',
5    directory_name  => 'tmp_files_dir',
6    file_name       => 'gbr1.txt');
7end;
8/
9
10begin
11  dbms_cloud.put_object (
12    credential_name => 'obj_store_cred',
13    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr2.txt',
14    directory_name  => 'tmp_files_dir',
15    file_name       => 'gbr2.txt');
16end;
17/
18
19begin
20  dbms_cloud.put_object (
21    credential_name => 'obj_store_cred',
22    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire1.txt',
23    directory_name  => 'tmp_files_dir',
24    file_name       => 'ire1.txt');
25end;
26/
27
28begin
29  dbms_cloud.put_object (
30    credential_name => 'obj_store_cred',
31    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire2.txt',
32    directory_name  => 'tmp_files_dir',
33    file_name       => 'ire2.txt');
34end;
35/

CREATE_EXTERNAL_PART_TABLE 过程基于云对象存储中的文件创建名为 COUNTRY_PART_TAB_EXT 的外部分区表。

SQL

1--drop table country_part_tab_ext;
2
3begin
4  dbms_cloud.create_external_part_table(
5    table_name      => 'country_part_tab_ext',
6    credential_name => 'obj_store_cred',
7    format          => json_object('type' value 'csv', 'skipheaders' value '1'),
8    column_list     => 'country_code  varchar2(3),
9                        object_id     number,
10                        owner         varchar2(128),
11                        object_name   varchar2(128)',
12    partitioning_clause => 'partition by list (country_code) (
13                              partition part_gbr values (''GBR'') location (
14                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr1.txt'',
15                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr2.txt''
16                              ),
17                              partition part_ire values (''IRE'') location (
18                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire1.txt'',
19                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire2.txt''
20                              )
21                            )'
22  );
23end;
24/

我们查询外部表，它从云对象存储中读取数据。

SQL

1select country_code, count(*) as amount
2from   country_part_tab_ext
3group by country_code
4order by country_code;
5
6COU     AMOUNT
7--- ----------
8GBR       2000
9IRE       2000
10
11SQL>

VALIDATE_EXTERNAL_PART_TABLE 过程允许我们检查外部分区表的有效性。

SQL

1begin
2  dbms_cloud.validate_external_part_table('country_part_tab_ext');
3end;
4/

CREATE_HYBRID_PART_TABLE

CREATE_HYBRID_PART_TABLE 过程基于云对象存储中的文件创建一个名为 COUNTRY_HYBRID_PART_TAB_EXT 的外部混合分区表。

SQL

1--drop table country_hybrid_part_tab_ext;
2
3begin
4  dbms_cloud.create_hybrid_part_table(
5    table_name      => 'country_hybrid_part_tab_ext',
6    credential_name => 'obj_store_cred',
7    format          => json_object('type' value 'csv', 'skipheaders' value '1'),
8    column_list     => 'country_code  varchar2(3),
9                        object_id     number,
10                        owner         varchar2(128),
11                        object_name   varchar2(128)',
12    partitioning_clause => 'partition by list (country_code) (
13                              partition part_gbr values (''GBR'') external location (
14                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr1.txt'',
15                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr2.txt''
16                              ),
17                              partition part_ire values (''IRE'') external location (
18                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire1.txt'',
19                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire2.txt''
20                              ),
21                              partition part_usa values (''USA'')
22                            )'
23  );
24end;
25/

我们在常规分区中插入一行。

SQL

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

我们查询外部表，它从云对象存储中读取数据。

SQL

1select country_code, count(*) as amount
2from   country_hybrid_part_tab_ext
3group by country_code
4order by country_code;
5
6COU     AMOUNT
7--- ----------
8GBR       2000
9IRE       2000
10USA          1
11
12SQL>

VALIDATE_HYBRID_PART_TABLE 过程允许我们检查外部混合分区表的有效性。

SQL

1begin
2  dbms_cloud.validate_hybrid_part_table('country_hybrid_part_tab_ext');
3end;
4/

COPY_DATA

COPY_DATA 过程允许我们将数据从云对象存储复制到现有表中。

我们使用以下内容创建一个名为“emp.csv”的文件并将其放入我们的对象存储中。它是一个带有标题行的 CSV 文件。

SQL

1"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
27369,"SMITH","CLERK",7902,17-DEC-80,800,,20
37499,"ALLEN","SALESMAN",7698,20-FEB-81,1600,300,30
47521,"WARD","SALESMAN",7698,22-FEB-81,1250,500,30
57566,"JONES","MANAGER",7839,02-APR-81,2975,,20
67654,"MARTIN","SALESMAN",7698,28-SEP-81,1250,1400,30
77698,"BLAKE","MANAGER",7839,01-MAY-81,2850,,30
87782,"CLARK","MANAGER",7839,09-JUN-81,2450,,10
97788,"SCOTT","ANALYST",7566,19-APR-87,3000,,20
107839,"KING","PRESIDENT",,17-NOV-81,5000,,10
117844,"TURNER","SALESMAN",7698,08-SEP-81,1500,0,30
127876,"ADAMS","CLERK",7788,23-MAY-87,1100,,20
137900,"JAMES","CLERK",7698,03-DEC-81,950,,30
147902,"FORD","ANALYST",7566,03-DEC-81,3000,,20
157934,"MILLER","CLERK",7782,23-JAN-82,1300,,10

我们截断本地 EMP 表并使用 COPY_DATA 过程从云对象存储重新加载数据。

SQL

1truncate table emp;
2
3begin
4  dbms_cloud.copy_data(
5    table_name      => 'emp',
6    credential_name => 'obj_store_cred',
7    file_uri_list   => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/emp.csv',
8    format          => json_object('type' value 'csv', 'skipheaders' value '1')
9  );
10end;
11/

我们查询EMP表，可以看到数据已经加载完毕。

SQL

1select * from emp;
2
3     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
4---------- ---------- --------- ---------- --------- ---------- ---------- ----------
5      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
6      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
7      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
8      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
9      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
10      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
11      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
12      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
13      7839 KING       PRESIDENT            17-NOV-81       5000                    10
14      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
15      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
16      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
17      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
18      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
19
2014 rows selected.
21
22SQL>

与外部表示例类似，FORMAT 参数允许我们定制加载过程以适应数据文件内容。

Export Data

EXPORT_DATA 过程获取查询生成的数据，并以请求的格式将其导出到云对象存储。这似乎不适用于 DBMS_CLOUD 包的本地版本，但适用于自治数据库。

SQL

1begin
2  dbms_cloud.export_data (
3    credential_name => 'obj_store_cred',
4    file_uri_list   => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/emp.json',
5    query           => 'select * from emp',
6    format          => '{"type" : "JSON"}'
7  );
8end;
9/
10
11begin
12  dbms_cloud.export_data (
13    credential_name => 'obj_store_cred',
14    file_uri_list   => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/emp.csv',
15    query           => 'select * from emp',
16    format          => '{"type" : "CSV"}'
17  );
18end;
19/

SODA Collections

Oracle 数据库可用作使用简单 Oracle 文档访问 (SODA) 的文档存储。您可以在此处阅读有关 SODA 的信息。

我们创建一个名为“TestCollection1”的新集合。

SQL

1set serveroutput on
2
3declare
4  l_collection  soda_collection_t;
5begin
6  l_collection := dbms_soda.create_collection('TestCollection1');
7
8  if l_collection is not null then
9    dbms_output.put_line('Collection ID : ' || l_collection.get_name());
10  else
11    dbms_output.put_line('Collection does not exist.');  
12  end if;
13end;
14/
15Collection ID : TestCollection1
16
17
18PL/SQL procedure successfully completed.
19
20SQL>

使用以下内容创建一个名为“fruit.json”的文件并将其上传到您的云对象存储。

SQL

1{"fruit": "banana"}

COPY_COLLECTION 过程将数据从我们的云对象存储加载到集合中。

SQL

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

我们可以使用以下查询查看集合中的数据。

SQL

1select json_document
2from   "TestCollection1";
3
4JSON_DOCUMENT
5--------------------------------------------------------------------------------
6{"fruit":"banana"}
7
8SQL>

删除操作

许多 DBMS_CLOUD 操作会产生额外的文件（日志文件、坏文件、临时文件等）。一旦操作完成，这些需要清理。上面显示的几个过程具有返回操作 ID 值的重载，该值可与 DELETE_OPERATION 过程一起使用以清理额外的文件。或者，可以使用 USER_LOAD_OPERATIONS 视图显示当前会话的操作。

SQL

1SQL> desc user_load_operations
2 Name                                                  Null?    Type
3 ----------------------------------------------------- -------- ------------------------------------
4 ID                                                    NOT NULL NUMBER
5 TYPE                                                  NOT NULL VARCHAR2(128)
6 SID                                                   NOT NULL NUMBER
7 SERIAL#                                               NOT NULL NUMBER
8 START_TIME                                                     TIMESTAMP(6) WITH TIME ZONE
9 UPDATE_TIME                                                    TIMESTAMP(6) WITH TIME ZONE
10 STATUS                                                         VARCHAR2(9)
11 OWNER_NAME                                                     VARCHAR2(128)
12 TABLE_NAME                                                     VARCHAR2(128)
13 PARTITION_NAME                                                 VARCHAR2(128)
14 SUBPARTITION_NAME                                              VARCHAR2(128)
15 FILE_URI_LIST                                                  VARCHAR2(4000)
16 ROWS_LOADED                                                    NUMBER
17 LOGFILE_TABLE                                                  VARCHAR2(128)
18 BADFILE_TABLE                                                  VARCHAR2(128)
19 TEMPEXT_TABLE                                                  VARCHAR2(128)
20
21SQL>

我们使用 USER_LOAD_OPERATIONS 视图返回当前会话的操作。

SQL

1column type format a10
2
3select id, type
4from   user_load_operations
5order by 1;
6
7        ID TYPE
8---------- ----------
9         1 COPY
10        11 COPY
11
12SQL>

DELETE_OPERATION 过程允许我们清除与特定操作相关的附加文件。

SQL

1begin
2  dbms_cloud.delete_operation(1);
3end;
4/
5
6
7select id, type
8from   user_load_operations
9order by 1;
10
11        ID TYPE
12---------- ----------
13        11 COPY
14
15SQL>

DELETE_ALL_OPERATIONS 过程允许我们清理所有操作的附加文件，或者如果我们指定类型值，则清理特定类型操作的附加文件。

SQL

1-- Delete only COPY operations.
2begin
3  dbms_cloud.delete_all_operations('COPY');
4end;
5/
6
7-- Delete all operations.
8begin
9  dbms_cloud.delete_all_operations;
10end;
11/

SQL

1conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba
2
3--drop user testuser1 cascade;
4create user testuser1 identified by testuser1 quota unlimited on users;
5grant connect, resource to testuser1;
6
7grant create credential to testuser1;
8grant execute on dbms_cloud to testuser1;

SQL

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

SQL

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

SQL

1conn testuser1/testuser1@//localhost:1521/pdb1
2
3create table emp (
4  empno    number(4,0), 
5  ename    varchar2(10 byte), 
6  job      varchar2(9 byte), 
7  mgr      number(4,0), 
8  hiredate date, 
9  sal      number(7,2), 
10  comm     number(7,2), 
11  deptno   number(2,0), 
12  constraint pk_emp primary key (empno)
13);
14  
15insert 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);
16insert 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);
17insert 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);
18insert 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);
19insert 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);
20insert 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);
21insert 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);
22insert 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);
23insert 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);
24insert 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);
25insert 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);
26insert 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);
27insert 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);
28insert 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);
29commit;

SQL

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

SQL

1https://swiftobjectstorage.{region}.oraclecloud.com/v1/{namespace}/{bucket}/{objectname}
2
3https://objectstorage.{region}.oraclecloud.com/n/{namespace}/b/{bucket}/o/{objectname}

SQL

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

SQL

1begin
2  dbms_cloud.create_credential (
3    credential_name => 'obj_store_cred',
4    username        => 'me@example.com',
5    password        => '{my-Auth-Token}'
6  ) ;
7end;
8/

SQL

1begin
2  dbms_cloud.create_credential (
3    credential_name => 'obj_store_cred',
4    username        => 'my AWS access key',
5    password        => 'my AWS secret access key'
6  );
7end;
8/

SQL

1column credential_name format a25
2column username format a20
3
4select credential_name,
5       username,
6       enabled
7from   user_credentials
8order by credential_name;
9
10CREDENTIAL_NAME           USERNAME             ENABL
11------------------------- -------------------- -----
12OBJ_STORE_CRED            me@example.com       TRUE
13
14SQL>

SQL

1begin
2  dbms_credential.disable_credential('obj_store_cred');
3
4  dbms_credential.enable_credential('obj_store_cred');
5end;/

SQL

1begin
2  dbms_credential.update_credential(
3    credential_name => 'obj_store_cred',
4    attribute       => 'username', 
5    value           => 'me@example.com');
6
7  dbms_credential.update_credential(
8    credential_name => 'obj_store_cred',
9    attribute       => 'password', 
10    value           => '{my-Auth-Token}');
11end;
12/

SQL

1begin
2  dbms_cloud.drop_credential(credential_name => 'obj_store_cred');
3end;
4/

Bash

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

SQL

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

SQL

1declare
2  l_file blob;
3begin
4  l_file := utl_raw.cast_to_raw('This is another test file');
5
6  dbms_cloud.put_object (
7    credential_name => 'obj_store_cred',
8    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/test2.txt',
9    contents        => l_file);
10end;
11/

SQL

1set linesize 150
2column object_name format a12
3column checksum format a35
4column created format a35
5column last_modified format a35
6
7select *
8from   dbms_cloud.list_objects(
9        credential_name => 'obj_store_cred',
10        location_uri    => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket');
11
12OBJECT_NAME       BYTES CHECKSUM                            CREATED                             LAST_MODIFIED
13------------ ---------- ----------------------------------- ----------------------------------- -----------------------------------
14test1.txt            20 5dd39cab1c53c2c77cd352983f9641e1                                        11-SEP-21 08.45.42.779000 AM +00:00
15test2.txt            25 d0914057907f9d04dd9e68b1c1e180f0                                        11-SEP-21 08.45.54.148000 AM +00:00
16
17SQL>

SQL

1select dbms_cloud.get_metadata(
2         credential_name => 'obj_store_cred',
3         object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/test2.txt') as metadata
4from dual;
5
6METADATA
7--------------------------------------------------------------------------------
8{"Content-Length":25}
9
10SQL>

SQL

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

SQL

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

SQL

1begin
2  dbms_cloud.delete_object(
3    credential_name => 'obj_store_cred',
4    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/test1.txt');
5
6  dbms_cloud.delete_object(
7    credential_name => 'obj_store_cred',
8    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/test2.txt');
9end;
10/

SQL

1begin
2  dbms_cloud.delete_file(
3    directory_name => 'tmp_files_dir',
4    file_name      => 'test1.txt');
5
6  dbms_cloud.delete_file(
7    directory_name => 'tmp_files_dir',
8    file_name      => 'test2.txt');
9end;
10/

SQL

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

SQL

17369|"SMITH"|"CLERK"|7902|17-DEC-80|800||20
27499|"ALLEN"|"SALESMAN"|7698|20-FEB-81|1600|300|30
37521|"WARD"|"SALESMAN"|7698|22-FEB-81|1250|500|30
47566|"JONES"|"MANAGER"|7839|02-APR-81|2975||20
57654|"MARTIN"|"SALESMAN"|7698|28-SEP-81|1250|1400|30
67698|"BLAKE"|"MANAGER"|7839|01-MAY-81|2850||30
77782|"CLARK"|"MANAGER"|7839|09-JUN-81|2450||10
87788|"SCOTT"|"ANALYST"|7566|19-APR-87|3000||20
97839|"KING"|"PRESIDENT"||17-NOV-81|5000||10
107844|"TURNER"|"SALESMAN"|7698|08-SEP-81|1500|0|30
117876|"ADAMS"|"CLERK"|7788|23-MAY-87|1100||20
127900|"JAMES"|"CLERK"|7698|03-DEC-81|950||30
137902|"FORD"|"ANALYST"|7566|03-DEC-81|3000||20
147934|"MILLER"|"CLERK"|7782|23-JAN-82|1300||10

SQL

1--drop table emp_ext;
2
3begin
4  dbms_cloud.create_external_table(
5    table_name      => 'emp_ext',
6    credential_name => 'obj_store_cred',
7    file_uri_list   => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/emp.dat',
8    column_list     => 'empno     number(4),
9                        ename     varchar2(10),
10                        job       varchar2(9),
11                        mgr       number(4),
12                        hiredate  date,
13                        sal       number(7,2),
14                        comm      number(7,2),
15                        deptno    number(2)',
16    format          => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true')
17 );
18end;
19/

SQL

1select * from emp_ext;
2
3     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
4---------- ---------- --------- ---------- --------- ---------- ---------- ----------
5      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
6      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
7      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
8      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
9      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
10      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
11      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
12      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
13      7839 KING       PRESIDENT            17-NOV-81       5000                    10
14      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
15      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
16      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
17      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
18      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
19
2014 rows selected.
21
22SQL>

SQL

1"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
27369,"SMITH","CLERK",7902,17-DEC-80,800,,20
37499,"ALLEN","SALESMAN",7698,20-FEB-81,1600,300,30
47521,"WARD","SALESMAN",7698,22-FEB-81,1250,500,30
57566,"JONES","MANAGER",7839,02-APR-81,2975,,20
67654,"MARTIN","SALESMAN",7698,28-SEP-81,1250,1400,30
77698,"BLAKE","MANAGER",7839,01-MAY-81,2850,,30
87782,"CLARK","MANAGER",7839,09-JUN-81,2450,,10
97788,"SCOTT","ANALYST",7566,19-APR-87,3000,,20
107839,"KING","PRESIDENT",,17-NOV-81,5000,,10
117844,"TURNER","SALESMAN",7698,08-SEP-81,1500,0,30
127876,"ADAMS","CLERK",7788,23-MAY-87,1100,,20
137900,"JAMES","CLERK",7698,03-DEC-81,950,,30
147902,"FORD","ANALYST",7566,03-DEC-81,3000,,20
157934,"MILLER","CLERK",7782,23-JAN-82,1300,,10

SQL

1--drop table emp_csv_ext;
2
3begin
4  dbms_cloud.create_external_table(
5    table_name      => 'emp_csv_ext',
6    credential_name => 'obj_store_cred',
7    file_uri_list   => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/emp.csv',
8    column_list     => 'empno     number(4),
9                        ename     varchar2(10),
10                        job       varchar2(9),
11                        mgr       number(4),
12                        hiredate  date,
13                        sal       number(7,2),
14                        comm      number(7,2),
15                        deptno    number(2)',
16    format          => json_object('type' value 'csv', 'skipheaders' value '1')
17 );
18end;
19/

SQL

1select * from emp_csv_ext;
2
3     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
4---------- ---------- --------- ---------- --------- ---------- ---------- ----------
5      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
6      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
7      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
8      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
9      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
10      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
11      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
12      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
13      7839 KING       PRESIDENT            17-NOV-81       5000                    10
14      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
15      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
16      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
17      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
18      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
19
2014 rows selected.
21
22SQL>

SQL

1begin
2  dbms_cloud.validate_external_table('emp_csv_ext');
3end;
4/

SQL

1set markup csv on quote on
2set trimspool on linesize 1000 feedback off pagesize 0
3
4spool /tmp/files/gbr1.txt
5select 'GBR',
6       object_id,
7       owner,
8       object_name
9from   all_objects
10where  object_id <= 2000
11and    rownum <= 1000;
12spool off
13
14spool /tmp/files/gbr2.txt
15select 'GBR',
16       object_id,
17       owner,
18       object_name
19from   all_objects
20where  object_id BETWEEN 2000 AND 3999
21and    rownum <= 1000;
22spool off
23
24spool /tmp/files/ire1.txt
25select 'IRE',
26       object_id,
27       owner,
28       object_name
29from   all_objects
30where  object_id <= 2000
31and    rownum <= 1000;
32spool off
33
34spool /tmp/files/ire2.txt
35select 'IRE',
36       object_id,
37       owner,
38       object_name
39from   all_objects
40where  object_id BETWEEN 2000 AND 3999
41and    rownum <= 1000;
42spool off
43
44set markup csv off
45set trimspool on linesize 1000 feedback off pagesize 14

SQL

1begin
2  dbms_cloud.put_object (
3    credential_name => 'obj_store_cred',
4    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr1.txt',
5    directory_name  => 'tmp_files_dir',
6    file_name       => 'gbr1.txt');
7end;
8/
9
10begin
11  dbms_cloud.put_object (
12    credential_name => 'obj_store_cred',
13    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr2.txt',
14    directory_name  => 'tmp_files_dir',
15    file_name       => 'gbr2.txt');
16end;
17/
18
19begin
20  dbms_cloud.put_object (
21    credential_name => 'obj_store_cred',
22    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire1.txt',
23    directory_name  => 'tmp_files_dir',
24    file_name       => 'ire1.txt');
25end;
26/
27
28begin
29  dbms_cloud.put_object (
30    credential_name => 'obj_store_cred',
31    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire2.txt',
32    directory_name  => 'tmp_files_dir',
33    file_name       => 'ire2.txt');
34end;
35/

SQL

1--drop table country_part_tab_ext;
2
3begin
4  dbms_cloud.create_external_part_table(
5    table_name      => 'country_part_tab_ext',
6    credential_name => 'obj_store_cred',
7    format          => json_object('type' value 'csv', 'skipheaders' value '1'),
8    column_list     => 'country_code  varchar2(3),
9                        object_id     number,
10                        owner         varchar2(128),
11                        object_name   varchar2(128)',
12    partitioning_clause => 'partition by list (country_code) (
13                              partition part_gbr values (''GBR'') location (
14                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr1.txt'',
15                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr2.txt''
16                              ),
17                              partition part_ire values (''IRE'') location (
18                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire1.txt'',
19                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire2.txt''
20                              )
21                            )'
22  );
23end;
24/

SQL

1select country_code, count(*) as amount
2from   country_part_tab_ext
3group by country_code
4order by country_code;
5
6COU     AMOUNT
7--- ----------
8GBR       2000
9IRE       2000
10
11SQL>

SQL

1begin
2  dbms_cloud.validate_external_part_table('country_part_tab_ext');
3end;
4/

SQL

1--drop table country_hybrid_part_tab_ext;
2
3begin
4  dbms_cloud.create_hybrid_part_table(
5    table_name      => 'country_hybrid_part_tab_ext',
6    credential_name => 'obj_store_cred',
7    format          => json_object('type' value 'csv', 'skipheaders' value '1'),
8    column_list     => 'country_code  varchar2(3),
9                        object_id     number,
10                        owner         varchar2(128),
11                        object_name   varchar2(128)',
12    partitioning_clause => 'partition by list (country_code) (
13                              partition part_gbr values (''GBR'') external location (
14                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr1.txt'',
15                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr2.txt''
16                              ),
17                              partition part_ire values (''IRE'') external location (
18                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire1.txt'',
19                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire2.txt''
20                              ),
21                              partition part_usa values (''USA'')
22                            )'
23  );
24end;
25/

SQL

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

SQL

1select country_code, count(*) as amount
2from   country_hybrid_part_tab_ext
3group by country_code
4order by country_code;
5
6COU     AMOUNT
7--- ----------
8GBR       2000
9IRE       2000
10USA          1
11
12SQL>

SQL

1begin
2  dbms_cloud.validate_hybrid_part_table('country_hybrid_part_tab_ext');
3end;
4/

SQL

1"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
27369,"SMITH","CLERK",7902,17-DEC-80,800,,20
37499,"ALLEN","SALESMAN",7698,20-FEB-81,1600,300,30
47521,"WARD","SALESMAN",7698,22-FEB-81,1250,500,30
57566,"JONES","MANAGER",7839,02-APR-81,2975,,20
67654,"MARTIN","SALESMAN",7698,28-SEP-81,1250,1400,30
77698,"BLAKE","MANAGER",7839,01-MAY-81,2850,,30
87782,"CLARK","MANAGER",7839,09-JUN-81,2450,,10
97788,"SCOTT","ANALYST",7566,19-APR-87,3000,,20
107839,"KING","PRESIDENT",,17-NOV-81,5000,,10
117844,"TURNER","SALESMAN",7698,08-SEP-81,1500,0,30
127876,"ADAMS","CLERK",7788,23-MAY-87,1100,,20
137900,"JAMES","CLERK",7698,03-DEC-81,950,,30
147902,"FORD","ANALYST",7566,03-DEC-81,3000,,20
157934,"MILLER","CLERK",7782,23-JAN-82,1300,,10

SQL

1truncate table emp;
2
3begin
4  dbms_cloud.copy_data(
5    table_name      => 'emp',
6    credential_name => 'obj_store_cred',
7    file_uri_list   => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/emp.csv',
8    format          => json_object('type' value 'csv', 'skipheaders' value '1')
9  );
10end;
11/

SQL

1select * from emp;
2
3     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
4---------- ---------- --------- ---------- --------- ---------- ---------- ----------
5      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
6      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
7      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
8      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
9      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
10      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
11      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
12      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
13      7839 KING       PRESIDENT            17-NOV-81       5000                    10
14      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
15      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
16      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
17      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
18      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
19
2014 rows selected.
21
22SQL>

SQL

1begin
2  dbms_cloud.export_data (
3    credential_name => 'obj_store_cred',
4    file_uri_list   => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/emp.json',
5    query           => 'select * from emp',
6    format          => '{"type" : "JSON"}'
7  );
8end;
9/
10
11begin
12  dbms_cloud.export_data (
13    credential_name => 'obj_store_cred',
14    file_uri_list   => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/emp.csv',
15    query           => 'select * from emp',
16    format          => '{"type" : "CSV"}'
17  );
18end;
19/

SQL

1set serveroutput on
2
3declare
4  l_collection  soda_collection_t;
5begin
6  l_collection := dbms_soda.create_collection('TestCollection1');
7
8  if l_collection is not null then
9    dbms_output.put_line('Collection ID : ' || l_collection.get_name());
10  else
11    dbms_output.put_line('Collection does not exist.');  
12  end if;
13end;
14/
15Collection ID : TestCollection1
16
17
18PL/SQL procedure successfully completed.
19
20SQL>

SQL

1{"fruit": "banana"}

SQL

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

SQL

1select json_document
2from   "TestCollection1";
3
4JSON_DOCUMENT
5--------------------------------------------------------------------------------
6{"fruit":"banana"}
7
8SQL>

SQL

1SQL> desc user_load_operations
2 Name                                                  Null?    Type
3 ----------------------------------------------------- -------- ------------------------------------
4 ID                                                    NOT NULL NUMBER
5 TYPE                                                  NOT NULL VARCHAR2(128)
6 SID                                                   NOT NULL NUMBER
7 SERIAL#                                               NOT NULL NUMBER
8 START_TIME                                                     TIMESTAMP(6) WITH TIME ZONE
9 UPDATE_TIME                                                    TIMESTAMP(6) WITH TIME ZONE
10 STATUS                                                         VARCHAR2(9)
11 OWNER_NAME                                                     VARCHAR2(128)
12 TABLE_NAME                                                     VARCHAR2(128)
13 PARTITION_NAME                                                 VARCHAR2(128)
14 SUBPARTITION_NAME                                              VARCHAR2(128)
15 FILE_URI_LIST                                                  VARCHAR2(4000)
16 ROWS_LOADED                                                    NUMBER
17 LOGFILE_TABLE                                                  VARCHAR2(128)
18 BADFILE_TABLE                                                  VARCHAR2(128)
19 TEMPEXT_TABLE                                                  VARCHAR2(128)
20
21SQL>

SQL

1column type format a10
2
3select id, type
4from   user_load_operations
5order by 1;
6
7        ID TYPE
8---------- ----------
9         1 COPY
10        11 COPY
11
12SQL>

SQL

1begin
2  dbms_cloud.delete_operation(1);
3end;
4/
5
6
7select id, type
8from   user_load_operations
9order by 1;
10
11        ID TYPE
12---------- ----------
13        11 COPY
14
15SQL>

SQL

1-- Delete only COPY operations.
2begin
3  dbms_cloud.delete_all_operations('COPY');
4end;
5/
6
7-- Delete all operations.
8begin
9  dbms_cloud.delete_all_operations;
10end;
11/