最近のASK TOMのOffice Hourにて、SQLclの開発者とOracle CorporationでAPEXを使ってアプリケーションを開発している方（APEX自体の開発者ではない）が、SQLclのprojectコマンドとその使い方を紹介しています。
- Autonomous Databaseに新規でREST対応SQLが有効なスキーマを作成する。
- REST対応SQLが有効なスキーマをOAuth2で保護する。
- 作成したスキーマに紐づけてAPEXワークスペースを作成する。
- APEXワークスペースに表やアプリケーションを作成する。
- 手元のPCにAPEXの環境を作成する。
- SQLclでAutonomous Databaseに接続し、projectコマンドを実行して表やAPEXアプリケーションをエクスポートする。その後、エクスポートした内容からアーティファクトを作成し、ローカルの環境に適用する。
- Autonomous Database上のデータを、REST対応SQL経由でローカルの環境にコピーする。
REST対応SQLが有効なスキーマの作成 - Autonomous Database
create user wksp_myproj identified by "パスワード";
grant connect, resource to wksp_myproj;
begin
ords_admin.enable_schema(
p_enabled => TRUE,
p_schema => 'WKSP_MYPROJ',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'myproj',
p_auto_rest_auth=> true
);
commit;
end;
/
alter user wksp_myproj quota unlimited on data;OAuthユーザーの作成 - Autonomous Database
begin
-- create oauth user
oauth.create_client(
p_name => 'restsqluser'
,p_grant_type => 'client_credentials' -- default
,p_support_email => 'yuNN@dummy.com'
,p_privilege_names => null
);
-- assign SQL Developer role
oauth.grant_client_role(
p_client_name => 'restsqluser'
,p_role_name => 'SQL Developer'
);
commit;
end;
/SQL> select client_id, client_secret from user_ords_clients where name = 'restsqluser';
CLIENT_ID CLIENT_SECRET
___________________________ ___________________________
pOi**************PdC7w.. kUZN**************jlG-Q..
SQL>
curl -X POST -u [クライアントID]:[クライアント・シークレット] --data "grant_type=client_credentials" https://[ホスト名]/ords/myproj/oauth/token
% curl -X POST -u pOi************PdC7w..:kUZN***************jlG-Q.. --data "grant_type=client_credentials" https://***********-apexdev.adb.us-ashburn-1.oraclecloudapps.com/ords/myproj/oauth/token
{"access_token":"70cd734q-IcDJpAsIk3n5Q","token_type":"bearer","expires_in":3600}
JSONのレスポンスにaccess_tokenが含まれます。このトークンを使って、REST対応SQLの呼び出しを認証します。REST対応SQLを呼び出し、"select sysdate from dual"を実行します。
% curl -X POST -H "Authorization: Bearer 70cd734q-IcDJpAsIk3n5Q" -H "Content-Type: application/sql" --data-binary "select sysdate from dual" https://bp9ncf74sqibu4p-apexdev.adb.us-ashburn-1.oraclecloudapps.com/ords/myproj/_/sql
{"env":{"defaultTimeZone":"UTC"},"items":[{"statementId":1,"statementType":"query","statementPos":{"startLine":1,"endLine":2},"statementText":"select sysdate from dual","resultSet":{"metadata":[{"columnName":"SYSDATE","jsonColumnName":"sysdate","columnTypeName":"DATE","columnClassName":"java.sql.Timestamp","precision":7,"scale":0,"isNullable":1}],"items":[{"sysdate":"2024-12-06T05:07:28Z"}],"hasMore":false,"limit":10000,"offset":0,"count":1},"response":[],"result":0}]}
APEXワークスペースの作成 - Autonomous Database
set echo on
define WKSPNAME = &1
define ADMINNAME = &2
define ADMINPASS = &3
define ADMINMAIL = &4
begin
-- assign all required roles to default parsing schema
for c1 in (
select privilege from sys.dba_sys_privs
where grantee = 'APEX_GRANTS_FOR_NEW_USERS_ROLE'
)
loop
execute immediate 'grant ' || c1.privilege || ' to wksp_&WKSPNAME';
end loop;
-- add new workspace to apex instance
apex_instance_admin.add_workspace(
p_workspace => '&WKSPNAME',
p_primary_schema => 'WKSP_&WKSPNAME'
);
-- set current session to the created workspace.
apex_util.set_workspace('&WKSPNAME');
-- create admin account.
apex_util.create_user(
p_user_name => '&ADMINNAME',
p_web_password => '&ADMINPASS',
p_developer_privs => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
p_email_address => '&ADMINMAIL',
p_default_schema => 'WKSP_&WKSPNAME',
p_change_password_on_first_use => 'N'
);
end;
/
commit;
-- assign admin password
create user &ADMINNAME identified by "&ADMINPASS";
exit;SQL> @03_create_workspace MYPROJ MYPROJADM My********Eac noreply@oracle.com
SQL>
SQL> define WKSPNAME = &1
SQL> define ADMINNAME = &2
SQL> define ADMINPASS = &3
SQL> define ADMINMAIL = &4
SQL>
SQL> begin
2
3 -- assign all required roles to default parsing schema
4 for c1 in (
5 select privilege from sys.dba_sys_privs
6 where grantee = 'APEX_GRANTS_FOR_NEW_USERS_ROLE'
7 )
8 loop
9 execute immediate 'grant ' || c1.privilege || ' to wksp_&WKSPNAME';
10 end loop;
11
12 -- add new workspace to apex instance
13 apex_instance_admin.add_workspace(
14 p_workspace => '&WKSPNAME',
15 p_primary_schema => 'WKSP_&WKSPNAME'
16 );
17
18 -- set current session to the created workspace.
19 apex_util.set_workspace('&WKSPNAME');
20
21 -- create admin account.
22 apex_util.create_user(
23 p_user_name => '&ADMINNAME',
24 p_web_password => '&ADMINPASS',
25 p_developer_privs => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
26 p_email_address => '&ADMINMAIL',
27 p_default_schema => 'WKSP_&WKSPNAME',
28 p_change_password_on_first_use => 'N'
29 );
30 end;
31 /
旧:begin
-- assign all required roles to default parsing schema
for c1 in (
select privilege from sys.dba_sys_privs
where grantee = 'APEX_GRANTS_FOR_NEW_USERS_ROLE'
)
loop
execute immediate 'grant ' || c1.privilege || ' to wksp_&WKSPNAME';
end loop;
-- add new workspace to apex instance
apex_instance_admin.add_workspace(
p_workspace => '&WKSPNAME',
p_primary_schema => 'WKSP_&WKSPNAME'
);
-- set current session to the created workspace.
apex_util.set_workspace('&WKSPNAME');
-- create admin account.
apex_util.create_user(
p_user_name => '&ADMINNAME',
p_web_password => '&ADMINPASS',
p_developer_privs => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
p_email_address => '&ADMINMAIL',
p_default_schema => 'WKSP_&WKSPNAME',
p_change_password_on_first_use => 'N'
);
end;
新:begin
-- assign all required roles to default parsing schema
for c1 in (
select privilege from sys.dba_sys_privs
where grantee = 'APEX_GRANTS_FOR_NEW_USERS_ROLE'
)
loop
execute immediate 'grant ' || c1.privilege || ' to wksp_MYPROJ';
end loop;
-- add new workspace to apex instance
apex_instance_admin.add_workspace(
p_workspace => 'MYPROJ',
p_primary_schema => 'WKSP_MYPROJ'
);
-- set current session to the created workspace.
apex_util.set_workspace('MYPROJ');
-- create admin account.
apex_util.create_user(
p_user_name => 'MYPROJADM',
p_web_password => 'My*********ac',
p_developer_privs => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
p_email_address => 'noreply@oracle.com',
p_default_schema => 'WKSP_MYPROJ',
p_change_password_on_first_use => 'N'
);
end;
PL/SQLプロシージャが正常に完了しました。
SQL> commit;
コミットが完了しました。
SQL>
SQL> -- assign admin password
SQL> create user &ADMINNAME identified by "&ADMINPASS";
旧:create user &ADMINNAME identified by "&ADMINPASS"
新:create user MYPROJADM identified by "My*******ac"
User MYPROJADMは作成されました。
SQL>
SQL> exit;
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Production
Version 23.6.0.24.11から切断されました
ynakakoshi@Ns-Macbook Projects %
SQL> select workspace_id from apex_workspaces where workspace = 'MYPROJ';
WORKSPACE_ID
_____________________
118415045107961267
SQL>
表やAPEXアプリケーションの作成 - Autonomous Database
APEX環境の作成 - ローカル
git clone https://github.com/ujnak/apex-podman-setup
cd apex-podman-setup
sh config_apex.shapex-podman-setup % sh config_apex.sh
/opt/homebrew/bin/podman
/Users/ynakakoshi/sqlcl/bin/sql
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 276M 100 276M 0 0 32.4M 0 0:00:08 0:00:08 --:--:-- 35.1M
APEX VERSION detected: 24.1.0 APEX_240100
oradata
ords_config
Pod:
0db5f2b11e3e723dac746abfa09bee9b5f7d21f2ac58b1e8fe0bbc6cd6be4bee
Containers:
56b0925f5192dbca28141f53feede9c71ae1d2173a7061cf9216ba859e4783d4
560e19bc3c0cb1f42b4f60c82cbf3bf887837fffdaef9a3c5ad97e8cd9248fbb
The Oracle base remains unchanged with value /opt/oracle
SQL*Plus: Release 23.0.0.0.0 - Production on Fri Dec 6 06:12:31 2024
Version 23.5.0.24.07
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07
SQL>
User altered.
SQL>
[省略]
ORDS: Release 24.3 Production on Fri Dec 06 06:17:51 2024
Copyright (c) 2010, 2024, Oracle.
Configuration:
/etc/ords/config
The global setting named: standalone.http.port was set to: 8181
apex
apex-podman-setup %
sql sys/oracle@localhost/freepdb1 as sysdba
接続したのち、以下を実行します。
@create_workspace_with_id MYPROJ MYPROJADM [パスワード] [メールアドレス] [ワークスペースID]
apex-podman-setup % sql sys/oracle@localhost/freepdb1 as sysdba
SQLcl: 金 12月 06 15:22:52 2024のリリース24.3 Production
Copyright (c) 1982, 2024, Oracle. All rights reserved.
接続先:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07
SQL> @create_workspace_with_id MYPROJ MYPROJADM My*********ac noreply@oracle.com 118415045107961267
SQL>
SQL> define WKSPNAME = &1
SQL> define ADMINNAME = &2
SQL> define ADMINPASS = &3
SQL> define ADMINMAIL = &4
SQL> define WKSPID = &5
SQL>
SQL> -- create default parsing shema for worksapce apexdev.
SQL> create user wksp_&WKSPNAME default tablespace users temporary tablespace temp quota unlimited on users;
旧:create user wksp_&WKSPNAME default tablespace users temporary tablespace temp quota unlimited on users
新:create user wksp_MYPROJ default tablespace users temporary tablespace temp quota unlimited on users
User WKSP_MYPROJは作成されました。
SQL>
SQL> begin
2
3 for c1 in (
4 select privilege from sys.dba_sys_privs
5 where grantee = 'APEX_GRANTS_FOR_NEW_USERS_ROLE'
6 )
7 loop
8 execute immediate 'grant ' || c1.privilege || ' to wksp_&WKSPNAME';
9 end loop;
10
11 apex_instance_admin.add_workspace(
12 p_workspace_id => &WKSPID,
13 p_workspace => '&WKSPNAME',
14 p_primary_schema => 'WKSP_&WKSPNAME'
15 );
16
17 apex_util.set_workspace('&WKSPNAME');
18
19 apex_util.create_user(
20 p_user_name => '&ADMINNAME',
21 p_web_password => '&ADMINPASS',
22 p_developer_privs => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
23 p_email_address => '&ADMINMAIL',
24 p_default_schema => 'WKSP_&WKSPNAME',
25 p_change_password_on_first_use => 'N'
26 );
27 end;
28 /
旧:begin
for c1 in (
select privilege from sys.dba_sys_privs
where grantee = 'APEX_GRANTS_FOR_NEW_USERS_ROLE'
)
loop
execute immediate 'grant ' || c1.privilege || ' to wksp_&WKSPNAME';
end loop;
apex_instance_admin.add_workspace(
p_workspace_id => &WKSPID,
p_workspace => '&WKSPNAME',
p_primary_schema => 'WKSP_&WKSPNAME'
);
apex_util.set_workspace('&WKSPNAME');
apex_util.create_user(
p_user_name => '&ADMINNAME',
p_web_password => '&ADMINPASS',
p_developer_privs => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
p_email_address => '&ADMINMAIL',
p_default_schema => 'WKSP_&WKSPNAME',
p_change_password_on_first_use => 'N'
);
end;
新:begin
for c1 in (
select privilege from sys.dba_sys_privs
where grantee = 'APEX_GRANTS_FOR_NEW_USERS_ROLE'
)
loop
execute immediate 'grant ' || c1.privilege || ' to wksp_MYPROJ';
end loop;
apex_instance_admin.add_workspace(
p_workspace_id => 118415045107961267,
p_workspace => 'MYPROJ',
p_primary_schema => 'WKSP_MYPROJ'
);
apex_util.set_workspace('MYPROJ');
apex_util.create_user(
p_user_name => 'MYPROJADM',
p_web_password => 'My************ac',
p_developer_privs => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
p_email_address => 'noreply@oracle.com',
p_default_schema => 'WKSP_MYPROJ',
p_change_password_on_first_use => 'N'
);
end;
PL/SQLプロシージャが正常に完了しました。
SQL> commit;
コミットが完了しました。
SQL> exit;
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07から切断されました
apex-podman-setup %
以上でローカルのAPEX環境にワークスペースMYPROJが作成できました。
SQL> alter user wksp_myproj identified by "My**********ac";
User WKSP_MYPROJが変更されました。
SQL>
SQLclのprojectコマンドを使った環境のコピー
% mkdir myproj
% cd myproj
myproj %
SQLclを起動し、Autonomous DatabaseのスキーマWKSP_MYPROJに接続します。
myproj % sql -cloudconfig ~/Documents/Wallets/Wallet_APEXDEV.zip wksp_myproj/My********Eac@apexdev_low
SQLcl: 金 12月 06 15:47:49 2024のリリース24.3 Production
Copyright (c) 1982, 2024, Oracle. All rights reserved.
接続先:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Production
Version 23.6.0.24.11
SQL>
project initを実行し、SQLclのデータベース・プロジェクトを作成します。名前はmyprojとします。
SQL> project init -name myproj -schemas wksp_myproj
------------------------
PROJECT DETAILS
------------------------
Project name: myproj
Schema(s): WKSP_MYPROJ
Directory: /Users/yn*******hi/Documents/Projects/myproj
Connection name:
Project root: myproj
Your project has been successfully created
SQL>
Gitのリポジトリを初期化します。今回の作業ではローカル・リポジトリは作成しますが、GitHubやGitLabにリポジトリは作成しません。SQLclのprojectコマンドの実行にGitのローカル・リポジトリは必須ですが、今回は環境をコピーするだけなのでGitHubへの登録は不要です。
!git add .
!git commit -m "chore: initializing repository with default project files"
SQL> !git init --initial-branch=main
Initialized empty Git repository in /Users/ynakakoshi/Documents/Projects/myproj/.git/
SQL> !git add .
SQL> !git commit -m "chore: initializing repository with default project files"
[main (root-commit) 384bbc1] chore: initializing repository with default project files
9 files changed, 200 insertions(+)
create mode 100644 .dbtools/filters/project.filters
create mode 100644 .dbtools/project.config.json
create mode 100644 .dbtools/project.sqlformat.xml
create mode 100644 .gitignore
create mode 100644 README.md
create mode 100644 dist/README.md
create mode 100644 dist/install.sql
create mode 100644 src/README.md
create mode 100644 src/database/README.md
SQL>
ブランチbase-releaseを作成し、Autonomous Databaseから表やAPEXアプリケーションをプロジェクトmyprojにエクスポートします。エクスポートしたファイル（src以下にエクスポートされる）をブランチに追加します。
!git checkout -b base-release
project export
!git status
!git add src
SQL> !git checkout -b base-release
Switched to a new branch 'base-release'
SQL> project export
The current connection (description=(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=bp9ncf74sqibu4p_apexdev_low.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes))) WKSP_MYPROJ will be used for all operations
*** INDEXES ***
*** TABLES ***
*** VIEWS ***
*** REF_CONSTRAINTS ***
*** APEX_APPLICATION ***
Exporting Workspace MYPROJ - application 221:Demonstration - EMP / DEPT
-------------------------------
TABLE 2
APEX_APPLICATION 1
REF_CONSTRAINT 2
INDEX 2
VIEW 1
-------------------------------
Exported 8 objects
Elapsed 86 sec
SQL> !git status
On branch base-release
Untracked files:
(use "git add <file>..." to include in what will be committed)
src/database/wksp_myproj/
nothing added to commit but untracked files present (use "git add" to track)
SQL> !git add src
SQL>
!git status
!git commit -m "chore: base export of WKSP_MYPROJ schema"
SQL> !git status
On branch base-release
Changes to be committed:
(use "git restore --staged <file>..." to unstage)
new file: src/database/wksp_myproj/apex_apps/f221/f221.sql
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/f221.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/page_groups.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p00000.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p00001.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p00002.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p00003.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p00004.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p00005.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p00006.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p09999.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10000.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10010.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10020.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10030.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10031.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10032.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10033.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10034.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10035.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10036.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10040.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10041.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10042.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10043.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10044.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10050.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10051.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10053.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10054.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10060.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10061.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p20000.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p20010.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/acl_roles.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/app_static_files.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/authentications.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/authorizations.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/breadcrumbs.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/build_options.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/lists.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/lovs.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/plugins.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/breadcrumb_templates.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/button_templates.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/classic_report_templates.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/field_templates.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/global_template_options.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/legacy_calendar_templates.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/list_templates.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/page_templates.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/popup_lov_templates.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/region_templates.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/template_option_groups.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/universal_theme.yaml
new file: src/database/wksp_myproj/apex_apps/f221/readable/workspace/credentials.yaml
new file: src/database/wksp_myproj/indexes/emp_1.sql
new file: src/database/wksp_myproj/indexes/emp_2.sql
new file: src/database/wksp_myproj/ref_constraints/emp_dept_fk.sql
new file: src/database/wksp_myproj/ref_constraints/emp_mgr_fk.sql
new file: src/database/wksp_myproj/tables/dept.sql
new file: src/database/wksp_myproj/tables/emp.sql
new file: src/database/wksp_myproj/views/emp_dept_v.sql
SQL> !git commit -m "chore: base export of WKSP_MYPROJ schema"
[base-release 20ced9b] chore: base export of WKSP_MYPROJ schema
63 files changed, 66559 insertions(+)
create mode 100644 src/database/wksp_myproj/apex_apps/f221/f221.sql
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/f221.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/page_groups.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p00000.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p00001.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p00002.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p00003.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p00004.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p00005.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p00006.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p09999.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10000.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10010.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10020.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10030.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10031.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10032.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10033.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10034.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10035.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10036.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10040.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10041.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10042.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10043.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10044.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10050.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10051.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10053.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10054.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10060.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p10061.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p20000.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/pages/p20010.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/acl_roles.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/app_static_files.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/authentications.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/authorizations.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/breadcrumbs.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/build_options.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/lists.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/lovs.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/plugins.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/breadcrumb_templates.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/button_templates.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/classic_report_templates.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/field_templates.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/global_template_options.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/legacy_calendar_templates.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/list_templates.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/page_templates.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/popup_lov_templates.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/region_templates.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/template_option_groups.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/application/shared_components/theme_42/universal_theme.yaml
create mode 100644 src/database/wksp_myproj/apex_apps/f221/readable/workspace/credentials.yaml
create mode 100644 src/database/wksp_myproj/indexes/emp_1.sql
create mode 100644 src/database/wksp_myproj/indexes/emp_2.sql
create mode 100644 src/database/wksp_myproj/ref_constraints/emp_dept_fk.sql
create mode 100644 src/database/wksp_myproj/ref_constraints/emp_mgr_fk.sql
create mode 100644 src/database/wksp_myproj/tables/dept.sql
create mode 100644 src/database/wksp_myproj/tables/emp.sql
create mode 100644 src/database/wksp_myproj/views/emp_dept_v.sql
SQL>
project stageを実行し、base-releaseをインストールするための（mainブランチと比較した）チェンジログを生成します。
!git status
!find dist/releases/next
SQL> project stage
Stage is Comparing:
Old Branch refs/heads/main
New Branch refs/heads/base-release
Stage successfully created, please review and commit your changes to repository
SQL> !git status
On branch base-release
Untracked files:
(use "git add <file>..." to include in what will be committed)
dist/releases/
dist/utils/
nothing added to commit but untracked files present (use "git add" to track)
SQL> !find dist/releases/next
dist/releases/next
dist/releases/next/release.changelog.xml
dist/releases/next/changes
dist/releases/next/changes/base-release
dist/releases/next/changes/base-release/code
dist/releases/next/changes/base-release/code/_custom
dist/releases/next/changes/base-release/wksp_myproj
dist/releases/next/changes/base-release/wksp_myproj/ref_constraint
dist/releases/next/changes/base-release/wksp_myproj/ref_constraint/emp_mgr_fk.sql
dist/releases/next/changes/base-release/wksp_myproj/ref_constraint/emp_dept_fk.sql
dist/releases/next/changes/base-release/wksp_myproj/table
dist/releases/next/changes/base-release/wksp_myproj/table/emp.sql
dist/releases/next/changes/base-release/wksp_myproj/table/dept.sql
dist/releases/next/changes/base-release/wksp_myproj/index
dist/releases/next/changes/base-release/wksp_myproj/index/emp_2.sql
dist/releases/next/changes/base-release/wksp_myproj/index/emp_1.sql
dist/releases/next/changes/base-release/stage.changelog.xml
dist/releases/next/code
dist/releases/next/code/code.changelog.xml
dist/releases/next/code/wksp_myproj
dist/releases/next/code/wksp_myproj/view
dist/releases/next/code/wksp_myproj/view/emp_dept_v.sql
SQL>
project release -version 1.0 -verbose
SQL> project release -version 1.0 -verbose
Creating a release version 1.0 for the current body of work
Updated change:dist/releases/main.changelog.xml
Moved folder "dist/releases/next" to "dist/releases/1.0"
Created file: dist/releases/next
Created change:dist/releases/next/release.changelog.xml
Created change:dist/releases/next/release.changelog.xml
Process completed successfully
SQL>
今回のリリース（Version 1.0）で適用される変更はdist/releases/1.0以下にまとめられています。project gen-artifactを実行し、アーティファクトとしてzipファイルを作成します。
!find dist/releases/1.0
project gen-artifact
SQL> !find dist/releases/1.0
dist/releases/1.0
dist/releases/1.0/release.changelog.xml
dist/releases/1.0/changes
dist/releases/1.0/changes/base-release
dist/releases/1.0/changes/base-release/code
dist/releases/1.0/changes/base-release/code/_custom
dist/releases/1.0/changes/base-release/wksp_myproj
dist/releases/1.0/changes/base-release/wksp_myproj/ref_constraint
dist/releases/1.0/changes/base-release/wksp_myproj/ref_constraint/emp_mgr_fk.sql
dist/releases/1.0/changes/base-release/wksp_myproj/ref_constraint/emp_dept_fk.sql
dist/releases/1.0/changes/base-release/wksp_myproj/table
dist/releases/1.0/changes/base-release/wksp_myproj/table/emp.sql
dist/releases/1.0/changes/base-release/wksp_myproj/table/dept.sql
dist/releases/1.0/changes/base-release/wksp_myproj/index
dist/releases/1.0/changes/base-release/wksp_myproj/index/emp_2.sql
dist/releases/1.0/changes/base-release/wksp_myproj/index/emp_1.sql
dist/releases/1.0/changes/base-release/stage.changelog.xml
dist/releases/1.0/code
dist/releases/1.0/code/code.changelog.xml
dist/releases/1.0/code/wksp_myproj
dist/releases/1.0/code/wksp_myproj/view
dist/releases/1.0/code/wksp_myproj/view/emp_dept_v.sql
SQL> project gen-artifact
Your artifact has been generated myproj-1.0.zip
SQL>
myproj % sql wksp_myproj/[パスワード]@localhost/freepdb1
SQLcl: 金 12月 06 16:32:50 2024のリリース24.3 Production
Copyright (c) 1982, 2024, Oracle. All rights reserved.
接続先:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07
SQL>
アーティファクトmyproj-1.0.zipをローカル環境にデプロイします。
SQL> project deploy -file artifact/myproj-1.0.zip -verbose
Check database connection...
Extract the file name: myproj-1.0
Artifact decompression in progress...
Artifact decompressed: /var/folders/fw/z20l7ys92zz_b2ryx2224y040000gn/T/bd56e2a6-6a92-444e-9ac2-c1a6e7031a9914375841397961038520
Starting the migration...
Running Changeset: base-release/wksp_myproj/table/dept.sql::b04d77535ce4d3477b4775c32ee177117eb2e849::WKSP_MYPROJ
Running Changeset: base-release/wksp_myproj/table/emp.sql::67ea942cdc254e93ba9cd9b1e4a0d38a2d586ab9::WKSP_MYPROJ
Running Changeset: base-release/wksp_myproj/ref_constraint/emp_dept_fk.sql::0ec3e2f9216a2bb4d9888bd93d5207de0255674d::WKSP_MYPROJ
Running Changeset: base-release/wksp_myproj/ref_constraint/emp_mgr_fk.sql::80728f400b510a76539aec3717998728dfb933af::WKSP_MYPROJ
Running Changeset: base-release/wksp_myproj/index/emp_1.sql::68cb87a9d748d75ad10c7877e960014abb5eb39e::WKSP_MYPROJ
Running Changeset: base-release/wksp_myproj/index/emp_2.sql::1bee5d3419726d3d1f7d8fbb044e53215266e578::WKSP_MYPROJ
Running Changeset: ../wksp_myproj/view/emp_dept_v.sql::e9c4fa13d41d45e366ca783319908d0ab8bbdacc::WKSP_MYPROJ
Running Changeset: releases/apex/f221/f221.xml::INSTALL_221::SQLCLWKSP_MYPROJ
PL/SQLプロシージャが正常に完了しました。
--application/set_environment
APPLICATION 221 - Demonstration - EMP / DEPT
--application/delete_application
--application/create_application
--application/user_interfaces
--workspace/credentials/アプリケーション221のプッシュ通知資格証明
--application/shared_components/navigation/lists/ナビゲーション・メニュー
--application/shared_components/navigation/lists/ナビゲーション・バー
--application/shared_components/navigation/lists/ページ・ナビゲーション
--application/shared_components/navigation/lists/アプリケーション構成
--application/shared_components/navigation/lists/ユーザー・インタフェース
--application/shared_components/navigation/lists/アクティビティ・レポート
--application/shared_components/navigation/lists/アクセス制御
--application/shared_components/navigation/lists/フィードバック
--application/shared_components/navigation/lists/ユーザー設定
--application/shared_components/navigation/listentry
--application/shared_components/files/icons_app_icon_32_png
--application/shared_components/files/icons_app_icon_144_rounded_png
--application/shared_components/files/icons_app_icon_192_png
--application/shared_components/files/icons_app_icon_256_rounded_png
--application/shared_components/files/icons_app_icon_512_png
--application/plugin_settings
--application/shared_components/security/authorizations/管理権限
--application/shared_components/security/authorizations/リーダー権限
--application/shared_components/security/authorizations/コントリビューション権限
--application/shared_components/security/app_access_control/管理者
--application/shared_components/security/app_access_control/コントリビュータ
--application/shared_components/security/app_access_control/リーダー
--application/shared_components/navigation/navigation_bar
--application/shared_components/logic/application_settings
--application/shared_components/navigation/tabs/standard
--application/shared_components/navigation/tabs/parent
--application/shared_components/user_interface/lovs/access_roles
--application/shared_components/user_interface/lovs/dept_dname
--application/shared_components/user_interface/lovs/desktop_theme_styles
--application/shared_components/user_interface/lovs/email_username_format
--application/shared_components/user_interface/lovs/emp_ename
--application/shared_components/user_interface/lovs/feedback_rating
--application/shared_components/user_interface/lovs/feedback_status
--application/shared_components/user_interface/lovs/timeframe_4_weeks
--application/shared_components/user_interface/lovs/user_theme_preference
--application/shared_components/user_interface/lovs/view_as_report_chart
--application/pages/page_groups
--application/shared_components/navigation/breadcrumbs/ブレッドクラム
--application/shared_components/navigation/breadcrumbentry
--application/shared_components/user_interface/templates/page/drawer
--application/shared_components/user_interface/templates/page/left_side_column
--application/shared_components/user_interface/templates/page/minimal_no_navigation
--application/shared_components/user_interface/templates/page/right_side_column
--application/shared_components/user_interface/templates/page/standard
--application/shared_components/user_interface/templates/page/wizard_modal_dialog
--application/shared_components/user_interface/templates/page/left_and_right_side_columns
--application/shared_components/user_interface/templates/page/login
--application/shared_components/user_interface/templates/page/master_detail
--application/shared_components/user_interface/templates/page/modal_dialog
--application/shared_components/user_interface/templates/button/icon
--application/shared_components/user_interface/templates/button/text
--application/shared_components/user_interface/templates/button/text_with_icon
--application/shared_components/user_interface/templates/region/alert
--application/shared_components/user_interface/templates/region/blank_with_attributes
--application/shared_components/user_interface/templates/region/blank_with_attributes_no_grid
--application/shared_components/user_interface/templates/region/carousel_container
--application/shared_components/user_interface/templates/region/image
--application/shared_components/user_interface/templates/region/inline_dialog
--application/shared_components/user_interface/templates/region/buttons_container
--application/shared_components/user_interface/templates/region/cards_container
--application/shared_components/user_interface/templates/region/content_block
--application/shared_components/user_interface/templates/region/collapsible
--application/shared_components/user_interface/templates/region/hero
--application/shared_components/user_interface/templates/region/inline_drawer
--application/shared_components/user_interface/templates/region/inline_popup
--application/shared_components/user_interface/templates/region/interactive_report
--application/shared_components/user_interface/templates/region/item_container
--application/shared_components/user_interface/templates/region/login
--application/shared_components/user_interface/templates/region/search_results_container
--application/shared_components/user_interface/templates/region/standard
--application/shared_components/user_interface/templates/region/tabs_container
--application/shared_components/user_interface/templates/region/title_bar
--application/shared_components/user_interface/templates/region/wizard_container
--application/shared_components/user_interface/templates/list/badge_list
--application/shared_components/user_interface/templates/list/cards
--application/shared_components/user_interface/templates/list/media_list
--application/shared_components/user_interface/templates/list/tabs
--application/shared_components/user_interface/templates/list/top_navigation_tabs
--application/shared_components/user_interface/templates/list/links_list
--application/shared_components/user_interface/templates/list/menu_bar
--application/shared_components/user_interface/templates/list/top_navigation_mega_menu
--application/shared_components/user_interface/templates/list/wizard_progress
--application/shared_components/user_interface/templates/list/menu_popup
--application/shared_components/user_interface/templates/list/navigation_bar
--application/shared_components/user_interface/templates/list/side_navigation_menu
--application/shared_components/user_interface/templates/list/top_navigation_menu
--application/shared_components/user_interface/templates/report/alerts
--application/shared_components/user_interface/templates/report/badge_list
--application/shared_components/user_interface/templates/report/cards
--application/shared_components/user_interface/templates/report/search_results
--application/shared_components/user_interface/templates/report/value_attribute_pairs_column
--application/shared_components/user_interface/templates/report/value_attribute_pairs_row
--application/shared_components/user_interface/templates/report/comments
--application/shared_components/user_interface/templates/report/contextual_info
--application/shared_components/user_interface/templates/report/standard
--application/shared_components/user_interface/templates/report/content_row
--application/shared_components/user_interface/templates/report/timeline
--application/shared_components/user_interface/templates/report/media_list
--application/shared_components/user_interface/templates/label/hidden
--application/shared_components/user_interface/templates/label/optional
--application/shared_components/user_interface/templates/label/optional_above
--application/shared_components/user_interface/templates/label/optional_floating
--application/shared_components/user_interface/templates/label/required
--application/shared_components/user_interface/templates/label/required_above
--application/shared_components/user_interface/templates/label/required_floating
--application/shared_components/user_interface/templates/breadcrumb/breadcrumb
--application/shared_components/user_interface/templates/popuplov
--application/shared_components/user_interface/templates/calendar/calendar
--application/shared_components/user_interface/themes
--application/shared_components/user_interface/theme_style
--application/shared_components/user_interface/theme_files
--application/shared_components/user_interface/template_opt_groups
--application/shared_components/user_interface/template_options
--application/shared_components/globalization/language
--application/shared_components/logic/build_options
--application/shared_components/globalization/messages
--application/shared_components/globalization/dyntranslations
--application/shared_components/security/authentications/oracle_apexアカウント
--application/shared_components/plugins/template_component/theme_42_avatar
--application/shared_components/plugins/template_component/theme_42_badge
--application/shared_components/plugins/template_component/theme_42_button
--application/shared_components/plugins/template_component/theme_42_comments
--application/shared_components/plugins/template_component/theme_42_media_list
--application/shared_components/plugins/template_component/theme_42_timeline
--application/shared_components/plugins/template_component/theme_42_content_row
--application/user_interfaces/combined_files
--application/pages/page_00000
--application/pages/page_00001
--application/pages/page_00002
--application/pages/page_00003
--application/pages/page_00004
--application/pages/page_00005
--application/pages/page_00006
--application/pages/page_09999
--application/pages/page_10000
--application/pages/page_10010
--application/pages/page_10020
--application/pages/page_10030
--application/pages/page_10031
--application/pages/page_10032
--application/pages/page_10033
--application/pages/page_10034
--application/pages/page_10035
--application/pages/page_10036
--application/pages/page_10040
--application/pages/page_10041
--application/pages/page_10042
--application/pages/page_10043
--application/pages/page_10044
--application/pages/page_10050
--application/pages/page_10051
--application/pages/page_10053
--application/pages/page_10054
--application/pages/page_10060
--application/pages/page_10061
--application/pages/page_20000
--application/pages/page_20010
--application/end_environment
...done
Liquibase: Update has been successful. Rows affected: 8
Installing/updating schemas
--Starting Liquibase at 2024-12-06T16:36:53.476125 (version 4.25.0.305.0400 #0 built at 2024-10-31 21:25+0000)
PL/SQLプロシージャが正常に完了しました。
--application/set_environment
APPLICATION 221 - Demonstration - EMP / DEPT
--application/delete_application
--application/create_application
--application/user_interfaces
--workspace/credentials/アプリケーション221のプッシュ通知資格証明
--application/shared_components/navigation/lists/ナビゲーション・メニュー
--application/shared_components/navigation/lists/ナビゲーション・バー
--application/shared_components/navigation/lists/ページ・ナビゲーション
--application/shared_components/navigation/lists/アプリケーション構成
--application/shared_components/navigation/lists/ユーザー・インタフェース
--application/shared_components/navigation/lists/アクティビティ・レポート
--application/shared_components/navigation/lists/アクセス制御
--application/shared_components/navigation/lists/フィードバック
--application/shared_components/navigation/lists/ユーザー設定
--application/shared_components/navigation/listentry
--application/shared_components/files/icons_app_icon_32_png
--application/shared_components/files/icons_app_icon_144_rounded_png
--application/shared_components/files/icons_app_icon_192_png
--application/shared_components/files/icons_app_icon_256_rounded_png
--application/shared_components/files/icons_app_icon_512_png
--application/plugin_settings
--application/shared_components/security/authorizations/管理権限
--application/shared_components/security/authorizations/リーダー権限
--application/shared_components/security/authorizations/コントリビューション権限
--application/shared_components/security/app_access_control/管理者
--application/shared_components/security/app_access_control/コントリビュータ
--application/shared_components/security/app_access_control/リーダー
--application/shared_components/navigation/navigation_bar
--application/shared_components/logic/application_settings
--application/shared_components/navigation/tabs/standard
--application/shared_components/navigation/tabs/parent
--application/shared_components/user_interface/lovs/access_roles
--application/shared_components/user_interface/lovs/dept_dname
--application/shared_components/user_interface/lovs/desktop_theme_styles
--application/shared_components/user_interface/lovs/email_username_format
--application/shared_components/user_interface/lovs/emp_ename
--application/shared_components/user_interface/lovs/feedback_rating
--application/shared_components/user_interface/lovs/feedback_status
--application/shared_components/user_interface/lovs/timeframe_4_weeks
--application/shared_components/user_interface/lovs/user_theme_preference
--application/shared_components/user_interface/lovs/view_as_report_chart
--application/pages/page_groups
--application/shared_components/navigation/breadcrumbs/ブレッドクラム
--application/shared_components/navigation/breadcrumbentry
--application/shared_components/user_interface/templates/page/drawer
--application/shared_components/user_interface/templates/page/left_side_column
--application/shared_components/user_interface/templates/page/minimal_no_navigation
--application/shared_components/user_interface/templates/page/right_side_column
--application/shared_components/user_interface/templates/page/standard
--application/shared_components/user_interface/templates/page/wizard_modal_dialog
--application/shared_components/user_interface/templates/page/left_and_right_side_columns
--application/shared_components/user_interface/templates/page/login
--application/shared_components/user_interface/templates/page/master_detail
--application/shared_components/user_interface/templates/page/modal_dialog
--application/shared_components/user_interface/templates/button/icon
--application/shared_components/user_interface/templates/button/text
--application/shared_components/user_interface/templates/button/text_with_icon
--application/shared_components/user_interface/templates/region/alert
--application/shared_components/user_interface/templates/region/blank_with_attributes
--application/shared_components/user_interface/templates/region/blank_with_attributes_no_grid
--application/shared_components/user_interface/templates/region/carousel_container
--application/shared_components/user_interface/templates/region/image
--application/shared_components/user_interface/templates/region/inline_dialog
--application/shared_components/user_interface/templates/region/buttons_container
--application/shared_components/user_interface/templates/region/cards_container
--application/shared_components/user_interface/templates/region/content_block
--application/shared_components/user_interface/templates/region/collapsible
--application/shared_components/user_interface/templates/region/hero
--application/shared_components/user_interface/templates/region/inline_drawer
--application/shared_components/user_interface/templates/region/inline_popup
--application/shared_components/user_interface/templates/region/interactive_report
--application/shared_components/user_interface/templates/region/item_container
--application/shared_components/user_interface/templates/region/login
--application/shared_components/user_interface/templates/region/search_results_container
--application/shared_components/user_interface/templates/region/standard
--application/shared_components/user_interface/templates/region/tabs_container
--application/shared_components/user_interface/templates/region/title_bar
--application/shared_components/user_interface/templates/region/wizard_container
--application/shared_components/user_interface/templates/list/badge_list
--application/shared_components/user_interface/templates/list/cards
--application/shared_components/user_interface/templates/list/media_list
--application/shared_components/user_interface/templates/list/tabs
--application/shared_components/user_interface/templates/list/top_navigation_tabs
--application/shared_components/user_interface/templates/list/links_list
--application/shared_components/user_interface/templates/list/menu_bar
--application/shared_components/user_interface/templates/list/top_navigation_mega_menu
--application/shared_components/user_interface/templates/list/wizard_progress
--application/shared_components/user_interface/templates/list/menu_popup
--application/shared_components/user_interface/templates/list/navigation_bar
--application/shared_components/user_interface/templates/list/side_navigation_menu
--application/shared_components/user_interface/templates/list/top_navigation_menu
--application/shared_components/user_interface/templates/report/alerts
--application/shared_components/user_interface/templates/report/badge_list
--application/shared_components/user_interface/templates/report/cards
--application/shared_components/user_interface/templates/report/search_results
--application/shared_components/user_interface/templates/report/value_attribute_pairs_column
--application/shared_components/user_interface/templates/report/value_attribute_pairs_row
--application/shared_components/user_interface/templates/report/comments
--application/shared_components/user_interface/templates/report/contextual_info
--application/shared_components/user_interface/templates/report/standard
--application/shared_components/user_interface/templates/report/content_row
--application/shared_components/user_interface/templates/report/timeline
--application/shared_components/user_interface/templates/report/media_list
--application/shared_components/user_interface/templates/label/hidden
--application/shared_components/user_interface/templates/label/optional
--application/shared_components/user_interface/templates/label/optional_above
--application/shared_components/user_interface/templates/label/optional_floating
--application/shared_components/user_interface/templates/label/required
--application/shared_components/user_interface/templates/label/required_above
--application/shared_components/user_interface/templates/label/required_floating
--application/shared_components/user_interface/templates/breadcrumb/breadcrumb
--application/shared_components/user_interface/templates/popuplov
--application/shared_components/user_interface/templates/calendar/calendar
--application/shared_components/user_interface/themes
--application/shared_components/user_interface/theme_style
--application/shared_components/user_interface/theme_files
--application/shared_components/user_interface/template_opt_groups
--application/shared_components/user_interface/template_options
--application/shared_components/globalization/language
--application/shared_components/logic/build_options
--application/shared_components/globalization/messages
--application/shared_components/globalization/dyntranslations
--application/shared_components/security/authentications/oracle_apexアカウント
--application/shared_components/plugins/template_component/theme_42_avatar
--application/shared_components/plugins/template_component/theme_42_badge
--application/shared_components/plugins/template_component/theme_42_button
--application/shared_components/plugins/template_component/theme_42_comments
--application/shared_components/plugins/template_component/theme_42_media_list
--application/shared_components/plugins/template_component/theme_42_timeline
--application/shared_components/plugins/template_component/theme_42_content_row
--application/user_interfaces/combined_files
--application/pages/page_00000
--application/pages/page_00001
--application/pages/page_00002
--application/pages/page_00003
--application/pages/page_00004
--application/pages/page_00005
--application/pages/page_00006
--application/pages/page_09999
--application/pages/page_10000
--application/pages/page_10010
--application/pages/page_10020
--application/pages/page_10030
--application/pages/page_10031
--application/pages/page_10032
--application/pages/page_10033
--application/pages/page_10034
--application/pages/page_10035
--application/pages/page_10036
--application/pages/page_10040
--application/pages/page_10041
--application/pages/page_10042
--application/pages/page_10043
--application/pages/page_10044
--application/pages/page_10050
--application/pages/page_10051
--application/pages/page_10053
--application/pages/page_10054
--application/pages/page_10060
--application/pages/page_10061
--application/pages/page_20000
--application/pages/page_20010
--application/end_environment
...done
UPDATE SUMMARY
Run: 8
Previously run: 0
Filtered out: 0
-------------------------------
Total change sets: 8
Produced logfile: sqlcl-lb-1733470613402.log
操作が正常に完了しました。
Migration has been completed
Removing the decompressed artifact: /var/folders/fw/z20l7ys92zz_b2ryx2224y040000gn/T/bd56e2a6-6a92-444e-9ac2-c1a6e7031a9914375841397961038520...
SQL>
以上でSQLclのprojectコマンドを使った環境のコピーは完了です。
REST対応SQLを使ったデータのコピー - ローカル環境
Web資格証明は基本認証かOAuth2クライアント資格証明のどちらかで作成します。
ユーザー・ロール割当ての追加をクリックします。
sql wksp_myproj/My*********ac@localhost/freepdb1
パッケージUTL_REST_ENBLED_SQL_COPYを作成します。
@utl_rest_enabled_sql_copy.pkb
SQL> @utl_rest_enabled_sql_copy.sql
Package UTL_REST_ENABLED_SQL_COPYがコンパイルされました
SQL> @utl_rest_enabled_sql_copy.pkb
Package Body UTL_REST_ENABLED_SQL_COPYがコンパイルされました
SQL>
DESCコマンドを実行し、実装されているファンクションまたはプロシージャを確認します。
desc utl_rest_enabled_sql_copy
SQL> desc utl_rest_enabled_sql_copy
FUNCTION COPY_TABLE_FROM_LOCAL_TO_REMOTE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------- -------------------- ------ --------
P_SERVER_STATIC_ID VARCHAR2 IN
P_TABLE_NAME VARCHAR2 IN
P_COMMIT_INTERVAL BINARY_INTEGER IN Y
P_CONTINUE_ON_ERROR BOOLEAN IN Y
FUNCTION COPY_TABLE_FROM_LOCAL_TO_REMOTE_S RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------- -------------------- ------ --------
P_APP_ID NUMBER IN
P_PAGE_ID NUMBER IN Y
P_USERNAME VARCHAR2 IN
P_SERVER_STATIC_ID VARCHAR2 IN
P_TABLE_NAME VARCHAR2 IN
P_COMMIT_INTERVAL BINARY_INTEGER IN Y
P_CONTINUE_ON_ERROR BOOLEAN IN Y
P_DEBUG_LEVEL BINARY_INTEGER IN Y
FUNCTION COPY_TABLE_FROM_REMOTE_TO_LOCAL RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------- -------------------- ------ --------
P_SERVER_STATIC_ID VARCHAR2 IN
P_TABLE_NAME VARCHAR2 IN
P_COMMIT_INTERVAL BINARY_INTEGER IN Y
FUNCTION COPY_TABLE_FROM_REMOTE_TO_LOCAL_S RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------- -------------------- ------ --------
P_APP_ID NUMBER IN
P_PAGE_ID NUMBER IN Y
P_USERNAME VARCHAR2 IN
P_SERVER_STATIC_ID VARCHAR2 IN
P_TABLE_NAME VARCHAR2 IN
P_COMMIT_INTERVAL BINARY_INTEGER IN Y
P_DEBUG_LEVEL BINARY_INTEGER IN Y
SQL>
今回はリモートのAutonomous Databaseからローカルの環境にコピーするため、プロシージャCOPY_TABLE_FROM_REMOTE_TO_LOCAL_Sを呼び出します。
variable rows number
begin
:rows := utl_rest_enabled_sql_copy.copy_table_from_remote_to_local_s(221,1,'MYPROJADM','MYPROJ_ON_ADB','DEPT');
end;
/
print :rows
select * from dept;SQL> variable rows number;
SQL> begin
2 :rows := utl_rest_enabled_sql_copy.copy_table_from_remote_to_local_s(221,1,'MYPROJADM','MYPROJ_ON_ADB','DEPT');
3 end;
4* /
PL/SQLプロシージャが正常に完了しました。
SQL> print :rows
ROWS
----------
4
SQL> select * from dept;
DEPTNO DNAME LOC
_________ _____________ ___________
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
begin
:rows := utl_rest_enabled_sql_copy.copy_table_from_remote_to_local_s(221,1,'MYPROJADM','MYPROJ_ON_ADB','EMP');
end;
/
print :rows
select * from emp;SQL> begin
2 :rows := utl_rest_enabled_sql_copy.copy_table_from_remote_to_local_s(221,1,'MYPROJADM','MYPROJ_ON_ADB','EMP');
3 end;
4* /
PL/SQLプロシージャが正常に完了しました。
SQL> print :rows
ROWS
----------
14
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
________ _________ ____________ _______ ___________ _______ _______ _________
7839 KING PRESIDENT 81-11-17 5000 10
7698 BLAKE MANAGER 7839 81-05-01 2850 30
7782 CLARK MANAGER 7839 81-06-09 2450 10
7566 JONES MANAGER 7839 81-04-02 2975 20
7788 SCOTT ANALYST 7566 82-12-09 3000 20
7902 FORD ANALYST 7566 81-12-03 3000 20
7369 SMITH CLERK 7902 80-12-17 800 20
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30
7876 ADAMS CLERK 7788 83-01-12 1100 20
7900 JAMES CLERK 7698 81-12-03 950 30
7934 MILLER CLERK 7782 82-01-23 1300 10
14行が選択されました。
SQL>
| create or replace package "UTL_REST_ENABLED_SQL_COPY" as | |
| /** | |
| * コピーするデータの列に関する必要な定義を保持する。 | |
| * | |
| * COLUMN_NAME, DATA_TYPE, DATA_LENGTHはUSER_TAB_COLUMNSビューより取り出す。 | |
| * column_positionはAPEX_EXECのcontextにおけるcolumn_positionの値を保持する。 | |
| * is_primary_keyはUSER_CONSTRAINTS, USER_CONS_COLUMNSなどより確認する。 | |
| */ | |
| type r_column_record_type is record ( | |
| column_name user_tab_columns.column_name%type, | |
| data_type user_tab_columns.data_type%type, | |
| data_length user_tab_columns.data_length%type, | |
| column_position integer, | |
| is_primary_key number -- 1 is true, 0 is false | |
| ); | |
| type t_operation_column_array_type is varray(4000) of r_column_record_type; | |
| /** | |
| * 指定した表のデータをコピーする。 | |
| * | |
| * ソースとターゲットの表定義はまったく同じであることが前提。 | |
| * | |
| * ソースは引数p_server_static_idに指定したREST対応SQLの表を対象とする。 | |
| * ターゲットは接続しているデータベースのローカルの表。 | |
| * | |
| * アプリケーションやREST対応SQLの定義は、接続しているデータベースに | |
| * あるAPEXの設定を参照する。 | |
| */ | |
| function copy_table_from_remote_to_local( | |
| p_server_static_id in varchar2 | |
| ,p_table_name in varchar2 | |
| ,p_commit_interval in pls_integer default 0 | |
| ) | |
| return number; | |
| /** | |
| * APEXのセッションを作成してから、copy_tableファンクションを呼び出す。 | |
| * SQLclなどで直接接続している際に使用する。 | |
| */ | |
| function copy_table_from_remote_to_local_s( | |
| -- apex_session.create_sessionへの引数。 | |
| p_app_id in number | |
| ,p_page_id in number default 1 | |
| ,p_username in varchar2 | |
| -- copy_table_from_remote_to_localへの引数。 | |
| ,p_server_static_id in varchar2 | |
| ,p_table_name in varchar2 | |
| ,p_commit_interval in pls_integer default 0 | |
| -- apex_session.create_sessionへの引数。必須ではないので最後。 | |
| ,p_debug_level in apex_debug.t_log_level default null | |
| ) | |
| return number; | |
| /** | |
| * 指定した表のデータをコピーする。 | |
| * | |
| * ソースとターゲットの表定義はまったく同じであることが前提。 | |
| * | |
| * ソースは接続しているデータベースのローカルの表。 | |
| * ターゲットは引数p_server_static_idに指定したREST対応SQLの表を対象とする。 | |
| */ | |
| function copy_table_from_local_to_remote( | |
| p_server_static_id in varchar2 | |
| ,p_table_name in varchar2 | |
| ,p_commit_interval in pls_integer default 0 | |
| ,p_continue_on_error in boolean default false | |
| ) | |
| return number; | |
| /** | |
| * APEXのセッションを作成してから、copy_tableを呼び出す。 | |
| * SQLclなどで直接接続している際に使用する。 | |
| * | |
| * アプリケーションやREST対応SQLの定義は、接続しているデータベースに | |
| * あるAPEXの設定を参照する。 | |
| */ | |
| function copy_table_from_local_to_remote_s( | |
| -- apex_session.create_sessionへの引数。 | |
| p_app_id in number | |
| ,p_page_id in number default 1 | |
| ,p_username in varchar2 | |
| -- copy_table_from_local_to_remomteへの引数。 | |
| ,p_server_static_id in varchar2 | |
| ,p_table_name in varchar2 | |
| ,p_commit_interval in pls_integer default 0 | |
| ,p_continue_on_error in boolean default false | |
| -- apex_session.create_sessionへの引数。必須ではないので最後。 | |
| ,p_debug_level in apex_debug.t_log_level default null | |
| ) | |
| return number; | |
| /** | |
| * 実験的な実装。 | |
| * | |
| * リモートの表にあるBLOBデータをローカルの表にコピーする。 | |
| * | |
| * 同じ主キー列、BLOB列があり、同じ主キーの行が存在することが前提。 | |
| * | |
| * 主キー列は１つのみ（複合主キーの対応はない）。 | |
| * 型はNUMBERかVARCHAR2のどちらか。 | |
| * | |
| * 引数p_keys_taregtでコピー対象とする行を選択する。 | |
| * p_keys_targetがnullの場合は全件が対象。 | |
| * | |
| * p_keys_target、p_keys_copied、p_keys_failedは | |
| * 対象となるキー値をカンマで区切る。 | |
| * | |
| * p_keys_failedは、p_keys_targetに指定したキー値の行がローカル表に | |
| * 存在しないものについては追加されない。リモート表に存在していて、かつ、 | |
| * コピーできなかった行がp_keys_failedに含まれる。 | |
| * | |
| * 戻り値はコピー件数。 | |
| */ | |
| function copy_blob_from_remote_to_local( | |
| p_server_static_id in varchar2 | |
| ,p_table_name in varchar2 | |
| ,p_key_column_name in varchar2 | |
| ,p_blob_column_name in varchar2 | |
| ,p_keys_target in varchar2 default null | |
| ,p_keys_copied out varchar2 | |
| ,p_keys_failed out varchar2 | |
| ) | |
| return number; | |
| /** | |
| * APEXのセッションを作成してから、copy_tableを呼び出す。 | |
| * SQLclなどで直接接続している際に使用する。 | |
| */ | |
| function copy_blob_from_remote_to_local_s( | |
| p_app_id in number | |
| ,p_page_id in number | |
| ,p_username in varchar2 | |
| ,p_server_static_id in varchar2 | |
| ,p_table_name in varchar2 | |
| ,p_key_column_name in varchar2 | |
| ,p_blob_column_name in varchar2 | |
| ,p_keys_target in varchar2 default null | |
| ,p_keys_copied out varchar2 | |
| ,p_keys_failed out varchar2 | |
| ,p_debug_level in apex_debug.t_log_level default null | |
| ) | |
| return number; | |
| end "UTL_REST_ENABLED_SQL_COPY"; | |
| / |
| create or replace package body "UTL_REST_ENABLED_SQL_COPY" as | |
| /* PL/SQLの文字列の最大長 */ | |
| C_MAX_LENGTH constant pls_integer := 32767; | |
| C_SINGLE_QUOTE constant char(1) := chr(39); | |
| /* PL/SQLのconditional compileを活用。通常はfalse */ | |
| $IF $$DEBUG = TRUE $THEN | |
| G_DEBUG constant boolean := true; | |
| $ELSE | |
| G_DEBUG constant boolean := false; | |
| $END | |
| /** | |
| * ログを出力するファンクション。 | |
| * dbms_output.put_lineを使っているが、ここを修正すれば、apex_debugに切り替え可能。 | |
| * 内部で呼び出しているapex_string.formatは引数を20個まで取れるが、 | |
| * このファンクションは10個までに制限している。 | |
| */ | |
| procedure log_message( | |
| p_message in varchar2 | |
| ,p0 in varchar2 default null | |
| ,p1 in varchar2 default null | |
| ,p2 in varchar2 default null | |
| ,p3 in varchar2 default null | |
| ,p4 in varchar2 default null | |
| ,p5 in varchar2 default null | |
| ,p6 in varchar2 default null | |
| ,p7 in varchar2 default null | |
| ,p8 in varchar2 default null | |
| ,p9 in varchar2 default null | |
| ) | |
| as | |
| begin | |
| if G_DEBUG then | |
| -- apex_debug.info(p_message, p0, p1, p2, p3, p4, p5, p6, p7, p8, p9); | |
| dbms_output.put_line(apex_string.format(p_message, p0, p1, p2, p3, p4, p5, p6, p7, p8, p9)); | |
| end if; | |
| end log_message; | |
| /** | |
| * １つの列定義をメモリ上の変数（の配列）に追加する。 | |
| */ | |
| procedure add_column_definition( | |
| p_operation_column_array in out t_operation_column_array_type | |
| ,p_column_count in out pls_integer | |
| ,p_column_name in user_tab_columns.column_name%type | |
| ,p_data_type in user_tab_columns.data_type%type | |
| ,p_data_length in user_tab_columns.data_length%type | |
| ,p_is_primary_key in number | |
| ) | |
| as | |
| begin | |
| p_column_count := p_column_count + 1; | |
| p_operation_column_array.extend; | |
| p_operation_column_array(p_column_count) := | |
| r_column_record_type(p_column_name, p_data_type, p_data_length, null, p_is_primary_key); | |
| end add_column_definition; | |
| /** | |
| * データ・コピーの対象表の定義情報を、レコードの配列に取り出す。 | |
| * コピーの方向がリモートからローカル、ローカルからリモートの両方で、 | |
| * ローカルの表定義を参照する。 | |
| * リモート表とローカル表の定義は一致していることが、データ・コピーの前提。 | |
| */ | |
| function get_column_definition( | |
| p_table_name in varchar2 | |
| ) | |
| return t_operation_column_array_type | |
| as | |
| l_column_count pls_integer; | |
| l_operation_column_array t_operation_column_array_type; | |
| l_operation_column r_column_record_type; | |
| l_is_primary_key number; | |
| l_data_type user_tab_columns.data_type%type; | |
| begin | |
| /* 列定義はUSER_TAB_COLUMNSを参照する。 */ | |
| l_column_count := 0; | |
| l_operation_column_array := t_operation_column_array_type(); | |
| for r in ( | |
| select column_name, data_type, data_length from user_tab_columns | |
| where table_name = p_table_name order by column_id | |
| ) | |
| loop | |
| /* | |
| * 列が主キーかどうか確認する。 | |
| */ | |
| begin | |
| select 1 into l_is_primary_key from user_constraints cons join user_cons_columns cols on cons.constraint_name = cols.constraint_name | |
| where cons.constraint_type = 'P' and cols.table_name = p_table_name and cols.column_name = r.column_name; | |
| exception | |
| when no_data_found then | |
| l_is_primary_key := 0; | |
| end; | |
| /* | |
| * data_typeについては、apex_exec.get_data_typeで扱える文字列に合わせる。 | |
| * https://docs.oracle.com/en/database/oracle/apex/24.1/aeapi/GET_DATA_TYPE-Function.html | |
| */ | |
| case | |
| when r.data_type in ('VARCHAR2','NUMBER','DATE','CLOB') then | |
| add_column_definition(l_operation_column_array, l_column_count, r.column_name | |
| ,r.data_type, r.data_length, l_is_primary_key); | |
| when r.data_type like 'TIMESTAMP(%)' then | |
| add_column_definition(l_operation_column_array, l_column_count, r.column_name | |
| ,'TIMESTAMP', r.data_length, l_is_primary_key); | |
| when r.data_type like 'TIMESTAMP(%) WITH TIME ZONE' then | |
| add_column_definition(l_operation_column_array, l_column_count, r.column_name | |
| ,'TIMESTAMP WITH TIME ZONE', r.data_length, l_is_primary_key); | |
| when r.data_type like 'TIMESTAMP(%) WITH LOCAL TIME ZONE' then | |
| add_column_definition(l_operation_column_array, l_column_count, r.column_name | |
| ,'TIMESTAMP WITH LOCAL TIME ZONE', r.data_length, l_is_primary_key); | |
| when r.data_type like 'INTERVAL DAY(%) TO SECOND(%)' then | |
| add_column_definition(l_operation_column_array, l_column_count, r.column_name | |
| ,'INTERVAL DAY TO SECOND', r.data_length, l_is_primary_key); | |
| when r.data_type like 'INTERVAL YEAR(%) TO MONTH' then | |
| add_column_definition(l_operation_column_array, l_column_count, r.column_name | |
| ,'INTERVAL YEAR TO MONTH', r.data_length, l_is_primary_key); | |
| /* | |
| BLOBは上手く処理できなかった。その他は通常使わない型なので処理対象に含めない。。 | |
| when r.data_type in ('BLOB', 'ANYDATA', 'SDO_GEOMETRY') then | |
| add_column_definition(l_operation_column_array, l_column_count, r.column_name | |
| ,r.data_type, r.data_length, l_is_primary_key); | |
| */ | |
| else | |
| null; | |
| end case; | |
| end loop; | |
| /* | |
| * 取り出した列定義を印刷する。 | |
| */ | |
| for i in 1..l_column_count | |
| loop | |
| l_operation_column := l_operation_column_array(i); | |
| log_message( | |
| 'i: %s, column_name: %s, data_type: %s, data_length: %s, column_position: %s, is_primary_key: %s' | |
| , i, l_operation_column.column_name, l_operation_column.data_type, l_operation_column.data_length | |
| , l_operation_column.column_position, l_operation_column.is_primary_key | |
| ); | |
| end loop; | |
| return l_operation_column_array; | |
| end get_column_definition; | |
| /** | |
| * リモート、ローカル双方でのデータの取り出しに使用するSELECT文を生成する。 | |
| */ | |
| function generate_select( | |
| p_table_name in varchar2 | |
| ,p_operation_column_array in t_operation_column_array_type | |
| ) | |
| return varchar2 | |
| as | |
| l_sql varchar2(C_MAX_LENGTH); | |
| l_operation_column r_column_record_type; | |
| begin | |
| l_sql := 'SELECT '; | |
| for i in 1..p_operation_column_array.count | |
| loop | |
| l_operation_column := p_operation_column_array(i); | |
| l_sql := l_sql || l_operation_column.column_name || ','; | |
| end loop; | |
| l_sql := substr(l_sql, 1, (length(l_sql) - 1) ); | |
| l_sql := l_sql || ' FROM ' || p_table_name; | |
| log_message('SELECT SQL: %s', l_sql); | |
| return l_sql; | |
| end generate_select; | |
| /** | |
| * ローカルでのデータの保存に使用するINSERT文を生成する。 | |
| * リモートではapex_exec.add_dml_rowを使うのでINSERT文は生成しない。 | |
| */ | |
| function generate_insert( | |
| p_table_name in varchar2 | |
| ,p_operation_column_array in t_operation_column_array_type | |
| ) | |
| return varchar2 | |
| as | |
| l_insert_sql varchar2(C_MAX_LENGTH); | |
| l_values_sql varchar2(C_MAX_LENGTH); | |
| l_operation_column r_column_record_type; | |
| begin | |
| l_insert_sql := 'INSERT INTO ' || dbms_assert.sql_object_name(p_table_name) || '('; | |
| l_values_sql := ') values('; | |
| for i in 1..p_operation_column_array.count | |
| loop | |
| l_operation_column := p_operation_column_array(i); | |
| l_insert_sql := l_insert_sql || l_operation_column.column_name || ','; | |
| l_values_sql := l_values_sql || ':' || i || ','; | |
| end loop; | |
| l_insert_sql := substr(l_insert_sql, 1, (length(l_insert_sql) - 1)) ; | |
| l_values_sql := substr(l_values_sql, 1, (length(l_values_sql) - 1)) ; | |
| l_insert_sql := l_insert_sql || l_values_sql || ')'; | |
| log_message('INSERT SQL: %s', l_insert_sql); | |
| return l_insert_sql; | |
| end generate_insert; | |
| /** | |
| * 公開されているAPIなので、パッケージ定義を参照のこと。 | |
| */ | |
| function copy_table_from_remote_to_local( | |
| p_server_static_id in varchar2 | |
| ,p_table_name in varchar2 | |
| ,p_commit_interval in pls_integer | |
| ) | |
| return number | |
| as | |
| l_operation_column_array t_operation_column_array_type; | |
| l_operation_column r_column_record_type; | |
| l_position pls_integer; | |
| l_cursor_id integer; | |
| l_rows_processed number; | |
| l_context apex_exec.t_context; | |
| l_remote_select_sql varchar2(C_MAX_LENGTH); | |
| l_local_insert_sql varchar2(C_MAX_LENGTH); | |
| l_local_values_sql varchar2(C_MAX_LENGTH); | |
| l_identity_sql varchar2(C_MAX_LENGTH); | |
| e_invalid_commit_interval exception; | |
| begin | |
| /* p_commit_intervalは0以上 */ | |
| if p_commit_interval < 0 then | |
| raise e_invalid_commit_interval; | |
| end if; | |
| /* 列情報を取り出す */ | |
| l_operation_column_array := get_column_definition(p_table_name); | |
| /* | |
| * ソースで実行するSELECT文とターゲットで実行するINSERT文を生成する。 | |
| */ | |
| l_remote_select_sql := generate_select(p_table_name, l_operation_column_array); | |
| l_local_insert_sql := generate_insert(p_table_name, l_operation_column_array); | |
| /* | |
| * SELECT文の実行のためにリモート・データベースを開く。 | |
| */ | |
| l_context := apex_exec.open_remote_sql_query( | |
| p_server_static_id => p_server_static_id | |
| ,p_sql_query => l_remote_select_sql | |
| ); | |
| /* | |
| * リモート・データベースの列情報を取得する。 | |
| */ | |
| for i in 1..l_operation_column_array.count | |
| loop | |
| l_operation_column := l_operation_column_array(i); | |
| l_position := apex_exec.get_column_position( l_context, l_operation_column.column_name ); | |
| log_message('GET_COLUMN_POSITION %s, %s', l_operation_column.column_name,l_position ); | |
| l_operation_column_array(i) := | |
| r_column_record_type(l_operation_column.column_name, l_operation_column.data_type, l_operation_column.data_length | |
| , l_position, l_operation_column.is_primary_key); | |
| end loop; | |
| /* | |
| * リモート・データベースから１行ずつ結果を取り出し、ローカル・データベースに書き込む。 | |
| */ | |
| l_rows_processed := 0; | |
| while apex_exec.next_row( l_context ) | |
| loop | |
| l_cursor_id := dbms_sql.open_cursor; | |
| dbms_sql.parse( l_cursor_id, l_local_insert_sql, dbms_sql.native ); | |
| for i in 1..l_operation_column_array.count | |
| loop | |
| l_operation_column := l_operation_column_array(i); | |
| case | |
| when l_operation_column.data_type = 'VARCHAR2' then | |
| dbms_sql.bind_variable( l_cursor_id, ':' || i, apex_exec.get_varchar2( l_context, l_operation_column.column_position) ); | |
| when l_operation_column.data_type = 'NUMBER' then | |
| dbms_sql.bind_variable( l_cursor_id, ':' || i, apex_exec.get_number( l_context, l_operation_column.column_position) ); | |
| when l_operation_column.data_type = 'DATE' then | |
| dbms_sql.bind_variable( l_cursor_id, ':' || i, apex_exec.get_date( l_context, l_operation_column.column_position) ); | |
| when l_operation_column.data_type = 'TIMESTAMP' then | |
| dbms_sql.bind_variable( l_cursor_id, ':' || i, apex_exec.get_timestamp( l_context, l_operation_column.column_position) ); | |
| when l_operation_column.data_type = 'TIMESTAMP WITH TIME ZONE' then | |
| dbms_sql.bind_variable( l_cursor_id, ':' || i, apex_exec.get_timestamp_tz( l_context, l_operation_column.column_position) ); | |
| when l_operation_column.data_type = 'TIMESTAMP WITH LOCAL TIME ZONE' then | |
| dbms_sql.bind_variable( l_cursor_id, ':' || i, apex_exec.get_timestamp_ltz( l_context, l_operation_column.column_position) ); | |
| when l_operation_column.data_type = 'CLOB' then | |
| dbms_sql.bind_variable( l_cursor_id, ':' || i, apex_exec.get_clob( l_context, l_operation_column.column_position) ); | |
| when l_operation_column.data_type = 'INTERVAL DAY TO SECOND' then | |
| dbms_sql.bind_variable( l_cursor_id, ':' || i, apex_exec.get_intervald2s( l_context, l_operation_column.column_position) ); | |
| when l_operation_column.data_type = 'INTERVAL YEAR TO MONTH' then | |
| dbms_sql.bind_variable( l_cursor_id, ':' || i, apex_exec.get_intervaly2m( l_context, l_operation_column.column_position) ); | |
| /* | |
| * get_column_definitionの中で対象から外しているため、以下のデータ・タイプが | |
| * 選択されることはない。 | |
| */ | |
| when l_operation_column.data_type = 'BLOB' then | |
| /* REST対応SQLでapex_exec.get_blobは動作しない。 */ | |
| dbms_sql.bind_variable( l_cursor_id, ':' || i, apex_exec.get_anydata( l_context, l_operation_column.column_position) ); | |
| when l_operation_column.data_type = 'ANYDATA' then | |
| dbms_sql.bind_variable( l_cursor_id, ':' || i, apex_exec.get_anydata( l_context, l_operation_column.column_position) ); | |
| when l_operation_column.data_type = 'SDO_GEOMETRY' then | |
| dbms_sql.bind_variable( l_cursor_id, ':' || i, apex_exec.get_sdo_geometry( l_context, l_operation_column.column_position) ); | |
| end case; | |
| end loop; | |
| begin | |
| l_rows_processed := l_rows_processed + dbms_sql.execute( l_cursor_id ); | |
| exception | |
| when dup_val_on_index then | |
| /* ignore ORA-1 unique key constraint exception */ | |
| null; | |
| end; | |
| dbms_sql.close_cursor( l_cursor_id ); | |
| /* | |
| * commitを呼び出す。 | |
| */ | |
| if p_commit_interval > 0 and mod(l_rows_processed, p_commit_interval ) = 0 then | |
| log_message('commit on %s', l_rows_processed); | |
| commit; | |
| end if; | |
| end loop; | |
| apex_exec.close( l_context ); | |
| /* | |
| * IDENTITY列があれば、シーケンスを進める。 | |
| */ | |
| for r in ( | |
| select column_name from user_tab_columns | |
| where table_name = p_table_name and data_type = 'NUMBER' | |
| and identity_column = 'YES' and default_on_null = 'YES' | |
| ) | |
| loop | |
| log_message('Update Identity Column: %s, %s', p_table_name, r.column_name); | |
| begin | |
| l_identity_sql := apex_string.format( | |
| 'alter table %s modify %s generated by default on null as identity (start with limit value)' | |
| , dbms_assert.sql_object_name(p_table_name), dbms_assert.simple_sql_name(r.column_name) | |
| ); | |
| execute immediate l_identity_sql; | |
| exception | |
| when others then | |
| /* 本質的な処理ではないので、例外は無視する。 */ | |
| log_message(SQLERRM); | |
| end; | |
| end loop; | |
| return l_rows_processed; | |
| exception | |
| when others then | |
| apex_exec.close( l_context ); | |
| raise; | |
| end copy_table_from_remote_to_local; | |
| /** | |
| * 公開されているAPIなので、パッケージ定義を参照のこと。 | |
| */ | |
| function copy_table_from_remote_to_local_s( | |
| p_app_id in number | |
| ,p_page_id in number | |
| ,p_username in varchar2 | |
| ,p_server_static_id in varchar2 | |
| ,p_table_name in varchar2 | |
| ,p_commit_interval in pls_integer | |
| ,p_debug_level in apex_debug.t_log_level | |
| ) | |
| return number | |
| as | |
| l_session_id number; | |
| l_rows_processed number; | |
| begin | |
| -- APEXセッションの開始。 | |
| apex_session.create_session( | |
| p_app_id => p_app_id | |
| ,p_page_id => p_page_id | |
| ,p_username => p_username | |
| ); | |
| l_session_id := nv('APP_SESSION'); | |
| -- デバッグ・レベルの設定 | |
| if p_debug_level is not null then | |
| apex_debug.enable( p_debug_level ); | |
| end if; | |
| -- 実際の処理 | |
| l_rows_processed := copy_table_from_remote_to_local( | |
| p_server_static_id => p_server_static_id | |
| ,p_table_name => p_table_name | |
| ,p_commit_interval => p_commit_interval | |
| ); | |
| -- APEXセッションの終了 | |
| apex_session.delete_session( | |
| p_session_id => l_session_id | |
| ); | |
| return l_rows_processed; | |
| end copy_table_from_remote_to_local_s; | |
| /** | |
| * 公開されているAPIなので、パッケージ定義を参照のこと。 | |
| */ | |
| function copy_table_from_local_to_remote( | |
| p_server_static_id in varchar2 | |
| ,p_table_name in varchar2 | |
| ,p_commit_interval in pls_integer | |
| ,p_continue_on_error in boolean | |
| ) | |
| return number | |
| as | |
| l_column_count pls_integer; | |
| l_operation_column_array t_operation_column_array_type; | |
| l_operation_column r_column_record_type; | |
| l_cursor_id number; | |
| l_dummy number; | |
| l_rows_processed number; | |
| l_rows_added number; | |
| l_context apex_exec.t_context; | |
| l_columns apex_exec.t_columns; | |
| l_is_primary_key boolean; | |
| l_rest_data_type number; | |
| l_local_select_sql varchar2(C_MAX_LENGTH); | |
| l_v_varchar2 varchar2(C_MAX_LENGTH); | |
| l_v_number number; | |
| l_v_date date; | |
| l_v_timestamp timestamp; | |
| l_v_timestamp_tz timestamp with time zone; | |
| l_v_timestamp_ltz timestamp with local time zone; | |
| l_v_clob clob; | |
| l_v_intervald2s interval day to second; | |
| l_v_intervaly2m interval year to month; | |
| l_v_blob blob; | |
| l_v_anydata anydata; | |
| l_v_sdo_geometry sdo_geometry; | |
| e_invalid_commit_interval exception; | |
| begin | |
| /* p_commit_intervalは0以上 */ | |
| if p_commit_interval < 0 then | |
| raise e_invalid_commit_interval; | |
| end if; | |
| /* 列情報を取り出す */ | |
| l_operation_column_array := get_column_definition(p_table_name); | |
| /* | |
| * ソースで実行するSELECT文を生成する。 | |
| */ | |
| l_local_select_sql := generate_select(p_table_name, l_operation_column_array); | |
| /* | |
| * リモートのDMLハンドルの列定義を生成する。 | |
| */ | |
| for i in 1..l_operation_column_array.count | |
| loop | |
| l_operation_column := l_operation_column_array(i); | |
| l_is_primary_key := (l_operation_column.is_primary_key = 1); | |
| l_rest_data_type := apex_exec.get_data_type( l_operation_column.data_type ); | |
| log_message('Add Column: %s, %s', l_operation_column.column_name, l_rest_data_type); | |
| apex_exec.add_column( | |
| p_columns => l_columns | |
| ,p_column_name => l_operation_column.column_name | |
| ,p_data_type => l_rest_data_type | |
| ,p_is_primary_key => l_is_primary_key | |
| ); | |
| end loop; | |
| /* | |
| * ローカルのDBからデータを取得する。 | |
| * | |
| * DBMS_SQLでSELECT文を実行するには、_dbms_sql_security_level の設定が必要みたい。 | |
| * dbms_sql.open_cursor でエラーが発生する。 | |
| * | |
| * ORA-29471: DBMS_SQLアクセスが拒否されました | |
| * | |
| * ALTER SYSTEM SET "_dbms_sql_security_level" = 384 SCOPE=SPFILE; | |
| */ | |
| l_cursor_id := dbms_sql.open_cursor(); | |
| dbms_sql.parse(l_cursor_id, l_local_select_sql, dbms_sql.native); | |
| for i in 1..l_operation_column_array.count | |
| loop | |
| l_operation_column := l_operation_column_array(i); | |
| case | |
| when l_operation_column.data_type = 'VARCHAR2' then | |
| dbms_sql.define_column(l_cursor_id, i, l_v_varchar2, l_operation_column.data_length); | |
| when l_operation_column.data_type = 'NUMBER' then | |
| dbms_sql.define_column(l_cursor_id, i, l_v_number); | |
| when l_operation_column.data_type = 'DATE' then | |
| dbms_sql.define_column(l_cursor_id, i, l_v_date); | |
| when l_operation_column.data_type = 'TIMESTAMP' then | |
| dbms_sql.define_column(l_cursor_id, i, l_v_timestamp); | |
| when l_operation_column.data_type = 'TIMESTAMP WITH TIME ZONE' then | |
| dbms_sql.define_column(l_cursor_id, i, l_v_timestamp_tz); | |
| when l_operation_column.data_type = 'TIMESTAMP WITH LOCAL TIME ZONE' then | |
| dbms_sql.define_column(l_cursor_id, i, l_v_timestamp_ltz); | |
| when l_operation_column.data_type = 'CLOB' then | |
| dbms_sql.define_column(l_cursor_id, i, l_v_clob); | |
| when l_operation_column.data_type = 'INTERVAL DAY TO SECOND' then | |
| dbms_sql.define_column(l_cursor_id, i, l_v_intervald2s); | |
| when l_operation_column.data_type = 'INTERVAL YEAR TO MONTH' then | |
| dbms_sql.define_column(l_cursor_id, i, l_v_intervaly2m); | |
| /* | |
| * get_column_definitionの中で対象から外しているため、以下のデータ・タイプが | |
| * 選択されることはない。 | |
| */ | |
| when l_operation_column.data_type = 'BLOB' then | |
| dbms_sql.define_column(l_cursor_id, i, l_v_blob); | |
| when l_operation_column.data_type = 'ANYDATA' then | |
| dbms_sql.define_column(l_cursor_id, i, l_v_anydata); | |
| when l_operation_column.data_type = 'SDO_GEOMETRY' then | |
| dbms_sql.define_column(l_cursor_id, i, l_v_sdo_geometry); | |
| end case; | |
| end loop; | |
| l_dummy := dbms_sql.execute( l_cursor_id ); | |
| /* | |
| * ローカルDBから１行ずつ取り出して、リモートに送信するリクエストに加える。 | |
| */ | |
| l_rows_processed := 0; | |
| l_rows_added := 0; | |
| while dbms_sql.fetch_rows(l_cursor_id) > 0 | |
| loop | |
| /* | |
| * p_commit_intervalの指定に合わせて、サーバーに更新要求を送信する。 | |
| */ | |
| if mod(l_rows_processed, p_commit_interval) = 0 then | |
| if l_rows_added > 0 then | |
| /* サーバーに要求を送信する。 */ | |
| log_message('Send Rquest: %s, %s', l_rows_processed, l_rows_added); | |
| apex_exec.execute_dml( | |
| p_context => l_context, | |
| p_continue_on_error => false | |
| ); | |
| apex_exec.close( l_context ); | |
| l_rows_added := 0; | |
| end if; | |
| /* リモートDBのコンテキストを作成する */ | |
| l_context := apex_exec.open_remote_dml_context( | |
| p_server_static_id => p_server_static_id | |
| ,p_columns => l_columns | |
| ,p_query_type => apex_exec.c_query_type_table | |
| ,p_table_name => p_table_name | |
| ,p_lost_update_detection => apex_exec.c_lost_update_none | |
| ); | |
| log_message('New Remote Context Created %s', l_context); | |
| end if; | |
| /* | |
| * 一行追加。 | |
| */ | |
| apex_exec.add_dml_row( | |
| p_context => l_context, | |
| p_operation => apex_exec.c_dml_operation_insert | |
| ); | |
| for i in 1..l_operation_column_array.count | |
| loop | |
| l_operation_column := l_operation_column_array(i); | |
| log_message('COLUMN_NAME: %s DATA_TYPE: %s', l_operation_column.column_name, l_operation_column.data_type); | |
| case | |
| when l_operation_column.data_type = 'VARCHAR2' then | |
| dbms_sql.column_value(l_cursor_id, i, l_v_varchar2); | |
| apex_exec.set_value(l_context, l_operation_column.column_name, l_v_varchar2); | |
| when l_operation_column.data_type = 'NUMBER' then | |
| dbms_sql.column_value(l_cursor_id, i, l_v_number); | |
| apex_exec.set_value(l_context, l_operation_column.column_name, l_v_number); | |
| when l_operation_column.data_type = 'DATE' then | |
| dbms_sql.column_value(l_cursor_id, i, l_v_date); | |
| apex_exec.set_value(l_context, l_operation_column.column_name, l_v_date); | |
| when l_operation_column.data_type = 'TIMESTAMP' then | |
| dbms_sql.column_value(l_cursor_id, i, l_v_timestamp); | |
| apex_exec.set_value(l_context, l_operation_column.column_name, l_v_timestamp); | |
| when l_operation_column.data_type = 'TIMESTAMP WITH TIME ZONE' then | |
| dbms_sql.column_value(l_cursor_id, i, l_v_timestamp_tz); | |
| apex_exec.set_value(l_context, l_operation_column.column_name, l_v_timestamp_tz); | |
| when l_operation_column.data_type = 'TIMESTAMP WITH LOCAL TIME ZONE' then | |
| dbms_sql.column_value(l_cursor_id, i, l_v_timestamp_ltz); | |
| apex_exec.set_value(l_context, l_operation_column.column_name, l_v_timestamp_ltz); | |
| when l_operation_column.data_type = 'CLOB' then | |
| dbms_sql.column_value(l_cursor_id, i, l_v_clob); | |
| apex_exec.set_value(l_context, l_operation_column.column_name, l_v_clob); | |
| when l_operation_column.data_type = 'INTERVAL DAY TO SECOND' then | |
| dbms_sql.column_value(l_cursor_id, i, l_v_intervald2s); | |
| apex_exec.set_value(l_context, l_operation_column.column_name, l_v_intervald2s); | |
| when l_operation_column.data_type = 'INTERVAL YEAR TO MONTH' then | |
| dbms_sql.column_value(l_cursor_id, i, l_v_intervaly2m); | |
| apex_exec.set_value(l_context, l_operation_column.column_name, l_v_intervaly2m); | |
| /* | |
| get_column_definitionで処理対象から外している。 | |
| */ | |
| when l_operation_column.data_type = 'BLOB' then | |
| dbms_sql.column_value(l_cursor_id, i, l_v_blob); | |
| apex_exec.set_value(l_context, l_operation_column.column_name, l_v_blob); | |
| when l_operation_column.data_type = 'ANYDATA' then | |
| dbms_sql.column_value(l_cursor_id, i, l_v_anydata); | |
| apex_exec.set_value(l_context, l_operation_column.column_name, l_v_anydata); | |
| when l_operation_column.data_type = 'SDO_GEOMETRY' then | |
| dbms_sql.column_value(l_cursor_id, i, l_v_sdo_geometry); | |
| apex_exec.set_value(l_context, l_operation_column.column_name, l_v_sdo_geometry); | |
| end case; | |
| end loop; | |
| l_rows_processed := l_rows_processed + 1; | |
| l_rows_added := l_rows_added + 1; | |
| end loop; | |
| dbms_sql.close_cursor( l_cursor_id ); | |
| /* | |
| * 未送信のリクエストを送信する。 | |
| */ | |
| if l_rows_added > 0 then | |
| /* サーバーに要求を送信する。 */ | |
| log_message('Send Final Rquest: %s, %s, %s', l_context, l_rows_processed, l_rows_added); | |
| begin | |
| apex_exec.execute_dml( | |
| p_context => l_context | |
| ,p_continue_on_error => p_continue_on_error | |
| ); | |
| exception | |
| when others then | |
| log_message(apex_exec.get_dml_status_message( l_context )); | |
| raise; | |
| end; | |
| apex_exec.close( l_context ); | |
| end if; | |
| return l_rows_processed; | |
| exception | |
| when others then | |
| dbms_sql.close_cursor( l_cursor_id ); | |
| apex_exec.close( l_context ); | |
| RAISE; | |
| end copy_table_from_local_to_remote; | |
| /** | |
| * 公開されているAPIなので、パッケージ定義を参照のこと。 | |
| */ | |
| function copy_table_from_local_to_remote_s( | |
| p_app_id in number | |
| ,p_page_id in number | |
| ,p_username in varchar2 | |
| ,p_server_static_id in varchar2 | |
| ,p_table_name in varchar2 | |
| ,p_commit_interval in pls_integer | |
| ,p_continue_on_error in boolean | |
| ,p_debug_level in apex_debug.t_log_level | |
| ) | |
| return number | |
| as | |
| l_session_id number; | |
| l_rows_processed number; | |
| begin | |
| -- APEXセッションの開始。 | |
| apex_session.create_session( | |
| p_app_id => p_app_id | |
| ,p_page_id => p_page_id | |
| ,p_username => p_username | |
| ); | |
| l_session_id := nv('APP_SESSION'); | |
| -- デバッグ・レベルの設定 | |
| if p_debug_level is not null then | |
| apex_debug.enable( p_debug_level ); | |
| end if; | |
| -- 実際の処理 | |
| l_rows_processed := copy_table_from_local_to_remote( | |
| p_server_static_id => p_server_static_id | |
| ,p_table_name => p_table_name | |
| ,p_commit_interval => p_commit_interval | |
| ,p_continue_on_error => p_continue_on_error | |
| ); | |
| -- APEXセッションの終了 | |
| apex_session.delete_session( | |
| p_session_id => l_session_id | |
| ); | |
| return l_rows_processed; | |
| end copy_table_from_local_to_remote_s; | |
| /** | |
| * カンマ区切りの文字列をそれぞれシンクルクォートで区切った上で | |
| * 連結し直す。 | |
| */ | |
| function concat_with_single_quote( | |
| p_string varchar2 | |
| ) | |
| return varchar2 | |
| as | |
| l_unquoted_strings apex_t_varchar2; | |
| l_quoted_strings apex_t_varchar2; | |
| begin | |
| l_unquoted_strings := apex_string.split(p_string,','); | |
| l_quoted_strings := apex_t_varchar2(); | |
| for i in 1..l_unquoted_strings.count | |
| loop | |
| apex_string.push(l_quoted_strings, C_SINGLE_QUOTE || l_unquoted_strings(i) || C_SINGLE_QUOTE); | |
| end loop; | |
| return apex_string.join(l_quoted_strings,','); | |
| end concat_with_single_quote; | |
| /** | |
| * 自律トランザクションでBLOB列を更新する。キーが数値のとき。 | |
| */ | |
| function update_blob( | |
| p_sql in varchar2 | |
| ,p_key in number | |
| ,p_blob in blob | |
| ) | |
| return number | |
| as | |
| l_rows_processed number; | |
| pragma autonomous_transaction; | |
| begin | |
| execute immediate p_sql using p_blob, p_key; | |
| l_rows_processed := SQL%ROWCOUNT; | |
| commit; | |
| return l_rows_processed; | |
| end update_blob; | |
| /** | |
| * 自律トランザクションでBLOB列を更新する。キーが文字列のとき。 | |
| */ | |
| function update_blob( | |
| p_sql in varchar2 | |
| ,p_key in varchar2 | |
| ,p_blob in blob | |
| ) | |
| return number | |
| as | |
| l_rows_processed number; | |
| pragma autonomous_transaction; | |
| begin | |
| execute immediate p_sql using p_blob, p_key; | |
| l_rows_processed := SQL%ROWCOUNT; | |
| commit; | |
| return l_rows_processed; | |
| end update_blob; | |
| /** | |
| * 公開されているAPIなので、パッケージ定義を参照のこと。 | |
| */ | |
| function copy_blob_from_remote_to_local( | |
| p_server_static_id in varchar2 | |
| ,p_table_name in varchar2 | |
| ,p_key_column_name in varchar2 | |
| ,p_blob_column_name in varchar2 | |
| ,p_keys_target in varchar2 default null | |
| ,p_keys_copied out varchar2 | |
| ,p_keys_failed out varchar2 | |
| ) | |
| return number | |
| as | |
| l_is_key_number boolean; -- true NUMBER, false VARCHAR2 | |
| l_key_column_data_type user_tab_columns.data_type%type; | |
| l_id_num number; | |
| l_id_str varchar2(400); -- 主キーとなる文字列が400以上ということはないはず。 | |
| c_selected_ids sys_refcursor; | |
| l_sql_select_keys varchar2(C_MAX_LENGTH); | |
| l_update_sql varchar2(C_MAX_LENGTH); | |
| l_is_updated number; -- 0 failed, 1 updated | |
| l_rows_processed number; | |
| l_keys_copied apex_t_varchar2; | |
| l_keys_failed apex_t_varchar2; | |
| l_context apex_exec.t_context; | |
| l_remote_sql_base varchar2(C_MAX_LENGTH); | |
| l_remote_sql varchar2(C_MAX_LENGTH); -- REST対応SQLとして実行するSELECT文 | |
| l_position pls_integer; | |
| l_clob clob; | |
| l_blob blob; | |
| e_key_column_not_exist exception; | |
| e_unsupported_key_column_type exception; | |
| begin | |
| /* | |
| * 主キーの存在と型をビューUSER_TAB_COLUMNSから確認する。 | |
| */ | |
| begin | |
| select data_type into l_key_column_data_type from user_tab_columns | |
| where table_name = p_table_name and column_name = p_key_column_name; | |
| case | |
| when l_key_column_data_type = 'NUMBER' then | |
| l_is_key_number := true; | |
| when l_key_column_data_type = 'VARCHAR2' then | |
| l_is_key_number := false; | |
| else | |
| raise e_unsupported_key_column_type; | |
| end case; | |
| exception | |
| when no_data_found then | |
| raise e_key_column_not_exist; | |
| end; | |
| /* | |
| * ローカルの表からコピー対象となる主キーを取り出すSELECT文を作成する。 | |
| */ | |
| l_sql_select_keys := 'SELECT ' || dbms_assert.simple_sql_name(p_key_column_name) | |
| || ' FROM ' || dbms_assert.sql_object_name(p_table_name); | |
| if p_keys_target is not null then | |
| l_sql_select_keys := l_sql_select_keys | |
| || ' WHERE ' || dbms_assert.simple_sql_name(p_key_column_name); | |
| /* | |
| * キー値のタイプが文字列の場合は、キー値をシングルクォートで囲む。 | |
| */ | |
| if l_is_key_number then | |
| l_sql_select_keys := l_sql_select_keys | |
| || ' IN (' || p_keys_target || ')'; | |
| else | |
| l_sql_select_keys := l_sql_select_keys | |
| || ' IN (' || concat_with_single_quote(p_keys_target) || ')'; | |
| end if; | |
| end if; | |
| log_message('KEY SQL: %s', l_sql_select_keys); | |
| /* | |
| * UPDATE文は条件によらず同じ。 | |
| */ | |
| l_update_sql := 'UPDATE ' || dbms_assert.sql_object_name(p_table_name) | |
| || ' SET ' || dbms_assert.simple_sql_name(p_blob_column_name) || ' = :1 WHERE ' | |
| || dbms_assert.simple_sql_name(p_key_column_name) || ' = :2'; | |
| log_message('UPDATE SQL: %s', l_update_sql); | |
| /* | |
| * ローカルのデータベースより、BLOBのアップデート対象となる行のキー値を１つずつ取り出し、 | |
| * １回取り出すごとにREST対応SQLを発行する。リモート側ではAPEX_UTIL.BLOB_TO_CLOBを呼び出し、 | |
| * BLOBをCLOBに変換し、呼び出し元のローカル側に返す。 | |
| * ローカル側は受信したCLOBをAPEX_UTIL.CLOB_TO_BLOBを呼び出してBLOBに戻して、ローカル表を更新する。 | |
| * | |
| * 元々BLOBを含むREST呼び出しはデータ量が多いので、一行ごとにREST APIを呼び出してもパフォーマンス | |
| * は変わらないはず。 | |
| * | |
| * リモート側のAPEX_UTIL.BLOB_TO_CLOB、ローカル側のAPEX_UTIL.CLOB_TO_BLOBの両方で | |
| * キャラクタセットの指定はしていない。リモートとローカルのデータベースで同じキャラクタセットであることが | |
| * 前提。 | |
| * | |
| * ローカル表の更新は自律トランザクションで行なっている。 | |
| */ | |
| l_rows_processed := 0; | |
| l_keys_copied := apex_t_varchar2(); | |
| l_keys_failed := apex_t_varchar2(); | |
| l_remote_sql_base := 'SELECT APEX_UTIL.BLOB_TO_CLOB(' || dbms_assert.simple_sql_name(p_blob_column_name) | |
| || ') CONTENT FROM ' || dbms_assert.sql_object_name(p_table_name) | |
| || ' WHERE ' || dbms_assert.simple_sql_name(p_key_column_name ) || ' = '; | |
| open c_selected_ids for l_sql_select_keys; | |
| loop | |
| l_remote_sql := l_remote_sql_base; | |
| if l_is_key_number then | |
| fetch c_selected_ids into l_id_num; | |
| exit when c_selected_ids%NOTFOUND; | |
| l_remote_sql := l_remote_sql || to_char(l_id_num); | |
| else | |
| fetch c_selected_ids into l_id_str; | |
| exit when c_selected_ids%NOTFOUND; | |
| l_remote_sql := l_remote_sql || C_SINGLE_QUOTE || l_id_str || C_SINGLE_QUOTE; | |
| end if; | |
| log_message('REMOTE SQL: %s', l_remote_sql); | |
| /* | |
| * REST対応SQLの発行 | |
| */ | |
| l_context := apex_exec.open_remote_sql_query( | |
| p_server_static_id => p_server_static_id | |
| ,p_sql_query => l_remote_sql | |
| ); | |
| /* 列名はCONTENT決め打ち */ | |
| l_position := apex_exec.get_column_position( l_context, 'CONTENT'); | |
| /* | |
| * ID一意指定なので、処理は１回だけ。 | |
| * キー値が一意でない場合の結果は不定。 | |
| */ | |
| if apex_exec.next_row( l_context ) then | |
| l_clob := apex_exec.get_clob( l_context, l_position ); | |
| /* リモート側と一致している前提で、キャラクタセットは指定していない */ | |
| l_blob := apex_util.clob_to_blob( l_clob ); | |
| /* | |
| * BLOB列を更新する。 | |
| */ | |
| if l_is_key_number then | |
| /* | |
| * 自律トランザクションによる更新。第２引数のタイプがNUMBERのファンクションが呼ばれる。 | |
| */ | |
| l_is_updated := update_blob( l_update_sql, l_id_num, l_blob ); | |
| if l_is_updated = 1 then | |
| apex_string.push(l_keys_copied, l_id_num); | |
| else | |
| apex_string.push(l_keys_failed, l_id_num); | |
| end if; | |
| else | |
| /* 第２引数が文字列 */ | |
| l_is_updated := update_blob( l_update_sql, l_id_str, l_blob ); | |
| if l_is_updated = 1 then | |
| apex_string.push(l_keys_copied, l_id_str); | |
| else | |
| apex_string.push(l_keys_failed, l_id_str); | |
| end if; | |
| end if; | |
| l_rows_processed := l_rows_processed + l_is_updated; | |
| else | |
| /* | |
| * ローカルにはキー値の行があって、リモートには無かった場合はkeys_failedに | |
| * エントリが追加される。 | |
| * 指定したキー値の行がローカル表にない場合は、keys_failedには追加されない。 | |
| */ | |
| if l_is_key_number then | |
| apex_string.push(l_keys_failed, l_id_num); | |
| else | |
| apex_string.push(l_keys_failed, l_id_str); | |
| end if; | |
| end if; | |
| apex_exec.close( l_context ); | |
| end loop; | |
| close c_selected_ids; | |
| /* | |
| * BLOBがコピーされたキー値、コピーされなかったキー値、コピーされた行数を呼び出し元に返す。 | |
| */ | |
| p_keys_copied := apex_string.join(l_keys_copied,','); | |
| p_keys_failed := apex_string.join(l_keys_failed,','); | |
| return l_rows_processed; | |
| end copy_blob_from_remote_to_local; | |
| /** | |
| * 公開されているAPIなので、パッケージ定義を参照のこと。 | |
| */ | |
| function copy_blob_from_remote_to_local_s( | |
| p_app_id in number | |
| ,p_page_id in number | |
| ,p_username in varchar2 | |
| ,p_server_static_id in varchar2 | |
| ,p_table_name in varchar2 | |
| ,p_key_column_name in varchar2 | |
| ,p_blob_column_name in varchar2 | |
| ,p_keys_target in varchar2 default null | |
| ,p_keys_copied out varchar2 | |
| ,p_keys_failed out varchar2 | |
| ,p_debug_level in apex_debug.t_log_level | |
| ) | |
| return number | |
| as | |
| l_session_id number; | |
| l_rows_processed number; | |
| begin | |
| -- APEXセッションの開始。 | |
| apex_session.create_session( | |
| p_app_id => p_app_id | |
| ,p_page_id => p_page_id | |
| ,p_username => p_username | |
| ); | |
| l_session_id := nv('APP_SESSION'); | |
| -- デバッグ・レベルの設定 | |
| if p_debug_level is not null then | |
| apex_debug.enable( p_debug_level ); | |
| end if; | |
| -- 実際の処理 | |
| l_rows_processed := copy_blob_from_remote_to_local( | |
| p_server_static_id => p_server_static_id | |
| ,p_table_name => p_table_name | |
| ,p_key_column_name => p_key_column_name | |
| ,p_blob_column_name => p_blob_column_name | |
| ,p_keys_target => p_keys_target | |
| ,p_keys_copied => p_keys_copied | |
| ,p_keys_failed => p_keys_failed | |
| ); | |
| -- APEXセッションの終了 | |
| apex_session.delete_session( | |
| p_session_id => l_session_id | |
| ); | |
| return l_rows_processed; | |
| end copy_blob_from_remote_to_local_s; | |
| end "UTL_REST_ENABLED_SQL_COPY"; | |
| / |
