Autonomous Database：SELECT文に対する事前認証済みリクエストURL（PAR URL）でバインド変数を利用する

# はじめに
Autonomous Databaseでは、従来から表やビュー、クエリに対して事前認証済みリクエストURL（PAR URL）が作成できましたが、バインド変数を使用したクエリに対してもPAR URLが作成できるようになったので、試してみました。

以前書いたPAR URLの作成に関する記事はこちら。

https://qiita.com/500InternalServerError/items/8d18a7a7b5e83d80cdeb

# 1. 事前準備

検証用に表empを作成し、3件のレコードを追加しました。

```
SQL> CREATE TABLE EMP (
  2      empno NUMBER,
  3      empname VARCHAR2(20) 
  4  );

Table created.

SQL> INSERT INTO emp VALUES (10,'JACKSON');

1 row created.

SQL> INSERT INTO emp VALUES (20,'WHITE');

1 row created.

SQL> INSERT INTO emp VALUES (30,'HARTMAN');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM emp;

     EMPNO EMPNAME
---------- --------------------
	    10 JACKSON
	    20 WHITE
	    30 HARTMAN

SQL> 
```

# 2. バインド変数を使用したSELECT文に対するPAR URLの作成

DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URLプロシージャを使用して、こちらのバインド変数を使用したSELECT文に対するPAR URLを作成します。

```
SELECT * FROM emp WHERE empno = :empno
```

こちらのPL/SQLブロックでDBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URLプロシージャを実行し、実行結果をDBMS_OUTPUT.PUT_LINEプロシージャで表示します。

```
DECLARE
    status CLOB;
    BEGIN
        DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL(
            sql_statement        => 'SELECT * FROM emp WHERE empno = :empno',
            expiration_minutes    => 360,
            service_name         => 'HIGH',
            result               => status);
        DBMS_OUTPUT.PUT_LINE(status);
    END;
/
```

set serveroutput onでDBMS_OUTPUT.PUT_LINEプロシージャの出力をオンにし、上記のPL/SQLブロックを実行します。
```
SQL> set serveroutput on
SQL> DECLARE
  2      status CLOB;
  3      BEGIN
  4          DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL(
  5              sql_statement        => 'SELECT * FROM emp WHERE empno = :empno',
  6              expiration_minutes    => 360,
  7              service_name         => 'HIGH',
  8              result               => status);
  9          DBMS_OUTPUT.PUT_LINE(status);
 10      END;
 11  /
{"status":"SUCCESS","id":"UThN6ffRNyzP-hwAyuiTz5yT75hPvjWf9c859B9KQQtJkiWbHdWW5i
MBSN5ce9tH","preauth_url":"https://dataaccess.adb.ap-tokyo-1.oraclecloudapps.com
/adb/p/kUQxRLBRvM17ms5BRkvGA5Yu7SQTg04yLHys7sEzhRyDJB7TtGISMe_0wmx0iQgAq1Acu-lTS
TQ/data","expiration_ts":"2024-07-30T06:33:21.302Z","expiration_count":null}

PL/SQL procedure successfully completed.

SQL>
```
出力された結果内のpreauth_urlの値が、生成されたPAR URLになります。
```
https://dataaccess.adb.ap-tokyo-1.oraclecloudapps.com
/adb/p/kUQxRLBRvM17ms5BRkvGA5Yu7SQTg04yLHys7sEzhRyDJB7TtGISMe_0wmx0iQgAq1Acu-lTS
TQ/data
```

# 3. 動作確認
PAR URLにバインド変数の値を渡すには、クエリ・ストリング（URLパラメータ）を使用します。

今回作成したPAR URLでは、empnoというバインド変数を使用していますので、表示されたPAR URLにクエリ・ストリング「?empno=＜empnoの値＞」を追加します。

PAR URLにクエリ・ストリング「?empno=10」を追加して、ブラウザからアクセスしてみます。
```
https://dataaccess.adb.ap-tokyo-1.oraclecloudapps.com/adb/p/kUQxRLBRvM17ms5BRkvGA5Yu7SQTg04yLHys7sEzhRyDJB7TtGISMe_0wmx0iQgAq1Acu-lTSTQ/data?empno=10
```

以下のようなページが表示されました。
empnoが10のデータが抽出されていることがわかります。
![スクリーンショット 2024-07-30 9.37.33.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/ce4a72c2-57db-a84f-1f7f-7c00daf2f968.png)

次にPAR URLにクエリ・ストリング「?empno=20」を追加して、ブラウザからアクセスしてみます。

```
https://dataaccess.adb.ap-tokyo-1.oraclecloudapps.com/adb/p/kUQxRLBRvM17ms5BRkvGA5Yu7SQTg04yLHys7sEzhRyDJB7TtGISMe_0wmx0iQgAq1Acu-lTSTQ/data?empno=20
```

以下のようなページが表示されました。
empnoが20のデータが抽出されていることがわかります。
![スクリーンショット 2024-07-30 9.51.15.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/c918a737-4708-48b2-c91f-3c6781618bfd.png)

以上で、SELECT文に対する事前認証済みリクエストURL（PAR URL）でバインド変数を利用できることが確認できました。

# 4. PAR URLの無効化
作成したPAR URLを有効期限、有効アクセス回数に到達する前に無効化するには、DBMS_DATA_ACCESS.INVALIDATE_URLプロシージャを使用します。
idにはPAR URL生成時に表示されるPAR URLのidの値を指定します。

```
SQL> set serveroutput on
SQL> DECLARE
  2      status CLOB;
  3      BEGIN
  4          DBMS_DATA_ACCESS.INVALIDATE_URL(
  5              id => 'UThN6ffRNyzP-hwAyuiTz5yT75hPvjWf9c859B9KQQtJkiWbHdWW5iMBSN5ce9tH',
  6              result => status);
  7          DBMS_OUTPUT.PUT_LINE(status);
  8      END;
  9  /
{"status":"SUCCESS"}

PL/SQL procedure successfully completed.

SQL>
```
作成したPAR URLが無効化できました。

# 参考情報
・[Autonomous Databaseでの読取り専用データ・アクセスに対する事前認証済リクエストURLの使用](https://docs.oracle.com/ja-jp/iaas/autonomous-database-serverless/doc/autonomous-preauthenticated-request-url.html)
・[DBMS_DATA_ACCESS Package](https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/dbms-data-access.html)