[PL/SQL] 커서 사용 시 발생 가능한 ORA-01002 오류

오류 발생 상황

-- Error 발생 시 Rollback 발생시키는 Procedure
CREATE OR REPLACE PROCEDURE pr_err_log_with_rollback (
	v_msg_id  	tbl_err_lst.msg_id%TYPE,
	v_inst_id   tbl_err_lst.inst_id%TYPE,
	v_pr_name   tbl_err_lst.pr_name%TYPE,
	v_err_desc  tbl_err_lst.err_desc%TYPE,
	v_err_code  tbl_err_lst.err_code%TYPE,
	v_err_msg   tbl_err_lst.err_msg%TYPE,
	v_err_line  tbl_err_lst.err_line%TYPE
)
IS
	-- 이 procedure에서 발생한 로그들을 임시 저장 (Rollback 시 삭제되는 로그들)
	CURSOR c_logs IS
	SELECT *
	  FROM tbl_log_lst
	 WHERE pr_date = TO_CHAR(SYSDATE, 'YYYYMMDD')
	   AND pr_seq = (
		SELECT MAX(pr_seq)
		  FROM tbl_log_lst
		 WHERE pr_date = TO_CHAR(SYSDATE, 'YYYYMMDD')
	);

	v_log c_logs%ROWTYPE;
BEGIN
	OPEN c_logs;

	ROLLBACK;

	-- Rollback 후에 다시 tbl_log_lst 테이블에 삭제된 로그 삽입
	LOOP
		FETCH c_logs INTO v_log;
		EXIT WHEN c_logs%NOTFOUND;
		INSERT INTO tbl_log_lst VALUES v_log;
	END LOOP;

	CLOSE c_logs;

	-- tbl_err_lst 테이블에 오류 로그 추가
	PR_ERR_LOG(v_msg_id, v_inst_id, v_pr_name, v_err_desc, v_err_code, v_err_msg, v_err_line);
	-- tbl_log_lst 테이블에 롤백 발생 로그 추가
	PR_LOG(v_pr_name, 'ROLLBACK', null, null);
END;

프로시저에서 예상 가능한 오류가 발생하는 경우에는 로그만 남겨두고 다른 작업들을 이어서 진행하도록 할 수도 있지만, 예상치 못한 오류가 발생하는 경우에는 보통 rollback을 통해 작업 내용들을 모두 되돌리고 오류를 확인할 수 있게 로그를 남겨둔다. 후자의 경우가 발생했을 때에 대상 procedure에서 호출해 공통적인 로직을 처리하도록 작성한 pr_err_log_with_rollback procedure는 rollback이 필요한 오류가 발생한 경우에 호출되어서 다음과 같은 처리를 담당하도록 설계했다.

1. error가 발생하면 지금까지 대상 procedure에서 작업했던 내용들을 모두 rollback시키고,
2. 대상 procedure 작업 도중에 생성되었던 로그들을 (함께 rollback되었을 것이기 때문에) 다시 삽입해준 뒤,
3. 오류 발생에 대한 로그와 procedure에서 rollback이 발생했음에 대한 로그를 생성해 준다.

(2)와 같은 처리를 위해서 c_logs라는 커서를 사용했는데, rollback의 대상이 되는 로그 record들을 rollback 전에 커서에 임시로 저장해 두고 rollback을 수행한 뒤 커서에 저장된 record들을 하나씩 fetch해서 다시 원래 테이블에 삽입해주도록 작성해 주었다. 다른 procedure의 중간 부분에서 이 procedure를 호출해 본 결과 제대로 작동하지 않고 다음과 같은 오류가 발생하는 것을 확인하게 되었다.

Error: # 1002, ORA-01002: 인출 시퀀스가 틀립니다 (FETCH OUT OF SEQUENCE)

이번 글에서는 ORA-01002 오류가 발생하는 원인들에 대해 알아본 부분들을 공유하고, 위와 같은 상황에서 어떤 방식으로 문제를 해결했는지를 기록해 두고자 한다.

