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

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

以下は動作イメージです。

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

実装手順

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

まずは対話モード・レポートを配置し、赤枠のように設定します。

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

アイテム名の「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

このように対話モード・レポートを定義すると、以下赤枠のように列情報が表示されます。

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

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

対話モード・レポートの Region Body に、以下赤枠のように非表示アイテムを並べます。

このアイテムには後ほど記載する処理によって、対話モード・レポートを表示する際の列名をセットします。
また以下赤枠のように、 APEX_COLLECTIONS の C001 といった各列のヘッダーにおいて、この非表示アイテムの値を参照させます。

加えて「サーバ側の条件」を以下赤枠のように設定し、紐づく非表示アイテムが NULL の場合、該当列を表示させないようにします。

入力されたSQLの実行処理

プロセス・タブにて以下赤枠のように設定します。

PL/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;

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