【PL/SQL】UTL_HTTPを使ったWeb API連携の実装方法

【PL/SQL】UTL_HTTPを使ったWeb API連携の実装方法 PL/SQL

Web APIとの連携をPL/SQLだけで完結させたい場合、UTL_HTTPは最も汎用的な選択肢です。GETでのデータ取得、POSTでのJSON送信、ヘッダー操作、タイムアウト、HTTPSやプロキシ、認証トークンの扱いまで一通りカバーできます。ここではACL設定から始め、GETとPOSTの実装、JSON連携、OAuthトークンの添付、エラー処理、TLSやプロキシ対応、ストリーム処理やファイル保存まで、実運用で必要になる要点を順に解説します。

前提準備とACL設定（外部接続の許可）

データベースから外部ホストへ接続するにはACLの設定が必須です。SMTPやHTTPなど接続先ごとにホスト名とポートを許可します。次の例ではapi.example.comの443番へAPP_USERからの接続を許可します。

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host         => 'api.example.com',
    lower_port   => 443,
    upper_port   => 443,
    ace          => xs$ace_type(
                      privilege_list => xs$name_list('connect','resolve'),
                      principal_name => 'APP_USER',
                      principal_type => xs_acl.ptype_db));
END;
/
GRANT EXECUTE ON UTL_HTTP TO APP_USER;

HTTPSに接続する場合、サーバ証明書の検証を通すためにOracle Walletへルート証明書を導入しておくと安全です。ウォレットのパスはUTL_HTTP.SET_WALLETで指定します。

最小のGETリクエスト（JSON取得）

GETはリクエストを作成し、必要なヘッダーを付け、レスポンスを読み切る構成です。返却ボディは行単位で読み込み、CLOBへ積み上げます。

DECLARE
  req   UTL_HTTP.req;
  resp  UTL_HTTP.resp;
  line  VARCHAR2(32767);
  body  CLOB;
BEGIN
  UTL_HTTP.SET_TRANSFER_TIMEOUT(15000); -- 15秒
  UTL_HTTP.SET_WALLET('file:/u01/app/wallet', 'walletpwd'); -- HTTPS用（必要な場合）

  req := UTL_HTTP.BEGIN_REQUEST('https://api.example.com/v1/users?active=true', 'GET', 'HTTP/1.1');
  UTL_HTTP.SET_HEADER(req, 'Accept', 'application/json');
  UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Oracle-UTL_HTTP/1.0');

  resp := UTL_HTTP.GET_RESPONSE(req);

  DBMS_LOB.CREATETEMPORARY(body, TRUE);
  LOOP
    UTL_HTTP.READ_LINE(resp, line, TRUE);
    DBMS_LOB.WRITEAPPEND(body, LENGTH(line||CHR(10)), line||CHR(10));
  END LOOP;
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY THEN
    NULL; -- 正常読み切り
  WHEN OTHERS THEN
    IF resp.status_code IS NOT NULL THEN
      DBMS_OUTPUT.PUT_LINE('HTTP STATUS='||resp.status_code);
    END IF;
    RAISE;
FINALLY
  UTL_HTTP.END_RESPONSE(resp);
  DBMS_OUTPUT.PUT_LINE('BODY LEN='||DBMS_LOB.GETLENGTH(body));
END;
/

読み込みはEND_OF_BODY例外で終了します。FINALLY節のようにEND_RESPONSEを必ず呼ぶ構造にしてリークを防ぎます。

POSTでJSONを送信する（Content-Typeとエンコーディング）

JSONを送る場合はUTF-8でContent-TypeとContent-Lengthを正しく指定します。UTL_HTTP.WRITE_TEXTは文字列を送信します。

DECLARE
  req   UTL_HTTP.req;
  resp  UTL_HTTP.resp;
  payload CLOB := '{"email":"user@example.com","name":"Sato"}';
