오늘도 그런가보다...

ORA-01002: fetch out of sequence

ORACLE에서 제공하는 문서상의 원인과 해결방법은 아래와 같다.

Cause: This error means that a fetch has been attempted from a cursor
       which is no longer vaild. 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-01403 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 reexecuting 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) Reexecute the statement after rebinding, the attempt to
           fetch again.
          
1) CURSOR를 이용해 어느 테이블의 데이터를 FETCH를 할 때, 그 해당 결과값들이 모두
   FETCH되어 더 이상 FETCH될 데이터가 없을 때 ORACLE은 ORA-01403에러를 발생시켜
   더 이상 FETCH될 데이터가 없다는 걸 알린다.
   하지만 프로그래머의 실수로 FETCH받는 부분의 LOOP제어가 잘 못되어 ORA-01403이
   발생했는데도 FETCH를 시도하게 되면 ORA-01002를 발생시킨다.
  
    while(1) {
        EXEC SQL FETCH _CURSOR
        INTO :data_basket;

        pFetch += nFetch;
        nFetch  = sqlca.sqlerrd[2] - pFetch;
          
        if(sqlca.sqlcode == 0)
            (void)put_in_file(data_basket, nFetch);
           
        if(sqlca.sqlcode == 1403) {
            if(nFetch > 0)
                (void)put_in_file(data_basket, nFetch);
               
            break; <-- 이 부분이 없으면 ORA-01403이 발생했는데도 FETCH를 시도하기
                       때문에 ORA-01002가 발생된다.
        }
       
        if(sqlca.sqlcode != 0 && sqlca.sqlcode != 1403) {
            printf("%s\n", sqlca.sqlerrm.sqlerrmc);
           
            exit(1);
        }
    }

2) SAMPLE_TABLE이란 이름의 테이블이 있다.
   a세션은 SAMPLE_TABLE에서 UPDATE를 하고 COMMIT or ROLLBACK을 하지 않은 상태이다.
   이때 b세션에서
  
   SELECT * FROM SAMPLE_TABLE;
   위의 쿼리를 조회하게 되면 a세션의 UPDATE 쿼리가 반영되지 않은 결과값이 리턴된다.
  
   SELECT * FROM SAMPLE_TABLE FOR UPDATE;
   위의 쿼리를 조회하게 되면 a세션의 UPDATE 쿼리에 대한 COMMIT or ROLLBACK이 될 때까지
   대기하게 된다.
  
   SELECT * FROM SAMPLE_TABLE FOR UPDATE WAIT 10;
   위의 쿼리를 조회하게 되면 a세션의 UPDATE 쿼리에 대한 COMMIT or ROLLBACK이 되지 않은
   상태이기 때문에 10초 동안 COMMIT or ROLLBACK 여부를 확인한 뒤 COMMIT or ROLLBACK되지
   않았으면 ORA-01002에러를 발생시킨다. 
   
   SELECT * FROM SAMPLE_TABLE FOR UPDATE NOWAIT;
   위의 쿼리를 조회하게 되면 a세션의 UPDATE 쿼리에 대한 COMMIT or ROLLBACK이 되지 않은
   상태이기 때문에 대기하지 않고 ORA-01002에러를 발생시킨다.

3) CURSOR로 선언된 SELECT문의 GROUP BY절이 잘 못되어서 발생하는 경우이다.
   Sample_Table)
   +-----+---------+------------+---------+
   | SEQ | PROD_NO | PROD_NAME  | PRICE   |
   +-----+---------+------------+---------+
   |   1 |    1001 | Nikon F6   | 3000000 |
   |   2 |    1002 | Nikon F5   | 2500000 |
   |   3 |    1003 | Nikon F4s  | 2000000 |
   |   4 |    1004 | Nikon F4h  | 2000000 |
   |   5 |    1005 | Nikon F3Ti | 1500000 |
   |   6 |    1006 | Nikon F3hp | 1500000 |
   |   7 |    1007 | Nikon F3AF | 1500000 |
   |   8 |    1008 | Nikon F3   | 1500000 |
   +-----+---------+------------+---------+
  
   Query)
   SELECT
       SEQ,
       PROD_NO,
       PROD_NAME,
       PRICE
   FROM
       SAMPLE_TABLE
   GROUP BY        -+
       SEQ,         | GROUP BY절이 잘 못되어 있다.
       PROD_NAME,   | 이 경우 ORA-01002에러가 발생한다.
       PRICE;      -+
      
