How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1)
今回のAmazon S3にアクセスすることが目的であることもあり、AWS Database Blogの以下の記事に従って作業することにしました。
Use the DBMS_CLOUD package in Amazon RDS Custom for Oracle for direct Amazon S3 integration
Amazon S3バケットを作成する
ポリシーを作成する
IAMユーザーを作成する
パッケージDBMS_CLOUDをインストールする
[oracle@apexs3 ~]$ . oraenv
ORACLE_SID = [oracle] ? FREE
The Oracle base has been set to /opt/oracle
[oracle@apexs3 ~]$
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys/[SYSのパスワード] --force_pdb_mode 'READ WRITE' -b dbms_cloud_install -d /home/oracle -l /home/oracle dbms_cloud_install.sql[oracle@apexs3 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys/****** --force_pdb_mode 'READ WRITE' -b dbms_cloud_install -d /home/oracle -l /home/oracle dbms_cloud_install.sql
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/dbms_cloud_install_catcon_2850.lst]
catcon::set_log_file_base_path: catcon: See [/home/oracle/dbms_cloud_install*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/home/oracle/dbms_cloud_install_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@apexs3 ~]$
[oracle@apexs3 ~]$ sqlplus / as sysdba
SQL*Plus: Release 23.0.0.0.0 - Production on Mon Dec 11 13:36:11 2023
Version 23.3.0.23.09
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL> column name format a10
column owner format a20
column object_name format a12
column status format a10
set tab off
select v.name, o.owner, o.object_name, o.status, o.sharing, o.oracle_maintained
from cdb_objects o join v$containers v on o.con_id=v.con_id
where o.object_name = 'DBMS_CLOUD'
order by name;SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4
NAME OWNER OBJECT_NAME STATUS SHARING O
---------- -------------------- ------------ ---------- ------------------ -
CDB$ROOT PUBLIC DBMS_CLOUD VALID METADATA LINK Y
CDB$ROOT C##CLOUD$SERVICE DBMS_CLOUD VALID METADATA LINK Y
CDB$ROOT C##CLOUD$SERVICE DBMS_CLOUD VALID METADATA LINK Y
FREEPDB1 PUBLIC DBMS_CLOUD VALID METADATA LINK Y
FREEPDB1 C##CLOUD$SERVICE DBMS_CLOUD VALID METADATA LINK Y
FREEPDB1 C##CLOUD$SERVICE DBMS_CLOUD VALID METADATA LINK Y
6 rows selected.
SQL>
データベースにSSLウォレットを設定する
$ORACLE_HOME/network/admin/sqlnet.oraに以下の１行を追記します。
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/oracle/wallet)))CDBにACEsを設定する
[oracle@apexs3 ~]$ sqlplus / as sysdba
SQL*Plus: Release 23.0.0.0.0 - Production on Mon Dec 11 13:46:24 2023
Version 23.3.0.23.09
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL> @setup_aces.sql
Session altered.
old 9: principal_name => upper('&clouduser'),
new 9: principal_name => upper('C##CLOUD$SERVICE'),
old 14: wallet_path => 'file:&sslwalletdir',
new 14: wallet_path => 'file:/home/oracle/wallet',
old 17: principal_name => upper('&clouduser'),
new 17: principal_name => upper('C##CLOUD$SERVICE'),
PL/SQL procedure successfully completed.
old 3: execute immediate 'alter database property set ssl_wallet=''&sslwalletdir''';
new 3: execute immediate 'alter database property set ssl_wallet=''/home/oracle/wallet''';
PL/SQL procedure successfully completed.
Session altered.
SQL>
PDBにACEsを設定する
スクリプト名はconfigure_role.sqlとします。
Oracle APEXの環境を手順通りに作成していると、FREEPDB1というPDBにAPEXがインストールされています。
sqlplus / as sysdba
alter session set container=FREEPDB1;
create role CLOUD_USER_ROLE;
@configure_role
[oracle@apexs3 ~]$ sqlplus / as sysdba
SQL*Plus: Release 23.0.0.0.0 - Production on Mon Dec 11 14:05:14 2023
Version 23.3.0.23.09
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL> alter session set container=FREEPDB1;
Session altered.
SQL> create role CLOUD_USER_ROLE;
Role created.
SQL> @configure_role
Session altered.
old 9: principal_name => upper('&cloudrole'),
new 9: principal_name => upper('CLOUD_USER_ROLE'),
old 14: wallet_path => 'file:&sslwalletdir',
new 14: wallet_path => 'file:/home/oracle/wallet',
old 17: principal_name => upper('&cloudrole'),
new 17: principal_name => upper('CLOUD_USER_ROLE'),
PL/SQL procedure successfully completed.
Session altered.
SQL>
ロールCLOUD_USER_ROLEに、ビューDBA_CREDENTIALSのSELECT権限とパッケージDBMS_CLOUDの実行権限を与えます。
SQL> grant execute on dbms_cloud to cloud_user_role;
Grant succeeded.
SQL> grant select on dba_credentials to cloud_user_role;
Grant succeeded.
SQL>
APEXのワークスペースのスキーマに作成したロールを割り当てます。以下ではWKSP_APEXDEVというスキーマが作成済みとしています。
SQL> grant cloud_user_role to wksp_apexdev;
Grant succeeded.
SQL>
以上で、Oracle APEXのSQLコマンドより、パッケージDBMS_CLOUDを呼び出す準備ができました。
DBMS_CLOUDを呼び出す
begin
dbms_cloud.create_credential(
credential_name => 'cred_demouser'
,username => 'アクセスキー'
,password => 'シークレットアクセスキー'
);
end;
declare
l_resp dbms_cloud_types.resp;
l_blob blob;
l_clob clob;
begin
l_clob := 'my first upload';
l_blob := apex_util.clob_to_blob(l_clob);
l_resp := dbms_cloud.send_request(
credential_name => 'cred_demouser'
,uri => 'https://s3.us-east-1.amazonaws.com/myoraclefreebucket/test.txt'
,method => 'PUT'
,body => l_blob
);
end;
select * from dbms_cloud.list_objects(
credential_name => 'cred_demouser'
,location_uri => 'https://s3.us-east-1.amazonaws.com/myoraclefreebucket'
)
declare
l_resp dbms_cloud_types.resp;
l_clob clob;
begin
l_resp := dbms_cloud.send_request(
credential_name => 'cred_demouser'
,uri => 'https://s3.us-east-1.amazonaws.com/myoraclefreebucket/test.txt'
,method => 'GET'
);
l_clob := dbms_cloud.get_response_text(
resp => l_resp
);
dbms_output.put_line(l_clob);
end;
begin
dbms_cloud.send_request(
credential_name => 'cred_demouser'
,uri => 'https://s3.us-east-1.amazonaws.com/myoraclefreebucket/test.txt'
,method => 'DELETE'
);
end;
