ORDS開発者ガイドのA. Setting-up a PL/SQL Gateway Userとして記載されている手順を確認しました。
オンプレ環境での確認
drop pluggable database xepdb1 including datafiles;
create pluggable database xepdb1 file_name_convert = ('pdbseed','xepdb1') admin user pdb_admin identified by oracle;
alter pluggable database xepdb1 open read write;
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Dec 12 14:29:10 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> alter pluggable database xepdb1 close immediate;
Pluggable database altered.
SQL> drop pluggable database xepdb1 including datafiles;
Pluggable database dropped.
SQL> create pluggable database xepdb1 file_name_convert = ('pdbseed','xepdb1') admin user pdb_admin identified by oracle;
Pluggable database created.
SQL> alter pluggable database xepdb1 open read write;
Pluggable database altered.
SQL> exit
マニュアルに記載されているinstall.sqlを実行します。
[oracle@localhost config]$ sqlplus sys/********@localhost/xepdb1 as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Dec 12 14:39:00 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> @install パスワード
SP2-0734: unknown command beginning "install.sq..." - rest of line ignored.
User created.
old 1: create user example_user1 identified by ^PWD
new 1: create user example_user1 identified by oracle
User created.
old 1: create user example_user2 identified by ^PWD
new 1: create user example_user2 identified by oracle
User created.
Grant succeeded.
Grant succeeded.
Session altered.
Procedure created.
Grant succeeded.
Grant succeeded.
Disconnected from Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
[oracle@localhost config]$
マニュアルに記載されているコマンドを実行し、ORDSを構成します。マニュアルではinstall-cliとなっていますが、installの間違いです。
PDB名はPDB1ではなくXEPDB1なので、その部分は変更します。SQL Developer Webの構成は不要なのでfalseを指定します。ログ出力の指定も省略しています。パスワードについてはプロンプトが表示されるので、手作業で入力します。
ords --config /etc/ords/config install --db-pool xepdb1 --admin-user sys --proxy-user --db-hostname localhost --db-port 1521 --db-servicename xepdb1 --feature-sdw false --gateway-user example_user1 --gateway-mode proxied
[oracle@localhost config]$ /usr/local/bin/ords --config /etc/ords/config install --db-pool xepdb1 --admin-user sys --proxy-user --db-hostname localhost --db-port 1521 --db-servicename xepdb1 --feature-sdw false --gateway-user example_user1 --gateway-mode proxied
ORDS: Release 22.4 Production on Mon Dec 12 05:36:34 2022
Copyright (c) 2010, 2022, Oracle.
Configuration:
/etc/ords/config/
Enter the database password for SYS AS SYSDBA: ********
Enter the database password for ORDS_PUBLIC_USER: ********
Confirm password: ********
Oracle REST Data Services - Non-Interactive Install
Retrieving information.
The setting named: db.connectionType was set to: basic in configuration: xepdb1
The setting named: db.hostname was set to: localhost in configuration: xepdb1
The setting named: db.port was set to: 1521 in configuration: xepdb1
The setting named: db.servicename was set to: xepdb1 in configuration: xepdb1
The setting named: plsql.gateway.mode was set to: proxied in configuration: xepdb1
The setting named: db.username was set to: ORDS_PUBLIC_USER in configuration: xepdb1
The setting named: db.password was set to: ****** in configuration: xepdb1
The setting named: feature.sdw was set to: false in configuration: xepdb1
[中略]
PL/SQL procedure successfully completed.
2022-12-12T05:37:52.978Z INFO Completed configuring PL/SQL gateway user for Oracle REST Data Services version 22.4.0.r3401044. Elapsed time: 00:00:00.235
[*** Info: Completed configuring PL/SQL gateway user for Oracle REST Data Services version 22.4.0.r3401044. Elapsed time: 00:00:00.235
]
2022-12-12T05:37:52.987Z INFO To run in standalone mode, use the ords serve command:
2022-12-12T05:37:52.988Z INFO ords --config /etc/ords/config serve
2022-12-12T05:37:52.989Z INFO Visit the ORDS Documentation to access tutorials, developer guides and more to help you get started with the new ORDS Command Line Interface (http://oracle.com/rest).
[oracle@localhost config]$
以上でORDSの構成は完了です。
[oracle@localhost config]$ sqlplus sys/********@localhost/xepdb1 as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Dec 12 14:39:00 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> grant execute on sample_plsql_app.sample_proc to ords_metadata;
Grant succeeded.
SQL> exit
ords_admin.add_plsql_gateway_procedure(
p_owner => 'SAMPLE_PLSQL_APP'
,p_package_name => null
,p_procedure_name => 'SAMPLE_PROC'
end;
/
[oracle@localhost config]$ sqlplus sys/oracle@localhost/xepdb1 as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Dec 12 14:41:05 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> begin
2 ords_admin.add_plsql_gateway_procedure(
3 p_owner => 'SAMPLE_PLSQL_APP'
4 ,p_package_name => null
5 ,p_procedure_name => 'SAMPLE_PROC'
6 );
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> exit
[oracle@localhost config]$ /usr/local/bin/ords --config /etc/ords/config serve
Autonomous Databaseでの確認
create user sample_plsql_app no authentication account lock;
grant execute on sample_plsql_app.sample_proc to ords_plsql_gateway;
grant execute on sample_plsql_app.sample_proc to ords_metadata;