4) 실제로 겪어본 케이스는 아니지만
   DB Link를 통해 Remote Database에서 Fetch를 받는 중에
   Remote Database가 Shutdown되면 ORA-01002에러가 발생된다고 한다.

ORA-01403: no data found

Pro*C프로그램상에서 실행된 쿼리(SELECT, UPDATE, DELETE)에 대하여
만족하는 값이 없을 시 발생하게 된다.
즉, 위의 DML(Data Manipulation Language)에 WHERE조건이 있으면,
그에 해당 조건을 만족하는 대상이 없거나 테이블이 비어있는 경우
WHERE조건이 없으면 테이블이 비어있는 경우라고 보면 되겠다.

단, MERGE문을 통한 UPDATE의 경우, 그 조건을 만족하는 대상이 없더라도
ORA-01403을 발생하지않고 sqlca.sqlerrd[2]의 값을 0으로 초기화하고
sqlca.sqlcode값은 0으로 리턴된다.

Host Arrays에서의 ORA-01403
Pro*C에선 SELECT문의 결과값을 Fetch받을 때 결과값을 한 ROW씩 받는 방법과
Host Arrays를 이용하여 여러ROW를 한번에 받는 방법이 있다.

Ex1)
가정 - DUAL테이블에는 100 ROWS가 존재한다.

char current_time[19 + 1];
...
EXEC SQL DECLARE xxxxxxxx CURSOR FOR
SELECT
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS)
FROM
    DUAL;
...
EXEC SQL FETCH xxxxxxxx INTO :current_time

위의 경우 ORACLE DATABASE는 100 ROWS 결과값을 다 만들고 나서 한 ROW씩 리턴을 하게
된다.

Ex2)
가정 - DUAL테이블에는 100 ROWS가 존재한다.

char current_time[30][19 + 1];
...
EXEC SQL DECLARE xxxxxxxx CURSOR FOR
SELECT
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS)
FROM
    DUAL;
...
EXEC SQL FETCH xxxxxxxx INTO :current_time

위의 경우 ORACLE DATABASE는 100 ROWS가 다 만들어질때까지 기다리지 않고 30 ROWS의
결과값이 만들어지면 30 ROWS의 값을 한번에 리턴하고 후속작업을 하게된다.

Ex2의 경우 마지막에 받을 10 ROWS가 문제가 된다.
즉 30 ROWS씩 리턴값을 받던 3번은 sqlca.sqlcode가 0이 되지만 마지막에 10 ROWS를 받은
마지막 한번은 sqlca.sqlcode가 1403이 되는 것이다.
이유는 array가 30 ROWS로 정의 되어있기 때문에 조건을 만족하는 결과값이 30 ROWS를
충족하지 못 해서이다.

필수적으로 Host Arrays를 사용할때 sqlca.sqlcode가 1403을 리턴했다면 sqlca.sqlerrd[2]의
값이 0인지 아닌지를 확인해될 것이다.
0이 아닌 양수라면 Fetch된 잔여 데이터들이 있다는 뜻이다.

ORA-01405: fetched column values is NULL

Pro*C로 작성된 프로그램으로 어느 테이블의 데이터를 Fetch받으려 할 때
그 테이블의 특정행의 특정컬럼값이 NULL일 경우 ORACLE은 ORA-01405에러를 발생시킨다.

컬럼 속성이 NULL값을 허용하는 컬럼의 경우 NVL([COLUMN_NAME], '')로 해결하면 된다고 하나
이는 경우에 따라 엄청난 노가다거리 일 수 있다.

만약 프로그램을 작성한 사람이 컴파일 스크립트화일의 수정이 가능하다면 아래와 같이
proc 옵션을 수정하여 쉽게 해결 할 수 있다.

proc mode=oracle dbms=v8 unsafe_null=yes iname=[proc_file.pc]

위의 옵션중 unsafe_null=yes가 ORA-01405에러의 해결열쇠이다.
하지만 unsafe_null=yes옵션은 mode=oracle, dbms=v8 또는 v7일때 그 기능이 유효하므로 같이
써줘야한다.