ClaudeのPostgreSQLのMCPサーバーを使ってローカルのAlloyDB Omniに問い合わせる

最近話題のClaudeのMCP（Model Context Protocol）ですが、MCPサーバーの実装サンプルにPostgreSQLがあったので動かしてみました。

このブログはOracle APEXに関する技術ブログですが、現時点ではOracle DatabaseのMCPサーバーは無く、また、MCPサーバーを組み込めるアプリもClaude Desktop appのみです。将来にOracle Databaseに接続するMCPサーバーや、MCPサーバーを組み込めるWebコンポーネントが作成されることで、Oracle APEXでも活用できるようになることを期待しています。

macOS上で行った作業を紹介します。

MCPサーバーを組み込むClaude Desktop appをインストールします。本作業ではmacOS版をインストールしましたが、Windows版もあるのでWindowsでも作業はできそうです。

https://claude.ai/download

PostgreSQLのサーバーですが、以前の記事でGoogle AlloyDB Omniを使ったときに簡単にインストールできたので、今回もAlloyDB Omniを使います。podmanを使用します。

後でWorldのサンプル・データベースをインストールするので、podmanのボリュームとしてpgSampleWorldを作成します。

podman volume create pgSampleWorld

% podman volume create pgSampleWorld

pgSampleWorld

% 

AlloyDB Omniのコンテナ・イメージをダウンロードします。

podman pull google/alloydbomni

% podman pull google/alloydbomni

Trying to pull docker.io/google/alloydbomni:latest...

Getting image source signatures

Copying blob sha256:9a05f2493d03e0bbb1066e3bb909096d9703c5418d053689f8f7ec5c1d7f2b60

Copying blob sha256:2fdd8bd0abec7d6bf9c9413fa4822358118e9e09fbd29407074e295e7480643f

Copying blob sha256:f5852518415488bf422f9b7f2b17ffd7d4d7185e7c8ab8bf2147deeba4634f9b

Copying blob sha256:0185c6859b00ddb146dc31ca9b6beaed53efb3407563057f05fb266b3f434287

Copying blob sha256:205213b8936196dbb62c16b3a96e0b7e220299b3a9ca113bccfd126dadb699e5

Copying blob sha256:c1e0ef7b956a07c7b090256aa16cbb0550a34d0625d1d23c5b1a76e92a58d01e

Copying blob sha256:f1c87667e7842cd5137ab5ab073fd291aab49798d524866faf5e29baf799b2ef

Copying blob sha256:f4e912a38a04c24a3b2c9357881eb1d2c4a79966b599994cf923a3b861deb717

Copying blob sha256:867c56a9d8dc8e0f8bf8540326bb5638c709e265ad3375249cc036c62b16338b

Copying blob sha256:149990bdbe96c90c9632f7494d32f308f2f410a17dc862496798163b371df491

Copying blob sha256:7b43d926df6c0192fef4a132f46301cb25c933a70475037b5817c9ee977cf7f9

Copying blob sha256:4f4fb700ef54461cfa02571ae0db9a0dc1e0cdb5577484a6d75e68dc38e8acc1

Copying blob sha256:5fe5be224054609d6669eb71c74f8414c654ed0495c9298a32aec1bd29ac4ef3

Copying blob sha256:b5897c548647a8d823411b5f365fccd16994b35efcd75164b5fc82d77c396ebe

Copying blob sha256:2cc17a1ffe78834f475a35e67e612c3af76ac44d459e0ab42339f0fb2347feb7

Copying blob sha256:8714a15a9f79d2f01947ddb11b2760a05d897325efc39ff988dc4e073cf7a93c

Copying blob sha256:94947c0ccc56de03ab622a35e70c99ab4636dae5dae427dfba5df79b2a84177f

Copying blob sha256:215fbeb3c59b120bf53d3081b76272a835b13ae7a77dd072d59e70cfcda3bb07

Copying blob sha256:5e6b93867b6c54a5b48c6497faf8bf50214ea208d0be670cea6db9d9f23482fd

Copying blob sha256:6bf938e955c48891853605a1636950a912b1c81b9749205ba17c26241e005702

Copying blob sha256:92a15b9c822ec96fc09ed2ceacfec6f0a20058f9dc2c601c490985ec306826f7

Copying blob sha256:a503bc9e96a17982d6a2ae33314b3127dc7bf9acb4db2ac1ebbb899b32147aeb

Copying blob sha256:4c80b84ecf5976c85858871790f90a50816f48ceefccab5c843d2c0bde955036

Copying blob sha256:e01e5606c56fe7fc61177d8118a6274a5fbb146c881f00de6a8bc4780412c6df

Copying blob sha256:09e9c6b61a697d1af8b69f1aa5f2d048adfbbfd705ca5752943cd49772d884e3

Copying blob sha256:0bf7b01fb1e47ec1361fadec05df0a950da5a4c46b49779eb34d0e091e165a5e

Copying blob sha256:d220fb7d6aeb40b0998b710f1a4b61c2ce10876ed6e8dd5c8cc0009b5eb07289

Copying blob sha256:5ed9845c51464bbab10c8deb0bd80b0bc484e2064fa4076176cb5a4421169557

Copying config sha256:fe0702e3d2389cf3cdf28f1f6c4342b3da53981820f5c9fba1249a7efc351eac

Writing manifest to image destination

fe0702e3d2389cf3cdf28f1f6c4342b3da53981820f5c9fba1249a7efc351eac

% 

AlloyDB Omniのコンテナを作成します。名前はmy-omniとしました。POSTGRES_PASSWORDに設定した値は、MCPサーバーの構成ファイルに書き込むので忘れないようにします。

podman run --name my-omni -e POSTGRES_PASSWORD=<パスワード> -v pgSampleWorld:/var/lib/postgresql/data -p 5432:5432 -d google/alloydbomni

% podman run --name my-omni -e POSTGRES_PASSWORD=********** -v pgSampleWorld:/var/lib/postgresql/data -p 5432:5432 -d google/alloydbomni