BEGIN
  UTL_HTTP.SET_TRANSFER_TIMEOUT(15000);
  UTL_HTTP.SET_WALLET('file:/u01/app/wallet', 'walletpwd');

  req := UTL_HTTP.BEGIN_REQUEST('https://api.example.com/v1/users', 'POST', 'HTTP/1.1');
  UTL_HTTP.SET_HEADER(req, 'Content-Type', 'application/json; charset=UTF-8');
  UTL_HTTP.SET_HEADER(req, 'Accept', 'application/json');
  UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Oracle-UTL_HTTP/1.0');
  UTL_HTTP.SET_HEADER(req, 'Content-Length', TO_CHAR(DBMS_LOB.GETLENGTH(payload)));

  UTL_HTTP.WRITE_TEXT(req, payload);
  resp := UTL_HTTP.GET_RESPONSE(req);

  DBMS_OUTPUT.PUT_LINE('STATUS='||resp.status_code||' '||resp.reason_phrase);
  UTL_HTTP.END_RESPONSE(resp);
END;
/

大きなボディを送る場合はチャンクに分けてWRITE_TEXTするか、BLOBを扱うAPIで段階送信します。

Bearerトークン（OAuth 2.0）の添付と再試行設計

多くのAPIはAuthorizationヘッダーを要求します。期限切れを想定して401時の再取得と再送を用意すると堅牢です。

DECLARE
  req   UTL_HTTP.req;
  resp  UTL_HTTP.resp;
  token VARCHAR2(4000) := 'eyJhbGciOi...'; -- 取得済みアクセストークン
  url   VARCHAR2(4000) := 'https://api.example.com/v1/resources';
  line  VARCHAR2(32767);
BEGIN
  UTL_HTTP.SET_TRANSFER_TIMEOUT(15000);
  req := UTL_HTTP.BEGIN_REQUEST(url, 'GET', 'HTTP/1.1');
  UTL_HTTP.SET_HEADER(req, 'Accept', 'application/json');
  UTL_HTTP.SET_HEADER(req, 'Authorization', 'Bearer '||token);

  resp := UTL_HTTP.GET_RESPONSE(req);
  IF resp.status_code = 401 THEN
    UTL_HTTP.END_RESPONSE(resp);
    -- ここでトークン再取得処理を呼ぶ（別プロシージャ化を推奨）
    token := REFRESH_TOKEN_FUNCTION;
    req := UTL_HTTP.BEGIN_REQUEST(url, 'GET', 'HTTP/1.1');
    UTL_HTTP.SET_HEADER(req, 'Accept', 'application/json');
    UTL_HTTP.SET_HEADER(req, 'Authorization', 'Bearer '||token);
    resp := UTL_HTTP.GET_RESPONSE(req);
  END IF;

  LOOP
    UTL_HTTP.READ_LINE(resp, line, TRUE);
    DBMS_OUTPUT.PUT_LINE(line);
  END LOOP;
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY THEN
    NULL;
FINALLY
  UTL_HTTP.END_RESPONSE(resp);
END;
/

エラー処理とHTTPステータスの取り扱い

UTL_HTTPはネットワーク異常やタイムアウトで例外を投げますが、HTTPの4xx/5xxは例外ではなくstatus_codeとして返ります。業務上の再試行回数、待機時間、ログ出力を標準化しておくと分析が容易になります。

EXCEPTION
  WHEN UTL_HTTP.REQUEST_FAILED THEN
    DBMS_OUTPUT.PUT_LINE('REQUEST_FAILED '||SQLERRM);
    RAISE;
  WHEN UTL_HTTP.TRANSFER_TIMEOUT THEN
    DBMS_OUTPUT.PUT_LINE('TIMEOUT '||SQLERRM);
    RAISE;
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR '||SQLCODE||' '||SQLERRM);
    RAISE;

リダイレクト、圧縮、Keep-Aliveの勘所

HTTP 301や302などのリダイレクトはLocationヘッダーを読み、新URLで再度BEGIN_REQUESTします。gzip等の圧縮を受け入れるにはAccept-Encodingヘッダーを付与し、Content-Encodingを見て解凍を検討します（DB内での解凍は難しいためリバースプロキシで展開する運用が無難です）。Keep-AliveはHTTP/1.1で標準ですが、連続アクセス時の接続再利用はミドル側で吸収し、UTL_HTTPは都度の接続でも支障ないようタイムアウトを短めに設定します。

