오라클 ORA-01002 오류와 해결방법

최근 MSSQL로 만들었던 서드파티 프로그램을 고객사 요청에 따라 Oracle DB로 변환하는 작업을 하고 있다.

그냥 단순하게 Adater바꾸고 Command 바꾸고 하면 끝날줄 알았는데... 생각보다 과정이 까다롭다.

일단 제일 문제가 되는 부분은 프로시저 호출이었는데 기존에 MSSQL 사용 당시 거의 대부분을 프로시저화 해서 호출해서 사용했다.

데이터 자체는 Oracle의 Migration을 이용해서 몽땅 다 옮겼는데 변환된 프로시저 대부분이 제대로 동작하지 않는 것을 확인하였다.

그래서 프로시저를 이리저리 수정중인데...

문제가 된 프로시저는 메뉴를 만드는 프로시저인데 임시테이블에 첫행을 Insert하고 재귀하면서 계층형 구조를 만든 뒤 Sys_RefCursor로 반환하는 프로시저이다.

 

create or replace PROCEDURE Code_GetEmployeeMenuList
(
  v1 IN CHAR,
  OUT_DATA OUT SYS_REFCURSOR
)
AS
   v_MaxLevel NUMBER(10,0) := 0;
BEGIN

   INSERT INTO t_v_menu
     ( SELECT DISTINCT mg.c ,
                       mg.i ,
                       mg.p ,
                       mg.gl 
       FROM mg
              JOIN cad gd   ON mg.c = gd.c
              AND mg.i = gd.i              
        WHERE mg.ProgramType = v1
                 AND mg.GroupYn = '0'
                 AND mg.UseYn = 'Y' );
                 
   SELECT MAX(gl)  
     INTO v_MaxLevel
     FROM t_v_menu;
     
   WHILE ( v_MaxLevel > 0 ) 
   LOOP 
         v_MaxLevel := v_MaxLevel - 1 ;
         INSERT INTO t_v_menu
           ( SELECT mg.c ,
                    mg.i ,
                    mg.p ,
                    mg.gl 
             FROM t_v_menu tbl
                    JOIN mg ON tbl.c = mg.c
                    AND tbl.p = mg.i
                    AND mg.gl = v_MaxLevel);
   END LOOP;
   
   
   OPEN OUT_DATA FOR
      SELECT DISTINCT mg.* 
        FROM t_v_menu tbl
               JOIN mg ON tbl.c = mg.c
               AND tbl.i = mg.i
        ORDER BY mg.RowNum_ ;
      

EXCEPTION WHEN OTHERS THEN utils.handleerror(SQLCODE,SQLERRM);
END;

(실제 테이블,컬럼명은 노출되면 안되므로 임시로 이름을 대충 붙였다.)

 

눈여겨 볼것은 Cursor를 열기전에 Insert문을 실행하는데, 여기서 문제가 발생한다.

---

 Cursor와 DML

ORA-01002가 발생되는 이유는 여러가지가 있는데, 구글 검색을 했을 땐 일반적으로, 4가지를 소개한다.

1. Cursor로 부터 마지막 결과행까지 리턴을 받아 더이상 결과 세트를 리턴 받을 수 없는 상황이나 FETCH를 시도하거나 로직상 그런 상황을 통제할 제어구문이 존재하지 않는 경우
2. For UPDATE문이 속한 SQL문을 Cursor를 통해 FETCH할 떄, COMMIT후 FETCH를 시도하는 경우
3. SQL문 중 잘못된 요소를 묶었을 때 (ex. Group By절의 요소를 잘못 선언했을때)
4. 드물게 쿼리중 DB가 원할하지 못해 연결이 끊어진 경우

내가 문제 생긴 것은 위의 4개의 사유에 다 해당하지 않기 때문에 뭐가 문제인지 알수가 없었는데, 계속 검색을 하다가 답을 찾을 수 있었다.

오라클에 cursor를 open 후에 RollBack을 처음으로 만나게 되면 Cursor를 바로 닫아버릴 수 있다고 한다.

다만, 무조건 발생되는 것은 아니고 커서 오픈 전 DML이 수행 된 적이 있을경우에만 발생하는데, 나의 경우에는 Insert를 수행하였기에 Commit/rollback으로 마무리 되지 않은 상태에서 Cursor가 Open되었고 이전 DML을 RollBack하면서 Cursor를 Close한것.

 

이 경우의 해결 방법은 간단한데, 커서 오픈전에 Commit/RollBack을 해주거나 여의치 않은 경우 Cursor를 Open 후 RollBack이 처음으로 발생하지 않도록 데이터나 로직을 정비하면 해결된다.

 

내 경우에는 Insert문이 끝나는 마지막 Loop후에 Commit 문을 추가했는데, 오류가 해결되는 것을 확인 할 수 있었다.

---

남은 문제...

이제 프로시저를 받을 수 있으니 정상적으로 데이터가 출력 될 줄 알았는데...

왠걸 Commit을 수행하니 임시테이블에 담은 데이터가 몽땅 날아간 것을 확인했다.

이것저것 테스트를 해봤는데 제일 이해가 안되는 것은 Insert를 따로 7개 정도 해놓고 프로시저를 실행했더니 기존에 Insert 시킨 7개의 데이터도 사라진 것.

Commit만 추가했다고 해서 이렇게 되진 않을 것 같은데... 트랜잭션이 어떻게 발생하는지 한번 더 확인을 해보아야 겠다.

---

참고 자료

https://pangate.com/999