以下のTypeScriptのMCPサーバーの実装を、アタッチしているOracle Database上で動作するMCPサーバーのフレームワークに合わせて、Zodのスキーマ定義を表定義に、データの投入をINSERT文に、ファンクションはPL/SQLno
メッセージが途中で切れているようです。確認させてください：
@/Users/********/Documents/mcp-app @/Users/********/Documents/ext-apps/examples/cohort-heatmap-server/server.ts 1. ディレクトリmcp-appをアタッチしました。
mcp-appフレームワークの構造を理解するため探索します。
{
"description": "Explore mcp-app Oracle MCP framework",
"subagent_type": "Explore",
"prompt": "Explore the directory `/Users/********/Documents/mcp-app` thoroughly to understand how this Oracle Database MCP server framework works.\n\nI need to convert a TypeScript MCP server (which uses Zod schemas and in-memory data generation) into this framework. Specifically I need to understand:\n\n1. **Directory structure** — what's in `app/`, `examples/`, `src/`, `ords/`? Where do MCP servers live?\n2. **How an existing example MCP server is structured** — look at `examples/` and find a complete server example. Pay special attention to any \"budget-allocator-server\" example that was recently added (per recent commits).\n3. **How tools are defined** — How are tool schemas declared? Is there an output_schema definition pattern (per recent commit \"add output_schema definition\")? How are inputs/outputs typed?\n4. **Database setup** — How are tables created? Look at `install-tables.sql`, `install-sampleserver.sql`, `install-all*.sql`. What's the convention?\n5. **How PL/SQL functions are written and registered** — How does a tool call dispatch into PL/SQL? What's the calling convention (parameters, return types like JSON/CLOB/object types)?\n6. **How data insertion is done** — INSERT statement patterns, sample data files.\n7. **README.md content** — high-level architecture description.\n\nReport:\n- Tree structure of relevant directories (don't dump everything — just the parts relevant to defining a new MCP server)\n- File paths of: a complete example server (preferably budget-allocator), the install scripts that would need to be modified or added, any PL/SQL package skeleton\n- Code snippets showing: (a) how a tool is registered with input/output schema, (b) how a PL/SQL function returns structured data to the MCP layer, (c) how a CREATE TABLE statement is written for sample data\n- Any naming conventions (table names, schema names, package names)\n\nBe thorough — I need enough detail to actually write a new server end-to-end. ~500 words is fine."
}Excellent! Now I have a comprehensive understanding. Let me create a detailed report for the user. ## Comprehensive Exploration Report: Oracle Database MCP Server Framework I've thoroughly explored the `/Users/********/Documents/mcp-app` directory. Here's what you need to understand to convert a TypeScript MCP server into this Oracle Database framework: --- ### **1. Directory Structure** ``` mcp-app/ ├── src/ # Core MCP framework (shared across all servers) │ ├── packages/ # PL/SQL packages (OJ_MCP_*) │ │ ├── oj_mcp_jsonrpc_utils.* │ │ ├── oj_mcp_app_utils.* │ │ ├── oj_mcp_app_methods.* │ │ └── oj_mcp_app_server.* # Main MCP request dispatcher │ ├── procedures/ # Request handlers │ │ ├── oj_mcp_post_handler.pls │ │ ├── oj_mcp_delete_handler.pls │ ├── tools/ # Sample tool implementations │ │ ├── get_schema.pls │ │ ├── run_sql.pls │ │ ├── get_current_user.pls │ │ └── register_tools.sql # Tool registration (uses uc_ai_tools_api) │ ├── tables/ │ └── resources/ # UI resource definitions ├── examples/ # Complete server examples │ ├── budget-allocator-server/ # Full-featured example (preferred template) │ │ ├── bas_create_tables.sql │ │ ├── bas_get_budget_data_response.sql │ │ ├── bas_prepare_budget_*.sql (sample data) │ │ ├── output_schema.json # Output schema definition │ │ └── bas_install.sql │ └── basic-server/ # Simpler example ├── ords/ # ORDS REST module definitions │ └── ORDS_REST_APEXDEV_sampleserver.sql ├── install-tables.sql ├── install-packages.sql └── install-sampleserver.sql # Main installation template ``` **Key insight:** Each MCP server is self-contained but uses the shared framework in `src/`. You add your server's tables, tools, and data under its own namespace (e.g., `bas_` prefix for budget-allocator). --- ### **2. How to Define a Complete Server: Budget-Allocator Example** The **budget-allocator-server** (added in commit `8c8a0f6`) is the canonical reference. It demonstrates: - **9 data tables** modeling nested Zod schemas (categories, config, historical months, analytics benchmarks) - **1 response function** (`bas_get_budget_data_response`) that builds complex JSON from tables - **1 output schema** (`output_schema.json`) defining the MCP tool output - **Data initialization** via SQL INSERTs and stored procedures File paths: - `/Users/********/Documents/mcp-app/examples/budget-allocator-server/bas_create_tables.sql` (schema) - `/Users/********/Documents/mcp-app/examples/budget-allocator-server/bas_get_budget_data_response.sql` (response function) - `/Users/********/Documents/mcp-app/examples/budget-allocator-server/output_schema.json` (schema definition) --- ### **3. Tool Definition & Registration Pattern** Tools are registered via the UC_AI framework (United Codes UC_AI). The pattern: **Input schema** (JSON Schema 2020-...
budget-allocator-serverの実際の実装を詳しく見て、変換のパターンを把握します。
{
"command": "ls /Users/********/Documents/mcp-app/examples/budget-allocator-server/",
"description": "List budget-allocator-server files"
}bas_create_tables.sql bas_drop_all.sql bas_generate_history.sql bas_get_budget_data_response.sql bas_install.sql bas_prepare_budget_analytics.sql bas_prepare_budget_categories.sql bas_prepare_budget_config.sql output_schema.json
{
"file_path": "/Users/********/Documents/mcp-app/examples/budget-allocator-server/bas_install.sql"
}1 set serveroutput on 2 -- create tables 3 @@bas_create_tables.sql 4 -- procedure to generate sample data. 5 @@bas_generate_history.sql 6 -- function to generate the response for tools call. 7 @@bas_get_budget_data_response.sql 8 -- insert test data 9 @@bas_prepare_budget_categories.sql 10 @@bas_prepare_budget_config.sql 11 -- 12 begin 13 bas_generate_history; 14 end; 15 / 16 -- 17 @@bas_prepare_budget_analytics.sql 18 commit; 19 -- generate tools call response 20 begin 21 dbms_output.put_line(bas_get_budget_data_response); 22 end; 23 / 24 exit; 25
{
"file_path": "/Users/********/Documents/mcp-app/examples/budget-allocator-server/bas_create_tables.sql"
}1 -- ============================================================ 2 -- Budget Application Table Definitions for Oracle Database 3 -- ============================================================ 4 5 -- ------------------------------------------------------------ 6 -- 1. bas_budget_categories 7 -- BudgetCategorySchema + BudgetCategoryInternal 8 -- ------------------------------------------------------------ 9 create table bas_budget_categories ( 10 id varchar2(100) not null, 11 name varchar2(200) not null, 12 color varchar2(50) not null, 13 default_percent number(10, 4) not null, 14 trend_per_month number(10, 4), 15 constraint bas_pk_budget_categories primary key (id) 16 ); 17 18 -- ------------------------------------------------------------ 19 -- 2. bas_budget_config 20 -- BudgetConfigSchema (scalar fields) 21 -- ------------------------------------------------------------ 22 create table bas_budget_config ( 23 id number generated always as identity, 24 default_budget number(15, 4) not null, 25 currency varchar2(10) not null, 26 currency_symbol varchar2(10) not null, 27 constraint bas_pk_budget_config primary key (id) 28 ); 29 30 -- ------------------------------------------------------------ 31 -- 3. bas_budget_config_preset_budgets 32 -- BudgetConfigSchema.presetBudgets: z.array(z.number()) 33 -- ------------------------------------------------------------ 34 create table bas_budget_config_preset_budgets ( 35 id number generated always as identity, 36 config_id number not null, 37 sort_order number not null, 38 preset_value number(15, 4) not null, 39 constraint bas_pk_config_presets primary key (id), 40 constraint bas_fk_presets_config foreign key (config_id) 41 references bas_budget_config (id) on delete cascade 42 ); 43 44 -- ------------------------------------------------------------ 45 -- 4. bas_historical_months 46 -- HistoricalMonthSchema (parent) 47 -- ------------------------------------------------------------ 48 create table bas_historical_months ( 49 id number generated always as identity, 50 month varchar2(20) not null, 51 constraint bas_pk_historical_months primary key (id), 52 constraint bas_uq_historical_months unique (month) 53 ); 54 55 -- ------------------------------------------------------------ 56 -- 5. bas_historical_month_allocations 57 -- HistoricalMonthSchema.allocations: z.record(z.string(), z.number()) 58 -- ------------------------------------------------------------ 59 create table bas_historical_month_allocations ( 60 id number generated always as identity, 61 month_id number not null, 62 category_id varchar2(100) not null, 63 allocation number(15...
{
"file_path": "/Users/********/Documents/mcp-app/examples/budget-allocator-server/bas_get_budget_data_response.sql"
}1 create or replace function bas_get_budget_data_response
2 return clob
3 as
4 -- root
5 l_root json_object_t := json_object_t();
6
7 -- config
8 l_config json_object_t := json_object_t();
9 l_categories json_array_t := json_array_t();
10 l_category json_object_t;
11 l_presets json_array_t := json_array_t();
12
13 -- analytics
14 l_analytics json_object_t := json_object_t();
15 l_history json_array_t := json_array_t();
16 l_month_obj json_object_t;
17 l_allocations json_object_t;
18 l_benchmarks json_array_t := json_array_t();
19 l_benchmark_obj json_object_t;
20 l_cat_benchmarks json_object_t;
21 l_percentiles json_object_t;
22 l_stages json_array_t := json_array_t();
23
24 -- work
25 l_analytics_id bas_budget_analytics.id%type;
26 l_default_stage bas_budget_analytics.default_stage%type;
27 begin
28 -- ============================================================
29 -- config.categories
30 -- trendPerMonth は BudgetCategorySchema 外のため除外
31 -- ============================================================
32 for r in (
33 select id, name, color, default_percent
34 from bas_budget_categories
35 order by id
36 ) loop
37 l_category := json_object_t();
38 l_category.put('id', r.id);
39 l_category.put('name', r.name);
40 l_category.put('color', r.color);
41 l_category.put('defaultPercent', r.default_percent);
42 l_categories.append(l_category);
43 end loop;
44
45 -- ============================================================
46 -- config.presetBudgets
47 -- ============================================================
48 for r in (
49 select preset_value
50 from bas_budget_config_preset_budgets
51 where config_id = (select id from bas_budget_config fetch first 1 rows only)
52 order by sort_order
53 ) loop
54 l_presets.append(r.preset_value);
55 end loop;
56
57 -- ============================================================
58 -- config
59 -- ============================================================
60 for r in (
61 select default_budget, currency, currency_symbol
62 from bas_budget_config
63 fetch first 1 rows only
64 ) loop
65 l_config.put('categories', l_categories);
66 l_config.put('presetBudgets', l_presets);
67 l_config.put('defaultBudget', r.default_budget);
68 l_config.put('currency', r.currency);
69 l_config.put('currencySymbol', r.currency_symbol);
70 end loop;
71
72 -- ============================================================
73 -- analytics parent
74 -- ============================================================
75...{
"file_path": "/Users/********/Documents/mcp-app/examples/budget-allocator-server/output_schema.json"
}1 {
2 "$schema": "https://json-schema.org/draft/2020-12/schema",
3 "$id": "bas_budget_data_response.schema.json",
4 "title": "BudgetDataResponse",
5 "type": "object",
6 "required": ["config", "analytics"],
7 "additionalProperties": false,
8 "properties": {
9
10 "config": {
11 "title": "BudgetConfig",
12 "description": "Static configuration for the budget application.",
13 "type": "object",
14 "required": ["categories", "presetBudgets", "defaultBudget", "currency", "currencySymbol"],
15 "additionalProperties": false,
16 "properties": {
17
18 "categories": {
19 "description": "List of budget categories available in the application.",
20 "type": "array",
21 "items": {
22 "title": "BudgetCategory",
23 "description": "A single budget category definition.",
24 "type": "object",
25 "required": ["id", "name", "color", "defaultPercent"],
26 "additionalProperties": false,
27 "properties": {
28 "id": {
29 "description": "Unique identifier for the category.",
30 "type": "string"
31 },
32 "name": {
33 "description": "Human-readable display name of the category.",
34 "type": "string"
35 },
36 "color": {
37 "description": "Display color as a 6-digit hex code (e.g. #3b82f6).",
38 "type": "string",
39 "pattern": "^#[0-9a-fA-F]{6}$"
40 },
41 "defaultPercent": {
42 "description": "Default allocation percentage for this category (0–100).",
43 "type": "number",
44 "minimum": 0,
45 "maximum": 100
46 }
47 }
48 }
49 },
50
51 "presetBudgets": {
52 "description": "Preset total budget amounts available for quick selection.",
53 "type": "array",
54 "items": {
55 "description": "A preset budget amount. Must be greater than zero.",
56 "type": "number",
57 "exclusiveMinimum": 0
58 }
59 },
60
61 "defaultBudget": {
62 "description": "Default total budget amount applied on initial load. Must be greater than zero.",
63 "type": "number",
64 "exclusiveMinimum": 0
65 },
66 "currency": {
67 "description": "ISO 4217 currency code (e.g. USD).",
68 "type": "string",
69 "minLength": 3,
70 "maxLength": 3
71 },
72 "currencySymbol": {
73 "description": "Currency symbol used for display (e.g. $).",
74 "type": "string",
75 "minLength": 1
76 }
77 }
78 },
79
80 "analytics": {
81 "title": "BudgetAnalytics",
82 "description": "Historical and benchmark analyti...{
"file_path": "/Users/********/Documents/mcp-app/examples/budget-allocator-server/bas_prepare_budget_categories.sql"
}1 set define off
2
3 insert into bas_budget_categories (id, name, color, default_percent, trend_per_month)
4 values ('marketing', 'Marketing', '#3b82f6', 25, 0.15);
5
6 insert into bas_budget_categories (id, name, color, default_percent, trend_per_month)
7 values ('engineering', 'Engineering', '#10b981', 35, -0.1);
8
9 insert into bas_budget_categories (id, name, color, default_percent, trend_per_month)
10 values ('operations', 'Operations', '#f59e0b', 15, 0.05);
11
12 insert into bas_budget_categories (id, name, color, default_percent, trend_per_month)
13 values ('sales', 'Sales', '#ef4444', 15, 0.08);
14
15 insert into bas_budget_categories (id, name, color, default_percent, trend_per_month)
16 values ('rd', 'R&D', '#8b5cf6', 10, -0.18);
17
18 commit;
19
20 set define on{
"file_path": "/Users/********/Documents/mcp-app/examples/budget-allocator-server/bas_generate_history.sql"
}1 -- ============================================================ 2 -- bas_generate_history 3 -- Generate 24 months of historical allocation data 4 -- Port of TypeScript generateHistory() with seededRandom(42) 5 -- ============================================================ 6 create or replace procedure bas_generate_history 7 as 8 -- --------------------------------------------------------- 9 -- Seeded LCG: seed = (seed * 1103515245 + 12345) & 0x7fffffff 10 -- PL/SQL has no unsigned 32-bit, so use MOD 2^31 (= 2147483648) 11 -- to reproduce the JS bitwise & 0x7fffffff behaviour. 12 -- --------------------------------------------------------- 13 v_seed number := 42; 14 v_modulus constant number := 2147483648; -- 0x80000000 15 16 -- Category row 17 type t_cat is record ( 18 id bas_budget_categories.id%type, 19 default_percent bas_budget_categories.default_percent%type, 20 trend_per_month bas_budget_categories.trend_per_month%type 21 ); 22 type t_cat_tab is table of t_cat index by pls_integer; 23 v_cats t_cat_tab; 24 25 -- Raw allocation accumulator per month 26 type t_alloc is table of number index by varchar2(100); 27 v_raw_alloc t_alloc; 28 29 v_month_date date; 30 v_month_str varchar2(7); -- 'YYYY-MM' 31 v_month_id bas_historical_months.id%type; 32 v_months_from_start number; 33 v_trend number; 34 v_noise number; 35 v_raw number; 36 v_total number; 37 v_cat_id varchar2(100); 38 v_rnd number; -- value in [0, 1) 39 40 -- --------------------------------------------------------- 41 -- Inner function: advance seed and return value in [0, 1) 42 -- Matches JS: seed = (seed * 1103515245 + 12345) & 0x7fffffff 43 -- return seed / 0x7fffffff 44 -- --------------------------------------------------------- 45 function seeded_random return number 46 as 47 begin 48 v_seed := mod(v_seed * 1103515245 + 12345, v_modulus); 49 return v_seed / 2147483647; -- 0x7fffffff 50 end seeded_random; 51 52 begin 53 -- Load all categories once 54 select id, default_percent, nvl(trend_per_month, 0) 55 bulk collect into v_cats 56 from bas_budget_categories 57 order by id; 58 59 if v_cats.count = 0 then 60 raise_application_error(-20001, 'bas_budget_categories is empty'); 61 end if; 62 63 -- Month loop: i = 23 downto 0 (24 months ending this month) 64 for i in reverse 0..23 loop 65 66 v_months_from_start := 23 - i; 67 v_month_date := add_months(trunc(sysdate, 'MM'), -i); 68 v_month_str := to_char(v_month_date, 'YYYY-MM'); 69 70 -- Upsert into bas_historical_months 71 begin 72 insert into bas_historical_months (month) 73 values (v_month_str) 74 returning i...
Now I have a complete picture. Let me check tool registration for the output_schema handling.
{
"command": "ls /Users/********/Documents/mcp-app/examples/basic-server/ 2>/dev/null && echo \"---\" && ls /Users/********/Documents/mcp-app/examples/",
"description": "List example servers"
}register-basic-servers.sql --- basic-server budget-allocator-server
{
"file_path": "/Users/********/Documents/mcp-app/examples/basic-server/register-basic-servers.sql"
}1 declare
2 /* Tools */
3 type t_apps is table of varchar2(40);
4 l_apps t_apps := t_apps(
5 'get-time-preact',
6 'get-time-react',
7 'get-time-solid',
8 'get-time-svelte',
9 'get-time-vue'
10 );
11 l_app varchar2(40);
12 C_INPUT_SCHEMA constant json_object_t := json_object_t.parse('{"$schema":"http://json-schema.org/draft-07/schema#","type":"object"}');
13 C_OUTPUT_SCHEMA constant clob := q'~
14 {
15 "$schema": "http://json-schema.org/draft-07/schema#",
16 "type": "object",
17 "properties": {
18 "time": {
19 "type": "string"
20 }
21 }
22 }
23 ~';
24 l_tool_id uc_ai_tools.id%type;
25 /* UI resources */
26 l_resource_id number;
27 l_update_user varchar2(80);
28 l_update_time timestamp;
29 C_DEFAULT_HTML constant clob := q'~<!DOCTYPE html><html><body></body></html>~';
30 begin
31 l_update_user := sys_context('USERENV', 'CURRENT_USER');
32 l_update_time := current_timestamp;
33 for i in 1..l_apps.count
34 loop
35 l_app := l_apps(i);
36 /* delete and create tool */
37 delete from uc_ai_tools where code = l_app;
38 l_tool_id := uc_ai_tools_api.create_tool_from_schema(
39 p_tool_code => l_app,
40 p_description => 'Returns the current server time as an ISO 8601 string.',
41 p_function_call => q'~return get_time();~',
42 p_json_schema => C_INPUT_SCHEMA,
43 p_tags => apex_t_varchar2('ext-apps')
44 );
45 /* create default UI resource, text should be updated later */
46 merge into oj_mcp_ui_resources t
47 using (
48 select
49 l_app name,
50 'ui://' || l_app || '/mcp-app.html' uri,
51 'text/html;profile=mcp-app' mime_type,
52 'Returns the current server time as an ISO 8601 string.' description,
53 C_DEFAULT_HTML text
54 from dual
55 ) s
56 on (t.name = s.name)
57 when matched then
58 update set
59 t.uri = s.uri,
60 t.mime_type = s.mime_type,
61 t.description = s.description,
62 t.text = case when t.text is null then s.text else t.text end,
63 t.updated_by = l_update_user,
64 t.updated_at = l_update_time
65 when not matched then
66 insert (name, uri, mime_type, description, text, created_by, created_at, updated_by, updated_at)
67 values (s.name, s.uri, s.mime_type, s.description, s.text, l_update_user, l_update_time, l_update_user, l_update_time)
68 ;
69 /* register ui resource to the tool */
70 select id into l_tool_id from uc_ai_tools where code = l_app;
71 select id into l_resource_id from oj_mcp_ui_resources where n...理解しました。budget-allocator-server のパターンに完全に倣って、cohort-heatmap-server をOracle向けに変換します。
ディレクトリを作成し、各ファイルを書き出します。
{
"command": "mkdir -p /Users/********/Documents/mcp-app/examples/cohort-heatmap-server && ls /Users/********/Documents/mcp-app/examples/",
"description": "Create cohort-heatmap-server directory"
}basic-server budget-allocator-server cohort-heatmap-server
{
"file_path": "/Users/********/Documents/mcp-app/examples/cohort-heatmap-server/chs_create_tables.sql",
"content": "-- ============================================================\n-- Cohort Heatmap Application Table Definitions for Oracle Database\n-- Port of cohort-heatmap-server/server.ts (Zod schemas)\n-- ============================================================\n\n-- ------------------------------------------------------------\n-- 1. chs_metric_params\n-- Internal RetentionParams keyed by metric name\n-- (paramsMap in TypeScript: retention / revenue / active)\n-- ------------------------------------------------------------\ncreate table chs_metric_params (\n metric varchar2(20) not null,\n base_retention number(10, 4) not null,\n decay_rate number(10, 4) not null,\n floor_value number(10, 4) not null,\n noise number(10, 4) not null,\n constraint chs_pk_metric_params primary key (metric),\n constraint chs_ck_metric_name\n check (metric in ('retention', 'revenue', 'active'))\n);\n\n-- ------------------------------------------------------------\n-- 2. chs_dataset_meta\n-- CohortDataSchema scalar fields: metric, periodType, generatedAt\n-- ------------------------------------------------------------\ncreate table chs_dataset_meta (\n id number generated always as identity,\n metric varchar2(20) not null,\n period_type varchar2(20) not null,\n generated_at timestamp default systimestamp not null,\n constraint chs_pk_dataset_meta primary key (id),\n constraint chs_fk_meta_metric foreign key (metric)\n references chs_metric_params (metric),\n constraint chs_ck_period_type\n check (period_type in ('monthly', 'weekly'))\n);\n\n-- ------------------------------------------------------------\n-- 3. chs_periods\n-- CohortDataSchema.periods[] / CohortDataSchema.periodLabels[]\n-- ------------------------------------------------------------\ncreate table chs_periods (\n period_index number not null,\n period_code varchar2(10) not null,\n period_label varchar2(50) not null,\n constraint chs_pk_periods primary key (period_index)\n);\n\n-- ------------------------------------------------------------\n-- 4. chs_cohorts\n-- CohortRowSchema (parent): cohortId, cohortLabel, originalUsers\n-- ------------------------------------------------------------\ncreate table chs_cohorts (\n cohort_index number not null,\n cohort_id varchar2(20) not null,\n cohort_label varchar2(50) not null,\n original_users number not null,\n constraint chs_pk_cohorts primary key (cohort_index)\n);\n\n-- ------------------------------------------------------------\n-- 5. chs_cohort_cells\n-- CohortRowSchema.cells[] = CohortCellSchema\n-- ------------------------------------------------------------\ncreate table chs_cohort_cells (\n cohort_index number not null,\n period_index number not null,\n retention number(10, 6) not null,\n users_retained number not null,\n users_original number not null,\n constraint chs_pk_cohort_cells primary key (cohort_index, period_index),\n constraint chs_fk_cells_cohort foreign key (cohort_index)\n references chs_cohorts (cohort_index) on delete cascade\n);\n\n-- ============================================================\n-- Comments\n-- ============================================================\ncomment on table chs_metric_params is 'RetentionParams per metric (paramsMap)';\ncomment on table chs_dataset_meta is 'CohortDataSchema scalar fields';\ncomment on table chs_periods is 'CohortDataSchema.periods[] / periodLabels[]';\ncomment on table chs_cohorts is 'CohortRowSchema parent';\ncomment on table chs_cohort_cells is 'CohortRowSchema.cells[] = CohortCellSchema';\n"
}File created successfully at: /Users/********/Documents/mcp-app/examples/cohort-heatmap-server/chs_create_tables.sql
{
"file_path": "/Users/********/Documents/mcp-app/examples/cohort-heatmap-server/chs_prepare_metric_params.sql",
"content": "set define off\n\n-- Mirror of TypeScript paramsMap in generateCohortData()\ninsert into chs_metric_params (metric, base_retention, decay_rate, floor_value, noise)\nvalues ('retention', 0.75, 0.12, 0.08, 0.04);\n\ninsert into chs_metric_params (metric, base_retention, decay_rate, floor_value, noise)\nvalues ('revenue', 0.70, 0.10, 0.15, 0.06);\n\ninsert into chs_metric_params (metric, base_retention, decay_rate, floor_value, noise)\nvalues ('active', 0.60, 0.18, 0.05, 0.05);\n\ncommit;\n\nset define on\n"
}File created successfully at: /Users/********/Documents/mcp-app/examples/cohort-heatmap-server/chs_prepare_metric_params.sql
{
"file_path": "/Users/********/Documents/mcp-app/examples/cohort-heatmap-server/chs_generate_cohort_data.sql",
"content": "-- ============================================================\n-- chs_generate_cohort_data\n-- Port of TypeScript generateCohortData() / generateRetention()\n-- Uses seeded LCG (seed = 42) for deterministic output, matching\n-- the bas_generate_history convention.\n-- ============================================================\ncreate or replace procedure chs_generate_cohort_data(\n p_metric in varchar2 default 'retention',\n p_period_type in varchar2 default 'monthly',\n p_cohort_count in number default 12,\n p_max_periods in number default 12\n)\nas\n -- ---------------------------------------------------------\n -- Seeded LCG (matches bas_generate_history)\n -- seed = (seed * 1103515245 + 12345) mod 2^31\n -- ---------------------------------------------------------\n v_seed number := 42;\n v_modulus constant number := 2147483648; -- 0x80000000\n\n -- RetentionParams loaded from chs_metric_params\n v_base_ret chs_metric_params.base_retention%type;\n v_decay chs_metric_params.decay_rate%type;\n v_floor chs_metric_params.floor_value%type;\n v_noise_p chs_metric_params.noise%type;\n\n -- Loop locals\n v_cohort_date date;\n v_cohort_id varchar2(20);\n v_cohort_label varchar2(50);\n v_original number;\n v_periods_avail number;\n v_p_max number;\n v_retention number;\n v_prev_ret number;\n v_base number;\n v_variation number;\n v_rnd number;\n\n -- ---------------------------------------------------------\n -- Inner: advance seed and return value in [0, 1)\n -- ---------------------------------------------------------\n function seeded_random return number\n as\n begin\n v_seed := mod(v_seed * 1103515245 + 12345, v_modulus);\n return v_seed / 2147483647; -- 0x7fffffff\n end seeded_random;\n\nbegin\n -- Load metric params (raises NO_DATA_FOUND if metric is unknown)\n select base_retention, decay_rate, floor_value, noise\n into v_base_ret, v_decay, v_floor, v_noise_p\n from chs_metric_params\n where metric = p_metric;\n\n -- Idempotent reset (cells deleted via FK cascade)\n delete from chs_cohorts;\n delete from chs_periods;\n delete from chs_dataset_meta;\n\n -- Insert dataset meta\n insert into chs_dataset_meta (metric, period_type)\n values (p_metric, p_period_type);\n\n -- ============================================================\n -- Period headers: M0..M(maxPeriods-1) / \"Month 0\"..\"Month N\"\n -- ============================================================\n for p in 0 .. p_max_periods - 1 loop\n insert into chs_periods (period_index, period_code, period_label)\n values (\n p,\n 'M' || p,\n case when p = 0 then 'Month 0' else 'Month ' || p end\n );\n end loop;\n\n -- ============================================================\n -- Cohorts (oldest first)\n -- ============================================================\n for c in 0 .. p_cohort_count - 1 loop\n\n v_cohort_date := add_months(trunc(sysdate, 'MM'), -(p_cohort_count - 1 - c));\n v_cohort_id := to_char(v_cohort_date, 'YYYY-MM');\n v_cohort_label := to_char(\n v_cohort_date,\n 'Mon YYYY',\n 'NLS_DATE_LANGUAGE=AMERICAN'\n );\n\n -- originalUsers = floor(1000 + random * 4000)\n v_rnd := seeded_random();\n v_original := floor(1000 + v_rnd * 4000);\n\n insert into chs_cohorts (cohort_index, cohort_id, cohort_label, original_users)\n values (c, v_cohort_id, v_cohort_label, v_original);\n\n -- Newer cohorts have fewer periods\n v_periods_avail := p_cohort_count - c;\n v_p_max := least(v_periods_avail, p_max_periods);\n v_prev_ret := 1.0;\n\n for p in 0 .. v_p_max - 1 loop\n -- generateRetention(): period 0 always returns 1.0\n if p = 0 then\n v_retention := 1.0;\n else\n v_rnd := seeded_random();\n v_base := v_base_ret * exp(-v_decay * (p - 1)) + v_floor;\n v_variation := (v_rnd - 0.5) * 2 * v_noise_p;\n v_retention := greatest(0, least(1, v_base + v_variation));\n end if;\n\n -- Retention must not jump above previous + 0.02\n v_retention := least(v_retention, v_prev_ret + 0.02);\n v_prev_ret := v_retention;\n\n insert into chs_cohort_cells\n (cohort_index, period_index, retention, users_retained, users_original)\n values (\n c,\n p,\n v_retention,\n round(v_original * v_retention),\n v_original\n );\n end loop;\n\n end loop;\n\n commit;\nend chs_generate_cohort_data;\n/\n"
}File created successfully at: /Users/********/Documents/mcp-app/examples/cohort-heatmap-server/chs_generate_cohort_data.sql
{
"file_path": "/Users/********/Documents/mcp-app/examples/cohort-heatmap-server/chs_get_cohort_data_response.sql",
"content": "create or replace function chs_get_cohort_data_response\n return clob\nas\n -- root\n l_root json_object_t := json_object_t();\n\n -- cohorts[]\n l_cohorts json_array_t := json_array_t();\n l_cohort_obj json_object_t;\n l_cells json_array_t;\n l_cell json_object_t;\n\n -- periods[] / periodLabels[]\n l_periods json_array_t := json_array_t();\n l_period_labels json_array_t := json_array_t();\n\n -- meta\n l_metric chs_dataset_meta.metric%type;\n l_period_type chs_dataset_meta.period_type%type;\n l_generated_at chs_dataset_meta.generated_at%type;\nbegin\n -- ============================================================\n -- meta: metric / periodType / generatedAt\n -- ============================================================\n select metric, period_type, generated_at\n into l_metric, l_period_type, l_generated_at\n from chs_dataset_meta\n fetch first 1 rows only;\n\n -- ============================================================\n -- periods[] and periodLabels[]\n -- ============================================================\n for r in (\n select period_code, period_label\n from chs_periods\n order by period_index\n ) loop\n l_periods.append(r.period_code);\n l_period_labels.append(r.period_label);\n end loop;\n\n -- ============================================================\n -- cohorts[] (with nested cells[])\n -- ============================================================\n for ch in (\n select cohort_index, cohort_id, cohort_label, original_users\n from chs_cohorts\n order by cohort_index\n ) loop\n l_cohort_obj := json_object_t();\n l_cells := json_array_t();\n\n for cell in (\n select cohort_index, period_index, retention,\n users_retained, users_original\n from chs_cohort_cells\n where cohort_index = ch.cohort_index\n order by period_index\n ) loop\n l_cell := json_object_t();\n l_cell.put('cohortIndex', cell.cohort_index);\n l_cell.put('periodIndex', cell.period_index);\n l_cell.put('retention', cell.retention);\n l_cell.put('usersRetained', cell.users_retained);\n l_cell.put('usersOriginal', cell.users_original);\n l_cells.append(l_cell);\n end loop;\n\n l_cohort_obj.put('cohortId', ch.cohort_id);\n l_cohort_obj.put('cohortLabel', ch.cohort_label);\n l_cohort_obj.put('originalUsers', ch.original_users);\n l_cohort_obj.put('cells', l_cells);\n l_cohorts.append(l_cohort_obj);\n end loop;\n\n -- ============================================================\n -- CohortDataSchema (root)\n -- ============================================================\n l_root.put('cohorts', l_cohorts);\n l_root.put('periods', l_periods);\n l_root.put('periodLabels', l_period_labels);\n l_root.put('metric', l_metric);\n l_root.put('periodType', l_period_type);\n l_root.put(\n 'generatedAt',\n to_char(\n l_generated_at at time zone 'UTC',\n 'YYYY-MM-DD\"T\"HH24:MI:SS.FF3\"Z\"'\n )\n );\n\n return l_root.to_clob();\nend chs_get_cohort_data_response;\n/\n"
}File created successfully at: /Users/********/Documents/mcp-app/examples/cohort-heatmap-server/chs_get_cohort_data_response.sql
{
"file_path": "/Users/********/Documents/mcp-app/examples/cohort-heatmap-server/output_schema.json",
"content": "{\n \"$schema\": \"https://json-schema.org/draft/2020-12/schema\",\n \"$id\": \"chs_cohort_data_response.schema.json\",\n \"title\": \"CohortData\",\n \"description\": \"Cohort retention heatmap dataset.\",\n \"type\": \"object\",\n \"required\": [\"cohorts\", \"periods\", \"periodLabels\", \"metric\", \"periodType\", \"generatedAt\"],\n \"additionalProperties\": false,\n \"properties\": {\n\n \"cohorts\": {\n \"description\": \"Per-cohort retention rows ordered oldest first.\",\n \"type\": \"array\",\n \"items\": {\n \"title\": \"CohortRow\",\n \"description\": \"A single cohort row with its retention cells.\",\n \"type\": \"object\",\n \"required\": [\"cohortId\", \"cohortLabel\", \"originalUsers\", \"cells\"],\n \"additionalProperties\": false,\n \"properties\": {\n \"cohortId\": {\n \"description\": \"Cohort identifier in YYYY-MM format.\",\n \"type\": \"string\",\n \"pattern\": \"^[0-9]{4}-(0[1-9]|1[0-2])$\"\n },\n \"cohortLabel\": {\n \"description\": \"Human-readable cohort label (e.g. 'Jan 2026').\",\n \"type\": \"string\"\n },\n \"originalUsers\": {\n \"description\": \"Number of users in the cohort at period 0.\",\n \"type\": \"number\",\n \"minimum\": 0\n },\n \"cells\": {\n \"description\": \"Retention cells for this cohort, ordered by periodIndex ascending.\",\n \"type\": \"array\",\n \"items\": {\n \"title\": \"CohortCell\",\n \"description\": \"Retention measurement for one cohort/period intersection.\",\n \"type\": \"object\",\n \"required\": [\n \"cohortIndex\",\n \"periodIndex\",\n \"retention\",\n \"usersRetained\",\n \"usersOriginal\"\n ],\n \"additionalProperties\": false,\n \"properties\": {\n \"cohortIndex\": {\n \"description\": \"Zero-based row index of the cohort.\",\n \"type\": \"number\",\n \"minimum\": 0\n },\n \"periodIndex\": {\n \"description\": \"Zero-based column index of the period (0 = signup period).\",\n \"type\": \"number\",\n \"minimum\": 0\n },\n \"retention\": {\n \"description\": \"Retention rate in [0, 1].\",\n \"type\": \"number\",\n \"minimum\": 0,\n \"maximum\": 1\n },\n \"usersRetained\": {\n \"description\": \"Number of users still active in this period.\",\n \"type\": \"number\",\n \"minimum\": 0\n },\n \"usersOriginal\": {\n \"description\": \"Original cohort size (constant across cells of the same cohort).\",\n \"type\": \"number\",\n \"minimum\": 0\n }\n }\n }\n }\n }\n }\n },\n\n \"periods\": {\n \"description\": \"Period column codes (e.g. 'M0', 'M1', ...).\",\n \"type\": \"array\",\n \"items\": {\n \"type\": \"string\"\n }\n },\n\n \"periodLabels\": {\n \"description\": \"Human-readable period labels (e.g. 'Month 0', 'Month 1', ...).\",\n \"type\": \"array\",\n \"items\": {\n \"type\": \"string\"\n }\n },\n\n \"metric\": {\n \"description\": \"Retention metric this dataset was generated for.\",\n \"type\": \"string\",\n \"enum\": [\"retention\", \"revenue\", \"active\"]\n },\n\n \"periodType\": {\n \"description\": \"Period granularity used to build the cohorts.\",\n \"type\": \"string\",\n \"enum\": [\"monthly\", \"weekly\"]\n },\n\n \"generatedAt\": {\n \"description\": \"ISO 8601 UTC timestamp when the dataset was generated.\",\n \"type\": \"string\",\n \"format\": \"date-time\"\n }\n }\n}\n"
}File created successfully at: /Users/********/Documents/mcp-app/examples/cohort-heatmap-server/output_schema.json
{
"file_path": "/Users/********/Documents/mcp-app/examples/cohort-heatmap-server/chs_install.sql",
"content": "set serveroutput on\n-- create tables\n@@chs_create_tables.sql\n-- procedure to generate sample data.\n@@chs_generate_cohort_data.sql\n-- function to generate the response for tools call.\n@@chs_get_cohort_data_response.sql\n-- insert configuration data (RetentionParams per metric)\n@@chs_prepare_metric_params.sql\n--\nbegin\n chs_generate_cohort_data;\nend;\n/\ncommit;\n-- generate tools call response\nbegin\n dbms_output.put_line(chs_get_cohort_data_response);\nend;\n/\nexit;\n"
}File created successfully at: /Users/********/Documents/mcp-app/examples/cohort-heatmap-server/chs_install.sql
{
"file_path": "/Users/********/Documents/mcp-app/examples/cohort-heatmap-server/chs_drop_all.sql",
"content": "-- ============================================================\n-- Drop all cohort-heatmap-server objects\n-- ============================================================\ndrop function chs_get_cohort_data_response;\ndrop procedure chs_generate_cohort_data;\n\ndrop table chs_cohort_cells cascade constraints purge;\ndrop table chs_cohorts cascade constraints purge;\ndrop table chs_periods cascade constraints purge;\ndrop table chs_dataset_meta cascade constraints purge;\ndrop table chs_metric_params cascade constraints purge;\nexit;\n"
}File created successfully at: /Users/********/Documents/mcp-app/examples/cohort-heatmap-server/chs_drop_all.sql
{
"command": "ls -la /Users/********/Documents/mcp-app/examples/cohort-heatmap-server/",
"description": "Verify created files"
}total 64 drwxr-xr-x@ 9 ******** staff 288 Apr 17 10:15 . drwxr-xr-x 5 ******** staff 160 Apr 17 10:13 .. -rw-r--r--@ 1 ******** staff 3815 Apr 17 10:14 chs_create_tables.sql -rw-r--r--@ 1 ******** staff 547 Apr 17 10:15 chs_drop_all.sql -rw-r--r--@ 1 ******** staff 4943 Apr 17 10:14 chs_generate_cohort_data.sql -rw-r--r--@ 1 ******** staff 3365 Apr 17 10:15 chs_get_cohort_data_response.sql -rw-r--r--@ 1 ******** staff 469 Apr 17 10:15 chs_install.sql -rw-r--r--@ 1 ******** staff 499 Apr 17 10:14 chs_prepare_metric_params.sql -rw-r--r--@ 1 ******** staff 3813 Apr 17 10:15 output_schema.json
budget-allocator-server のパターンに合わせて、cohort-heatmap-server のOracle版を作成しました（プレフィックスは chs_）。
| TypeScript (server.ts) | Oracle (chs_*) |
|---|---|
| paramsMap (RetentionParams) | テーブル [chs_metric_params](examples/cohort-heatmap-server/chs_create_tables.sql) |
| CohortDataSchema のスカラ (metric/periodType/generatedAt) | テーブル chs_dataset_meta |
| periods[] / periodLabels[] | テーブル chs_periods |
| CohortRowSchema (親) | テーブル chs_cohorts |
| CohortRowSchema.cells[] (CohortCellSchema) | テーブル chs_cohort_cells (FK cascade) |
| generateCohortData() / generateRetention() | プロシージャ [chs_generate_cohort_data](examples/cohort-heatmap-server/chs_generate_cohort_data.sql) (シード付きLCG) |
| ツールハンドラの戻り (structuredContent) | ファンクション [chs_get_cohort_data_response](examples/cohort-heatmap-server/chs_get_cohort_data_response.sql) → CLOB |
| CohortDataSchema (Zod) | [output_schema.json](examples/cohort-heatmap-server/output_schema.json) (JSON Schema 2020-12) |
server.tsの以下の定義をJSON Schemaの定義に変換して。
// Schemas - types are derived from these using z.infer
const GetCohortDataInputSchema = z.object({
metric: z
.enum(["retention", "revenue", "active"])
.optional()
.default("retention"),
periodType: z.enum(["monthly", "weekly"]).optional().default("monthly"),
cohortCount: z.number().min(3).max(24).optional().default(12),
maxPeriods: z.number().min(3).max(24).optional().default(12),
});
GetCohortDataInputSchema をJSON Schema (draft-07、basic-server の入力スキーマ形式に準拠) に変換しました。output_schema.json と並べて保存します。{
"file_path": "/Users/********/Documents/mcp-app/examples/cohort-heatmap-server/input_schema.json",
"content": "{\n \"$schema\": \"http://json-schema.org/draft-07/schema#\",\n \"$id\": \"chs_get_cohort_data_input.schema.json\",\n \"title\": \"GetCohortDataInput\",\n \"description\": \"Input parameters for the get-cohort-data tool.\",\n \"type\": \"object\",\n \"additionalProperties\": false,\n \"properties\": {\n \"metric\": {\n \"description\": \"Retention metric to compute.\",\n \"type\": \"string\",\n \"enum\": [\"retention\", \"revenue\", \"active\"],\n \"default\": \"retention\"\n },\n \"periodType\": {\n \"description\": \"Period granularity used to build the cohorts.\",\n \"type\": \"string\",\n \"enum\": [\"monthly\", \"weekly\"],\n \"default\": \"monthly\"\n },\n \"cohortCount\": {\n \"description\": \"Number of cohorts to generate.\",\n \"type\": \"number\",\n \"minimum\": 3,\n \"maximum\": 24,\n \"default\": 12\n },\n \"maxPeriods\": {\n \"description\": \"Maximum number of periods (columns) per cohort.\",\n \"type\": \"number\",\n \"minimum\": 3,\n \"maximum\": 24,\n \"default\": 12\n }\n }\n}\n"
}File created successfully at: /Users/********/Documents/mcp-app/examples/cohort-heatmap-server/input_schema.json
保存しました: [input_schema.json](examples/cohort-heatmap-server/input_schema.json)
| Zod | JSON Schema |
|---|---|
| z.enum([...]) | "type": "string" + "enum": [...] |
| .optional().default(x) | required から外し "default": x を付与 |
| z.number().min(a).max(b) | "type": "number" + "minimum": a + "maximum": b |
| z.object({...}) (strict) | "type": "object" + "additionalProperties": false |
全フィールドが .optional().default(...) のため required 配列はありません。
PL/SQLファンクションchs_get_cohort_data_responseは、CLOB型の引数p_parametersを持ち、input_schema.jsonで定義されるJSONドキュメントを受け付けるように改変して。
chs_get_cohort_data_response を p_parameters CLOB を受け取るように改変します。入力JSONをパースし、デフォルト値を適用し、検証してから chs_generate_cohort_data を呼んで再生成、その後JSONを返します。{
"file_path": "/Users/********/Documents/mcp-app/examples/cohort-heatmap-server/chs_get_cohort_data_response.sql",
"content": "create or replace function chs_get_cohort_data_response(\n p_parameters in clob default null\n) return clob\nas\n -- ---------------------------------------------------------\n -- Parsed inputs (defaults mirror input_schema.json)\n -- ---------------------------------------------------------\n l_params json_object_t;\n l_metric varchar2(20) := 'retention';\n l_period_type varchar2(20) := 'monthly';\n l_cohort_count number := 12;\n l_max_periods number := 12;\n\n -- root\n l_root json_object_t := json_object_t();\n\n -- cohorts[]\n l_cohorts json_array_t := json_array_t();\n l_cohort_obj json_object_t;\n l_cells json_array_t;\n l_cell json_object_t;\n\n -- periods[] / periodLabels[]\n l_periods json_array_t := json_array_t();\n l_period_labels json_array_t := json_array_t();\n\n -- meta\n l_meta_metric chs_dataset_meta.metric%type;\n l_meta_period chs_dataset_meta.period_type%type;\n l_generated_at chs_dataset_meta.generated_at%type;\nbegin\n -- ============================================================\n -- Parse p_parameters per input_schema.json\n -- (GetCohortDataInput: metric / periodType / cohortCount / maxPeriods)\n -- All fields are optional; missing fields fall back to defaults.\n -- ============================================================\n if p_parameters is not null and dbms_lob.getlength(p_parameters) > 0 then\n l_params := json_object_t.parse(p_parameters);\n\n if l_params.has('metric') then\n l_metric := l_params.get_string('metric');\n end if;\n if l_params.has('periodType') then\n l_period_type := l_params.get_string('periodType');\n end if;\n if l_params.has('cohortCount') then\n l_cohort_count := l_params.get_number('cohortCount');\n end if;\n if l_params.has('maxPeriods') then\n l_max_periods := l_params.get_number('maxPeriods');\n end if;\n end if;\n\n -- ============================================================\n -- Validate against input_schema constraints\n -- ============================================================\n if l_metric not in ('retention', 'revenue', 'active') then\n raise_application_error(-20002,\n 'metric must be one of: retention, revenue, active');\n end if;\n if l_period_type not in ('monthly', 'weekly') then\n raise_application_error(-20003,\n 'periodType must be one of: monthly, weekly');\n end if;\n if l_cohort_count < 3 or l_cohort_count > 24 then\n raise_application_error(-20004,\n 'cohortCount must be between 3 and 24');\n end if;\n if l_max_periods < 3 or l_max_periods > 24 then\n raise_application_error(-20005,\n 'maxPeriods must be between 3 and 24');\n end if;\n\n -- ============================================================\n -- Re-generate the dataset with the requested parameters.\n -- chs_generate_cohort_data commits internally.\n -- ============================================================\n chs_generate_cohort_data(\n p_metric => l_metric,\n p_period_type => l_period_type,\n p_cohort_count => l_cohort_count,\n p_max_periods => l_max_periods\n );\n\n -- ============================================================\n -- meta: metric / periodType / generatedAt\n -- ============================================================\n select metric, period_type, generated_at\n into l_meta_metric, l_meta_period, l_generated_at\n from chs_dataset_meta\n fetch first 1 rows only;\n\n -- ============================================================\n -- periods[] and periodLabels[]\n -- ============================================================\n for r in (\n select period_code, period_label\n from chs_periods\n order by period_index\n ) loop\n l_periods.append(r.period_code);\n l_period_labels.append(r.period_label);\n end loop;\n\n -- ============================================================\n -- cohorts[] (with nested cells[])\n -- ============================================================\n for ch in (\n select cohort_index, cohort_id, cohort_label, original_users\n from chs_cohorts\n order by cohort_index\n ) loop\n l_cohort_obj := json_object_t();\n l_cells := json_array_t();\n\n for cell in (\n select cohort_index, period_index, retention,\n users_retained, users_original\n from chs_cohort_cells\n where cohort_index = ch.cohort_index\n order by period_index\n ) loop\n l_cell := json_object_t();\n l_cell.put('cohortIndex', cell.cohort_index);\n l_cell.put('periodIndex', cell.period_index);\n l_cell.put('retention', cell.retention);\n l_cell.put('usersRetained', cell.users_retained);\n l_cell.put('usersOriginal', cell.users_original);\n l_cells.append(l_cell);\n end loop;\n\n l_cohort_obj.put('cohortId', ch.cohort_id);\n l_cohort_obj.put('cohortLabel', ch.cohort_label);\n l_cohort_obj.put('originalUsers', ch.original_users);\n l_cohort_obj.put('cells', l_cells);\n l_cohorts.append(l_cohort_obj);\n end loop;\n\n -- ============================================================\n -- CohortDataSchema (root)\n -- ============================================================\n l_root.put('cohorts', l_cohorts);\n l_root.put('periods', l_periods);\n l_root.put('periodLabels', l_period_labels);\n l_root.put('metric', l_meta_metric);\n l_root.put('periodType', l_meta_period);\n l_root.put(\n 'generatedAt',\n to_char(\n l_generated_at at time zone 'UTC',\n 'YYYY-MM-DD\"T\"HH24:MI:SS.FF3\"Z\"'\n )\n );\n\n return l_root.to_clob();\nend chs_get_cohort_data_response;\n/\n"
}The file /Users/********/Documents/mcp-app/examples/cohort-heatmap-server/chs_get_cohort_data_response.sql has been updated successfully.
[chs_get_cohort_data_response.sql](examples/cohort-heatmap-server/chs_get_cohort_data_response.sql) を改変しました。
chs_get_cohort_data_responseの現在の出力をstrucuredContentの値とし、typeがtextのtextとしてformatCohortSummaryもPL/SQLのコードで実装し、chs_get_cohort_data_responseの出力が以下の形式と一致するようにしてください。
return {
content: [{ type: "text", text: formatCohortSummary(data) }],
structuredContent: data,
};
chs_get_cohort_data_response を { content: [...], structuredContent: data } 形式の出力にし、formatCohortSummary を PL/SQL で実装しました。{
"file_path": "/Users/********/Documents/mcp-app/examples/cohort-heatmap-server/chs_get_cohort_data_response.sql",
"content": "create or replace function chs_get_cohort_data_response(\n p_parameters in clob default null\n) return clob\nas\n -- ---------------------------------------------------------\n -- Parsed inputs (defaults mirror input_schema.json)\n -- ---------------------------------------------------------\n l_params json_object_t;\n l_metric varchar2(20) := 'retention';\n l_period_type varchar2(20) := 'monthly';\n l_cohort_count number := 12;\n l_max_periods number := 12;\n\n -- root wrapper: { content: [...], structuredContent: data }\n l_root json_object_t := json_object_t();\n l_content json_array_t := json_array_t();\n l_content_item json_object_t;\n\n -- structuredContent = CohortData\n l_data json_object_t := json_object_t();\n\n -- cohorts[]\n l_cohorts json_array_t := json_array_t();\n l_cohort_obj json_object_t;\n l_cells json_array_t;\n l_cell json_object_t;\n\n -- periods[] / periodLabels[]\n l_periods json_array_t := json_array_t();\n l_period_labels json_array_t := json_array_t();\n\n -- meta\n l_meta_metric chs_dataset_meta.metric%type;\n l_meta_period chs_dataset_meta.period_type%type;\n l_generated_at chs_dataset_meta.generated_at%type;\n\n -- formatCohortSummary() locals\n l_n_cohorts number;\n l_n_periods number;\n l_avg_retention number;\n l_pct_str varchar2(50);\n l_summary_text varchar2(4000);\nbegin\n -- ============================================================\n -- Parse p_parameters per input_schema.json\n -- (GetCohortDataInput: metric / periodType / cohortCount / maxPeriods)\n -- All fields are optional; missing fields fall back to defaults.\n -- ============================================================\n if p_parameters is not null and dbms_lob.getlength(p_parameters) > 0 then\n l_params := json_object_t.parse(p_parameters);\n\n if l_params.has('metric') then\n l_metric := l_params.get_string('metric');\n end if;\n if l_params.has('periodType') then\n l_period_type := l_params.get_string('periodType');\n end if;\n if l_params.has('cohortCount') then\n l_cohort_count := l_params.get_number('cohortCount');\n end if;\n if l_params.has('maxPeriods') then\n l_max_periods := l_params.get_number('maxPeriods');\n end if;\n end if;\n\n -- ============================================================\n -- Validate against input_schema constraints\n -- ============================================================\n if l_metric not in ('retention', 'revenue', 'active') then\n raise_application_error(-20002,\n 'metric must be one of: retention, revenue, active');\n end if;\n if l_period_type not in ('monthly', 'weekly') then\n raise_application_error(-20003,\n 'periodType must be one of: monthly, weekly');\n end if;\n if l_cohort_count < 3 or l_cohort_count > 24 then\n raise_application_error(-20004,\n 'cohortCount must be between 3 and 24');\n end if;\n if l_max_periods < 3 or l_max_periods > 24 then\n raise_application_error(-20005,\n 'maxPeriods must be between 3 and 24');\n end if;\n\n -- ============================================================\n -- Re-generate the dataset with the requested parameters.\n -- chs_generate_cohort_data commits internally.\n -- ============================================================\n chs_generate_cohort_data(\n p_metric => l_metric,\n p_period_type => l_period_type,\n p_cohort_count => l_cohort_count,\n p_max_periods => l_max_periods\n );\n\n -- ============================================================\n -- meta: metric / periodType / generatedAt\n -- ============================================================\n select metric, period_type, generated_at\n into l_meta_metric, l_meta_period, l_generated_at\n from chs_dataset_meta\n fetch first 1 rows only;\n\n -- ============================================================\n -- periods[] and periodLabels[]\n -- ============================================================\n for r in (\n select period_code, period_label\n from chs_periods\n order by period_index\n ) loop\n l_periods.append(r.period_code);\n l_period_labels.append(r.period_label);\n end loop;\n\n -- ============================================================\n -- cohorts[] (with nested cells[])\n -- ============================================================\n for ch in (\n select cohort_index, cohort_id, cohort_label, original_users\n from chs_cohorts\n order by cohort_index\n ) loop\n l_cohort_obj := json_object_t();\n l_cells := json_array_t();\n\n for cell in (\n select cohort_index, period_index, retention,\n users_retained, users_original\n from chs_cohort_cells\n where cohort_index = ch.cohort_index\n order by period_index\n ) loop\n l_cell := json_object_t();\n l_cell.put('cohortIndex', cell.cohort_index);\n l_cell.put('periodIndex', cell.period_index);\n l_cell.put('retention', cell.retention);\n l_cell.put('usersRetained', cell.users_retained);\n l_cell.put('usersOriginal', cell.users_original);\n l_cells.append(l_cell);\n end loop;\n\n l_cohort_obj.put('cohortId', ch.cohort_id);\n l_cohort_obj.put('cohortLabel', ch.cohort_label);\n l_cohort_obj.put('originalUsers', ch.original_users);\n l_cohort_obj.put('cells', l_cells);\n l_cohorts.append(l_cohort_obj);\n end loop;\n\n -- ============================================================\n -- Build structuredContent (CohortData)\n -- ============================================================\n l_data.put('cohorts', l_cohorts);\n l_data.put('periods', l_periods);\n l_data.put('periodLabels', l_period_labels);\n l_data.put('metric', l_meta_metric);\n l_data.put('periodType', l_meta_period);\n l_data.put(\n 'generatedAt',\n to_char(\n l_generated_at at time zone 'UTC',\n 'YYYY-MM-DD\"T\"HH24:MI:SS.FF3\"Z\"'\n )\n );\n\n -- ============================================================\n -- formatCohortSummary(data)\n -- Port of TypeScript:\n -- const avgRetention = data.cohorts\n -- .flatMap((c) => c.cells)\n -- .filter((cell) => cell.periodIndex > 0)\n -- .reduce((sum, cell, _, arr) => sum + cell.retention / arr.length, 0);\n -- `Cohort Analysis: ${cohorts.length} cohorts, ${periods.length} periods\n -- Average retention: ${(avgRetention * 100).toFixed(1)}%\n -- Metric: ${metric}, Period: ${periodType}`\n -- ============================================================\n select count(*) into l_n_cohorts from chs_cohorts;\n select count(*) into l_n_periods from chs_periods;\n select nvl(avg(retention), 0)\n into l_avg_retention\n from chs_cohort_cells\n where period_index > 0;\n\n -- toFixed(1) equivalent: round to 1 decimal, force unit digit\n l_pct_str := trim(to_char(round(l_avg_retention * 100, 1), '99990.0'));\n\n l_summary_text :=\n 'Cohort Analysis: ' || l_n_cohorts || ' cohorts, '\n || l_n_periods || ' periods' || chr(10) ||\n 'Average retention: ' || l_pct_str || '%' || chr(10) ||\n 'Metric: ' || l_meta_metric || ', Period: ' || l_meta_period;\n\n -- ============================================================\n -- content[0] = { type: \"text\", text: formatCohortSummary(data) }\n -- ============================================================\n l_content_item := json_object_t();\n l_content_item.put('type', 'text');\n l_content_item.put('text', l_summary_text);\n l_content.append(l_content_item);\n\n -- ============================================================\n -- root = { content: [...], structuredContent: data }\n -- ============================================================\n l_root.put('content', l_content);\n l_root.put('structuredContent', l_data);\n\n return l_root.to_clob();\nend chs_get_cohort_data_response;\n/\n"
}The file /Users/********/Documents/mcp-app/examples/cohort-heatmap-server/chs_get_cohort_data_response.sql has been updated successfully.
[chs_get_cohort_data_response.sql](examples/cohort-heatmap-server/chs_get_cohort_data_response.sql) を更新しました。
l_n_cohorts / l_n_periods … chs_cohorts / chs_periods の count(*)
- l_avg_retention … chs_cohort_cells where period_index > 0 の avg(retention) (空集合時は nvl(..., 0) で TS の reduce(..., 0) と整合)
- (x 100).toFixed(1)</code> … <code class="inline-code">to_char(round(x100, 1), '99990.0') + trim (例: 0.5 → "0.5")
- 改行は chr(10) でテンプレートリテラルの実改行を再現
{
"content": [
{
"type": "text",
"text": "Cohort Analysis: 12 cohorts, 12 periods\nAverage retention: 45.3%\nMetric: retention, Period: monthly"
}
],
"structuredContent": {
"cohorts": [...],
"periods": ["M0","M1",...],
"periodLabels": ["Month 0","Month 1",...],
"metric": "retention",
"periodType": "monthly",
"generatedAt": "2026-04-17T12:34:56.789Z"
}
}