[오라클] IN절 1000개 이상인 경우 처리 방법 - ORA-01795

 

ORA-01795 maximum number of expressions in a list is 1000

 

IN 절안에 값이 1000개 까지만 사용할 수 있다는 오류 내용이다.

 

IN (1, 2, ..., 1000) 

 

이런 경우 회피할 수 있는 방법이 있는데,

값을 직접 IN절에 넣는게 아니라 가공하여 넣는 방법이다.

 

방법1. 임시 테이블 활용

임시 테이블을 만들 수 있는 경우라면

임시 테이블에 데이터를 넣고, 해당 테이블을 사용하는 방법

IN ( SELECT VALUE
       FROM TEMP
      WHERE 조건
   )

 

 

방법2. 각 값을 UNION ALL 로 엮어주는 방법

mybatis 처럼 소스를 이용하여 쿼리를 가공할 수 있는 경우 값들을 UNION ALL로 엮어주는 방법이다.

IN ( SELECT [값1] FROM DUAL
      UNION ALL
     SELECT [값2] FROM DUAL
      UNION ALL 
     ... 
   )

 

Mybatis를 활용한 예제

IN
<foreach collection="문자열변수" item="item" index="index" open="(" close=")" separator=" UNION ALL ">
   SELECT #{item} FROM DUAL
</foreach>

 

 

방법3. 스트링 문자열을 구분자로 분리하여 ROW처럼 만드는 방법

TABLE FUNCTIONS를 활용하는 방법

(오라클 9i 버전 이상부터 사용 가능)

IN ( SELECT VALUE
       FROM TABLE (FN_SPLIT_TABLE([값문자열] , ',')) /* 문자열 내 구분자는 ,(콤마) '1','2','3',... */
   )

 

[값문자열] 문자열을 ',' 구분자로 분리하여 테이블형태로 반환하는 FN_SPLIT_TABLE 함수 만들기

TABLE FUNCTIONS를 활용하면 결과값을 테이블 형태로 반환해서 사용할 수 있다.

 

1. 함수에서 반환하는 레코드의 스키마를 정의

CREATE OR REPLACE TYPE "TP_SPLIT_ROW" AS OBJECT(VALUE VARCHAR2 (32767))

 

2. 함수에서 반환하는 레코드의 집합(테이블) 정의

CREATE OR REPLACE TYPE "TP_SPLIT_TABLE" AS TABLE OF TP_SPLIT_ROW

 

3. FN_SPLIT_TABLE 함수 정의

CREATE OR REPLACE FUNCTION FN_SPLIT 
(
    i_strValue VARCHAR2
,   i_splitChar VARCHAR2 := ',' /* 구분자 기본값 ',' */
)
RETURN TP_SPLIT_TABLE PIPELINED
IS
    v_pos PLS_INTEGER;
    v_strValue  VARCHAR2 (32767) := i_strValue;
BEGIN
   LOOP
      v_pos := INSTR (v_strValue, i_splitChar);

      IF v_pos IS NULL THEN
        EXIT;
      END IF;        

      IF v_pos > 0 THEN
      BEGIN
         PIPE ROW (TP_SPLIT_ROW(SUBSTR(v_strValue, 1, v_pos - 1)));
         v_strValue := SUBSTR (v_strValue, v_pos + LENGTH (i_splitChar));
      END;
      ELSE
         PIPE ROW (TP_SPLIT_ROW(v_strValue));
         EXIT;
      END IF;
   END LOOP;

   RETURN;
END;