日日是Oracle APEX: 東京都のオープンデータAPIを呼び出してデータを取得する

2025年1月24日追記：Oracle APEX 24.2では、RESTデータ・ソースでトークンによるページネーションがサポートされました。そのため、cursorおよびendCursorを使ったPL/SQLのコーディングは不要になりました。APEX 24.2の記事はこちらです。

Oracle APEX 21.2のREST関連の新機能を紹介する記事を書く際に、最初はe-StatのAPIではなく東京都のオープンデータAPIを使うつもりでした。

東京都のAPIではレスポンスに含まれるendCursorの値を次のリクエストのパラメータcursorに与えることによりページ送りを実現しています。これは、Oracle APEXのRESTの新機能では扱うことができません。構造的にもフラットではなく、そのままリレーショナル・データベースの表に投入するのは難しいです。

設定ではできないため、東京都スポーツ施設一覧のAPIを呼び出して得られるデータを表に保存するコードを書いてみました。以下、その作業ログです。

最初に応答を保存する３つの表を作成します。クイックSQLの以下の定義を使いました。

# semantics: default
tky_od_requests
    requested_url  vc200
    requested_date date /default sysdate

tky_od_objects
    request_id number /fk tky_od_requests
    object json

tky_od_facilities
    facility_name vc100
    organization  vc200
    access_route  json
    address       vc200
    latitude      number
    longitude     number
    contact       json
    equipments    json
    types         json
    events        json
    numbers       json
    note          vc400
    capacity      json

表TKY_OD_REQUESTSには呼び出したオープンデータAPIのURLと、その日付を保存します。表TKY_OD_OBJECTSには、APIを呼び出して得られたデータをJSONのまま保存します。表TKY_OD_FACILITIESにはスポーツ施設の情報を施設ごとに保存します。

最初にAPIを呼び出し取得したスポーツ施設ひとつあたり、ひとつのJSONオブジェクトとして保存します。すべての施設のデータを読み出すまで、複数回のAPI呼び出しを行います。

	declare
	-- APIリクエスト関連
	l_loop_count_limit pls_integer := 6; -- データ量が多い場合はもっと大きい値に変更する。ただし、この値よりlimitを大きくするのが先。
	l_request_url constant varchar2(200) := 'https://api.data.metro.tokyo.lg.jp/v1/SportsFacility';
	l_request_url_actual varchar2(400);
	l_body blob;
	l_array json_array_t;
	l_id number;
	-- ページ制御関連
	l_page json_object_t;
	l_cursor varchar2(200) := '';
	l_cursor_prev varchar2(200) := ''; -- 前回のカーソル値を保持
	l_completed boolean := FALSE;
	-- データ関連
	l_obj_array json_array_t;
	l_obj json_object_t;
	l_obj_count pls_integer;
	l_obj_blob blob;
	begin
	insert into tky_od_requests(requested_url) values(l_request_url) returning id into l_id;
	apex_web_service.g_request_headers.delete();
	apex_web_service.g_request_headers(1).name := 'Content-Type';
	apex_web_service.g_request_headers(1).value := 'application/json';
	<<outer_loop>>
	while true
	loop
	-- 大量のリクエストの発行を禁止する。
	l_loop_count_limit := l_loop_count_limit - 1;
	if l_loop_count_limit < 0 then
	dbms_output.put_line('Request terminated.');
	exit;
	end if;
	-- オープンデータを取得するAPIの発行。 limitは10だが、試験以外ではもっと大きくする。
	l_request_url_actual := l_request_url \|\| '?limit=10';
	if l_cursor is not null then
	l_request_url_actual := l_request_url_actual \|\| '&cursor=' \|\| apex_util.url_encode(l_cursor);
	end if;
	l_body := apex_web_service.make_rest_request_b(
	p_url => l_request_url_actual
	, p_http_method => 'GET'
	);
	-- 東京のオープンデータAPIのレスポンスはJSONの配列。
	l_array := json_array_t.parse(l_body);
	if l_array.get_size() <> 2 then -- 配列の要素数は２であるはず。
	dbms_output.put_line('Number of elements should be 2');
	exit;
	end if;
	-- データの配列と未取得の情報の有無が返される。
	<<inner_loop>>
	for i in 0..1
	loop
	if l_array.get(i).is_array then
	-- 配列であれば、含まれるオブジェクトを１行として保存する。
	l_obj_array := treat(l_array.get(i) as json_array_t);
	l_obj_count := l_obj_array.get_size();
	for j in 0..(l_obj_count - 1)
	loop
	l_obj := treat(l_obj_array.get(j) as json_object_t);
	l_obj_blob := l_obj.to_blob;
	insert into tky_od_objects(request_id, object) values(l_id, l_obj_blob);
	end loop;
	else
	-- オブジェクトであれば未取得の情報の有無を確認する。
	l_page := treat(l_array.get(i) as json_object_t);
	if l_page.get_string('moreResults') = 'MORE_RESULTS_AFTER_LIMIT' then
	l_cursor_prev := l_cursor;
	l_cursor := l_page.get_string('endCursor');
	if l_cursor_prev = l_cursor then
	dbms_output.put_line('previous cursor and current cursor should be different, exit.');
	exit outer_loop;
	end if;
	else
	-- これが最後のデータ。
	l_completed := TRUE;
	end if;
	end if;
	end loop;
	-- 最後のリクエストであればループを抜ける。
	if l_completed then
	dbms_output.put_line('Request completed.');
	exit;
	end if;
	end loop;
	commit;
	end;

