[이기종 DB간 Data 공유] MS-SQL에서 Oracle에 있는 Data 가져와서 동기화 맞추는 기능 구현

 새로 도입하는 시스템이 MS-SQL을 사용한다고 해서 기존에 사용하던 Oracle에서 일부 Data는 초기에 한번, 일부 Data는 주기적으로 동기화를 맞춰주는 작업을 해야합니다. 초기에 한번 이전해 주는 작업은 MS-SQL의 SSIS라는 기능을 이용해서 넘겨주기로 했고, 기능 테스트도 이미 했죠. 문제는 주기적으로 Data의 동기화를 맞춰주는 작업인데요. DB 복제 솔루션을 이용하거나 ETL을 이용하면 쉽게 해결되겠지만, 예산이 없어서 보류하고요. 다른 방법을 찾아보기로 결정했었죠. 그래서 알아본게 다음의 두가지입니다.

1. Oracle Gateway를 이용한 이기종 DBMS간 Data 공유
2. MS-SQL의 Open Query를 이용한 이기종 DBMS간 Data 공유

 Oracle DB에 원천 Data가 있으니 변경된 내용이 생길때 바로바로 적용하기 위해서는 Oracle Gateway를 이용하는게 좋을것 같긴 한데요. 문제는 이건 제가 한번도 해보질 않아서 프로젝트 일정에 맞출수가 없을 것 같더군요. 그래서 MS-SQL에서 구현하기로 했습니다. Open query는 예전에도 써본적이 있거든요.
자꾸 이러면 실력이 늘지 않을텐데 큰일입니다. ^^;  자~ 이제 MS-SQL의 Open query를 이용해서 주기적으로 Data 동기화를 맞춰주는 기능을 구현해보도록 하겠습니다.

 제일 먼저 할 일은 MS-SQL 서버에 Oracle client를 설치하는 일입니다. 설치가 완료되면 Data를 가져올 Oracle server의 정보를 tnsnames.ora에 설정해주면 됩니다. 이제 Linked server를 설정해줄 차례입니다.

 Open Query를 사용하기 위해서 우선 Oracle과 Link를 걸어야 합니다. SQL Server Management Studio에서 [연결된 서버]라는 항목에 추가를 해주면 됩니다. 이때 [Oracle provider for OLEDB]를 선택해서 사용하시는게 좋습니다. MS에서 제공하는 OLEDB provider를 사용할 수도 있는데, 이건 뭔가 문제가 있어보이더군요. Open query에서는 테스트해보지 않았지만, SSIS에서 Oracle의 Data를 가져올때 데이터형을 좀 잘못 가져오는것 같더군요. tnsnames.ora에 설정된 이름과 사용할 Oracle의 계정 정보 등을 입력해서 linked server를 생성하시면 됩니다.

 이제 Open query에 대해서 얘기해볼까합니다. Open query를 이용하면 MS-SQL에서 Oracle DB에 insert, select, update, delete를 실행 할 수 있습니다.

USE TESTDB
GO

SELECT * FROM OPENQUERY(LINK_NAME, `SELECT * FROM 테이블이름`);


UPDATE OPENQUERY(LINK_NAME, `SELECT A FROM 테이블이름 WHERE 조건1`)
SET A='AA' WHERE 조건2;

DELETE OPENQUERY(LINK_NAME, `SELECT A FROM 테이블이름 WHERE 조건1`)

 위의 query문을 보시면 update문을 실행시키는데 select문을 실행시키고 set을 해주는 부분이 보일겁니다. 그리고 조건이 2개 붙은 부분이 보일겁니다. [조건2]가 붙는 이유는 [조건1]에 ms-sql의 프로시져에서 사용하는 변수를 적용할 수 없기 때문입니다. 변수로 처리하지 않고 문자열로 넣어서 처리할 수 있는 부분은 [조건1]에 다 넣어주고, 변수로 받아서 넣어줘야할 내용은 [조건2]에 넣어주면 됩니다. 아래에 프로시져 코드를 보시면 바로 이해하실 겁니다.

이제 배치작업을 위해서 해당 기능을 프로시져로 만듭니다. 제가 작성한 간단한 샘플 코드를 보여드리겠습니다.

USE [testdb]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [tdb].[user_sync]

