- データベースにサンプル・スキーマSHをインストールし、SQLclのMCPサーバーが使用するデータベース・ユーザーMCPUSERを作成します。
- mcpoとSQLclをインストールしたコンテナ・イメージを作成します。作成したコンテナ・イメージからコンテナの作成と実行を行い、コンテナにSQLclが参照するデータベースへの接続情報を保存します。
- Ollamaにgpt-oss:20bをロードし実行します。
- Open WebUIをコンテナとして実行します。
- ブラウザからOpen WebUIにアクセスし、ツールとしてSQLclを組み込んだmcpoを設定します。
- チャットを行い動作を確認します。
データベースの準備
podman run -d --name sales-db -p 1521:1521 container-registry.oracle.com/database/free:latest
% podman run -d --name sales-db -p 1521:1521 container-registry.oracle.com/database/free:latest
f0c94a522a9b3c32fba7fa3295774de338c8958bdf63a0df15b1491232d12a30
%
コンテナのログにDATABASE IS READY TO USE!と出力されていることを確認します。出力されていれば、データベースは利用できます。
podman logs sales-db
% podman logs sales-db
Starting Oracle Net Listener.
Oracle Net Listener started.
Starting Oracle Database instance FREE.
Oracle Database instance FREE started.
The Oracle base remains unchanged with value /opt/oracle
#########################
DATABASE IS READY TO USE!
#########################
The following output is now a tail of the alert.log:
FREEPDB1(3):Opening pdb with Resource Manager plan: DEFAULT_PLAN
2025-08-07T00:44:35.601724+00:00
Completed: Pluggable database FREEPDB1 opened read write
Completed: ALTER DATABASE OPEN
2025-08-07T00:44:35.741920+00:00
===========================================================
Dumping current patch information
===========================================================
No patches have been applied
===========================================================
%
SYSおよびSYSTEMのパスワードを設定します。デフォルトではランダムなパスワードが設定されています。
podman exec sales-db ./setPassword.sh <SYSのパスワード>
% podman exec sales-db ./setPassword.sh *******
The Oracle base remains unchanged with value /opt/oracle
SQL*Plus: Release 23.0.0.0.0 - Production on Thu Aug 7 00:48:52 2025
Version 23.8.0.25.04
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04
SQL>
User altered.
SQL>
User altered.
SQL>
Session altered.
SQL>
User altered.
SQL> Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04
%
Oracle Database Sample Schemas 23cのv23.3をダウンロードします。
curl -OL https://github.com/oracle-samples/db-sample-schemas/archive/refs/tags/v23.3.zip
% curl -OL https://github.com/oracle-samples/db-sample-schemas/archive/refs/tags/v23.3.zip
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
0 0 0 0 0 0 0 0 --:--:-- --:--:-- --:--:-- 0
100 11.6M 0 11.6M 0 0 3674k 0 --:--:-- 0:00:03 --:--:-- 4709k
%
ダウンロードしたv23.3.zipを解凍し、sales_historyのサンプル・データをインストールします。インストール作業にSQLclを使用します。
データベースにユーザーSYSTEMで接続し、sh_install.sqlを実行します。スクリプトを実行すると、新規に作成するスキーマSHに設定するパスワードの入力を求められます。この後の作業でデータベースにスキーマSHで接続するため、入力するパスワードは覚えておきます。
スキーマSHのデフォルト表領域や上書きの設定はデフォルトのままで、変更は不要です。
cd db-sample-schemas-23.3/sales_history
sql system/[パスワード]@localhost/freepdb1
@sh_install
% unzip v23.3.zip
Archive: v23.3.zip
e3325a83e56c516815844025418a96ecaf219751
creating: db-sample-schemas-23.3
extracting: db-sample-schemas-23.3/.gitignore
inflating: db-sample-schemas-23.3/LICENSE.txt
inflating: db-sample-schemas-23.3/README.md
inflating: db-sample-schemas-23.3/README.txt
inflating: db-sample-schemas-23.3/SECURITY.md
[中略]
inflating: db-sample-schemas-23.3/sales_history/sh_install.sql
inflating: db-sample-schemas-23.3/sales_history/sh_populate.sql
inflating: db-sample-schemas-23.3/sales_history/sh_uninstall.sql
inflating: db-sample-schemas-23.3/sales_history/supplementary_demographics.csv
inflating: db-sample-schemas-23.3/sales_history/times.csv
% cd db-sample-schemas-23.3/sales_history
sales_history % sql system/*******@localhost/freepdb1
SQLcl: 木 8月 07 10:08:22 2025のリリース25.2 Production
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Last Successful login time: 木 8月 07 2025 10:08:23 +09:00
接続先:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04
SQL> @sh_install
Thank you for installing the Oracle Sales History Sample Schema.
This installation script will automatically exit your database session
at the end of the installation or if any error is encountered.
The entire installation will be logged into the 'sh_install.log' log file.
Enter a password for the user SH: ******
Enter a tablespace for SH [USERS]:
Do you want to overwrite the schema, if it already exists? [YES|no]:
Start time: 07-AUG-25 01.09.00.140452 AM +00:00
****** Creating COUNTRIES table ....
Table COUNTRIESは作成されました。
****** Creating CUSTOMERS table ....
Table CUSTOMERSは作成されました。
****** Creating PROMOTIONS table ....
Table PROMOTIONSは作成されました。
[中略]
Installation verification
____________________________
Verification:
Table provided actual
_____________________________ ___________ _________
channels 5 5
costs 82112 82112
countries 35 35
customers 55500 55500
products 72 72
promotions 503 503
sales 918843 918843
times 1826 1826
supplementary_demographics 4500 4500
Thank you!
___________________________________________________________
The installation of the sample schema is now finished.
Please check the installation verification output above.
You will now be disconnected from the database.
Thank you for using Oracle Database!
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04から切断されました
sales_history %
スキーマSHに接続し、マテリアライズド・ビューCAL_MONTH_SALES_MVとFWEEK_PSCAT_SALES_MVを完全リフレッシュします。
begin
dbms_mview.refresh('CAL_MONTH_SALES_MV', method => 'C');
dbms_mview.refresh('FWEEK_PSCAT_SALES_MV', method => 'C');
end;
/sales_history % sql sh/********@localhost/freepdb1
SQLcl: 木 8月 07 10:17:01 2025のリリース25.2 Production
Copyright (c) 1982, 2025, Oracle. All rights reserved.
接続先:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04
SQL> begin
2 dbms_mview.refresh('CAL_MONTH_SALES_MV', method => 'C');
3 dbms_mview.refresh('FWEEK_PSCAT_SALES_MV', method => 'C');
4 end;
5* /
PL/SQLプロシージャが正常に完了しました。
SQL> exit
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04から切断されました
sales_history %
grant connect,resource to mcpuser;
alter user mcpuser quota 10m on users;
sales_history % sql sys/******@localhost/freepdb1 as sysdba
SQLcl: 木 8月 07 10:30:27 2025のリリース25.2 Production
Copyright (c) 1982, 2025, Oracle. All rights reserved.
接続先:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04
SQL> create user mcpuser identified by mcpuser;
User MCPUSERは作成されました。
SQL> grant connect,resource to mcpuser;
Grantが正常に実行されました。
SQL> alter user mcpuser quota 10m on users;
User MCPUSERが変更されました。
SQL>
作成したデータベース・ユーザーMCPUSERに、スキーマSHが持つ表やビューのSELECT権限を与えます。
grant select any table on schema sh to mcpuser;
SQL> grant select any table on schema sh to mcpuser;
Grantが正常に実行されました。
SQL> exit
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04から切断されました
sales_history %
データベースにユーザーMCPUSERで接続し、スキーマSHの表やビューを参照するシノニムを作成します。
set serveroutput on
declare
l_statement varchar2(32767);
l_select varchar2(32767);
l_count number;
begin
for r in (
select owner, object_name from all_objects
where owner = 'SH' and object_type in ('TABLE','VIEW')
and instr(object_name,'$') = 0
)
loop
if true then
/* create synonym */
l_statement := 'create synonym ' || r.object_name || ' for ' || r.owner || '.' || r.object_name;
dbms_output.put_line(l_statement);
execute immediate l_statement;
-- verify synonym by counting rows
l_select := 'select count(*) into :cnt from ' || r.object_name;
execute immediate l_select into l_count;
dbms_output.put_line(l_select || ' / ' || l_count);
else
/* drop synonym */
l_statement := 'drop synonym ' || r.object_name;
dbms_output.put_line(l_statement);
execute immediate l_statement;
end if;
end loop;
end;sales_history % sql mcpuser/mcpuser@localhost/freepdb1
SQLcl: 木 8月 07 10:57:26 2025のリリース25.2 Production
Copyright (c) 1982, 2025, Oracle. All rights reserved.
接続先:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04
SQL> set serveroutput on
SQL> declare
2 l_statement varchar2(32767);
3 l_select varchar2(32767);
4 l_count number;
5 begin
6 for r in (
7 select owner, object_name from all_objects
8 where owner = 'SH' and object_type in ('TABLE','VIEW')
9 and instr(object_name,'$') = 0
10 )
11 loop
12 if true then
13 /* create synonym */
14 l_statement := 'create synonym ' || r.object_name || ' for ' || r.owner || '.' || r.object_name;
15 dbms_output.put_line(l_statement);
16 execute immediate l_statement;
17 -- verify synonym by counting rows
18 l_select := 'select count(*) into :cnt from ' || r.object_name;
19 execute immediate l_select into l_count;
20 dbms_output.put_line(l_select || ' / ' || l_count);
21 else
22 /* drop synonym */
23 l_statement := 'drop synonym ' || r.object_name;
24 dbms_output.put_line(l_statement);
25 execute immediate l_statement;
26 end if;
27 end loop;
28 end;
29* /
create synonym CAL_MONTH_SALES_MV for SH.CAL_MONTH_SALES_MV
select count(*) into :cnt from CAL_MONTH_SALES_MV / 48
create synonym CHANNELS for SH.CHANNELS
select count(*) into :cnt from CHANNELS / 5
create synonym COSTS for SH.COSTS
select count(*) into :cnt from COSTS / 82112
create synonym COUNTRIES for SH.COUNTRIES
select count(*) into :cnt from COUNTRIES / 35
create synonym CUSTOMERS for SH.CUSTOMERS
select count(*) into :cnt from CUSTOMERS / 55500
create synonym FWEEK_PSCAT_SALES_MV for SH.FWEEK_PSCAT_SALES_MV
select count(*) into :cnt from FWEEK_PSCAT_SALES_MV / 10512
create synonym PRODUCTS for SH.PRODUCTS
select count(*) into :cnt from PRODUCTS / 72
create synonym PROFITS for SH.PROFITS
select count(*) into :cnt from PROFITS / 916039
create synonym PROMOTIONS for SH.PROMOTIONS
select count(*) into :cnt from PROMOTIONS / 503
create synonym SALES for SH.SALES
select count(*) into :cnt from SALES / 918843
create synonym SUPPLEMENTARY_DEMOGRAPHICS for SH.SUPPLEMENTARY_DEMOGRAPHICS
select count(*) into :cnt from SUPPLEMENTARY_DEMOGRAPHICS / 4500
create synonym TIMES for SH.TIMES
select count(*) into :cnt from TIMES / 1826
PL/SQLプロシージャが正常に完了しました。
SQL> exit
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04から切断されました
sales_history %
set serveroutput on
set feedback off
spool metadata.json
declare
l_metadata json;
l_count pls_integer;
begin
l_count := 0;
dbms_output.put_line('[');
for r in (
select object_name from all_objects
where owner = 'SH' and object_type in ('TABLE','VIEW')
and instr(object_name,'$') = 0
)
loop
if l_count > 0 then
dbms_output.put_line(',');
end if;
l_metadata := dbms_developer.get_metadata(r.object_name,level => 'BASIC');
-- remove scheme from object info.
select json_transform(l_metadata, remove '$.objectInfo.schema') into l_metadata;
dbms_output.put_line(json_serialize(l_metadata pretty));
l_count := l_count + 1;
end loop;
dbms_output.put_line(']');
end;
/
spool offsales_history % sql sh/*******@localhost/freepdb1
SQLcl: 木 8月 07 12:01:42 2025のリリース25.2 Production
Copyright (c) 1982, 2025, Oracle. All rights reserved.
接続先:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04
SQL> set serveroutput on
SQL> set feedback off
SQL> spool metadata.json
SQL> declare
2 l_metadata json;
3 l_count pls_integer;
4 begin
5 l_count := 0;
6 dbms_output.put_line('[');
7 for r in (
8 select object_name from all_objects
9 where owner = 'SH' and object_type in ('TABLE','VIEW')
10 and instr(object_name,'$') = 0
11 )
12 loop
13 if l_count > 0 then
14 dbms_output.put_line(',');
15 end if;
16 l_metadata := dbms_developer.get_metadata(r.object_name,level => 'BASIC');
17 -- remove scheme from object info.
18 select json_transform(l_metadata, remove '$.objectInfo.schema') into l_metadata;
19 dbms_output.put_line(json_serialize(l_metadata pretty));
20 l_count := l_count + 1;
21 end loop;
22 dbms_output.put_line(']');
23 end;
24* /
[
{
"objectType" : "TABLE",
"objectInfo" :
{
"name" : "CAL_MONTH_SALES_MV",
"columns" :
[
{
"name" : "CALENDAR_MONTH_DESC",
"notNull" : true,
"dataType" :
{
"type" : "VARCHAR2",
"length" : 8,
"sizeUnits" : "BYTE"
}
},
{
"name" : "DOLLARS",
"notNull" : false,
"dataType" :
{
"type" : "NUMBER"
}
}
]
},
"etag" : "8C9210395BE477CBC7AA962A9F82E790"
}
,
{
"objectType" : "TABLE",
"objectInfo" :
{
"name" : "CHANNELS",
"columns" :
[
{
"name" : "CHANNEL_ID",
"notNull" : true,
"dataType" :
{
"type" : "NUMBER"
}
},
{
"name" : "CHANNEL_DESC",
"notNull" : true,
"dataType" :
[中略]
{
"name" : "END_OF_FIS_YEAR",
"notNull" : true,
"dataType" :
{
"type" : "DATE"
}
}
]
},
"etag" : "16A2DBCED85E5EF2F3A14DDDB492ABFD"
}
]
SQL> spool off
SQL> exit
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04から切断されました
sales_history %
mcpoとSQLclを組み込んだコンテナの実行
curl -OL https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip
コンテナ・イメージを作成します。
podman build -f Dockerfile -t mcpo:latest
% curl -OL https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 87.8M 100 87.8M 0 0 9800k 0 0:00:09 0:00:09 --:--:-- 9359k
% podman build -f Dockerfile -t mcpo:latest
STEP 1/12: FROM oraclelinux:9
STEP 2/12: RUN dnf update -y && dnf install -y sudo passwd unzip python3.12 python3.12-pip npm java-21-openjdk-headless && dnf clean all
--> Using cache 23e0b3aa2d45ddd2b3bf2e0181677e0a187370adc22398e8c9c97b735058ea83
--> 23e0b3aa2d45
STEP 3/12: ENV API_KEY=top-secret
--> 241ef719729a
STEP 4/12: RUN pip3.12 install --no-cache-dir mcpo
Collecting mcpo
Obtaining dependency information for mcpo from https://files.pythonhosted.org/packages/3b/b2/54aab11bfcb7cdd574d1796dcaecf8f93e4d92d150286ba0d2d6e68b9524/mcpo-0.0.17-py3-none-any.whl.metadata
Downloading mcpo-0.0.17-py3-none-any.whl.metadata (7.0 kB)
Collecting click>=8.1.8 (from mcpo)
Obtaining dependency information for click>=8.1.8 from https://files.pythonhosted.org/packages/85/32/10bb5764d90a8eee674e9dc6f4db6a0ab47c8c4d0d83c27f7c39ac415a4d/click-8.2.1-py3-none-any.whl.metadata
Downloading click-8.2.1-py3-none-any.whl.metadata (2.5 kB)
Collecting fastapi>=0.115.12 (from mcpo)
Obtaining dependency information for fastapi>=0.115.12 from https://files.pythonhosted.org/packages/e5/47/d63c60f59a59467fda0f93f46335c9d18526d7071f025cb5b89d5353ea42/fastapi-0.116.1-py3-none-any.whl.metadata
Downloading fastapi-0.116.1-py3-none-any.whl.metadata (28 kB)
Collecting mcp>=1.12.1 (from mcpo)
[中略]
--> ddbcfb283d6d
STEP 11/12: EXPOSE 8000
--> 02e68addb64b
STEP 12/12: CMD ["sh","-c","mcpo --port 8000 --api-key $API_KEY -- /home/oracle/sqlcl/bin/sql -mcp"]
COMMIT mcpo:latest
--> 6c4e198228ed
Successfully tagged localhost/mcpo:latest
6c4e198228eddbd7daf7c9e7426e57da09179adea3567944b3fb8a2465338cf9
%
コンテナ・イメージがlocalhost/mcpo:latestとして作成されます。
podman run -e API_KEY=my_secret -p 8091:8000 --name mcpo mcpo:latest
% podman run -e API_KEY=my_secret -p 8091:8000 --name mcpo mcpo:latest
2025-08-07 03:24:40,514 - INFO - Starting MCPO Server...
2025-08-07 03:24:40,514 - INFO - Name: MCP OpenAPI Proxy
2025-08-07 03:24:40,514 - INFO - Version: 1.0
2025-08-07 03:24:40,514 - INFO - Description: Automatically generated API from MCP Tool Schemas
2025-08-07 03:24:40,514 - INFO - Hostname: 928ffdcd4c04
2025-08-07 03:24:40,514 - INFO - Port: 8000
2025-08-07 03:24:40,514 - INFO - API Key: Provided
2025-08-07 03:24:40,514 - INFO - CORS Allowed Origins: ['*']
2025-08-07 03:24:40,514 - INFO - Path Prefix: /
2025-08-07 03:24:40,514 - INFO - Configuring for a single Stdio MCP Server with command: /home/oracle/sqlcl/bin/sql -mcp
2025-08-07 03:24:40,514 - INFO - Uvicorn server starting...
INFO: Started server process [1]
INFO: Waiting for application startup.
OpenJDK 64-Bit Server VM warning: Unable to get SVE vector length on this system. Disabling SVE. Specify -XX:UseSVE=0 to shun this warning.
---------- MCP SERVER STARTUP ----------
MCP Server started successfully on Thu Aug 07 03:24:40 UTC 2025
Press Ctrl+C to stop the server
----------------------------------------
Aug 07, 2025 3:24:41 AM io.modelcontextprotocol.server.McpAsyncServer$AsyncServerImpl lambda$asyncInitializeRequestHandler$5
INFO: Client initialize request - Protocol: 2025-06-18, Capabilities: ClientCapabilities[experimental=null, roots=null, sampling=null], Info: Implementation[name=mcp, version=0.1.0]
Aug 07, 2025 3:24:41 AM io.modelcontextprotocol.server.McpAsyncServer$AsyncServerImpl lambda$asyncInitializeRequestHandler$5
WARNING: Client requested unsupported protocol version: 2025-06-18, so the server will sugggest the 2024-11-05 version instead
INFO: Application startup complete.
INFO: Uvicorn running on http://0.0.0.0:8000 (Press CTRL+C to quit)
Starting MCP OpenAPI Proxy on 0.0.0.0:8000 with command: /home/oracle/sqlcl/bin/sql -mcp
INFO: 10.88.0.6:33308 - "GET /openapi.json HTTP/1.1" 200 OK
INFO: 10.88.0.6:33308 - "GET /favicon.ico HTTP/1.1" 404 Not Found
SQLclのMCPサーバーが提供しているツールの仕様が、OpenAPIのドキュメントとして表示されます。
% podman exec -it mcpo sh
sh-5.1$
./sqlcl/bin/sql /nolog
conn -save mydb -savepwd mcpuser/mcpuser@host.containers.internal/freepdb1
exit
exit
sh-5.1$ ./sqlcl/bin/sql /nolog
OpenJDK 64-Bit Server VM warning: Unable to get SVE vector length on this system. Disabling SVE. Specify -XX:UseSVE=0 to shun this warning.
SQLcl: Release 25.2 Production on Thu Aug 07 03:31:31 2025
Copyright (c) 1982, 2025, Oracle. All rights reserved.
/bin/bash: line 1: tput: command not found
/bin/bash: line 1: tput: command not found
SQL> conn -save mydb -savepwd mcpuser/mcpuser@host.containers.internal/freepdb1
Name: mydb
Connect String: host.containers.internal/freepdb1
User: mcpuser
Password: ******
Connected.
SQL> exit
Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04
sh-5.1$ exit
exit
%
Ollamaとgpt-oss:20bの実行
Open WebUIの実行
podman volume create open-webui
% podman volume create open-webui
open-webui
%
% podman run -d -p 3000:8080 -v open-webui:/app/backend/data -e OLLAMA_BASE_URL=http://host.containers.internal:11434 --name open-webui --restart always ghcr.io/open-webui/open-webui:latest
ff13fa1ee43b4b98be34e875900259f8f754cd4286e4f4e308ff428030cafd73
ynakakoshi@Ns-Macbook ~ %
以上でOpen WebUIが実行されました。私の環境ではポート8080が使用済みだったため、オプション-p 3000:8080を指定しホスト・ポートの3000番でOpen WebUIにアクセスするようにしています。
mcpoをツールとして追加
動作確認
