PL/SQLのコレクション と 12c新機能のWITH句ファンクション で IN句1000個の壁(ORA-1795エラー)を回避／克服してみる。

Oracle Database では IN句に1000個以上の値を指定すると、ORA-1795エラーが発生してしまいます。

SQL> SELECT COUNT(*) FROM TBL_X T1
  2  WHERE C1 IN (
  3        1
  4    ,   2
  5    ,   3
:
(中略)
:
4001    ,3999
4002    ,4000
4003  );
  ,1001
   *
ERROR at line 1003:
ORA-01795: maximum number of expressions in a list is 1000

SQL>

このエラー(ORA-1795)を PL/SQLのコレクション と Oracle Database 12c新機能 の WITH句のファンクション で 回避／克服してみるやで彡(ﾟ)(ﾟ)

-- PL/SQL Collection Type
CREATE OR REPLACE TYPE tp_num_array IS TABLE OF NUMBER;
/

-- Select Query plus WITH FUNCTION
WITH
  FUNCTION fnc_num_array RETURN tp_num_array IS
    -- PL/SQL Collection
    arr_num_c1 tp_num_array := tp_num_array(NULL);
  BEGIN
    arr_num_c1.extend(3999);
    arr_num_c1(1) := 1;
    arr_num_c1(2) := 2;
    arr_num_c1(3) := 3;
--:
--(中略)
--:
    arr_num_c1(3999) := 3999;
    arr_num_c1(4000) := 4000;
    RETURN arr_num_c1;
  END;
SELECT COUNT(*) FROM TBL_X
WHERE C1 IN (
  -- TABLE Function
  SELECT * FROM TABLE(fnc_num_array)
)
/

結果は以下の通り。上手く行ったで。1000個の壁を越えたやで彡(^)(^)

SQL> -- PL/SQL Collection Type
SQL> CREATE OR REPLACE TYPE tp_num_array IS TABLE OF NUMBER;
  2  /

Type created.

SQL> -- Select Query plus WITH FUNCTION
SQL> WITH
  2    -- WITH FUNCTION
  3    FUNCTION fnc_num_array RETURN tp_num_array IS
  4      -- PL/SQL Collection
  5      arr_num_c1 tp_num_array := tp_num_array(NULL);
  6    BEGIN
  7      arr_num_c1.extend(3999);
  8      arr_num_c1(1) := 1;
  9      arr_num_c1(2) := 2;
 10      arr_num_c1(3) := 3;
:
(中略)
:
4006      arr_num_c1(3999) := 3999;
4007      arr_num_c1(4000) := 4000;
4008      RETURN arr_num_c1;
4009    END;
4010  -- Select Query Body
4011  SELECT COUNT(*) FROM TBL_X
4012  WHERE C1 IN (
4013    -- TABLE Function
4014    SELECT * FROM TABLE(fnc_num_array)
4015  )
4016  /

  COUNT(*)
----------
      4000

SQL>

トリッキー かつ かなり無理矢理なやり方 なんやけどね……彡(-)(-)
でも 12c新機能の WITH句ファンクション は結構使えるんやね。彡(ﾟ)(ﾟ)