ラベル CSV の投稿を表示しています。 すべての投稿を表示
ラベル CSV の投稿を表示しています。 すべての投稿を表示

2025年6月13日金曜日

Oracle REST Data ServicesのBatch Loadを呼び出し表にCSVをアップロードする

Oracle REST Data Servicesで表のAutoRESTを有効にすると、Batch LoadというCSVファイルをアップロードする機能が使えます。

Oracle REST Data Services Developer's Guide Release 25.1

公式のドキュメント以外では、以下のブログ記事でBatch Loadが紹介されています。

Move Large Volumes of Data Over REST with APEX & ORDS

Bulk Load an Oracle Table from CSV via REST
Batch Loading CSV to Oracle Database, Again, via REST APIs
Batch Loadは手元のCSVファイルの内容を、AutoRESTを有効にしたデータベースの表にアップロードする機能です。SQLclのLOADコマンドやその他の方法と比較して特別に速いというわけではありませんが、curlコマンドが実行できればデータのロードができるという手軽さがあります。

以下より、Batch Loadを実際に試してみます。

APEXの対話モード・レポートからダウンロードしたCSVファイルを、AutoRESTを有効にした表にアップロードしてみます。

対話モード・レポートのページを持つAPEXアプリケーションを作成します。

アプリケーション・ビルダーよりアプリケーションの作成を開始します。

名前はCar Accidentsとして、ファイルからのアプリケーションの作成をクリックします。


コピー・アンド・ペーストを選択し、サンプルのCSVデータとして車両速度を選択します。Posted Speed, Vehical Speed, Date of Incedent, License Plate, Location, Stateといったデータが貼り付けられます。Vehical、Incedentはスペルが間違っていますが、列名は後で日本語にするので、そのまま作業します。

次へ進みます。


データをロードする表名はCAR_ACCIDENTSとします。

データのロードをクリックします。


610行のデータが表CAR_ACCIDENTSに保存されます。

アプリケーションの作成をクリックします。


アプリケーション作成ウィザードが起動します。

今回はCar Accidentsレポートの対話モード・レポートのページだけを使用します。その他のページは削除します。


ページをCar Accidentsレポートだけにして、アプリケーションの作成をクリックします。


アプリケーションCar Accidentsが作成されます。

ダウンロードされるCSVファイルの文字エンコーディングを設定します。

アプリケーション定義を開きます。


グローバリゼーションを開きます。

アプリケーションのプライマリ言語が日本語(ja)の場合、自動CSVエンコーディングをオンにするとダウンロードするCSVファイルの文字エンコーディングはShift-JISになります。オフの場合はUTF-8です。

今回はORDSにアップロードします。CSVファイルの文字エンコーディングはUTF-8が都合が良いため、自動CSVエンコーディングをオフにして変更を適用します。


CSVファイルをダウンロードするのに使用する対話モード・レポートのページを編集します。


ダウンロードするCSVファイルの名前を設定します。

リージョンCar Accidentsの属性タブを開き、ダウンロードのファイル名に以下を設定します。

car_accidents-&SYSDATE_YYYYMMDD.

この設定により、対話モード・レポートからダウンロードしたCSVファイルのファイル名はcar_accidents-20250613.csvのようになります。

ファイル名が設定されていないと、リージョンの識別の名前がファイル名として使用されます。今回の例ではCar Accidents.csvとなります。ファイル名に空白が含まれることになり、扱いにくくなります。


対話モード・レポートの列のヘッダーが、ダウンロードされるCSVファイルの列名になります。

列POSTED_SPEEDのヘッダーは制限速度とします。


同様に列VEHICAL_SPEEDは車両速度、列DATE_OF_INCEDENTは事故発生日、列LICENSE_PLATEはナンバープレート、列LOCATIONは場所、列STATEは州とします。

以上を設定してアプリケーションを実行します。

対話モード・レポートのヘッダーは、以下のように日本語で表示されます。


ページ・デザイナに戻り、列IDのタイプをプレーン・テキストに変更します。

タイプが非表示の場合、ダウンロードするCSVファイルに列として含めることができなくなります。


対話モード・レポートの作成後に表示可能にした列は、最初はレポート上で非表示になります。

ダウンロードの対象にするには、列を表示する必要があります。

アクションの列を開きます。


表示しないにあるIDをレポートに表示に移動し、適用をクリックします。


レポート上にID、制限速度、車両速度、事故発生日、ナンバープレート、場所、州が表示されます。


対話モード・レポートに一覧されているデータを、CSVファイルにダウンロードします。

アクションのダウンロードを開きます。


