[ORACLE] ORA-01002 인출 시퀀스가 틀립니다.

오라클에서 프로시져를 코딩하다가 다음과 같은 에러를 확인할 수 있다.

ORA-01002 인출 시퀀스가 틀립니다.

해당 원인은 크게 3가지인데

1. CURSOR 및 FETCH로 인한 문제
2. GROUP BY를 잘못 묶었을 때 
3. DB가 원활하지 못하게 shotdown 되는 경우

필자의 경우 루프문(FOR문)을 돌면서 롤백을 발생시킬 때 해당 에러가 발생하였다.

PROCEDURE PRO_A
	...
    FOR IN (A) LOOP
    	UPDATE
        INSERT
        ...
        FOR IN (B) LOOP <- ROLLBACK 발생 후 ORA-01002 인출시퀀스가 없습니다. 에러 발생
	    UPDATE
            INSERT
            ...
            IF(ERROR?) THEN
            	ROLLBACK;
                CONTINUE;
            END IF;
            ...
            COMMIT;
        END LOOP;
        ...
    END LOOP;
END;

해당 로직에서 하위 루프문에 ROLLBACK이 발생한 경우 FOR문을 돌지 못하고 'ORA-01002 인출 시퀀스가 틀립니다.' 에러 문구와 함께 빠져나와버린다.

여기서 1번 이유인 CURSOR와 FETCH에 대한 문제임을 직감했으나 CURSOR와 FETCH의 뜻을 몰라 찾아보았다.

CURSOR

커서(CURSOR)는 SQL 문의 단위를 의미한다. 
하나의 결과를 리턴하는 경우에는 SQL문의 결과가 암시적 커서에 저장된다.
여러 행의 결과를 리턴하는 경우에는 사용자가 선언하여 사용하는 명시적 커서에 저장된다.

FETCH

커서에 저장된 후 지정된 변수에 값을 추출하는 작업이다.

결과적으로 SQL문이 발생하면 커서가 열려서 값을 메모리에 저장하고 패치작업으로 값을 추출한 후 커서를 닫는다.

 

해당 내용을 파악하고 다시 문제점을 찾아보면 FOR문에는 각각의 커서가 명시되어 있을 것이다. 

하위 FOR문에서 ROLLBACK을 수행하면 위에서 수행한 UPDATE,INSERT한 값들도 모두 ROLLBACK이 되었을 것이다.

필자의 경우 상위 FOR문에 UPDATE,INSERT한 데이터와 하위 FOR문에 패치된 데이터가 영향이 있다보니 ROLLBACK으로 꼬여버린 상황이 발생한 것이다.

그래서 다음과 같이 수정하였다.

PROCEDURE PRO_A
	...
    FOR IN (A) LOOP
    	-- UPDATE,INSERT할 데이터만 변수에 저장
        ...
        FOR IN (B) LOOP <- ROLLBACK 발생 후 ORA-01002 인출시퀀스가 없습니다. 에러 발생
	    UPDATE
            INSERT
            ...
            IF(ERROR?) THEN
            	ROLLBACK;
                CONTINUE;
            END IF;
            ...
            COMMIT;
        END LOOP;
        ...
        -- 상위에 저장된 변수 UPDATE,INSERT
        UPDATE
        INSERT
        ...
        COMMIT;
    END LOOP;
END;

 

변경한 것은 2가지인데

1. 상위 FOR문에서 UPDATE,INSERT했던 부분을 변수로 저장하여 하위 포문이 올바르게 수행된 이후에 UPDATE,INSERT하도록 수정하니 해당 에러가 사라졌다.

2. 상위 FOR문 마지막에 COMMIT을 찍지 않으니 인출 시퀀스 발생 에러가 또다시 발생하여 COMMIT을 찍어주었다.