Oracle APEXで列数が動的に変わる対話モード・レポートを実装してみる

Oracle APEXで列数を動的に変えられる対話モード・レポートを作る要件があり、試行錯誤の結果どうにか実装できたのでメモとして残します。

以下は動作イメージです。
![report_sample.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/33182/29dfaf56-d964-4597-8d33-c2057f9af916.gif)

ちなみにクラシック・レポートであれば汎用列という機能があるので、以降で紹介する方法より大分簡単に実装できます。

## 実装手順

### 対話モード・レポートの定義

まずは対話モード・レポートを配置し、赤枠のように設定します。
![WS000000.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/33182/bfa9b81d-08cc-4bbe-9636-e89c87d6adc9.jpeg)

入力するPL/SQLファンクションは以下の通りです。

:::note warn
アイテム名の「P13」はご自身のページ番号に合わせて修正してください。
:::

```
IF :P13_SQL IS NULL THEN
RETURN Q'[select
    C001,
    C002,
    C003,
    C004,
    C005,
    N001,
    N002,
    N003,
    N004,
    N005,
    D001,
    D002,
    D003,
    D004,
    D005
from APEX_COLLECTIONS]';
ELSE RETURN :P13_FIXED_SQL;
END IF;
```

対話モード・レポートは入力されたSQLやファンクションを評価するタイミングで、表示する列が定義されます。
そのため何かしら列を `SELECT` する構文の記載がないとエラーになります。
上記コードでは実際に表示するかどうかに関わらず、 `APEX_COLLECTIONS` に定義されている列を十分な数だけ `SELECT` しています。
`APEX_COLLECTIONS` の定義情報は下記をご参照ください。
https://docs.oracle.com/cd/G13834_01/aeapi/Accessing-a-Collection.html#GUID-19432196-C604-475D-80AC-283CDCF4FF86

このように対話モード・レポートを定義すると、以下赤枠のように列情報が表示されます。
![WS000000-2.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/33182/d6db82ab-d336-4f3f-9610-2739add54d47.jpeg)

なお各列の表示/非表示や、具体的な値、列名を `APEX_COLLECTIONS` にセットする処理は後ほど実装します。

### 列名を格納するアイテムの追加

対話モード・レポートの Region Body に、以下赤枠のように非表示アイテムを並べます。
![WS000001.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/33182/5400b07a-876a-4fe5-9e1a-d6966a6ca9c3.jpeg)

このアイテムには後ほど記載する処理によって、対話モード・レポートを表示する際の列名をセットします。
また以下赤枠のように、 `APEX_COLLECTIONS` の `C001` といった各列のヘッダーにおいて、この非表示アイテムの値を参照させます。
![WS000001-2.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/33182/00bb964b-1ee1-42de-be3d-99f5f58496af.jpeg)

加えて「サーバ側の条件」を以下赤枠のように設定し、紐づく非表示アイテムが `NULL` の場合、該当列を表示させないようにします。
![WS000002.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/33182/4b55a27a-8df1-4e10-84b0-10b5349c9fec.jpeg)

### 入力されたSQLの実行処理

プロセス・タブにて以下赤枠のように設定します。
![WS000003.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/33182/a8d97154-0944-48ff-8222-bfbbb9b842b4.jpeg)

PL/SQLコードの中身は以下の通りです。