JSONの安全な組み立てと解析

JSONボディは文字列連結ではなくJSON_OBJECTまたはJSON_ARRAYAGGを使えばエスケープ事故を避けられます。レスポンス解析はJSON_VALUEやJSON_TABLEに渡すと表形式に展開できます。

DECLARE
  body CLOB;
  id   NUMBER;
  name VARCHAR2(50);
BEGIN
  SELECT JSON_OBJECT('email' VALUE 'user@example.com', 'name' VALUE 'Sato' RETURNING CLOB)
    INTO body FROM dual;

  -- レスポンスbodyをCLOBへ受けた後の解析例（変数resp_bodyを仮定）
  SELECT JSON_VALUE(resp_body, '$.id'), JSON_VALUE(resp_body, '$.name')
    INTO id, name
    FROM dual;
END;
/

ファイルダウンロードとBLOB保存（バイナリ対応）

バイナリを扱う場合はUTL_HTTP.READ_RAWでRAWを受け、BLOBへ書き込みます。Content-Lengthが分かるときはループ回数を見積もり、分からない場合はREAD_RAWが尽きるまで読みます。

DECLARE
  req   UTL_HTTP.req;
  resp  UTL_HTTP.resp;
  buf   RAW(32767);
  amt   BINARY_INTEGER := 32767;
  blob  BLOB;
BEGIN
  DBMS_LOB.CREATETEMPORARY(blob, TRUE);
  req := UTL_HTTP.BEGIN_REQUEST('https://api.example.com/files/report.pdf', 'GET', 'HTTP/1.1');
  UTL_HTTP.SET_HEADER(req, 'Accept', 'application/pdf');
  resp := UTL_HTTP.GET_RESPONSE(req);

  LOOP
    UTL_HTTP.READ_RAW(resp, buf, amt);
    DBMS_LOB.WRITEAPPEND(blob, UTL_RAW.LENGTH(buf), buf);
  END LOOP;
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY THEN
    NULL;
FINALLY
  UTL_HTTP.END_RESPONSE(resp);
  INSERT INTO file_store(id, content, mime) VALUES (file_seq.NEXTVAL, blob, 'application/pdf');
  COMMIT;
END;
/

プロキシ、自己署名証明書、社内APIの取り扱い

社内から外部へはプロキシ経由になることが多く、UTL_HTTP.SET_PROXYで指定できます。自己署名証明書を使う社内APIへは、そのCAをWalletへ登録しておくことで検証を通せます。テストでは検証を無効化したくなりますが本番での事故につながるため、必ず信頼済みCAを導入する方針に統一します。

UTL_HTTP.SET_PROXY('http://proxy.corp.local:8080');

共通ラッパーの実装と再利用（テンプレート）

毎回のヘッダー付与、タイムアウト、ウォレット、エラー処理を統一するため、薄いラッパーパッケージを用意します。これにより個別処理はURLとボディだけに集中できます。

CREATE OR REPLACE PACKAGE http_client AS
  FUNCTION get_json(p_url VARCHAR2, p_bearer VARCHAR2 := NULL) RETURN CLOB;
  FUNCTION post_json(p_url VARCHAR2, p_body CLOB, p_bearer VARCHAR2 := NULL) RETURN CLOB;