view raw gistfile1.txt hosted with ❤ by GitHub

表TKY_OD_OBJECTSに保存されたJSONのデータをパースして、表TKY_OD_FACILITIESへ入れ替えます。

	declare
	l_object json_object_t;
	l_sports json_object_t;
	r_fac tky_od_facilities%rowtype;
	l_coo json_object_t;
	-- JSON要素を文字列として返す。
	function elm(
	p_object in json_object_t,
	p_name in varchar2
	) return varchar2
	is
	l_elem json_element_t;
	begin
	l_elem := p_object.get(p_name);
	if l_elem is not null then
	return l_elem.to_string;
	end if;
	return '';
	end;
	-- 文字列を返す。
	function str(
	p_object in json_object_t,
	p_name1 in varchar2,
	p_name2 in varchar2
	) return varchar2
	is
	l_obj json_object_t;
	begin
	l_obj := p_object.get_object(p_name1);
	if l_obj is not null then
	return l_obj.get_string(p_name2);
	end if;
	return '';
	end;
	begin
	for r in (select id, object from tky_od_objects) -- 必要に応じてwhere request_id = の条件をつける
	loop
	l_object := json_object_t.parse(r.object);
	r_fac.facility_name := str(l_object,'名称','表記');
	r_fac.organization := str(l_object,'管理者','表記');
	r_fac.access_route := elm(l_object,'アクセス');
	r_fac.address := str(l_object,'住所','表記');
	r_fac.contact := elm(l_object,'連絡先');
	l_coo := l_object.get_object('地理座標');
	if l_coo is not null then
	r_fac.latitude := l_coo.get_number('緯度');
	r_fac.longitude := l_coo.get_number('経度');
	else
	r_fac.latitude := null;
	r_fac.longitude := null;
	end if;
	l_sports := l_object.get_object('ex:スポーツ設備');
	r_fac.equipments := elm(l_sports,'設備');
	r_fac.types := elm(l_sports,'種別');
	r_fac.events := elm(l_sports,'ex:目的種目');
	r_fac.numbers := elm(l_sports,'ex:数量属性');
	r_fac.note := l_sports.get_string('備考');
	r_fac.capacity := elm(l_sports,'収容人数');
	insert into tky_od_facilities values r_fac;
	end loop;
	commit;
	end;