日日是Oracle APEX: Oracle Autonomous DatabaseでREST対応SQLを基本認証で有効にする

2024年12月2日月曜日

Oracle Autonomous DatabaseでREST対応SQLを基本認証で有効にする

Oracle Autonomous DatabaseにREST対応SQLを使って、SELECT文の実行ができるように構成します。REST対応SQLのRESTサービスによって実行されたSELECT文の結果は、JSONドキュメントとして返されます。SELECT文を実行する都度REST APIの呼び出しが行われ、一般的なドライバのように接続を管理する必要はありません。

今回の記事では、REST対応SQLでアクセスするスキーマを作成します。スキーマはあらかじめデータベース・アクション（SQL Developer Web）で作成し、そのスキーマを既存のスキーマとして選択して、APEXのワークスペースを作成することにします。APEXのワークスペースの作成時に新規スキーマとして作成したスキーマの場合、データベース・アクションからサインインできない場合があります。おそらく設定が足りていないのだと思いますが、データベース・アクションからスキーマを作成すると、データベース・アクションからサインインできないということは発生しません。

Autonomous Databaseの管理者ユーザーADMINにて、データベース・アクションにサインインします。

サインインしたら、管理のデータベース・ユーザーを呼び出します。

ユーザーの作成をクリックし、新規にデータベース・ユーザーを作成します。

ユーザー名はAPEXの命名書式に合わせてWKSP_MCPとします。必ずしも、接頭辞としてWKSP_つける必要はありません。そして、パスワードを設定します。このユーザー名とパスワードがREST対応SQLの呼び出し時の基本認証に与えるユーザー名とパスワードになります。

表領域DATA（Autonomous Databaseの場合、ユーザー向けの表領域がDATAです）の割当て制限はUNLIMITEDにしています。

REST対応SQLを有効にするため、Webアクセスはオン、承認が必要もオンにします。REST別名はWKSP_を除いてmcpにします。これはREST対応SQLのエンドポイントのURLに含まれます。また、後で作成するAPEXワークスペース名もmcpにします。

作成するユーザーにはデフォルトでロールCONNECTをRESOURCEがグラントされます。

以上でユーザーの作成を行います。

コードの表示をオンにすると、実際に実行されるコマンドを確認できます。作業を自動化したい場合などに有効です。

ユーザーWKSP_MCPが作成されれば、データベース・アクションでの作業は完了です。

この時点で、REST対応SQLによるSELECT文の実行ができます。

curlを使ってselect sysdate from dualを実行してみます。

curl -i -X POST -u wksp_mcp:[パスワード] https://[ID]-[インスタンス名].adb.[リージョン].oraclecloudapps.com/ords/mcp/_/sql -H "Content-Type: application/sql" --data-binary 'select sysdate from dual'

% curl -i -X POST -u wksp_mcp:********** https://***********-apexdev.adb.us-ashburn-1.oraclecloudapps.com/ords/mcp/_/sql -H "Content-Type: application/sql" --data-binary 'select sysdate from dual'

HTTP/1.1 200 OK

Date: Mon, 02 Dec 2024 09:15:53 GMT

Content-Type: application/json

Transfer-Encoding: chunked

Connection: keep-alive

Strict-Transport-Security: max-age=31536000;includeSubDomains

X-Content-Type-Options: nosniff

X-Frame-Options: SAMEORIGIN

{"env":{"defaultTimeZone":"UTC"},"items":[{"statementId":1,"statementType":"query","statementPos":{"startLine":1,"endLine":2},"statementText":"select sysdate from dual","resultSet":{"metadata":[{"columnName":"SYSDATE","jsonColumnName":"sysdate","columnTypeName":"DATE","columnClassName":"java.sql.Timestamp","precision":7,"scale":0,"isNullable":1}],"items":[{"sysdate":"2024-12-02T09:15:53Z"}],"hasMore":false,"limit":10000,"offset":0,"count":1},"response":[],"result":0}]}% %

続いて、APEXのワークスペースを作成します。

Oracle APEXの画面に接続し、管理サービスを呼び出します。

ユーザーADMIN（Autonomous Databaseの管理ユーザー）のパスワードを入力し、APEXの管理サービスにサインインします。

管理サービスよりワークスペースの作成をクリックします。

新規のスキーマを選択します。

データベース・ユーザーとして、先ほど作成したWKSP_MCPを選択します。ワークスペース名はREST別名と同じMCPをにします。ワークスペース・ユーザー名、ワークスペース・パスワードとして、ワークスペースにサインインするユーザーを指定します。このユーザーはワークスペースの管理者ユーザーになります。Autonomous Databaseの場合は、（パスワード管理をAPEXではなくデータベースで行うようにするため）データベース・ユーザーとして作成されます。今回はワークスペース・ユーザー名はMCPDEVとしています。

指定したワークスペース・ユーザーがすでにデータベース・ユーザーとして存在する場合は、パスワードは設定済みのものが優先されます。

以上でワークスペースの作成を実行します。

APEXのワークスペースとしてMCPが作成されます。

リンクMCPにアクセスし、ワークスペースMCPにサインインします。

サインインには、ワークスペース作成時に指定したワークスペース・ユーザー名、ワークスペース・パスワードを指定します。

検証に使用するデータをスキーマWKSP_MCPにインストールします。APEXのサンプル・データセットより国のデータをインストールします。

