Oracle에서 Varchar2로 선언된 Field의 Numeric 데이터 비교

뭐.... 그냥 개발하면 이런 Case가 없겠지만...

Package 제품이다 보니 사용자쪽에서 Varchar2로 선언된 Field에 Numeric Data를 집어 넣은 Case가 발생하였다.

Mysql에서는 조건절에 일반 numeric data처럼 ''를 사용하지 않으면 잘 실행되었는데..

( 즉.. SELECT ... FROM Campaign WHERE insertionOrder > 6 )

Oracle에서는 위와 같이 하면... "ORA-01722: invalid number" 오류가 발생한다.

예전에는 됐던것 같은데.... 하고 생각해보니 내가 이렇게 썼던 기억은 없는것 같다.^^

물론 ''를 줄 경우에는 character 비교가 되니 10 같은 값은 numeric으로 비교하면 6보다 커도 character로 비교되면 6보다 작아 나오지 않는 다는...-.-

염대리님하고 상의를 해본 결과 아래와 같은 Query가 나왔다.

select insertionOrder from Campaign where decode(length(translate(insertionOrder, 'A1234567890','A')),null, to_number(insertionOrder), 0) > 6

위에 파란색으로 표시된 부분은 Numeric value일 경우 null을 Return해 주는 Query이다.

( translate function(A,B,C) 는 A 문자열내에 있는 B character들을 C character들로 치환 해 주는 function이다. 즉 위의 경우는 A->A , 1 ~ 9 , 0-> 치환 없음 이 되므로 translate 실행 결과 숫자 데이터들은  null(확실하지는 않지만 length 결과가 null이 되는 걸로 봐서는 null이 맞을 듯 싶다.)이 되면서 이를 통해 numeric data 여부를 판단할 수 있는 것이다. 

염대리님이 찾아 낸 또다른 Tip은

select * from ( select * from Campaign where LOWER(insertionOrder) = UPPER(insertionOrder)) where insertionOrder > 6

인데 애석하게도 우리 System은 Character insensitive 환경을 구성하여

일단 Character 값을 LOWER(insertionOrder) = UPPER(insertionOrder) 로는 제거할 수 없다는...-.-


( 그나저나 어떤 설정으로 Character insensitive하게 구성했는지.. 

  아직 까진 그 option을 본적이 없는데...

  그러고 보니 "_" 두개 이상일경우에 like 검색 잘 되지 않던 Issue도 option으로 해결 한것 같은데.. 

  흠.. 언젠가 본사 쪽 사람이랑 얘기할 기회가 생기면 이 부분에 대해 한번 물어 봐야 겠다.^^ )