데이터베이스 질문과 답변

ORA-01843 관련 문의 드립니다.(윈도우즈 서버 2012 문제일수도...) 0 4 2,031

by 지누기 서버 장비 ORA-01843 [2013.11.04 12:40:16]

안녕하세요, 개발 중에 문의드릴 것이 있어 이렇게 올리게 되었습니다.

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//주차, 주 시작일, 주 종료일 구하기 펑션
create or replace FUNCTION FN_GET_WEEKDAYS(IN_DATE VARCHAR2
                                                                      ,IN_TYPE VARCHAR2
                                                                       ) RETURN VARCHAR2
IS
     LI_WEEK_NUM VARCHAR2(2)  := '';
     LI_START_DT VARCHAR2(8)    := '';
     LI_END_DT VARCHAR2(8)    := '';
BEGIN
    LI_WEEK_NUM := TO_CHAR(TO_DATE(IN_DATE, 'YYYYMMDD'), 'IW');
    IF (IN_TYPE = 'WN') THEN
        RETURN LI_WEEK_NUM;
    ELSIF (IN_TYPE = 'SD') THEN
        LI_START_DT := (LI_WEEK_NUM-2)*7+NEXT_DAY((TRUNC(TO_DATE(SUBSTR(IN_DATE, 0, 4)||'0101','YYYYMMDD'),'MM')-1),'일');
        RETURN LI_START_DT;
    ELSIF (IN_TYPE = 'ED') THEN
        LI_END_DT := (LI_WEEK_NUM-2)*7+NEXT_DAY((TRUNC(TO_DATE(SUBSTR(IN_DATE, 0, 4)||'0101','YYYYMMDD'),'MM')-1),'일')+ 6;
        RETURN LI_END_DT;
    END IF;
END FN_GET_WEEKDAYS;
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

현재 다음과 같은 함수를 사용하여 날짜와 플래그를 넣을 경우, (예: fn_get_weekdays('20130101', 'WN'))

날짜에 해당하는 주차, 주의 첫날과 주의 마지막날을 구하는 함수를 만들어 사용하고 있습니다.

제가 개발하는 프로그램에는 이 펑션의 날짜 부분에 varchar2(8) 의 날짜 데이터 컬럼을 넣어서 사용하고 있었습니다.

그런데 이번에 윈도우즈 서버 2012 장비에 오라클 서버(11g 엔터프라이즈 버전)를 설치하고, 제 프로그램을 돌렸는데

계속 '지정한 월이 부적합합니다'라고 에러가 나기 시작했습니다. 서버사이드에서는 쿼리가 문제가 없는데,

클라이언트 사이드에서만 계속 이 에러가 납니다. 다른 테스트용 데스크탑에 서버 설치해서 사용할 때는 이런 문제가

없었거든요. 쿼리 문제일 수도 있는 것 같지만, 이게 윈도우즈 서버 2012의 문제인 것 같긴 한데,

이와 유사한 경험이 있으시거나 해결해보신 분이 있으시면

답변 좀 부탁드리겠습니다. 항상 많은 도움 얻고 있습니다. 읽어주셔서 감사합니다.
by 마농 [2013.11.04 12:56:59]
정확한 에러 상황을 알려주세요.
1. 호출형태
  - 특정 1개의 값을 이용해 펑션을 호출했는지?
  - 아니면 테이블의 컬럼을 이용해 호출 했는지?
  ==> 테이블에 날짜형식으로 부적합한 자료가 있을 수 있습니다.
  ==> 이 경우 해당 자료를 찾아 수정해주시면 됩니다.
2. 에러 시점
  - 펑션 호출할때 에러가 나는건지?
  - 펑션호출후 리턴값을 다른데서 사용할 때 에러나는건지?
  ==> li_start_dt, li_end_dt 는 varchar2(8) 컬럼인데 여기에 대입하는 값은 Date 네요
  ==> Date 를 Varchar2로 묵시적 형변환이 일어나게 되는데
  ==> 이때 원치않는 형식(예 11/11/04)으로 입력될 수 있습니다.
  ==> 명시적으로 TO_CHAR 함수를 통해 형변환 하세요.

잘 모르겠으면 호출시 사용한 쿼리를 직접 올려주세요.
by 지누기 [2013.11.04 15:09:32]
1. 호출형태 : 쿼리 안에서 테이블의 날짜 컬럼(varchar2(8)자리 : '20130101')형태의 컬럼을 