レポート形式としてCSVを選択し、ダウンロードをクリックします。


結果として以下の内容を含むファイルが、car_accidents-20250613.csvのような日付付きのファイル名でダウンロードされます。

ID,制限速度,車両速度,事故発生日,ナンバープレート,場所,州

473,25,23,2025/05/30,6EFE807,School Road,VA

483,25,27,2025/05/14,E80455B2,School Road,VA

488,25,21,2025/05/31,EFE80456,School Road,VA

494,25,43,2025/05/18,E804564,School Road,VA

497,25,20,2025/05/13,EFE80455,School Road,VA

506,25,33,2025/05/16,E063A15D6,School Road,VA

522,25,22,2025/05/13,2EDF6E2,School Road,DC


[略]


このCSVファイルのアップロード先となる表を作成します。

表の名前はCAR_ACCIDENTS_JAとします。以下のDDLを実行します。

主キー列のIDはデータのインサート時に値を設定できるようにby default on nullを付けます。列名はCSVのヘッダーに一致するように日本語にします。
create table car_accidents_ja
(
    "ID"             number generated by default on null as identity not null,
    "制限速度"        number,
    "車両速度"        number,
    "事故発生日"      date,
    "ナンバープレート" varchar2(50),
    "場所"           varchar2(50),
    "州"             varchar2(50),
    primary key ("ID") using index enable
);


作成した表CAR_ACCIDENTS_JAのAutoRESTを有効にします。

プロシージャORDS.ENABLE_OBJECTを実行します。
begin
    ords.enable_object(
        p_enabled => true
        ,p_schema => '表CAR_ACCIDENTS_JAがあるスキーマ'
        ,p_object => 'CAR_ACCIDENTS_JA'
        ,p_object_type => 'TABLE'
        ,p_object_alias => 'car_accidents_ja'
        ,p_auto_rest_auth => false
    );
end;
今回は検証ということで、p_auto_rest_authはfalseとしています。実際はtrueを設定し、必ずAutoRESTの呼び出しを保護します。


表CAR_ACCIDENTS_JAのAutoRESTに関する設定を確認します。

select * from user_ords_objects where parsing_object = 'CAR_ACCIDENTS_JA'


ダウロードしたCSVファイルを、Batch Loadを呼び出して表CAR_ACCIDENTS_JAにアップロードします。

Batch Loadのエンドポイントは以下の形式になります。

https://[ホスト名]/ords/[ワークスペース名]/[オブジェクト別名]/batchload

このエンドポイントはSQL Developer Webを開いて確認することができます。


SQLワークシートのナビゲータより対象の表でコンテキスト・メニューを開き、RESTからcURLコマンド...を実行します。


AutoRESTとして呼び出せる操作と、それに対応したcURLコマンドの例が表示されます。


以下のcurlコマンドを実行します。オプションとしてbatchRows=100およびdateFormat=YYYY/MM/DDを与えています。Batch Loadのオプションについては、ドキュメントを参照してください。

curl -H"Content-Type: text/csv" --data-binary @[CSVファイル名] 'https://[ホスト名]/ords/[ワークスペース名]/car_accidents_ja/batchload?batchRows=100&dateFormat=YYYY/MM/DD'

% curl -H"Content-Type: text/csv" --data-binary @car_accidents-20250613.csv 'https://*********-apexdev.adb.us-ashburn-1.oraclecloudapps.com/ords/apexdev/car_accidents_ja/batchload?batchRows=100&dateFormat=YYYY/MM/DD'

#INFO Number of rows processed: 610

#INFO Number of rows in error: 0

#INFO Last row processed in final committed batch: 610

SUCCESS: Processed without errors

% 


表CAR_ACCIDENTSに保存されている行数が610なので、#INFO Number of rows processed: 610と返されれば、すべての行がアップロードできています。

確認のためアップロードしたデータをダウンロードします。AutoRESTのGET ALLを呼び出し、JSON形式でファイルに保存します。ページングをせずに全行を取得するためlimit=1000を指定します。

curl -o car_accidents.json 'https://[ホスト名]/ords/[ワークスペース名]/car_accidents_ja/limit=1000'

% curl -o car_accidents.json 'https://**********-apexdev.adb.us-ashburn-1.oraclecloudapps.com/ords/apexdev/car_accidents_ja/?limit=1000'                     

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current

                                 Dload  Upload   Total   Spent    Left  Speed

100  176k    0  176k    0     0   142k      0 --:--:--  0:00:01 --:--:--  142k

% 


ダウンロードしたJSONをjqコマンドでCSV形式に変換します。

