[오라클 함수활용]IS_DATE를 활용한 날짜 예외처리 및 조회

문자로 저장된 날짜를 날짜 타입으로 변환시 오류가 발생하는 경우가 있습니다

 

오류가 발생하는 원인은 현실에 존재하지 않는 날짜를 날짜타입으로 변환하려고 하기 때문인데요

 * 예 2019-02-31일을 날짜형으로 변환함, 2019년 2월은 28일까지 있으므로 변환오류 발생

 

대부분 프로그래밍 언어가 제공하는 IS_DATE()나 IS_NUMBER() 등의 함수를 활용하여 예외처리를 하지만

 

오라클에서는 위 함수를 기본적으로 제공하지 않기 때문에 만들어서 사용해야 합니다

 

 

 

 

아래는 샘플데이터를 만들어서 IS_DATE를 활용하는 예제입니다

 

#샘플데이터 생성

 

/* IS_DATE 연습 데이터 저장 테이블 */
CREATE TABLE EXER_CHCK_IS_DATE
(
ID VARCHAR2(10)
,REG_DATE VARCHAR2(10)
);



/* IS_DATE 연습용 데이터 생성 */
INSERT INTO EXER_CHCK_IS_DATE
SELECT  'ID'||DBMS_RANDOM.STRING('X',8) ID                   /* ID */
        ,TRUNC(DBMS_RANDOM.VALUE(1900,2020))  ||'-'||        /* 1900년~2020년 */  
         LPAD(TRUNC(DBMS_RANDOM.VALUE(1,12)),2,0)  ||'-'||   /* 1월~12월 */
         LPAD(TRUNC(DBMS_RANDOM.VALUE(1,35)),2,0)   REG_DATE /* 1~35일 */


FROM DUAL
CONNECT BY LEVEL <=1000;

COMMIT ;

/* REG_DATE칼럼을 TO_DATE로 조회 
 
 ORA-01839 오류발생
*/
SELECT ID
       ,REG_DATE
       ,TO_DATE(REG_DATE,'YYYY-MM-DD') REG_DATE_MODF

FROM EXER_CHCK_IS_DATE

 

위 구문으로 조회하면 아래와 같은 오류가 발생합니다(ORA-01839)

 

 

 

아래는 IS_DATE함수 생성구문입니다.

 

여러 스키마에 걸쳐서 함수를 만드는 것 보다, 유틸리티를 저장하는 스키마를 따로 생성하여 관리하는것을 권장드립니다. 여러 버전의 FN_IS_DATE가 만들어지면 관리도 힘들뿐더러 출력결과를 예상하기 어렵습니다

 

 

EXPECPTION 절에서 오류데이터를 처리하는 방법을 기재하면 되므로

필요에 따라 다음과 같은 방법으로 활용이 가능합니다 

 

결과값이 NULL이되거나 9999-12-31을 출력하거나(아래 예시와 같이)

 

RETURN 데이터 형을 NUMBER로 하여 문제가 없으면 0을 문제가 있으면 1을

 

또는 VARCHAR로 처리하여 TRUE FALSE로 출력할 수 있습니다

 

 

 

# IS_DATE()함수 생성

CREATE OR REPLACE FUNCTION FN_IS_DATE(V_STR_DATE IN VARCHAR2, V_DATE_FORMAT IN VARCHAR2 DEFAULT 'YYYY-MM-DD')
RETURN DATE
/* 함수로 날짜변환 해보고, 되면 변환된 것을 리턴하고 
   안되면 9999-12-31이나 NULL(주석)을  리턴한다
*/
IS    
     V_DATE DATE;

BEGIN

  V_DATE := TO_DATE(V_STR_DATE, V_DATE_FORMAT);

  RETURN V_DATE;

EXCEPTION
  WHEN OTHERS THEN 
  V_DATE :=TO_DATE('9999-12-31','YYYY-MM-DD');
  --V_DATE :=NULL ;
  
  RETURN V_DATE;

END;

 

 

 

위에서 만든 IS_DATE 함수를 활용하여 조회해봅니다

 

# IS_DATE()로 조회

/* REG_DATE 칼럼을 FN_IS_DATE로 조회
 
 변환 가능한 날짜와 예외처리된 날짜를 리턴하여 오류없이 조회가능 */

SELECT ID
       ,REG_DATE
--       ,TO_DATE(REG_DATE,'YYYY-MM-DD') REG_DATE_MODF
       ,FN_IS_DATE(REG_DATE,'YYYY-MM-DD') REG_DATE_MODF

FROM EXER_CHCK_IS_DATE

 

 

 

889행부터 REG_DATE_MODF 칼럼에 9999-12-31 데이터가 찍히는것을 볼 수 있습니다(랜덤함수로 데이터를 생성하였으므로 행수는 저의 DB기준입니다)

889행 부터의 원본 데이터(REG_DATE)는 현실에 존재하지 않는 날짜이므로 정정작업을 하여 해소하여야 합니다

 

 

 

아래와 같이 조건절에 오류데이터를 명시하여 해당내역을 가진 데이터만 조회 할 수 있습니다

 

아래내역을 추출하여 정정작업에 사용합니다

SELECT ID
       ,REG_DATE
--       ,TO_DATE(REG_DATE,'YYYY-MM-DD') REG_DATE_MODF
       ,FN_IS_DATE(REG_DATE,'YYYY-MM-DD') REG_DATE_MODF

FROM EXER_CHCK_IS_DATE
WHERE FN_IS_DATE(REG_DATE,'YYYY-MM-DD') = TO_DATE('9999-12-31','YYYY-MM-DD')

 

 

 

 

이상으로 포스팅을 마칩니다.