펑션에 넣고 셀렉트 하는 형식입니다.

SELECT RSLTINSYMD
             ,FN_GET_WEEKDAYS(RSLTINSYMD, 'WN') AS WEEKNUM
             ,FN_GET_WEEKDAYS(RSLTINSYMD, 'SD')  AS START_DT
             ,FN_GET_WEEKDAYS(RSLTINSYMD, 'ED')  AS END_DT
    FROM GERM_INFO_TBL
 WHERE RSLTINSYMD BETWEEN :IN_START_DT AND :IN_END_DT;

다음의 쿼리로 사용하고 있습니다.

데이터는 체크하여 'xxxx0230', 'xxxx1232' 등의 오류가 날 수 있는 데이터는 없음을 확인했습니다.

2. 에러 호출 시점이 언급하신 대로 펑션의 li_start_dt, li_end_dt에 값을 넣을 때 발생하고 있습니다.

말씀해주신대로 각각 TO_CHAR로 감싸줬는데, 역시 서버에 직접 접속해서 돌린 쿼리에서는 이상이 없고,

클라이언트 프로그램으로 돌렸을 때만 동일한 부분에서 오류가 나고 있습니다.

에러 로그로 확인한 오류가 다음과 같습니다.

Unable to execute query : ORA-01846: not a valid day of the week
ORA-06512: at "ARCUSER.FN_GET_WEEKDAYS", line 15  -- li_start_dt에 값을 넣는 부분입니다.

혹시 제가 틀린 부분이 있는지 확인 부탁드릴께요.

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//주차, 주 시작일, 주 종료일 구하기 펑션

create or replace FUNCTION FN_GET_WEEKDAYS(IN_DATE VARCHAR2
                                                                      ,IN_TYPE VARCHAR2
                                                                       ) RETURN VARCHAR2
IS
     LI_WEEK_NUM VARCHAR2(2)  := '';
     LI_START_DT VARCHAR2(8)    := '';
     LI_END_DT VARCHAR2(8)    := '';
BEGIN
    LI_WEEK_NUM := TO_CHAR(TO_DATE(IN_DATE, 'YYYYMMDD'), 'IW');
    IF (IN_TYPE = 'WN') THEN
        RETURN LI_WEEK_NUM;
    ELSIF (IN_TYPE = 'SD') THEN
        LI_START_DT := TO_CHAR((LI_WEEK_NUM-2)*7+NEXT_DAY((TRUNC(TO_DATE(SUBSTR(IN_DATE, 0, 4)||'0101','YYYYMMDD'),'MM')-1),'일'), 'yy/mm/dd');
        RETURN LI_START_DT;
    ELSIF (IN_TYPE = 'ED') THEN
        LI_END_DT := TO_CHAR((LI_WEEK_NUM-2)*7+NEXT_DAY((TRUNC(TO_DATE(SUBSTR(IN_DATE, 0, 4)||'0101','YYYYMMDD'),'MM')-1),'일')+ 6, 'yy/mm/dd');
        RETURN LI_END_DT;
    END IF;
END FN_GET_WEEKDAYS;

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
by 마농 [2013.11.04 15:29:29] 
-- 펑션에 Exception 구문 추가해서 확인해 보세요.
BEGIN
  -- 생략 --
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'Error('||in_date||')';
END;
/
-- 오류자료 추출
SELECT *
  FROM germ_info_tbl
 WHERE fn_get_weekdays(rsltinsymd, 'WN') LIKE 'Error%'
by 지누기 [2013.11.05 09:11:56]
li_start_dt, li_end_dt 구하는 부분을 변경하여 해결하였습니다.

LI_START_DT := TO_CHAR(trunc(to_date(IN_DATE, 'yyyyMMdd'), 'IW'), 'yy/mm/dd');

LI_END_DT := TO_CHAR(trunc(to_date(IN_DATE, 'yyyyMMdd'), 'IW') + 6, 'yy/mm/dd');

NEXT_DAY 함수를 쓰고 안에서 TO_DATE로 변수를 지정할 때, TO_DATE로 변환하는 IN_DATE가

철자로 붙었을 때 마농님께서 언급해주셨던 묵시적 형변환이 일어나서 계속 에러가 있었던 것으로

추측됩니다. 정확한 원인은 계속 테스트해보겠습니다. 조언 감사했습니다~^^
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입