jq -r ' .items[] | [ .id, ."制限速度", ."車両速度", ."事故発生日", ."ナンバープレート", ."場所", ."州" ] | @csv ' car_accidents.json > result.csv

% jq -r ' .items[] | [ .id, ."制限速度", ."車両速度", ."事故発生日", ."ナンバープレート", ."場所", ."州" ] | @csv ' car_accidents.json > result.csv

% 


生成したCSVファイルresult.csvの内容を確認します。行数は610になります。

wc -l result.csv
head -10 result.csv

% wc -l result.csv

     610 result.csv

% head -10 result.csv 

160,55,48,"2025-05-30T00:00:00Z","EDF6E06","Highway 5","VA"

162,55,52,"2025-05-28T00:00:00Z","FE80443","Highway 5","VA"

166,55,55,"2025-05-13T00:00:00Z","EDF6E0631","Highway 5","VA"

169,55,63,"2025-06-06T00:00:00Z","76EFE8045","Highway 5","VA"

170,55,53,"2025-05-18T00:00:00Z","4422ED8","Highway 5","VA"

171,55,51,"2025-06-03T00:00:00Z","063A15D7","Highway 5","VA"

172,55,50,"2025-06-07T00:00:00Z","24EDF6E8","Highway 5","VA"

174,55,59,"2025-05-30T00:00:00Z","E804424","Highway 5","VA"

175,55,48,"2025-06-08T00:00:00Z","6EFE80446","Highway 5","VA"

177,55,57,"2025-06-09T00:00:00Z","E063A158","Highway 5","PA"

% 


AutoRESTを保護した場合、つまりORDS.ENABLE_OBJECTの実行時にp_auto_rest_authにtrueを指定した場合は、p_schemaに指定したスキーマをユーザー名、そのスキーマのパスワードをパスワードとして、REST呼び出しを認証します。
begin
    ords.enable_object(
        p_enabled => true
        ,p_schema => '表CAR_ACCIDENTS_JAがあるスキーマ'
        ,p_object => 'CAR_ACCIDENTS_JA'
        ,p_object_type => 'TABLE'
        ,p_object_alias => 'car_accidents_ja'
        ,p_auto_rest_auth => true
    );
end;
curlコマンドでは-uまたは--userオプションでユーザー名、パスワードを指定します。結果としてREST APIとして発行されるHTTPリクエストにBasic認証が設定されます。

curl -u スキーマ名:パスワード  ....

OAuth2のクライアントクリデンシャル認証でも保護できますが、その場合はcurlコマンドで簡単にBatch Loadを呼び出すということは難しくなります。そのため、実際にはBatch LoadでCSVファイルをアップロードする表は、それ用のスキーマを作成して最小権限で運用することになりそうです。

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

完

2024年2月9日金曜日

APEX_REGION.OPEN_QUERY_CONTEXT呼び出し時にp_outer_sqlを指定して出力する列を選択する

APEXのPL/SQL APIとして提供されているAPEX_REGION.OPEN_QUERY_CONTEXTの引数のひとつにp_outer_sqlがあります。p_outer_sqlにリージョンのデータ・ソースを囲む形で記述したSELECT文を与えることにより、APEX_REGION.OPEN_QUERY_CONTEXTの出力結果を置き換えることができます。

このp_outer_sqlの使い方のひとつを、以前に記事「ファセットによる検索結果の合計や平均を計算する」で紹介しています。ファセットを適用した検索結果の列SALおよびCOMMの合計、平均、最小値、最大値、件数を別のレポートに表示しています。この例ではAPEX_REGION.OPEN_QUERYに引数p_outer_sqlを指定して、その結果をパイプライン表関数とする、少々複雑な実装を行っています。

本記事では引数p_outer_sqlの、もう少し簡単な使用例を紹介します。こちらの記事「ファセット検索の検索結果をAPEX_REGION.OPEN_QUERY_CONTEXTを呼び出してCSV形式でダウンロードする」にて、ファセット検索の結果をCSVでダウンロードする実装を紹介しています。このダウンロード処理に、引数p_outer_sqlを使って以下の機能を追加します。
  1. CSVへ出力する列を選択する。
  2. 列MGRとDEPTNOの出力を名前ではなく、生データの数値にする。
出力列を選択するページ・アイテムをP3_PRINT_COLUMNSとして作成します。


ページ・アイテムP3_PRINT_COLUMNSには、以下の設定を行います。