```sql
DECLARE
    l_cursor_id INTEGER;
    l_desc_tab dbms_sql.desc_tab3;
    l_col_cnt   INTEGER;
    l_char_columns varchar2(4000);
    l_num_columns varchar2(4000);
    l_date_columns varchar2(4000);
    l_char_counter number := 0;
    l_num_counter number := 0;
    l_date_counter number := 0;
BEGIN
    -- l_desc_tab にSELECTした各列の名前を格納
    l_cursor_id := DBMS_SQL.OPEN_CURSOR;
    dbms_sql.parse(l_cursor_id, :P13_SQL, dbms_sql.native);
    dbms_sql.describe_columns3(l_cursor_id, l_col_cnt, l_desc_tab);
    
    -- SELECTした列の数だけループさせ、列名を P13_xxx_COL_LABEL_x に格納
    FOR i IN 1..l_col_cnt LOOP
        -- 当該列の型が VARCHAR2 の場合
        IF  l_desc_tab(i).col_type = 1 then -- VARCHAR2
            l_char_counter := l_char_counter + 1;
            IF l_char_counter > 5 THEN
                CONTINUE;
            END IF;
            APEX_UTIL.SET_SESSION_STATE('P13_VAR_COL_LABEL_'||l_char_counter, l_desc_tab(i).col_name, true); 
            l_char_columns := l_char_columns || ',"' || l_desc_tab(i).col_name || '" AS C00' || l_char_counter;
        END IF;
        
        -- 当該列の型が NUMBER の場合
        IF  l_desc_tab(i).col_type = 2 then -- NUMBER
            l_num_counter := l_num_counter + 1;
            IF l_num_counter > 5 THEN
                CONTINUE;
            END IF;
            APEX_UTIL.SET_SESSION_STATE('P13_NUM_COL_LABEL_'||l_num_counter, l_desc_tab(i).col_name, true); 
            l_num_columns := l_num_columns || ',"' || l_desc_tab(i).col_name || '" AS N00' || l_num_counter;
        END IF;
        
        -- 当該列の型が DATE の場合
        IF  l_desc_tab(i).col_type = 12 then -- DATE
            l_date_counter := l_date_counter + 1;
            IF l_date_counter > 5 THEN
                CONTINUE;
            END IF;
            APEX_UTIL.SET_SESSION_STATE('P13_DATE_COL_LABEL_'||l_date_counter, l_desc_tab(i).col_name, true); 
            l_date_columns := l_date_columns || ',"' || l_desc_tab(i).col_name || '" AS D00' || l_date_columns;
        END IF;
    END LOOP;
    
    -- 余った P13_xxx_COL_LABEL_x に NULL をセット (非表示にするため)
    IF l_char_counter < 5 THEN
        FOR i IN l_char_counter..4 LOOP
            l_char_counter := l_char_counter + 1;
            APEX_UTIL.SET_SESSION_STATE('P13_VAR_COL_LABEL_'||l_char_counter, NULL, true); 
        END LOOP;
    END IF;
    
    IF l_num_counter < 5 THEN
        FOR i IN l_num_counter..4 LOOP
            l_num_counter := l_num_counter + 1;
            APEX_UTIL.SET_SESSION_STATE('P13_NUM_COL_LABEL_'||l_num_counter, NULL, true); 
        END LOOP;
    END IF;
    
    IF l_date_counter < 5 THEN
        FOR i IN l_date_counter..4 LOOP
            l_date_counter := l_date_counter + 1;
            APEX_UTIL.SET_SESSION_STATE('P13_DATE_COL_LABEL_'||l_date_counter, NULL, true); 
        END LOOP;
    END IF;
    
    -- 最終的に実行するSQL文から不要な文字列 (カンマ) を削除
    IF INSTR(l_char_columns, ',') = 1 THEN 
        l_char_columns := SUBSTR(l_char_columns, 2);
    END IF; 
    
    IF INSTR(l_num_columns, ',') = 1 THEN 
        l_num_columns := SUBSTR(l_num_columns, 2);
    END IF; 
    
    IF INSTR(l_date_columns, ',') = 1 THEN 
        l_date_columns := SUBSTR(l_date_columns, 2);
    END IF; 
    
    -- 最終的に実行するSQL文を生成してAPEXアイテム P13_FIXED_SQL に格納
    :P13_FIXED_SQL := 'SELECT ' || l_num_columns ||
        (CASE WHEN l_date_columns IS NOT NULL THEN ' , '|| l_date_columns ELSE NULL END ) || 
        (CASE WHEN l_char_columns IS NOT NULL THEN ' , '|| l_char_columns ELSE NULL END ) || 
        ' FROM ('|| :P13_SQL ||' )';
END;
```

以上で冒頭に例示したような、表示列が動的に変わる対話モード・レポートを実装できました。