dc8cb2273f0d1e25de431f3f7960e301afcc17dea88940e56791e754852e3129

% 

コンテナmy-omniが実行されていることを確認します。

podman ps

% podman ps

CONTAINER ID  IMAGE                                COMMAND     CREATED        STATUS        PORTS                   NAMES

dc8cb2273f0d  docker.io/google/alloydbomni:latest  postgres    3 minutes ago  Up 3 minutes  0.0.0.0:5432->5432/tcp  my-omni

% 

PostgreSQLのworldのサンプル・データベースをダウンロードします。

https://www.postgresql.org/ftp/projects/pgFoundry/dbsamples/world/world-1.0/

ダウンロードしたworld-1.0.tar.gzを解凍します。

tar xzvf world-1.0.tar.gz

 % tar xzvf world-1.0.tar.gz 

x dbsamples-0.1/

x dbsamples-0.1/world/

x dbsamples-0.1/world/world.sql

x dbsamples-0.1/world/README

%       

展開したファイルに含まれているworld.sqlを実行します。

cd dbsamples-0.1/world
podman exec -i my-omni psql -U postgres < ./world.sql

% cd dbsamples-0.1/world 

world % podman exec -i my-omni psql -U postgres < ./world.sql

BEGIN

SET

CREATE TABLE

CREATE TABLE

CREATE TABLE

COPY 4079

COPY 239

COPY 984

ALTER TABLE

ALTER TABLE

ALTER TABLE

ALTER TABLE

ALTER TABLE

COMMIT

ANALYZE

ANALYZE

ANALYZE

 world % 

以上でPostgreSQLの準備はできました。

Claude Desktop appにPostgreSQLのMCPサーバーを組み込みます。以下を参照します。

https://github.com/modelcontextprotocol/servers/tree/main/src/postgres

~/Library/Application\ Support/Claude以下にclaude_desktop_config.jsonというファイルを作成し、以下を記述します。[パスワード]の部分はAlloyDB Omniのコンテナ作成時に与えたパスワードに置き換えます。

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": [
        "-y",
        "@modelcontextprotocol/server-postgres",
        "postgresql://postgres:[パスワード]@localhost:5432/postgres"
      ]
    }
  }
}

% cd ~/Library/Application\ Support/Claude

Claude % cat claude_desktop_config.json 

{

  "mcpServers": {

    "postgres": {

      "command": "npx",

      "args": [

        "-y",

        "@modelcontextprotocol/server-postgres",

        "postgresql://postgres:*********@localhost:5432/postgres"

      ]

    }

  }

}

Claude % 

Claude Desktop Appを起動します。すでに起動している場合は再起動します。

チャットを入力する画面の右下にトンカチのアイコンが表示されます。postgresのMCPサーバーだけであれば、1になっているはずです。

ClaudeのSettings...を開きます。

Developerを開き、MCPサーバーとしてpostgresが認識されていることを確認します。途中の作業を失念してしまいましたが、もし、Developerがないときは、メニューのどこかに（Help?）Enable Developer Modeというコマンドがあるはずなので、それを実行してDeveloper Modeに変更します。

Claudeに問い合わせを行います。

「私のpostgresから、どのような情報を問い合わせることができますか？」

MCPサーバーへの接続の承認が求められます。Allow for This Chatをクリックします。

Claudeがデータベースに問い合わせを発行して、回答してくれます。

「東京の人口を教えてください。」

MCPサーバーpostgresへ送信されたSELECT文とその結果も回答に含まれています。View Result from query from postgres(local)をクリックすると表示されます。

すごい。

MCPサーバーでは、TypeScriptとPython向けのSDKが提供されています。

https://github.com/modelcontextprotocol/servers

Oracle Database向けのMCPサーバーがあれば、嬉しいのですが。

今回の記事は以上になります。

完

Oracle APEXのアプリからOpenAIのBatch APIを呼び出す

OpenAIが2024年4月にBatch APIをリリースしています。少し時間が経ちましたが、OpenAIのBatch APIを呼び出して、Chat Completions API + Structured Outputsで文章のJSON表現を取り出す仕組みと、Embeddings APIでエンべディングを取得する仕組みを、Oracle APEXのアプリケーションとして実装してみました。OpenAI Batch APIの呼び出しは、パッケージUTL_OPENAI_BATCH_APIに実装しています。APEXアプリケーションは、そのパッケージに実装したファンクションを呼び出して動作を確認するために作成しています。

Oracle APEXでは、ほぼすべての処理をデータベースで実行していて、OpenAIのChat Completions APIやEmbeddings APIはデータベースのサーバーから呼び出されます。ブラウザからデータベースを介してChat Completions APIが呼び出されるときは、ブラウザからORDSを介したデータベースへの接続と、データベースからOpenAIのAPIサーバーへの接続の両方が、APIの処理が終わるまで維持されます。データベースはAPIの処理中は、その応答を待機して何も処理は行いませんが、セッションつまりサーバー・プロセスは占有したままになります。リソースの利用効率としては、あまり良くありません。OpenAIの新しい推論モデルo1のような、レスポンスが返されるまでに長時間かかる場合は、特に良くありません。

OpenAIのBatch APIを呼び出すことにより、この点が改善されます（注: 現時点ではo1-previewはBatch APIでは使えないので、通常のモデルでの話です）。Batch APIという名前の通り、会話の用途には使用できませんが、ドキュメントの要約、Structured Outputsを指定したドキュメントのJSON出力、エンべディングの生成などは、対話的に処理する必要がありません。Batch APIの応答は待機する必要がなく、Retrieve batchによるポーリングを行うことによりリクエストの完了を確認します。占有されるサーバー・プロセスは発生しません。また、コストも50%程度削減できるようです。削減されるコストについては、OpenAIのPricingのページを確認してください。

Batch APIのリクエストはOpenAI Files APIを使って、ファイルとしてOpenAIのストレージにアップロードする必要があります。また、結果のアウトプット・ファイルやエラーが記載されたファイルも、OpenAIのストレージに作成されます。そのために以前の記事「OpenAIのFiles APIを使ってファイルをアップロードする」で紹介している、OpenAIのFiles APIを呼び出すPL/SQLパッケージUTL_OPENAI_FILES_APIをあらかじめ作成しておきます。

