[오라클] ORA-02287: 시퀀스 번호는 이 위치에 사용할 수 없습니다.

ORA-02287: 시퀀스 번호는 이 위치에 사용할 수 없습니다.

ORA-02287: sequence number not allowed here

 

오라클에서 INSERT 구문 작성 시, 위와 같은 에러를 보는 경우가 있습니다.

데이터를 INSERT할 때, SELECT 한 결과를 바로 INSERT할 때 발생하는 데, 만날 수 있는 오류입니다.

(즉 INSERT INTO SELECT 구문)

 

아래 쿼리는 SELECT GROUP BY 한 결과를 바로 INSERT 할 때 발생한 프로시져의 일부를 발췌한 예시입니다. (설명 이해를 돕기위해 임의로 작성한 예시 코드입니다.)

 

GROUP BY 한 SELECT 결과에서는 시퀀스(Sequence) 를 함께 사용할 수 없습니다.

시퀀스 생성을 허용하는 범위를 넘어서는 경우가 몇가지 있는데, 먼저 GROUP BY절을 포함한 SELECT의 결과 컬럼으로 사용하는 경우입니다.

 

단일 SELECT 하는 경우 시퀀스를 얻어오는데에 문제가 없지만, GROUP BY 가 적용되는 경우에는 논리적으로 시퀀스 값을 얻어올 수 없습니다.

 

INSERT INTO tbl_test1 (data_id, name_id, company_id, cost_value, date_write,data_status)

SELECT tbl_test1_seq.nextval, name_id, company_id, MAX(cost_value), date_create, db_sts

FROM tbl_test2

WHERE work_number = p_work_id    -- p_work_id 는 프로시져 내 변수

AND db_sts IS NOT NULL

GROUP BY name_id

 

 

 

문제를 해결하기 위한 간단한 방법으로는 GROUP BY - SELECT 결과와 시퀀스를 분리하면 됩니다.

 

 

    INSERT INTO tbl_test1 (data_id, name_id, company_id, cost_value, date_write, data_status)

SELECT tbl_test1_seq.nextval, name_id, company_id, good_cost_value, date_create, db_sts

FROM (SELECT name_id AS name_id, company_id AS company_id, MAX(cost_value) AS good_cost_value, date_create, db_sts

FROM tbl_test2

WHERE work_number = p_work_id    -- p_work_id 는 프로시져 내 변수

AND db_sts IS NOT NULL

GROUP BY name_id

) 

 

GROUP BY - SELECT 된 결과를 먼저 구하고, 그 결과를 다시 SELECT 할 때, 시퀀스와 함께 SELECT 하면 됩니다.

 

위와 같은 시퀀스 오류가 발생하는 이유를

간단히 설명하면 시퀀스는 오라클에서 동적으로 생성 해주는 키 값을 얻어오는 것인데, 그 값이 생성되기도 전에, 그 결과 집합을 GROUP BY로 얻는 것 자체가 논리적으로 맞지 않기 때문입니다. 그러므로 GROUP BY가 끝난 결과 데이터를 받아와 순차적으로 시퀀스를 붙여주도록 만든 쿼리가 바로 위의 쿼리입니다.

 

위와 같은 이유로 SELECT 구문 사용 시, GROUP BY 나 ORDER BY 절이 포함된 경우에 시퀀스를 함께 사용할 수 없습니다. 시퀀스를 SELECT의 결과 컬럼으로 가져오도록 코드가 되어 있다면, 아직 할당도 안된 상태의 컬럼 값이 존재하는 데이터를 그룹으로 묶거나 순서를 정하는 것은 문제가 있습니다.

 

역시 좀 더 범위를 넓혀보면 시퀀스는 DISTINCT절의 컬럼, WHERE 조건절, UNION/MINUS/INTERSECT 등과 함께 사용이 불가능하며, 서브쿼리(Subquery)로도 사용할 수 없습니다. (Subquery인 경우 SELECT 뿐만 아니라, UPDATE와 DELETE 구문에서도 사용할 수 없습니다.)

 

마지막으로 한가지만 더 덧붙이자면..

그럼 오라클은 왜 알아서 먼저 시퀀스를 할당하고, Group By하면 되는데 그렇지 않는냐 할 수도 있는데, 최종적인 결과 집합이 Group By 에 의해 결정되는데, Group By 하기 이전의 임시적인 SELECT 집합은 결과 집합도 아닌데, 해당 집합을 대상으로 시퀀스를 먼저 할당하는 것은 논리적으로 모순입니다.

 

마치 타임머신을 타고 과거로 되돌아가 나를 죽인다면 현 세계에서의 나도 죽느냐? 그럼 현 세계에서는 내가 존재하지 않게 되는데, 어떻게 미래 시대의 내가 과거의 나를 죽일 수 있느냐 하는 문제와 같아 보입니다.

 

오라클 구문 오류 하나 설명하는데, 타임머신은 왜나와! 뾰로롱~~~