AS

    DECLARE ora_cursor CURSOR FOR

    SELECT TRG_FLAG, USER_ID, USER_NAME, TEL, CEL, EMAIL, ZIP_CD, ADDR1, ADDR2

       FROM OPENQUERY(VOCA2, 'SELECT B.TRG_FLAG, B.USER_ID, A.USER_NAME, A.TEL, A.CEL, 

                                                               A.EMAIL, A.ZIP_CD, A.ADDR1, A.ADDR2

                                                     FROM USER_TEST A, USER_TRG B

                                                    WHERE B.USER_ID = A.USER_ID(+)  AND B.MIG_DATE IS NULL');

    OPEN ora_cursor

    DECLARE @TRG_FLAG        VARCHAR(1)

    DECLARE @USER_ID            VARCHAR(30)

    DECLARE @USER_NAME            VARCHAR(50)

    DECLARE @TEL            VARCHAR(50)

    DECLARE @CEL            VARCHAR(50)

    DECLARE @EMAIL        VARCHAR(100)

    DECLARE @ZIP_CD            VARCHAR(7)

    DECLARE @ADDR1        VARCHAR(100)

    DECLARE @ADDR2        VARCHAR(100)

    FETCH NEXT FROM ora_cursor INTO @TRG_FLAG, @ USER_ID , @USER_NAME, @USER_TEL, @USER_CEL, @USER_EMAIL, @ZIP_CD, @USER_ADDR1, @USER_ADDR2

    WHILE @@FETCH_STATUS = 0

    BEGIN

        IF (@TRG_FLAG = 'I')

            BEGIN

                INSERT INTO USER_TEST  ( USER_ID , USER_NAME, USER_TEL, USER_CEL, USER_EMAIL, ZIP_CD, USER_ADDR1, USER_ADDR2)

                VALUES(@USER_ID, @USER_NAME, @USER_TEL, @USER_CEL, @USER_EMAIL, @ZIP_CD, @USER_ADDR1, @USER_ADDR2);

                UPDATE OPENQUERY(VOCA2, 'SELECT MIG_FLAG, MIG_DATE, USER_ID FROM  USER_TRG  WHERE TRG_FLAG=''I'' AND MIG_FLAG=''N''')

                    SET MIG_FLAG='Y', MIG_DATE=GETDATE()

                    WHERE USER_ID=@USER_ID;

            END;

        ELSE IF (@TRG_FLAG = 'U')

            BEGIN

                UPDATE  USER_TEST    SET USER_NAME=@USER_NAME, USER_TEL=@USER_TEL, USER_CEL=@USER_CEL, USER_EMAIL=@USER_EMAIL, ZIP_CD=@ZIP_CD,

                                           USER_ADDR1=@USER_ADDR1, USER_ADDR2=@USER_ADDR2

                WHERE USER_ID=@USER_ID;

                UPDATE OPENQUERY(VOCA2, 'SELECT MIG_FLAG, MIG_DATE, USER_ID FROM  USER_TRG   WHERE TRG_FLAG=''U'' AND MIG_FLAG=''N''')

                    SET MIG_FLAG='Y', MIG_DATE=GETDATE()

                    WHERE USER_ID=@USER_ID;

            END;

        ELSE IF (@TRG_FLAG = 'D')

            BEGIN

                DELETE FROM  USER_TEST    WHERE USER_ID=@USER_ID;

                UPDATE OPENQUERY(VOCA2, 'SELECT MIG_FLAG, MIG_DATE, USER_ID FROM  USER_TRG   WHERE TRG_FLAG=''D'' AND MIG_FLAG=''N''')

                    SET MIG_FLAG='Y', MIG_DATE=GETDATE()

                    WHERE USER_ID=@USER_ID;

            END;

        INSERT INTO dbo.USER_TEST_LOG(REG_DATE, USER_ID, TRG_FLAG) VALUES(GETDATE(), @USER_ID, @TRG_FLAG);

        

    FETCH NEXT FROM ora_cursor INTO @TRG_FLAG, @USER_ID, @USER_NAME, @USER_TEL, @USER_CEL, @USER_EMAIL, @ZIP_CD, @USER_ADDR1, @USER_ADDR2 

    END;

    CLOSE ora_cursor                                        -- 커서 클로즈

    DEALLOCATE ora_cursor                              -- 커서 메모리 반환

GO

자 이제 배치작업으로 주기적으로 실행시키는 일만 남았네요.