今回作成したパッケージUTL_OPENAI_BATCH_APIのコードは、記事の末尾に添付します。

作成したAPEXアプリケーションのエクスポートは以下です。

https://github.com/ujnak/apexapps/blob/master/exports/sample-openai-batch-api.zip

以下より、作成したAPEXアプリケーションを紹介します。

最初にOpenAIへ発行するリクエストやレスポンスを保持する表を作成します。クイックSQLの以下のモデルを使用します。

表OPENAI_BATCH_SUBMISSIONSはBatch APIで発行するリクエストを保持します。表OPENAI_BATCH_REQUESTSはそれぞれのバッチに含まれるChat Completions APIまたはEmbeddings APIのリクエストを保持します。OPENAI_BATCH_SUBMISSIONSとはSUBMISSION_IDをキーとした親子関係があります。表OPENAI_BATCH_RESPONSESはoutput_file_idで指定されたバッチの結果出力ファイルの内容をパースして、それぞれのリクエストに対応したレスポンスごとに保存します。OPENAI_BATCH_REQUESTSとはCUSTOM_IDで紐づきます。バッチの入力ファイル、出力ファイルともに改行で区切られたJSONファイル(Newline Delimited JSON - NDJSONまたはJSON LInes - JSONL）なので、OPENAI_BATCH_REQUESTSおよびOPENAI_BATCH_RESPONSESともに、１行のJSONが表の１行になります。

	# prefix: openai
	# genpk: no
	batch_submissions
	submission_id /pk
	input_file_id vc80
	endpoint vc80
	completion_window vc8
	request_file blob
	batch_object json
	batch_id vc80 -- value in batch_object
	output_file_id vc80 -- value in batch_object
	status vc20 -- value in batch_object
	response_file blob
	error_file_id vc80
	error_file blob
	batch_requests
	custom_id /pk
	submission_id /nn /fk batch_submissions
	method vc8 /default POST
	url vc80
	body json
	batch_responses /pk id
	id vc80 /nn
	custom_id /fk batch_requests
	response json
	status_code num
	request_id vc80
	body json
	first_message clob

view raw openai-batch-schema.quicksql hosted with ❤ by GitHub

バッチ・リクエストの発行と結果の取得は、アプリケーションのホーム・ページに実装しています。パッケージUTL_OPENAI_BATCH_APIには、おおむねボタン名に対応したプロシージャまたはファンクションが含まれています。ボタンのクリックで、パッケージに実装されたそれらの処理が呼び出されます。

Createボタンを押すとダイアログが開きます。

Endpointとして/v1/chat/completionsまたは/v1/embeddingsのどちらかを選択します。Completion Windowも指定します。以下では24hを指定しています。

Create Batchをクリックして、表OPENAI_BATCH_SUBMISSIONSに１行挿入します。

Createの横にDeleteボタンがあります。選択されているSubmission IDの行を表OPENAI_BATCH_SUBMISSIONSから削除する際に使用しますが、そのSUBMISSION_IDに紐づくリクエストが表OPENAI_BATCH_REQUESTSに存在する場合はエラーが発生します。

Endpointとして/v1/chat/completionsを指定した場合は、Append Chatのボタンが表示されます。

今回は以下の情報を設定しています。Modelとしてはgpt-4o-mini、System Messageに「あなたは日本の昔話に詳しいアシスタントです。」User Messageとして、次のメッセージ「以下の物語より、登場人物とその関係についてJSON形式で表現してください。」に続けて、青空文庫より竹取物語をコピペしました。

https://www.aozora.gr.jp/cards/001072/files/48310_42692.html

Structured Outputsとして、以下のJSON Schemaを指定しています。

	{
	"$schema" : "http://json-schema.org/draft-07/schema#",
	"title" : "StoryCharactersSchema",
	"type" : "object",
	"properties" :
	{
	"characters" :
	{
	"type" : "array",
	"items" :
	{
	"type" : "object",
	"properties" :
	{
	"name" :
	{
	"type" : "string",
	"description" : "The name of the character."
	},
	"role" :
	{
	"type" : "string",
	"description" : "The role of the character in the story."
	},
	"relations" :
	{
	"type" : "object",
	"additionalProperties" :
	{
	"type" : "string",
	"description" : "The relationship of the character to another character."
	},
	"description" : "Relationships of the character with other characters."
	}
	},
	"required" :
	[
	"name",
	"role"
	],
	"additionalProperties" : false
	}
	}
	},
	"required" :
	[
	"characters"
	],
	"additionalProperties" : false
	}

view raw StoryCharactersSchema.json hosted with ❤ by GitHub

以上でボタンAppend Chatをクリックします。

竹取物語は長文で、レポートにエラーが発生します。

このエラーは対話モード・レポートで発生しているエラーです。リクエストは正常に表OPENAI_BATCH_REQUESTSに追加されています。

表示列からBodyを除くと、対話モード・レポートのエラーが無くなります。

同様に、リクエストにカチカチ山を追加します。

https://www.aozora.gr.jp/cards/000329/files/18377_11982.html

複数のリクエストを追加したのち、ボタンSubmit Batchをクリックします。

複数のリクエストを改行区切りのJSONでファイルにまとめて、そのファイルをOpenAIのストレージにアップロードしたのち、Batch APIのCreate Batchリクエストを発行します。

Create Batchの発行直後はStatusがvalidatingになるようです。

Update Batchをクリックすると、発行済みのバッチ・リクエストのステータスを更新します。

バッチが処理中の場合はin_progressになります。statusが取りえる状態は、OpenAIのBatch APIのガイドに一覧されています。

バッチ処理が完了するとStatusはcompletedになります。statusの種類にはfailedがありますが、これはvalidationでの失敗から遷移する状態で、バッチ処理が失敗していてもstatusはcompletedになります。失敗しているのはバッチに含まれている、個々のリクエストであって、その場合はバッチ処理自体は成功していると見做されているようです。

アプリケーションにはList batchのページが含まれています。このページでは、Batch APIのList batchリクエストを発行し、今までに発行したバッチ処理を一覧します。

この中にError File Idの項目があります。バッチに含まれているリクエストでエラーが発生している場合、そのエラー・メッセージはError Fileに書き込まれます。Error Fileが作成されていると、そのError FileにError File IDが割り当てられます。

エラーの内容はError File IDを指定して、OpenAIのストレージからError Fileをダウンロードすることで確認できます。

パッケージUTL_OPENAI_BATCH_APIに含まれるプロシージャdownload_batch_responseでは、output_file_idで指定できるバッチの出力ファイルを表OPENAI_BATCH_SUBMISSIONSの列RESPONSE_FILEに保存するとともに、error_file_idがあればError Fileを列ERROR_FILEに保存します。

バッチ処理が完了した後、ボタンGet Resultをクリックすることにより、OpenAIのストレージに保存されたOutput FileとError File（もしあれば）を表OPENAI_BATCH_SUBMISSIONSへダウンロードします。また、続けてプロシージャparse_batch_responseを呼び出し、バッチのレスポンスに含まれるChat Completions APIとしてのレスポンスに加えて、そのレスポンスに含まれる最初のメッセージも取り出し、表OPENAI_BATCH_RESPONSESの列BODYとFIRST_RESPONSEに保存します。

表OPENAI_BATCH_RESPONSESの対話モード・レポートの編集アイコンをクリックすると、フォーム形式でレスポンスを確認できます。

竹取物語のJSON出力として、以下が得られました。

{
  "characters" :
  [
    {
      "name" : "竹取の翁",
      "role" : "主人公",
      "relations" :
      {
        "妻" : "竹取の翁の妻",
        "娘" : "赫映姫（かぐやひめ）"
      }
    },
    {
      "name" : "竹取の妻",
      "role" : "翁の妻",
      "relations" :
      {
        "夫" : "竹取の翁",
        "娘" : "赫映姫（かぐやひめ）"
      }
    },
    {
      "name" : "赫映姫",
      "role" : "翁の養女、月の姫",
      "relations" :
      {
        "父" : "竹取の翁",
        "母" : "竹取の妻"
      }
    },
    {
      "name" : "石造皇子",
      "role" : "求婚者の一人",
      "relations" :
      {
        "姫" : "赫映姫（かぐやひめ）"
      }
    },
    {
      "name" : "車持皇子",
      "role" : "求婚者の一人",
      "relations" :
      {
        "姫" : "赫映姫（かぐやひめ）"
      }
    },
    {
      "name" : "阿倍御主人",
      "role" : "求婚者の一人",
      "relations" :
      {
        "姫" : "赫映姫（かぐやひめ）",
        "大臣" : "大納言大伴御行の友人"
      }
    },
    {
      "name" : "大納言大伴御行",
      "role" : "求婚者の一人",
      "relations" :
      {
        "姫" : "赫映姫（かぐやひめ）",
        "友人" : "阿倍御主人"
      }
    },
    {
      "name" : "中納言石上麻呂",
      "role" : "求婚者の一人",
      "relations" :
      {
        "姫" : "赫映姫（かぐやひめ）"
      }
    }
  ]
}

カチカチ山のJSON出力です。

{
  "characters" :
  [
    {
      "name" : "おじいさん",
      "role" : "物語の主人公。おばあさんと二人三脚で生活している。たぬきにだまされて悲劇に見舞われる。",
      "relations" :
      {
        "おばあさん" : "妻",
        "たぬき" : "敵",
        "白うさぎ" : "友人"
      }
    },
    {
      "name" : "おばあさん",
      "role" : "おじいさんの妻。たぬきに騙されて命を落とす。",
      "relations" :
      {
        "おじいさん" : "夫",
        "たぬき" : "敵"
      }
    },
    {
      "name" : "たぬき",
      "role" : "物語の悪役。おじいさんの畑を荒らし、おばあさんを欺き、最終的におじいさんを裏切る。",
      "relations" :
      {
        "おじいさん" : "敵",
        "おばあさん" : "敵",
        "白うさぎ" : "競争相手"
      }
    },
    {
      "name" : "白うさぎ",
      "role" : "おじいさんの友人。たぬきの復讐を助けようとする。",
      "relations" :
      {
        "おじいさん" : "友人",
        "たぬき" : "敵"
      }
    }
  ]
}

すごい。

「Any sufficiently advanced technology is indistinguishable from magic.」

生のRequest File（バッチのリクエストとなったファイル - OpenAIのAPIではInput File）、Response File（バッチの出力ファイル - OpenAIのAPIではOutput File）、Error Fileは、Batchesのページに表OPENAI_BATCH_SUBMISSIONSの対話モード・レポートを作成してあり、そのレポートからダウンロードできるようになっています。

Embeddingsの場合は、Endpointとして/v1/embeddingsを設定したエントリを作成し、ボタンAdd Embeddingsをクリックしてリクエストを追加します。

バッチ処理のリクエストの発行、確認および結果のダウンロードはChat Completions APIのときと同様にボタンSubmit Batch、Update Status、Get Resultをクリックして実行します。

Embeddingsに関しては、Output Fileのそれぞれの行のJSONからbodyを取り出すところまでは処理しています。bodyからエンべディングを取り出す実装は含めていません。

OpenAIのBatch APIを使うことにより、以下が可能になります。

1. データベースを効果的に利用できます。
2. OpenAIへ支払う費用が削減（50%オフなので半減）できます。
3. Structured Outputsにより非定型文書よりグラフとして扱えるデータを生成したり、セマンティック検索で使用するエンべディングを生成することができます。それらの形式のデータは、直接Oracle Database 23aiのグラフやベクトルの機能で活用できます。

今回の記事は以上になります。

Oracle APEXのアプリケーション作成の参考になれば幸いです。

完

	create or replace package "UTL_OPENAI_BATCH_API" as
	/**
	* OpenAIにBatchで送信するリクエストを作成する。submission_idが返されるので、
	* そのsubmission_idを指定して、Chat completionsのリクエストまたはembeddingsの
	* リクエストを追加する。
	*
	* Chat completionsの場合はendpointは/v1/chat/completions
	* リクエストの追加にはappend_chatを呼び出す。
	*
	* Embeddingsの場合は/v1/embeddings
	* リクエストの追加にはappend_embeddingを呼び出す。
	*/
	function create_batch(
	p_endpoint in varchar2
	,p_completion_window in varchar2 default '24h'
	)
	return number;
	/**
	* バッチにChat Completionsのリクエストを追加する。
	*/
	function append_chat(
	p_submission_id in number
	,p_model in varchar2
	,p_system_message in clob
	,p_user_message in clob
	,p_json_schema_name in varchar2 default null -- response_format is set to json_schema if not null
	,p_json_schema_strict in boolean default true
	,p_json_schema in clob default null
	,p_max_tokens in number default null
	)
	return number;
	/**
	* バッチにEmbeddingのリクエストを追加する。inputが配列の場合の対応は除く。
	*/
	function append_embedding(
	p_submission_id in number
	,p_model in varchar2
	,p_input in clob
	,p_encoding_format in varchar2 default null
	,p_dimensions in number default null
	)
	return number;
	/**
	* 追加されたリクエストをファイルにまとめ、OpenAIにアップロードする。
	* OpenAIのBatch APIを呼び出す。
	*/
	function submit_batch(
	p_submission_id in number
	,p_credential_static_id in varchar2
	)
	return varchar2;
	/**
	* バッチのステータスを確認する。openai_batch_submissionsのstatus列をOpenAIから取り出した
	* Batch Objectのstatusの値で更新する。
	*/
	procedure update_batch_status(
	p_submission_id in number
	,p_credential_static_id in varchar2
	);
	/**
	* Batchの処理結果が保存されているファイルをデータベースにダウンロードする。
	* OpenAIに保存されているバッチの入力ファイルと出力ファイルの両方を削除する。
	* エラー出力があれば、それもダウンロードする。
	*/
	procedure download_batch_response(
	p_submission_id in number
	,p_credential_static_id in varchar2
	);
	/**
	* openai_batch_submissionsのresponse_file列に保存されたバッチの処理結果はJSONL（改行区切りJSON）である。
	* その出力を１行ごとに分割し、openai_batch_responsesに書き込む。
	*/
	procedure parse_batch_response(
	p_submission_id in number
	);
	/**
	* 処理中のバッチをキャンセルする。
	*
	* p_batch_idかp_submission_idのどちらかは必須。
	* 両方指定されている場合は、batch_idの指定を優先する。
	*/
	procedure cancel_batch(
	p_batch_id in varchar2 default null
	,p_submission_id in number default null
	,p_credential_static_id in varchar2
	);
	end "UTL_OPENAI_BATCH_API";
	/

view raw utl_openai_batch_api.sql hosted with ❤ by GitHub

	create or replace package body "UTL_OPENAI_BATCH_API" as
	/**
	* バッチを作成する。
	*/
	function create_batch(
	p_endpoint in varchar2
	,p_completion_window in varchar2
	)
	return number
	as
	l_submission_id openai_batch_submissions.submission_id%type;
	begin
	insert into openai_batch_submissions(endpoint, completion_window) values(p_endpoint, p_completion_window)
	returning submission_id into l_submission_id;
	return l_submission_id;
	end create_batch;
	/**
	* 個別のリクエストをバッチに追加する。戻り値はcustom_id。
	*
	* methodはPOST、p_urlはバッチのendpointを引き継ぐので、指定は不要。
	*/
	function append_request(
	p_submission_id in number
	,p_method in varchar2 default 'POST'
	,p_url in varchar2 default null
	,p_body in clob -- JSON
	)
	return number
	as
	l_custom_id openai_batch_requests.custom_id%type;
	begin
	insert into openai_batch_requests(submission_id, method, url, body) values(p_submission_id, p_method, p_url, p_body)
	returning custom_id into l_custom_id;
	return l_custom_id;
	end append_request;
	/**
	* バッチにChat requestを追加する。
	*/
	function append_chat(
	p_submission_id in number
	,p_model in varchar2
	,p_system_message in clob
	,p_user_message in clob
	,p_json_schema_name in varchar2
	,p_json_schema_strict in boolean
	,p_json_schema in clob -- for Structured Output, response_format type is set to json_schema if supplied.
	,p_max_tokens in number
	)
	return number
	as
	l_request json_object_t;
	l_messages json_array_t;
	l_message json_object_t;
	l_response_format json_object_t;
	l_request_clob clob;
	l_json_schema json_object_t;
	begin
	/*
	* リクエストに含まれるメッセージとしてsystemとuserを、それぞれ１つに限定。
	* 対話するわけでは無いので、それで十分なはず。
	*/
	l_messages := json_array_t();
	/* systemメッセージの追加 */
	l_message := json_object_t();
	l_message.put('role', 'system');
	l_message.put('content', p_system_message);
	l_messages.append(l_message);
	/* userメッセージの追加 */
	l_message := json_object_t();
	l_message.put('role', 'user');
	l_message.put('content', p_user_message);
	l_messages.append(l_message);
	/* リクエストの作成 */
	l_request := json_object_t();
	l_request.put('model', p_model);
	l_request.put('messages', l_messages);
	/*
	* JSON Schema nameの指定があれば、Structured Outputを要求する
	*/
	if p_json_schema_name is not null then
	l_response_format := json_object_t();
	l_response_format.put('type', 'json_schema');
	/*
	* schemaに与えているオブジェクトが本来のJSON Schemaだが、
	* nameとstrictを含めたオブジェクトをjson_schemaとして与える。
	*/
	l_json_schema := json_object_t();
	l_json_schema.put('name', p_json_schema_name);
	l_json_schema.put('schema', json_object_t(p_json_schema));
	l_json_schema.put('strict', p_json_schema_strict);
	l_response_format.put('json_schema', l_json_schema);
	l_request.put('response_format', l_response_format);
	end if;
	/*
	* max_tokensの指定があれば、リクエストに含める。
	*/
	if p_max_tokens is not null then
	l_request.put('max_tokens', p_max_tokens);
	end if;
	l_request_clob := l_request.to_clob();
	return append_request(
	p_submission_id => p_submission_id
	,p_body => l_request_clob
	);
	end append_chat;
	/**
	* バッチへEmbedding requestを追加する。
	* Batchでリクエストするので、配列の対応は除く。
	*/
	function append_embedding(
	p_submission_id in number
	,p_model in varchar2
	,p_input in clob
	,p_encoding_format in varchar2
	,p_dimensions in number
	)
	return number
	as
	l_request json_object_t;
	l_request_clob clob;
	begin
	l_request := json_object_t();
	l_request.put('input', p_input);
	l_request.put('model', p_model);
	if p_encoding_format is not null then
	l_request.put('encoding_format', p_encoding_format);
	end if;
	if p_dimensions is not null then
	l_request.put('dimensions', p_dimensions);
	end if;
	l_request_clob := l_request.to_clob();
	return append_request(
	p_submission_id => p_submission_id
	,p_body => l_request_clob
	);
	end append_embedding;
	/**
	* OpenAIにバッチ・リクエストを送信する。
	*/
	function submit_batch(
	p_submission_id in number
	,p_credential_static_id in varchar2
	)
	return varchar2
	as
	l_request json_object_t;
	l_file_content_clob clob;
	l_file_content blob;
	l_filename varchar2(80);
	l_batch_request json_object_t;
	l_batch_request_clob clob;
	l_file clob;
	l_batch_object clob;
	l_batch_object_json json_object_t;
	e_api_call_failed exception;
	l_input_file_id openai_batch_submissions.input_file_id%type;
	l_endpoint openai_batch_submissions.endpoint%type;
	l_completion_window openai_batch_submissions.completion_window%type;
	l_batch_id openai_batch_submissions.batch_id%type;
	l_output_file_id openai_batch_submissions.output_file_id%type;
	l_status openai_batch_submissions.status%type;
	begin
	/*
	* endpointとcompletion_windowの指定を取り出す。
	*/
	select endpoint, completion_window into l_endpoint, l_completion_window
	from openai_batch_submissions
	where submission_id = p_submission_id;
	/*
	* リクエストをJSONLのフォーマットにまとめる。
	*/
	l_file_content_clob := '';
	for r in (
	select * from openai_batch_requests
	where submission_id = p_submission_id order by custom_id asc
	)
	loop
	l_request := json_object_t();
	l_request.put('custom_id', to_char(r.custom_id));
	l_request.put('method', coalesce(r.method, 'POST'));
	l_request.put('url', coalesce(r.url, l_endpoint));
	l_request.put('body', json_object_t(r.body));
	l_file_content_clob := l_file_content_clob || l_request.to_clob() || apex_application.LF;
	end loop;
	l_file_content := apex_util.clob_to_blob(l_file_content_clob);
	/* update batch request, debug purpose */
	update openai_batch_submissions set request_file = l_file_content
	where submission_id = p_submission_id;
	l_filename := apex_string.format('batchinput-%s.jsonl', p_submission_id);
	/* upload file */
	l_input_file_id := utl_openai_files_api.upload_file(
	p_filename => l_filename
	,p_content_type => 'application/jsonlines'
	,p_file_content => l_file_content
	,p_purpose => 'batch'
	,p_credential_static_id => p_credential_static_id
	,p_file => l_file
	);
	update openai_batch_submissions set input_file_id = l_input_file_id
	where submission_id = p_submission_id;
	/* バッチ要求の発行 */
	l_batch_request := json_object_t();
	l_batch_request.put('input_file_id', l_input_file_id);
	l_batch_request.put('endpoint', l_endpoint);
	l_batch_request.put('completion_window', l_completion_window);
	l_batch_request_clob := l_batch_request.to_clob();
	apex_web_service.clear_request_headers();
	apex_web_service.set_request_headers('Content-Type', 'application/json');
	l_batch_object := apex_web_service.make_rest_request(
	p_url => 'https://api.openai.com/v1/batches'
	,p_http_method => 'POST'
	,p_body => l_batch_request_clob
	,p_credential_static_id => p_credential_static_id
	);
	if apex_web_service.g_status_code <> 200 then
	raise e_api_call_failed;
	end if;
	l_batch_object_json := json_object_t(l_batch_object);
	l_batch_id := l_batch_object_json.get_string('id');
	l_output_file_id := l_batch_object_json.get_string('output_file_id'); -- サブミット時点ではNULLなはず。
	l_status := l_batch_object_json.get_string('status');
	update openai_batch_submissions set batch_id = l_batch_id, output_file_id = l_output_file_id
	,batch_object = l_batch_object, status = l_status
	where submission_id = p_submission_id;
	return l_batch_id;
	end submit_batch;
	/**
	* バッチのステータスを更新する。
	*/
	procedure update_batch_status(
	p_submission_id in number
	,p_credential_static_id in varchar2
	)
	as
	l_url varchar2(400);
	l_batch_object clob;
	l_batch_object_json json_object_t;
	e_api_call_failed exception;
	l_batch_id openai_batch_submissions.batch_id%type;
	l_output_file_id openai_batch_submissions.output_file_id%type;
	l_status openai_batch_submissions.status%type;
	l_error_file_id openai_batch_submissions.error_file_id%type;
	begin
	select batch_id into l_batch_id from openai_batch_submissions
	where submission_id = p_submission_id;
	l_url := apex_string.format('https://api.openai.com/v1/batches/%s', l_batch_id);
	apex_web_service.clear_request_headers();
	apex_web_service.set_request_headers('Content-Type', 'application/json');
	l_batch_object := apex_web_service.make_rest_request(
	p_url => l_url
	,p_http_method => 'GET'
	,p_credential_static_id => p_credential_static_id
	);
	if apex_web_service.g_status_code <> 200 then
	raise e_api_call_failed;
	end if;
	l_batch_object_json := json_object_t(l_batch_object);
	l_batch_id := l_batch_object_json.get_string('id');
	l_output_file_id := l_batch_object_json.get_string('output_file_id');
	l_status := l_batch_object_json.get_string('status');
	l_error_file_id := l_batch_object_json.get_string('error_file_id');
	update openai_batch_submissions set batch_id = l_batch_id, output_file_id = l_output_file_id
	,batch_object = l_batch_object, status = l_status, error_file_id = l_error_file_id
	where submission_id = p_submission_id;
	end update_batch_status;
	/**
	* 結果のファイルをダウンロードする。
	*/
	procedure download_batch_response(
	p_submission_id in number
	,p_credential_static_id in varchar2
	)
	as
	l_file clob;
	l_input_file_id openai_batch_submissions.input_file_id%type;
	l_file_id openai_batch_submissions.output_file_id%type;
	l_output_file_id openai_batch_submissions.output_file_id%type;
	l_status openai_batch_submissions.status%type;
	l_response_file openai_batch_submissions.response_file%type;
	l_error_file_id openai_batch_submissions.error_file_id%type;
	l_error_file openai_batch_submissions.error_file%type;
	begin
	select status, input_file_id, output_file_id, response_file, error_file_id, error_file
	into l_status, l_input_file_id, l_output_file_id, l_response_file, l_error_file_id, l_error_file
	from openai_batch_submissions
	where submission_id = p_submission_id;
	/*
	* 処理が完了していて、結果のファイルが未ダウンロードであれば、ダウンロードする。
	*/
	if l_status = 'completed' and l_output_file_id is not null and l_response_file is null then
	l_file_id := utl_openai_files_api.retrieve_file(
	p_file_id => l_output_file_id
	,p_credential_static_id => p_credential_static_id
	,p_file => l_file
	,p_file_content => l_response_file
	);
	if l_response_file is not null then
	update openai_batch_submissions set response_file = l_response_file where submission_id = p_submission_id;
	end if;
	/*
	* OpenAIにアップロードされているファイルを削除する。
	*
	*/
	begin
	l_file_id := utl_openai_files_api.delete_file(
	p_file_id => l_input_file_id
	,p_credential_static_id => p_credential_static_id
	,p_file => l_file
	);
	exception
	when others then
	apex_debug.info('failed to delete file: %s', l_input_file_id);
	end;
	begin
	l_file_id := utl_openai_files_api.delete_file(
	p_file_id => l_output_file_id
	,p_credential_static_id => p_credential_static_id
	,p_file => l_file
	);
	exception
	when others then
	apex_debug.info('failed to delete file: %s', l_output_file_id);
	end;
	end if;
	/*
	* エラー・ファイルがあればダウンロードする。
	*/
	if l_status = 'completed' and l_error_file_id is not null and l_error_file is null then
	l_file_id := utl_openai_files_api.retrieve_file(
	p_file_id => l_error_file_id
	,p_credential_static_id => p_credential_static_id
	,p_file => l_file
	,p_file_content => l_error_file
	);
	if l_error_file is not null then
	update openai_batch_submissions set error_file = l_error_file where submission_id = p_submission_id;
	end if;
	/*
	* OpenAIにアップロードされているファイルを削除する。
	*
	*/
	begin
	l_file_id := utl_openai_files_api.delete_file(
	p_file_id => l_error_file_id
	,p_credential_static_id => p_credential_static_id
	,p_file => l_file
	);
	exception
	when others then
	apex_debug.info('failed to delete file: %s', l_error_file_id);
	end;
	end if;
	end download_batch_response;
	/**
	* ダウンロードしたresponse_fileの内容で、openai_batch_responsesを更新する。
	*/
	procedure parse_batch_response(
	p_submission_id in number
	)
	as
	l_response_file blob;
	l_responses apex_t_varchar2;
	l_response_json json_object_t;
	l_line varchar2(32767);
	l_id openai_batch_responses.id%type;
	l_custom_id openai_batch_responses.custom_id%type;
	l_response json_object_t;
	l_response_clob clob;
	l_status_code openai_batch_responses.status_code%type;
	l_request_id openai_batch_responses.request_id%type;
	l_body json_object_t;
	l_body_clob clob;
	/* chat completionsの応答より、最初のメッセージを取り出す。 */
	l_choices json_array_t;
	l_choice json_object_t;
	l_message json_object_t;
	l_content clob;
	begin
	select response_file into l_response_file from openai_batch_submissions where submission_id = p_submission_id;
	if l_response_file is null then
	return; -- ファイルの内容が無ければ、何もしない。
	end if;
	/*
	* CLOBの内容をLFで分割する。結果はVARCHAR2の配列なので、それぞれのレスポンスのサイズが
	* 32kを超える場合の結果は不明。
	*/
	l_responses := apex_string.split(apex_util.blob_to_clob(l_response_file));
	for i in 1..l_responses.count
	loop
	l_line := trim(l_responses(i));
	if l_line is null then
	continue;
	end if;
	l_response_json := json_object_t(l_line);
	l_id := l_response_json.get_string('id');
	l_custom_id := to_number(l_response_json.get_string('custom_id'));
	l_response := l_response_json.get_object('response');
	l_response_clob := l_response.to_clob();
	l_status_code := l_response.get_number('status_code');
	l_request_id := l_response.get_string('request_id');
	l_body := l_response.get_object('body');
	l_body_clob := l_body.to_clob();
	/*
	* bodyに含まれる最初のメッセージを取り出す。
	*/
	l_choices := l_body.get_array('choices');
	if l_choices is not null then
	l_choice := treat(l_choices.get(0) as json_object_t);
	if l_choice is not null then
	l_message := l_choice.get_object('message');
	if l_message is not null then
	l_content := l_message.get_clob('content');
	-- 最初の文字が { であれば、pretty printを行う。
	if l_content is not null and substr(l_content,1,1) = '{' then
	select json_serialize(l_content returning clob pretty) into l_content from dual;
	end if;
	end if;
	end if;
	end if;
	begin
	insert into openai_batch_responses(id, custom_id, response, status_code, request_id, body, first_message)
	values(l_id, l_custom_id, l_response_clob, l_status_code, l_request_id, l_body_clob, l_content);
	exception
	when dup_val_on_index then
	null;
	end;
	end loop;
	end parse_batch_response;
	/**
	* 継続中のバッチをキャンセルする。
	*/
	procedure cancel_batch(
	p_batch_id in varchar2 default null
	,p_submission_id in number default null
	,p_credential_static_id in varchar2
	)
	as
	l_url varchar2(400);
	l_batch_object clob;
	l_batch_object_json json_object_t;
	e_api_call_failed exception;
	l_input_file_id openai_batch_submissions.input_file_id%type;
	l_endpoint openai_batch_submissions.endpoint%type;
	l_completion_window openai_batch_submissions.completion_window%type;
	l_batch_id openai_batch_submissions.batch_id%type;
	l_output_file_id openai_batch_submissions.output_file_id%type;
	l_status openai_batch_submissions.status%type;
	e_no_required_argument exception;
	begin
	if p_batch_id is null then
	if p_submission_id is null then
	raise e_no_required_argument;
	end if;
	select batch_id into l_batch_id from openai_batch_submissions
	where submission_id = p_submission_id;
	else
	l_batch_id := p_batch_id;
	end if;
	l_url := apex_string.format('https://api.openai.com/v1/batches/%s/cancel', l_batch_id);
	apex_web_service.clear_request_headers();
	apex_web_service.set_request_headers('Content-Type', 'application/json');
	l_batch_object := apex_web_service.make_rest_request(
	p_url => l_url
	,p_http_method => 'POST'
	,p_credential_static_id => p_credential_static_id
	);
	if apex_web_service.g_status_code <> 200 then
	raise e_api_call_failed;
	end if;
	/*
	* submission_idの指定があるときは、OPENAI_BATCH_SUBMISSIONを更新する。
	*/
	if p_submission_id is not null then
	l_batch_object_json := json_object_t(l_batch_object);
	l_status := l_batch_object_json.get_string('status');
	update openai_batch_submissions set batch_object = l_batch_object, status = l_status
	where submission_id = p_submission_id;
	end if;
	end cancel_batch;
	end "UTL_OPENAI_BATCH_API";
	/

view raw utl_openai_batch_api.pkb hosted with ❤ by GitHub

バー・チャートのY軸のラベルを動的に変更する

バー・チャートにY軸のラベルを動的に変更する方法を紹介します。

以下の動画のように、選択リストで円またはドルを選択したときに、Y軸の表示を... 円または... ドルのように表示を変えます。以前に「チャートのプロパティを動的に変更する」という記事にて、Y軸の最大値の変更方法について紹介しています。今回の記事はラベルを変更するためにconverterを入れ替えています。APEXのチャートはOracle JETを使用していますが、プロパティが異なると変更の仕方も変わるようです。

今回作成したアプリケーションのエクスポートを以下に置きました。

https://github.com/ujnak/apexapps/blob/master/exports/sample-y-axis-unit-change.zip

バー・チャートのシリーズのSQL問合せとして、以下を設定しています。ページ・アイテムP1_CURRENCYはDOLLAR（表示値はドル）またはYEN（表示値は円）のどちらかを選択する選択リスト、P1_RATEは1ドルに相当する円レートを設定する数値フィールドです。

with price_v as (
    select 1599 price, 'notebook' product from dual
    union all
    select 999  price, 'smart phone' product from dual
    union all
    select 1299 price, 'tablet' product from dual
)
select
    case when :P1_CURRENCY = 'YEN' then
        ceil(price * :P1_RATE)
    else
        price
    end price,
    product
from price_v

Y軸がPRICEになります。yの値のパターンは# ドルとし、初期はドル表示とします。

ページ・アイテムP1_CURRENCYを変更したときに、動的アクションで以下のJavaScriptを実行します。クラスIntlNumberConverterはOracle JETが提供しているクラスです。プロパティconverterには、コンバータとなるクラス(IntlNumberConverterやIntlDataTimeConverter)のインスタンスを作成し、それをconverterに割り当てる必要があります。

let p = '# ' + apex.items.P1_CURRENCY.displayValueFor(
    apex.items.P1_CURRENCY.value
);

let currencyConverter = new oj.IntlNumberConverter({
        style: 'decimal',
        pattern: p
});
console.log('pattern is updated ', p);

let chart = apex.region("PRICE").widget();

chart.ojChart(
    'option',
    'yAxis.tickLabel.converter',
    currencyConverter
);
chart.ojChart(
    'option',
    'valueFormats.value.converter',
    currencyConverter
);
chart.ojChart(
    'option',
    'valueFormats.label.converter',
    currencyConverter
);

Y軸の表示（その他の設定はツール・チップ内での表示に使用されます）について、converterを設定すると表示が変わります。通貨が変わったことをチャート自体に反映させるため、後続の処理としてバー・チャートのリフレッシュを実行します。

Y軸のパターンの初期状態は# ドルですが、ページが再ロードされたときのP1_CURRENCYの値が（変更イベントが発生しないため）反映されません。動的アクションの初期化時に実行をオンにするという対処方法もありますが、今回はチャート・リージョンの初期化JavaScriptファンクションを記述することにより対応します。

以下のコードを記述します。

function( options ) {
    let p = '# ' + apex.items.P1_CURRENCY.displayValueFor(
        apex.items.P1_CURRENCY.value
    );

    let currencyConverter = {
        style: 'decimal',
        pattern: p
    };
    console.log('pattern is updated ', p);
    
    options.yAxis.tickLabel.converter    = currencyConverter;
    options.valueFormats.label.converter = currencyConverter;
    options.valueFormats.value.converter = currencyConverter;

    return options;
}

以上でアプリケーションは完成です。

チャートをカスタマイズするためには、Oracle JETのリファレンスを読み解く必要があります。今回の記事がOracle APEXのアプリケーション作成の参考になれば幸いです。

完