오류 발생 원인

ORA-01002: fetch out of sequence

Cause: This error means that a fetch has been attempted from a cursor which is no longer valid. Note that a PL/SQL cursor loop implicitly does fetches, and thus may also cause this error. There are a number of possible causes for this error, including:

1. Fetching from a cursor after the last row has been retrieved and the ORA-1403 error returned.
2. If the cursor has been opened with the FOR UPDATE clause, fetching after a COMMIT has been issued will return the error.
3. Rebinding any placeholders in the SQL statement, then issuing a fetch before re-executing the statement.

Action:

1. Do not issue a fetch statement after the last row has been retrieved - there are no more rows to fetch.
2. Do not issue a COMMIT inside a fetch loop for a cursor that has been opened FOR UPDATE.
3. Re-execute the statement after rebinding, then attempt to fetch again.

ORA-01002 can have multiple causes including:

• A PL/SQL loop does fetches without notice
• Attempting to fetch from a cursor that is no longer valid (fetching from a row which has been retrieved).
• Fetching after a COMMIT has already been issued and a cursor is opened with the FOR UPDATE clause.
  Issuing a fetch before re-executing a SQL after rebinding placeholders.

You may want to try using cursor attributes to dodge ORA-01002 in the future. To resolve a current ORA-01002, there are three actions you can perform:

• After the last record is received, do not issue a fetch
• Inside a fetch loop on a SELECT FOR UPDATE, do not use a COMMIT
• Try fetching again after re-executing the statement (after rebinding)

문서에서는 ORA-01002 오류의 원인과 해결 방안에 대해 위와 같이 다루고 있다. 가능한 원인들을 간단히 정리해 보자면 다음과 같다.

1. 마지막 record까지 모두 추출된 커서에 대해 다시 fetching을 시도하는 경우. 반복문 내부에 EXIT WHEN emp_cur%NOTFOUND와 같이 fetch할 데이터가 없는 경우 반복문을 종료하도록 명시하지 않는 경우 이 오류가 발생할 수 있다.
2. FOR UPDATE 문을 사용해 정의된 커서에 대해 commit이 발생한 이후에 fetching 시도하는 경우. Oracle의 FOR UPDATE 문은 SELECT한 결과에 대해 사용자가 임의로 배타적인 lock을 설정하기 위해 사용하는 명령어이다. 주로 record의 갱신이나 삭제 작업 전에 다른 트랜잭션에 의한 데이터 조작을 막기 위해 lock을 설정할 때 사용한다. FOR UPDATE문을 사용해 정의된 커서의 transaction에서 commit을 하게 되면 커서를 무효화 하면서 lock이 해제되며 자연스럽게 commit 이후의 fetch는 ORA-01002 오류를 발생시킨다.
3. 커서로 결과를 받아온 동적 sql statement의 바인딩 변수가 변경되었을 때 sql statement를 다시 실행하지 않고 fetching을 시도하는 경우. Oracle에서는 OPEN FOR 문으로 동적 sql statement와 커서 변수를 연결할 수 있고, OPEN FOR 문에 USING 절로 바인딩 변수를 지정해 사용 가능하다. 이때 커서와 연결된 statement의 바인딩 변수 값이 재할당되면 커서가 더 이상 유효하지 않게 되면서 오류가 발생한다.

공통적으로 확인할 수 있는 것은 더 이상 유효하지 않은 커서에 대해 fetching을 시도하는 경우에 ORA-01002 오류가 발생한다는 점이다. 문제의 상황에서 발생하는 오류도 위 세 가지 상황에 정확히 들어맞는 것은 아니지만, 유효하지 않은 커서에 대한 fetch 시도 때문에 발생한 것임을 이해할 수 있다.

