[SQL] 오라클 호환성 VARCHAR2

오라클 포팅작업을 하다보니 오라클 호환성 기능에 대해 새롭게 살펴보게 되는 것 같다.

오라클에서는 숫자형으로 선언된 컬럼에 공백값(‘’)을 입력하면 NULL 값 처리가 된다. DB2에서는 공백값을 입력하면 형 변환 오류가 발생하여 입력이 실패한다. 만일 문자열로 된 숫자 값 (예: ‘1234’) 식의 값을 입력하면 자동 캐스팅 되어 입력된다.

 

iBatis에 대해서는 잘 모르지만, DBMS Migration 작업을 하다보면 XML 문서에 SQL문들을 모아 놓고 dynamic 하게 SQL을 실행하는 환경을 보게 된다. 아마도 XML문서에 값들도 동적으로 받아 처리되도록 하다보니, 매개변수의 데이터 유형이 DB에 선언된 컬럼 유형과는 다르게 지정되는 경우가 많다. (아마도 개발편의성 때문에 대부분은 VARCHAR로 일괄 선언하는 경우가 많지 않을까 싶다.)

 

이렇게 되다 보니 숫자형으로 선언된 컬럼에 문자열 값이 입력이 되다보니 DB2에서는 자동으로 형 변환을 못하고 에러를 반환하는 경우가 많다. (V9.5 이후로 오라클 호환성 기능이 나오면서 자동형변환 처리는 많이 편해진 편이다.)

 

오라클처럼 공백값(‘’)을 NULL 값으로 처리하도록 하기 위해서는 호환성 벡터 0x20 값이 적용되어야 하며, 적용 후 DB 생성 작업을 해야 된다.

참고로 DB2 정보센터 문서에는 “호환성 벡터”를 적용하는 경우 DB locale은 유니코드(utf-8)을 사용할 것을 권고하고 있다.

또한 DB2 DeveloperWorks 기술문서에 VARCHAR2 (0x20) 값은 위험할 수 있다고 권고되는 값이니 업무 상 필요한 경우에만 사용하도록 주의해야 한다.

 

공백(‘’) 테스트 (오라클)

SQL> create table t1 (c1 integer, c2 integer); SQL> insert into t1 values (1,''); SQL> insert into t1 values (2,'   ');          ORA-01722: SQL> insert into t1 values (3,NULL); SQL> select * from t1;         C1         C2 ---------- ----------          1          3

 

 

공백(‘’) 테스트 (DB2 – 호환성 적용 전)

db2 "create table t1(c1 int, c2 int)" db2 “insert into t1 values (1,'')” SQL0420N  함수 "INTEGER"의 문자열 인수에 유효하지 않은 문자가 있습니다. db2 “insert into t1 values (2,'   ')” SQL0420N  함수 "INTEGER"의 문자열 인수에 유효하지 않은 문자가 있습니다. db2 "insert into t1 values (3,'1234')" DB20000I  The SQL command completed successfully. db2 "insert into t1 values (4,NULL)" DB20000I  The SQL command completed successfully. db2 "select * from t1" C1          C2 ----------- -----------           3        1234           4           -   2 record(s) selected.

 

공백(‘’) 테스트 (DB2 – 호환성 적용 후)

db2set DB2_COMPATIBILITY_VECTOR=20 db2stop force db2start db2 create db test db2 connect to test db2 "create table t1(c1 int, c2 int)" db2 “insert into t1 values (1,'')” DB20000I  The SQL command completed successfully. db2 "insert into t1 values (2,'   ')" SQL0420N  Invalid character found in a character string argument of the function "INTEGER".   db2 "select * from t1" C1          C2 ----------- -----------           1           -   1 record(s) selected.

 

참고로 0x20 값 적용하고 DB 생성 후, 0x20 값을 제거해도 공백(‘’)은 NULL 값으로 인식된다.

 

참고

1) developerWorks 오라클 호환성 기술문서

https://www.ibm.com/developerworks/mydeveloperworks/blogs/SQLTips4DB2LUW/entry/about_the_db2_compatibility_vector_and_what_not_to_say_at_the_dinner_table282?lang=en

 

2) 호환성 벡터 값

http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index.jsp?topic=%2Fcom.ibm.db2.luw.apdv.porting.doc%2Fdoc%2Fr0052867.html