APEXの開発ツールにサインインしたら、SQLワークショップのユーティリティのサンプル・データセットを開きます。

サンプル・データセットの国のインストールをクリックします。

次をクリックします。

データセットのインストールを実行します。

EBA_COUNTRY_REGIONS、EBA_COUNTRY_SUB_REGIONSといった表がスキーマWKSP_MCPにインストールされます。

表にインストールが完了しました。終了をクリックします。

これらの表を対象としたREST対応SQL、つまりREST APIによるSELECT文の実行と、実行結果としてのJSONドキュメントの取り出しが可能になりました。

確認のため、以下のSELECT文をREST対応SQLとして実行してみます。

select * from eba_country_regions

curlコマンドは以下になります。

curl -i -X POST -u wksp_mcp:[パスワード] https://[ID]-[インスタンス}.adb.[リージョン].oraclecloudapps.com/ords/mcp/_/sql -H "Content-Type: application/sql" --data-binary 'select * from eba_country_regions'

% curl -i -X POST -u wksp_mcp:********** https://**************-apexdev.adb.us-ashburn-1.oraclecloudapps.com/ords/mcp/_/sql -H "Content-Type: application/sql" --data-binary 'select * from eba_country_regions'

HTTP/1.1 200 OK

Date: Mon, 02 Dec 2024 09:30:19 GMT

Content-Type: application/json

Transfer-Encoding: chunked

Connection: keep-alive

Strict-Transport-Security: max-age=31536000;includeSubDomains

X-Content-Type-Options: nosniff

X-Frame-Options: SAMEORIGIN

{"env":{"defaultTimeZone":"UTC"},"items":[{"statementId":1,"statementType":"query","statementPos":{"startLine":1,"endLine":2},"statementText":"select * from eba_country_regions","resultSet":{"metadata":[{"columnName":"ID","jsonColumnName":"id","columnTypeName":"NUMBER","columnClassName":"java.math.BigDecimal","precision":0,"scale":-127,"isNullable":0},{"columnName":"NAME","jsonColumnName":"name","columnTypeName":"VARCHAR2","columnClassName":"java.lang.String","precision":255,"scale":0,"isNullable":0}],"items":[{"id":10,"name":"America"},{"id":20,"name":"Europe"},{"id":30,"name":"Asia"},{"id":40,"name":"Oceania"},{"id":50,"name":"Africa"}],"hasMore":false,"limit":10000,"offset":0,"count":5},"response":[],"result":0}]}%

整形した結果を以下になります。

{
  "env": {
    "defaultTimeZone": "UTC"
  },
  "items": [
    {
      "statementId": 1,
      "statementType": "query",
      "statementPos": {
        "startLine": 1,
        "endLine": 2
      },
      "statementText": "select * from eba_country_regions",
      "resultSet": {
        "metadata": [
          {
            "columnName": "ID",
            "jsonColumnName": "id",
            "columnTypeName": "NUMBER",
            "columnClassName": "java.math.BigDecimal",
            "precision": 0,
            "scale": -127,
            "isNullable": 0
          },
          {
            "columnName": "NAME",
            "jsonColumnName": "name",
            "columnTypeName": "VARCHAR2",
            "columnClassName": "java.lang.String",
            "precision": 255,
            "scale": 0,
            "isNullable": 0
          }
        ],
        "items": [
          {
            "id": 10,
            "name": "America"
          },
          {
            "id": 20,
            "name": "Europe"
          },
          {
            "id": 30,
            "name": "Asia"
          },
          {
            "id": 40,
            "name": "Oceania"
          },
          {
            "id": 50,
            "name": "Africa"
          }
        ],
        "hasMore": false,
        "limit": 10000,
        "offset": 0,
        "count": 5
      },
      "response": [],
      "result": 0
    }
  ]
}

Claude 3.5 SonnetにREST対応SQLをFetch APIで呼び出し、JSONを取得するコードを書いてもらいました。Content-Typeとbodyの指定は変更しましたが、それ以外はそのまま使えました。

const fetchDataWithBasicAuth = async (url, data, username, password) => {
  // Basic認証の資格情報をBase64エンコード
  const credentials = btoa(`${username}:${password}`);

  try {
    const response = await fetch(url, {
      method: 'POST',
      headers: {
        'Authorization': `Basic ${credentials}`,
        'Content-Type': 'application/sql',
        'Accept': 'application/json'
      },
      body: data
    });

    // レスポンスが正常でない場合はエラーをスロー
    if (!response.ok) {
      throw new Error(`HTTP error! status: ${response.status}`);
    }

    // JSONレスポンスをパース
    const jsonData = await response.json();
    return jsonData;

  } catch (error) {
    console.error('リクエストエラー:', error);
    throw error;
  }
};

// 使用例
const url = 'https://************-apexdev.adb.us-ashburn-1.oraclecloudapps.com/ords/mcp/_/sql';
const postData = 'select sysdate from dual';

// 関数の呼び出し
fetchDataWithBasicAuth(url, postData, 'wksp_mcp', '**********')
  .then(data => {
    console.log('取得したデータ:', data);
  })
  .catch(error => {
    console.error('エラー発生:', error);
  });

ブラウザで確認しました。ブラウザでの実行ではCORSのエラーが発生するため、ADBにアクセスして実行しています。

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

後はこの呼び出しをTypeScript版のMCPサーバーに組み込むことができれば、Oracle Databaseに問い合わせるMCPサーバーができるはず。

完