커서의 동작 방식을 살펴보면 커서를 open하는 시점에 PL/SQL 변수에 값이 담기는 것이 아니라, 우선 내부 메모리 공간인 Context Area로 데이터를 가져온 뒤 fetch 시점에 PL/SQL 변수에 값을 담아 후속 작업을 진행한다. 커서는 실제 데이터를 저장하는 것이 아니라 Context Area에 저장해 둔 결과 data set을 가리키는 일종의 포인터 역할을 하는 것이다. 따라서 결과 data set이 변경된다면 이를 가리키는 커서가 더 이상 유효하지 않게 된다.

그렇다면 DML로 갱신된 정보가 있는 transaction이 commit 또는 rollback으로 마무리되지 않은 상황에서 해당 transaction 내에서 갱신된 data set을 가리키는 커서가 open된 이후에 rollback이 발생한다면 어떤 일이 발생할까? open된 커서가 가리키는 data set이 갱신되기 이전의 상태로 변경되면서, 커서가 더 이상 유효하지 않게 되고 더 이상 data set을 fetch 불가능하기 때문에 커서가 무효화되고 ORA-01002 오류가 발생하게 되는 것이다.

오류 해결 방법

문서에서 제시한 발생 가능한 원인들이 ORA-01002 오류의 이유라면 각각의 원인을 해결해서 오류를 없앨 수 있다. 이 문제의 경우 커서의 동작 방식 때문이 생각했던 것과 달랐기 때문에 발생하는 문제였으므로, 커서의 동작 방식과는 다르게 원래 상정했던 것과 같이 PL/SQL 변수 자체에 임시로 저장하고자 하는 값을 모두 담아둔다면 이 문제를 해결할 수 있을 것이다. 여기에서는 중첩 테이블 자료형을 이용해서 임시 저장이 필요한 로그 정보들을 저장해 두는 방식으로 구현했다. 테스트 결과 오류 없이 정상적으로 작동하는 것을 확인할 수 있었다.

-- Error 발생 시 Rollback 발생시키는 Procedure
CREATE OR REPLACE PROCEDURE pr_err_log_with_rollback (
	v_msg_id  	tbl_err_lst.msg_id%TYPE,
	v_inst_id   tbl_err_lst.inst_id%TYPE,
	v_pr_name   tbl_err_lst.pr_name%TYPE,
	v_err_desc  tbl_err_lst.err_desc%TYPE,
	v_err_code  tbl_err_lst.err_code%TYPE,
	v_err_msg   tbl_err_lst.err_msg%TYPE,
	v_err_line  tbl_err_lst.err_line%TYPE
)
IS
	TYPE nt_logs IS TABLE OF tbl_log_lst%ROWTYPE;
	vnt_logs nt_logs;
BEGIN
	-- 이 procedure에서 발생한 로그들을 임시 저장 (Rollback 시 삭제되는 로그들)
	vnt_logs := nt_logs();

	FOR v_log IN (
		SELECT *
		FROM tbl_log_lst
		WHERE pr_date = TO_CHAR(SYSDATE, 'YYYYMMDD')
		AND pr_seq = (
			SELECT MAX(pr_seq)
			FROM tbl_log_lst
			WHERE pr_date = TO_CHAR(SYSDATE, 'YYYYMMDD')
		)
	)
	LOOP
		vnt_logs.EXTEND;
		vnt_logs(vnt_logs.LAST) := v_log;
	END LOOP;

	ROLLBACK;

	-- Rollback 후에 다시 tbl_log_lst 테이블에 삭제된 로그 삽입
	FOR i IN vnt_logs.FIRST..vnt_logs.LAST
	LOOP
		INSERT INTO tbl_log_lst VALUES vnt_logs(i);
	END LOOP;

	-- tbl_err_lst 테이블에 오류 로그 추가
	PR_ERR_LOG(v_msg_id, v_inst_id, v_pr_name, v_err_desc, v_err_code, v_err_msg, v_err_line);
	-- tbl_log_lst 테이블에 롤백 발생 로그 추가
	PR_LOG(v_pr_name, 'ROLLBACK', null, null);
END;