END;
/
CREATE OR REPLACE PACKAGE BODY http_client AS
  c_wallet CONSTANT VARCHAR2(200) := 'file:/u01/app/wallet';
  c_wpwd   CONSTANT VARCHAR2(100) := 'walletpwd';
  PROCEDURE set_common IS BEGIN
    UTL_HTTP.SET_TRANSFER_TIMEOUT(15000);
    UTL_HTTP.SET_WALLET(c_wallet, c_wpwd);
    -- UTL_HTTP.SET_PROXY('http://proxy.corp.local:8080'); -- 必要時
  END;
  FUNCTION get_json(p_url VARCHAR2, p_bearer VARCHAR2) RETURN CLOB IS
    req  UTL_HTTP.req; resp UTL_HTTP.resp; line VARCHAR2(32767); out CLOB;
  BEGIN
    set_common;
    req := UTL_HTTP.BEGIN_REQUEST(p_url, 'GET', 'HTTP/1.1');
    UTL_HTTP.SET_HEADER(req, 'Accept', 'application/json');
    UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Oracle-UTL_HTTP/1.0');
    IF p_bearer IS NOT NULL THEN
      UTL_HTTP.SET_HEADER(req, 'Authorization', 'Bearer '||p_bearer);
    END IF;
    resp := UTL_HTTP.GET_RESPONSE(req);
    DBMS_LOB.CREATETEMPORARY(out, TRUE);
    LOOP
      UTL_HTTP.READ_LINE(resp, line, TRUE);
      DBMS_LOB.WRITEAPPEND(out, LENGTH(line||CHR(10)), line||CHR(10));
    END LOOP;
  EXCEPTION
    WHEN UTL_HTTP.END_OF_BODY THEN
      NULL;
  FINALLY
    UTL_HTTP.END_RESPONSE(resp);
    RETURN out;
  END;
  FUNCTION post_json(p_url VARCHAR2, p_body CLOB, p_bearer VARCHAR2) RETURN CLOB IS
    req  UTL_HTTP.req; resp UTL_HTTP.resp; line VARCHAR2(32767); out CLOB;
  BEGIN
    set_common;
    req := UTL_HTTP.BEGIN_REQUEST(p_url, 'POST', 'HTTP/1.1');
    UTL_HTTP.SET_HEADER(req, 'Content-Type', 'application/json; charset=UTF-8');
    UTL_HTTP.SET_HEADER(req, 'Accept', 'application/json');
    IF p_bearer IS NOT NULL THEN
      UTL_HTTP.SET_HEADER(req, 'Authorization', 'Bearer '||p_bearer);
    END IF;
    UTL_HTTP.SET_HEADER(req, 'Content-Length', TO_CHAR(DBMS_LOB.GETLENGTH(p_body)));
    UTL_HTTP.WRITE_TEXT(req, p_body);
    resp := UTL_HTTP.GET_RESPONSE(req);
    DBMS_LOB.CREATETEMPORARY(out, TRUE);
    LOOP
      UTL_HTTP.READ_LINE(resp, line, TRUE);
      DBMS_LOB.WRITEAPPEND(out, LENGTH(line||CHR(10)), line||CHR(10));
    END LOOP;
  EXCEPTION
    WHEN UTL_HTTP.END_OF_BODY THEN
      NULL;
  FINALLY
    UTL_HTTP.END_RESPONSE(resp);
    RETURN out;
  END;
END;
/

トラブルシュートの勘所と運用ベストプラクティス

ACL未設定やDNS解決不可はORA-24247として現れ、タイムアウトはUTL_HTTP.TRANSFER_TIMEOUT、TLSエラーはORA-29024等のエラーで気づけます。まずはACL、ウォレット、プロキシ、ファイアウォールの順に切り分けます。運用ではAPIキーやトークンをDB内の暗号化ストアに保管し、権限を限定した専用スキーマからのみ参照させます。リトライは冪等なGETや安全なPOSTに限定し、重複実行の副作用を避けます。レスポンスはできる限り早めにJSON_TABLEへ展開し、ログには先頭数百文字だけを記録して機微情報の漏えいを防ぐと安全です。

まとめ

UTL_HTTPを使うと、PL/SQLから外部Web APIへ安全かつ柔軟にアクセスできます。ACLとウォレットを整え、GETとPOSTのテンプレート、Authorizationの付与、HTTPステータスに基づく再試行と例外処理、BLOB保存やJSON解析の流れを標準化すれば、アプリケーション層に頼らずデータベース内だけで堅牢な連携基盤を構築できます。まずは小さなラッパーを作り、運用要件（タイムアウト、監査ログ、秘密情報の保管）を一度で満たす作法をチーム内で統一することが成功の近道です。