Oracle DATE 열에 저장된 잘못된(손상된) 값을 식별하는 방법

Oracle DATE 열에 저장된 잘못된(손상된) 값을 식별하는 방법

Oracle 10.2.0.5

테이블에서 DATE 열에 "잘못된" 값을 가진 행을 식별하는 가장 쉬운 방법은 무엇입니까?여기서 "잘못됨"은 날짜 값에 대한 Oracle 규칙을 위반하는 이진 표현을 의미합니다.

최근 열에 잘못된 날짜가 저장되어 문제가 발생했습니다.

쿼리 술어를 사용하여 특정 문제가 있는 행을 찾을 수 있었습니다.

  WHERE TO_CHAR(date_expr,'YYYYMMDDHH24MISS') = '00000000000000'

제가 가지고 있던 경우, 세기 바이트는 유효하지 않았습니다.

 select dump(h.bid_close_date) from mytable h where h.id = 54321

 Typ=12 Len=7: 220,111,11,2,1,1,1

세기 바이트는 100 + 2자리 세기여야 합니다.이 경우 세기 값이 "120"인 것처럼 100이 추가되어 연도가 "12011"이 되었습니다. (잘못된 DATE 값을 데이터베이스로 가져오는 유일한 방법은 OCI, 네이티브 7바이트 DATE 표현 사용)

이 경우 TO_CHAR 함수가 식별 가능한 문자열을 반환하여 wunky DATE 값을 식별하는 데 사용할 수 있습니다.

질문: DATE 열에서 "잘못된" 값을 가진 행을 식별하는 더 일반적인 접근 방식이나 더 쉬운 접근 방식이 있습니까(SQL SELECT 문을 사용하는 것이 좋습니다).

이것은 꽤 특이한 시나리오입니다(비록 전에 비슷한 것을 한 번 만난 적이 있지만).더 일반적인 문제는 날짜 열에서 문자열로 고정된 잘못된 날짜를 찾는 것입니다.독자적인 날짜 검증기를 구축하여 이에 대한 솔루션을 사용자의 상황에 맞게 조정할 수 있습니다.

이와 같은 것:

create or replace function is_a_date 
    ( p_date in date )
    return varchar2
is
    d date;
begin
    d := to_date(to_char(p_date,  'SYYYYMMDDHH24MISS'),  'SYYYYMMDDHH24MISS') ;
    if d != p_date then
        return 'not a proper date';
    else
        return 'good date';
    end if;
exception
    when others  then
        return 'not a date';
end;
/ 

이렇게 하면 날짜가 문자열로 변환되고 다시 반환됩니다.날짜 캐스팅에 의해 던져진 예외를 포착합니다.최종 제품이 입력 날짜와 동일하지 않으면 아마도 번역 과정에서 무언가가 손실되었을 것입니다. 솔직히 12011 날짜가 문자열에 성공적으로 캐스팅될 수 있을지 확신할 수 없기 때문에 이것은 벨트 앤 브레이스 방식입니다.테스트 데이터 없이 이 유틸리티를 작성하는 것은 조금 어렵습니다!

이 쿼리는 유효하지 않은 모든 날짜를 식별합니다.

 select h.id, dump(h.bid_close_date)
 from mytable h 
 where h.bid_close_date is not null
 and is_a_date(h.bid_close_date) != 'good date';

함수를 추가하지 않고 간단한 술어

TO_CHAR(date_col,'YYYYMMDDHH24MISS') = '000000000000'

Oracle DATE 열에 저장된 손상된 값을 식별할 수 있습니다.기능을 추가할 필요가 없습니다.손상된 날짜를 확인하려면 SQL SELECT 문에서 수행할 수 있어야 하며 사용자에게 데이터베이스에 대한 CREATE FUNCTION 권한이 필요하지 않습니다.

잘못된 월을 식별합니다.

SELECT rowid,
       pk_column,
       DUMP(date_column, 1010) AS dump1
FROM   table
WHERE  TO_NUMBER(SUBSTR(DUMP(date_column, 1010), INSTR(DUMP( date_column, 1010),
                                              ',', 1, 2
                                                     ) + 1,
                                  INSTR(DUMP(date_column, 1010), ',', 1, 3) - (
                                  INSTR(DUMP( date_column, 1010), ',', 1, 2) + 1
                                  ))) = 0; 

동일한 where 절을 사용하여 업데이트한 결과, 이 경우 월 번호가 0인 것을 확인했습니다.

저는 아마도 날짜가 법적 가치의 범위 내에 있는지 확인하는 것을 통해 체크 제약 조건으로 구현할 수 있는 방법을 선택할 것입니다.

그러나 1066-10-14의 날짜를 갖는 것이 유효한지 자문해 보십시오.이는 법적 가치이지만, 예를 들어 그날 인쇄된 송장이 없을 수 있습니다.따라서 유효하지 않은 날짜 검사를 응용프로그램의 컨텍스트에서 유효하다고 생각하는 항목에 적용할 수 있습니다.

는 는나가 요.SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0".

날짜가 잘못된 행을 식별하기 위해 다음을 수행했습니다.

declare
    cursor mydates is select table_pk, your_date_col from table;
    c_date table.your_date_col%type;
    c_pk table.table_pk%type;
    testrow table.your_date_col%type;
begin
    open mydates;
    loop
    begin
        fetch mydates into c_pk, c_date;
        exit when mydates%notfound;
        testrow := TO_TIMESTAMP(c_date,'YYYY-MM-DD HH24:MI:SS');
    exception when others then
        dbms_output.put_line('bad file: ' || c_pk);
    end;
    end loop;
    close mydates;
end;

그래서 제가 한 일은 커서를 만들고, 요소들을 반복적으로 살펴보고, 각각을 테스트하고, 식별자를 표시하는 것뿐이었습니다. 그래서 저는 나쁜 행들을 쉽게 찾을 수 있었습니다.

언급URL : https://stackoverflow.com/questions/8734540/how-to-identify-invalid-corrupted-values-stored-in-oracle-date-columns