タイプはチェックボックス・グループです。ラベルはPrint Columnsとしていますが、外観のテンプレートにHiddenを選択しているため表示はされません。レイアウトのラベル列スパンを0にして、テンプレート・オプションのStretch From Itemをチェックします。列の数を10としているため(列の数は8)、選択する列は横一列で、画面の左右いっぱいに表示されます。

セッション・ステートのストレージはセッションごと(永続)を選びます。ダウンロード処理を実行するAjaxコールバックのプロセスに引数を渡すことができないため、選択した列を保存しているページ・アイテムP3_PRINT_COLUMNSの値は、変更が発生したときにセッション・ステートに保存します。Ajaxコールバックはセッション・ステートに保存されたページ・アイテムP3_PRINT_COLUMNSの値を参照します。


LOVのタイプは静的値として、表示値と戻り値を設定します。


戻り値は必ずレポート列の列名にします。


ページ・アイテムP3_PRINT_COLUMNSに動的アクションを作成します。タイミングのイベントとして変更を選択します。


TRUEアクションとしてサーバー側のコードを実行を選択します。送信するアイテムにP3_PRINT_COLUMNSを指定し、この動的アクションが呼び出された時に、画面上のページ・アイテムP3_PRINT_COLUMNSの値をデータベースに送信します。データベースは受信した値をセッション・ステートに保存します。ページ・アイテムの値をセッション・ステートに保存するだけであれば、PL/SQLコードの実行は不要です。PL/SQLコードを空にはできないため、null;を記述します。


CSVのダウンロードを実行するPL/SQLコードに、引数p_outer_sqlの指定を追加します。



p_outer_sqlに以下を渡し、出力する列としてEMPNO、Employee Name、Job、Manager、Departmentを選択してCSVダウンロードを実行します。チェックボックス・グループで複数選択された値は':'(コロン)で区切られています。SELECT文の列として認識されるために','(カンマ)に置き換えています。

l_outer_sql := 'select ' || replace(:P3_PRINT_COLUMS,':',',') || ' from #APEX$SOURCE_DATA#';

置換文字列#APEX$SOURCE_DATA#は、指定したリージョン自体の問い合わせに置き換えられます。この結果セットは、レポート・リージョンに定義された列を持ちます。


以下のCSVファイルがダウンロードされます。


p_outer_sqlに以下を渡すと、列MGRとDEPTNOが数値になります。

l_outer_sql := 'select ' || replace(:P3_PRINT_COLUMS,':',',') || ' from emp where empno in (select empno from #APEX$SOURCE_DATA#)';


APEX_REGION.OPEN_QUERY_CONTEXTの引数p_outer_sqlの使い方の紹介は以上になります。

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

完

2024年2月6日火曜日

ファセット検索の検索結果をAPEX_REGION.OPEN_QUERY_CONTEXTを呼び出してCSV形式でダウンロードする

ファセット検索の検索条件が適用された状態で、クラシック・レポートをCSV形式でダウンロードする方法について、以前に以下の記事を書いています。

ファセット検索の結果をCSV形式でダウンロードする

上記では、クラシック・レポートの問合せコンテキストを取得し、パイプライン表関数を作っています。

最近のAPEXではAPIとして、APEX_REGION.OPEN_QUERY_CONTEXTが提供されています。このファンクションを使用することにより、面倒なパイプライン表関数の作成が不要になります。

サンプル・データセットのEMPから作成できるDemonstration - EMP / DEPT のアプリケーションに含まれているファセット検索のページに、CSV形式のダウンロードを行うボタンを実装してみます。

ファセット検索のページはページ番号3です。

ファセット検索のフィルタ済みリージョンとして指定されているクラシック・レポートのリージョンに静的IDを設定します。APEX_REGION.OPEN_QUERY_CONTEXTの引数p_region_idとして与えるリージョンIDを、ビューAPEX_APPLICATION_PAGE_REGIONSから取り出すために使用します。

今回はEMPLOYEESとしています。


Ajaxコールバックとして、CSV形式のダウンロードを実行するプロセスを作成します。識別の名前はCSVDOWNLOADとします。

PL/SQLコードとして以下を記述します。



作成したAjaxコールバックを呼び出すボタンDOWNLOADを作成します。

ボタンはどこにおいても動作します。今回はブレッドクラムの右端に配置しています。

動作のアクションとしてこのアプリケーションのページにリダイレクトを選択します。


ターゲットのページとして現在開いているページ番号である&APP_PAGE_ID.、AjaxコールバックCSVDOWNLOADを呼び出すために、リクエストに以下を記述します。

APPLICATION_PROCESS=CSVDOWNLOAD


以上で、CSVダウンロードが実装できました。

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

完