출처 : http://blog.naver.com/dudwo00/130013004957

분석함수는 8i 버전에서 persnal edition, enterprise edition에서 지원하고, 
9i버전에서는 모든 버전에 지원된다. 문법은 다음과 같다. 

SELECT Analytic_Function ( arguments )
       OVER( [ PARTITION BY 칼럼 ] [ ORDER BY 절 ] [ Windowing 절] )
FROM 테이블 명;


     - Partition By : 전체 집합을 기준에 의해 소그룹으로 나눔     
     - Order By     : PARTITION BY에 나열된 그룹을 정렬함
     - Windowing : 펑션의 대상이 되는 행 기준으로 범위를 세밀하게 조정
                   (메뉴얼: window IS a physical or logical SET of rows)

* Windowing절에 대한 설명

1. ROWS/RANGE  UNBOUNDED PRECEDING/
                         CURRENT ROW/
                         value_expr PRECEDING

2. ROWS/RANGE BETWEEN UNBOUNDED PRECEDING/
                      CURRENT ROW/
                      value_expr PRECEDING/FOLLOWING 
                      AND UNBOUNDED FOLLOWING
                      CURRENT ROW/
                      value_expr PRECEDING/FOLLOWING 
: RANGE는 값이며, ROWS는 행의 수를 의미한다.                      

- RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING
  : BETWEEN Current_Row -50 AND 150 의 파티션 내의 모든행이 window
    즉, Current_Row의 값 - 50 ~ Current_Row의 값 + 150의 값은 모두 하나의 그룹

- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  : 현재행을 기준으로 파티션 내의 앞/뒤 한건이 Window

- RANGE UNBOUNDED PRECEDING
  : 현재 행을 기준으로 파티션 내의 첫 번째 행까지가 Window

- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING     
  : 현재행을 기준으로 파티션 내의 첫 번째 행부터 끝 행까지가 Window. 즉, 파티션 모두가 Window



select
        ename
      , sal
      , sum(sal) over(partition by deptno order by sal) ASC순으로_부서별_sal누적
      , sum(sal) over(partition by deptno) 부서별_sal합계
      , sum(sal) over(order by deptno, sal) deptno_sal_ASC정렬후_누적
      , sum(sal) over() sal총계
from emp
order by deptno, sal;
--over() 안의 oder by는 정렬 후 해당 행과 이전행을 모두 함친 것의 계산 - 여기에서는 SUM()
--over() 안의 partition by는 group by 역할을 한다. 분석에 대한 범위를 지정하는 역할.

/*
ENAME            SAL ASC순으로_부서별_SAL누적 부서별_SAL합계 DEPTNO_SAL_ASC정렬후_누적    SAL총계
---------- --------- ------------------------ -------------- ------------------------- ----------
MILLER       1300.00                     1300           8750                      1300      29025
CLARK        2450.00                     3750           8750                      3750      29025
KING         5000.00                     8750           8750                      8750      29025
SMITH         800.00                      800          10875                      9550      29025
ADAMS        1100.00                     1900          10875                     10650      29025
JONES        2975.00                     4875          10875                     13625      29025
SCOTT        3000.00                    10875          10875                     19625      29025
FORD         3000.00                    10875          10875                     19625      29025
JAMES         950.00                      950           9400                     20575      29025
WARD         1250.00                     3450           9400                     23075      29025
MARTIN       1250.00                     3450           9400                     23075      29025
TURNER       1500.00                     4950           9400                     24575      29025
ALLEN        1600.00                     6550           9400                     26175      29025
BLAKE        2850.00                     9400           9400                     29025      29025

14 rows selected
*/


* 사원 중 직무별로 가장 많은 월급을 받는 사람의 사원번호, 직무명, 월급을 출력하라

SELECT T1.EMPNO, T2.JOB, T2.MAX_SAL
FROM EMP T1,
          (SELECT JOB, MAX(SAL) AS MAX_SAL 
            FROM EMP
            GROUP BY JOB) T2
WHERE T1.JOB = T2.JOB
AND T1.SAL = T2.MAX_SAL;

EMPNO JOB          MAX_SAL
----- --------- ----------
7788 ANALYST         3000
7902 ANALYST         3000
7934 CLERK           1300
7566 MANAGER         2975
7839 PRESIDENT       5000
7499 SALESMAN        1600

6 rows selected

* 아래와 같이 분석함수를 사용하면 위와 똑같은 결과를 가져올 수 있다. --놀라운 것은 테이블을 한번만 읽어서 처리를 할 수 있다는 것이다. 


select empno, job, max_job_sal sal
from (
select 
         empno
       , job
       , sal
       , max(sal) over (partition by job) max_job_sal 
from emp ) 
where sal = max_job_sal;
 
EMPNO JOB              SAL
----- --------- ----------
7788 ANALYST         3000
7902 ANALYST         3000
7934 CLERK           1300
7566 MANAGER         2975
7839 PRESIDENT       5000
7499 SALESMAN        1600

6 rows selected




* 모든 사원의 월급의 평균에 가장 근접한 사원의 사번과 월급 출력
select empno, sal
from (
      select
              empno
            , min(abs_sal) over() min_sal
            , abs_sal
            , sal
      from (
            select
                    empno
                  , sal
                  , abs(sal - avg(sal) over()) abs_sal
            from emp) )
where abs_sal = min_sal;
 
 
SQL> select avg(sal) from emp;

  AVG(SAL)
----------
2073.21428

EMPNO       SAL
----- ---------
7782   2450.00
 

부서별로 일련번호를 붙이되 각각의 부서마다 1로 시작하는 일련번호를 붙인다.
SELECT
        deptno
      , empno
      , ename
      , row_number() over(PARTITION BY deptno ORDER BY deptno)
FROM emp;
 
DEPTNO EMPNO ENAME      ROW_NUMBER()OVER(PARTITIONBYDE
------ ----- ---------- ------------------------------
    10  7782 CLARK                                   1
    10  7839 KING                                    2
    10  7934 MILLER                                  3
    20  7369 SMITH                                   1
    20  7876 ADAMS                                   2
    20  7902 FORD                                    3
    20  7788 SCOTT                                   4
    20  7566 JONES                                   5
    30  7499 ALLEN                                   1
    30  7698 BLAKE                                   2
    30  7654 MARTIN                                  3
    30  7900 JAMES                                   4
    30  7844 TURNER                                  5
    30  7521 WARD                                    6

14 rows selected







* 부서별 월급이 많은 순서대로 순위구하기
SELECT
        empno
      , ename
      , sal
      , rank() over(PARTITION BY deptno ORDER BY sal DESC)
FROM emp;
 
--rank() over(PARTITION BY deptno ORDER BY sal DESC)를 
--rank() over(PARTITION BY deptno ORDER BY sal ASC)로 고치면 역순이 된다. 
--rank()는 동일 순위에 대하여 동일한 값을 리턴하지만 공백이 생긴다.
--즉, 1위에 해당된느 행이 2개이면 2는 존재하지 않고, 바로 3으로 넘어간다.

EMPNO ENAME            SAL RANK()OVER(PARTITIONBYDEPTNOOR
----- ---------- --------- ------------------------------
7839 KING         5000.00                              1
7782 CLARK        2450.00                              2
7934 MILLER       1300.00                              3
7788 SCOTT        3000.00                              1
7902 FORD         3000.00                              1
7566 JONES        2975.00                              3
7876 ADAMS        1100.00                              4
7369 SMITH         800.00                              5
7698 BLAKE        2850.00                              1
7499 ALLEN        1600.00                              2
7844 TURNER       1500.00                              3
7521 WARD         1250.00                              4
7654 MARTIN       1250.00                              4
7900 JAMES         950.00                              6



SELECT
        empno
      , ename
      , sal
      , dense_rank() over(PARTITION BY deptno ORDER BY sal DESC)
FROM emp;
 
dense_rank()는 rank()와는 달리 1위가 2개 존재하면 다음의 순위는 2가 된다.

EMPNO ENAME            SAL DENSE_RANK()OVER(PARTITIONBYDE
----- ---------- --------- ------------------------------
7839 KING         5000.00                              1
7782 CLARK        2450.00                              2
7934 MILLER       1300.00                              3
7788 SCOTT        3000.00                              1
7902 FORD         3000.00                              1
7566 JONES        2975.00                              2
7876 ADAMS        1100.00                              3
7369 SMITH         800.00                              4
7698 BLAKE        2850.00                              1
7499 ALLEN        1600.00                              2
7844 TURNER       1500.00                              3
7521 WARD         1250.00                              4
7654 MARTIN       1250.00                              4
7900 JAMES         950.00                              5


* TOP n개 가져오기
SELECT 
         empno
       , ename
       , sal
       , rank
FROM (
      SELECT
              empno
            , ename
            , sal
            , rank() over(PARTITION BY deptno ORDER BY sal DESC) rank
      FROM emp)
WHERE rank <= 3;    
경우에 따라서 rank(), dense_rank()를 사용한다. 

EMPNO ENAME            SAL       RANK
----- ---------- --------- ----------
7839 KING         5000.00          1
7782 CLARK        2450.00          2
7934 MILLER       1300.00          3
7788 SCOTT        3000.00          1
7902 FORD         3000.00          1
7566 JONES        2975.00          3
7698 BLAKE        2850.00          1
7499 ALLEN        1600.00          2
7844 TURNER       1500.00          3



* 피봇
SELECT
         deptno
       , max(CASE WHEN rank = 1 THEN sal END) rank_1
       , max(CASE WHEN rank = 2 THEN sal END) rank_2
       , max(CASE WHEN rank = 3 THEN sal END) rank_3
FROM (
      SELECT 
               sal
             , deptno
             , rank
      FROM (
            SELECT
                    sal
                  , deptno
                  , rank() over(PARTITION BY deptno ORDER BY sal DESC) rank
            FROM emp)
      WHERE rank <= 3 )
GROUP BY deptno      
 
DEPTNO     RANK_1     RANK_2     RANK_3
------ ---------- ---------- ----------
    10       5000       2450       1300
    20       3000                  2975
    30       2850       1600       1500
      

* 앞 뒤행 가져오기 lead() lag()
* 입사일에 차이에 따른 전/후 월급차이
SELECT 
         empno
       , ename
       , sal - lag(sal, 1) over(ORDER BY hiredate) pre
       , sal
       , sal - lead(sal, 1) over(ORDER BY hiredate) NEXT
FROM emp;
EMPNO ENAME             PRE       SAL       NEXT
----- ---------- ---------- --------- ----------
7369 SMITH                    800.00       -800
7499 ALLEN             800   1600.00        350
7521 WARD             -350   1250.00      -1725
7566 JONES            1725   2975.00        125
7698 BLAKE            -125   2850.00        400
7782 CLARK            -400   2450.00        950
7844 TURNER           -950   1500.00        250
7654 MARTIN           -250   1250.00      -3750
7839 KING             3750   5000.00       4050
7900 JAMES           -4050    950.00      -2050
7902 FORD             2050   3000.00       1700
7934 MILLER          -1700   1300.00      -1700
7788 SCOTT            1700   3000.00       1900
7876 ADAMS           -1900   1100.00 

* 특정 범위내의 첫행/끝행 
SELECT DEPTNO,ENAME,SAL,
       FIRST_VALUE(ENAME) OVER(PARTITION BY DEPTNO 
                               ORDER BY SAL DESC
                               ROWS BETWEEN UNBOUNDED PRECEDING 
                                        AND UNBOUNDED FOLLOWING) AS FV,
       LAST_VALUE(ENAME) OVER(PARTITION BY DEPTNO
                              ORDER BY SAL DESC
                              ROWS BETWEEN UNBOUNDED PRECEDING 
                                       AND UNBOUNDED FOLLOWING) AS LV                                        
FROM EMP;
DEPTNO ENAME            SAL FV         LV
------ ---------- --------- ---------- ----------
    10 KING         5000.00 KING       MILLER
    10 CLARK        2450.00 KING       MILLER
    10 MILLER       1300.00 KING       MILLER
    20 SCOTT        3000.00 SCOTT      SMITH
    20 FORD         3000.00 SCOTT      SMITH
    20 JONES        2975.00 SCOTT      SMITH
    20 ADAMS        1100.00 SCOTT      SMITH
    20 SMITH         800.00 SCOTT      SMITH
    30 BLAKE        2850.00 BLAKE      JAMES
    30 ALLEN        1600.00 BLAKE      JAMES
    30 TURNER       1500.00 BLAKE      JAMES
    30 WARD         1250.00 BLAKE      JAMES
    30 MARTIN       1250.00 BLAKE      JAMES
    30 JAMES         950.00 BLAKE      JAMES


* RATIO_TO_REPORT : 그룹에서 현재 행의 비율
SELECT JOB, ENAME, SAL, 
       RATIO_TO_REPORT(SAL) OVER(PARTITION BY JOB) AS RR
FROM EMP
ORDER BY JOB, ENAME;
 
JOB       ENAME            SAL         RR
--------- ---------- --------- ----------
ANALYST   FORD         3000.00        0.5
ANALYST   SCOTT        3000.00        0.5
CLERK     ADAMS        1100.00 0.26506024
CLERK     JAMES         950.00 0.22891566
CLERK     MILLER       1300.00 0.31325301
CLERK     SMITH         800.00 0.19277108
MANAGER   BLAKE        2850.00 0.34441087
MANAGER   CLARK        2450.00 0.29607250
MANAGER   JONES        2975.00 0.35951661
PRESIDENT KING         5000.00          1
SALESMAN  ALLEN        1600.00 0.28571428
SALESMAN  MARTIN       1250.00 0.22321428
SALESMAN  TURNER       1500.00 0.26785714
SALESMAN  WARD         1250.00 0.22321428


SELECT ENAME,SAL,
       COUNT(*) OVER(ORDER BY SAL
                     RANGE BETWEEN 149 PRECEDING 
                               AND 0 FOLLOWING) AS m_cnt
FROM EMP;
 
ENAME            SAL      M_CNT
---------- --------- ----------
SMITH         800.00          1
JAMES         950.00          2  ---> 950 - 150  ~  950 + 0, 만약 149로 범위를 줄이면 1로 된다. 
ADAMS        1100.00          2
WARD         1250.00          3
MARTIN       1250.00          3
MILLER       1300.00          3
TURNER       1500.00          1
ALLEN        1600.00          2
CLARK        2450.00          1
BLAKE        2850.00          1
JONES        2975.00          2
SCOTT        3000.00          4
FORD         3000.00          4
KING         5000.00          1

저자 - Marko Asplund

Oracle Text는 모든 오라클 데이터베이스 제품(Express Edition 포함)에 기본적으로 포함되어 제공되는 강력한 검색 테크놀로지입니다. Oracle Text에서 제공되는 개발 API를 이용하여 완성도 높은 컨텐트 검색 애플리케이션을 쉽게 구현할 수 있습니다.

Oracle Text는 SQL 와일드카드 매칭 기능을 보완하는 기능을 제공하며, 구조형/비구조형 문서를 검색하는 용도로 활용됩니다. Oracle Text는 기본적 불리언 연산자(AND, OR, NOT, NEAR 등)의 조합을 지원하며 사운덱스(soundex) 검색, 퍼지(fuzzy) 검색, 결과 랭킹과 같은 고급 기능을 함께 제공하고 있습니다. 또 마이크로소프트 오피스와 PDF를 포함하는 수백여 종의 파일 타입이 지원됩니다. Oracle Text는 다양한 검색 관련 유즈 케이스 및 스토리지 구조에 적합한 검색 모델을 제공합니다. Oracle Text가 적용될 수 있는 몇 가지 예로 e-비즈니스, 문서/기록 관리, 이슈 트랙킹 시스템 등을 들 수 있습니다. 텍스트 정보는 데이터베이스 내에 구조적인 형태로 저장되거나, 또는 로컬 파일 시스템이나 웹에 비구조적인 형태로 저장될 수 있습니다.

Oracle Text는 커스텀 쿼리 연산자, DDL 신택스 확장, PL/SQL 프로시저, 데이터베이스 뷰 등을 포함하는 포괄적인 SQL 기반 검색 API를 제공합니다. 애플리케이션 개발자는 Oracle Text API를 이용하여 인덱싱, 쿼리, 보안, 프리젠테이션, 소프트웨어 설정 등의 컨트롤을 확보할 수 있습니다. 이러한 기능은 커스텀 애플리케이션이 아닌 상용 애플리케이션을 개발하는 경우에 특히 유용합니다. 상용 소프트웨어 제품을 개발하는 경우에는 소프트웨어의 설정을 가능한 한 단순한 형태로 유지하는 것이 관건이 됩니다. 애플리케이션의 복잡성을 최소화하는 것은 기술 지원, 유지 보수, 업그레이드 등 향후 제품 라이프사이클 전반에 걸친 이점을 제공합니다.

Oracle Text는 또 고성능 환경에서 구현이 까다로운 문서 레벨 승인(document level authorization) 기능을 지원하고 있습니다. 관계형 데이터 및 비구조형 데이터에 대해 텍스트가 혼합된 쿼리를 사용하는 것 또한 가능합니다. 이는 단일 쿼리 내에서 전체 텍스트 검색과 승인 기능을 함께 사용할 수 있음을 의미합니다. 최종적인 결과를 얻기 위해 결과 셋을 분리하고 필터링을 적용하는 작업이 최소화되므로 애플리케이션 개발 업무의 단순화가 가능합니다. Oracle Text는 성능 최적화와 관련된 애플리케이션 개발자의 업무 부담을 줄여 줍니다.

Oracle Text는 또 프로그래밍 언어로부터 독립적이며 Java, PHP 애플리케이션과 유연하게 연동됩니다.

언젠가 필자는 엔터프라이즈 컨텐트 관리(ECM) 시스템의 검색 기능을 개선하는 작업에 참여한 일이 있습니다. 필자는 프로젝트를 시작하면서 제일 먼저 Oracle Text의 활용 가능성을 검토하는 작업에 착수했습니다. 검토 결과, Oracle Text가 애플리케이션의 검색 기능 구현에 매우 적합한 도구임이 판명되었습니다. Oracle Text는 고급 검색 기능을 포함하고 있으며, 다양한 파일 유형을 지원할 뿐 아니라, 커스터마이즈 및 확장이 매우 용이합니다. 기존 검색 테크놀로지들의 경우 데이터베이스 외부의 파일 컨텐트를 검색하고, 다시 데이터베이스 메타데이터를 검색하고, 결과를 승인하고, 마지막으로 서로 분리된 결과 셋을 병합하는 번거로운 절차를 요구한다는 문제가 있었습니다. Oracle Text는 이러한 모든 작업을 데이터베이스 내부에서 수행합니다. 이 프로젝트의 ECM 시스템은 이미 오라클 데이터베이스에 메타데이터를 저장하고 있었습니다. 이미 기반이 마련되어 있는 환경에서, 추가적인 비용을 들이지 않고 Oracle Text를 활용하는 것은 어찌 보면 당연한 선택이었습니다.

데이터베이스에서 텍스트 검색 쿼리를 구현하기 위한 일반적인 방법이 아래와 같습니다.

SELECT * FROM issues

WHERE LOWER(author) LIKE '%word1%' AND LOWER(author) LIKE '%word2%' ...    

여기서 각각의 키워드는 컬럼별로 제각각 매치되며, 키워드의 순서에 관계없이 매치가 가능합니다. 하지만 관계형 데이터베이스는 이러한 쿼리를 효과적으로 실행하도록 설계되지 않았으며, 따라서 이러한 접근법은 확장성 면에서 심각한 한계를 갖습니다. 물론 별도의 인덱싱/검색 솔루션을 고안해 낼 수도 있겠지만, 이미 검색 테크놀로지 구현 비용을 별도로 지출한 경우가 아니라면 비용효율적인 솔루션을 얻어내기가 매우 어려울 것입니다.

본 문서는 가상의 이슈 트래킹 애플리케이션에서 Oracle Text를 활용하는 방법을 소개하고 있습니다. 애플리케이션에서 사용자에 의해 생성된 '이슈'는 메타데이터와 첨부 파일로 구성됩니다. 애플리케이션은 Oracle Text를 이용하여 메타데이터 및 첨부 파일 컨텐트에 대한 전체 텍스트 검색을 수행합니다.

본 예제는 Linux 기반 Oracle Database XE 환경에서 테스트 되었으며, 다른 오라클 플랫폼에서도 문제 없이 동작할 것으로 예상됩니다.

인덱싱 프로세스와 검색

Oracle Text는 사용자가 컨텐트 검색을 수행하기 전에 조회 가능한 데이터 아이템에 대한 인덱스를 생성합니다. 이는 검색 성능의 개선을 위해 반드시 필요한 작업입니다. Oracle Text 인덱싱 프로세스는 파이프라인을 모델로 하며, 데이터 저장소에서 인출된 데이터 아이템은 일련의 변환 과정을 거친 후 인덱스에 그 키워드가 추가됩니다. 인덱싱 프로세스는 여러 단계로 나뉘며, 각 단계별로 처리되는 엔티티는 애플리케이션 개발자에 의해 설정이 가능합니다.

Oracle Text는 여러 가지 인덱스 타입을 지원하며, 인덱스 타입의 선택은 그 활용 목적에 따라 달라집니다. 대용량 문서에 대한 전체 텍스트 검색을 위해서는 CONTEXT 인덱스 타입이 가장 적합합니다. 인덱싱 프로세스는 다음과 같은 단계를 거쳐 진행됩니다.

1. 데이터 인출: 데이터 저장소(웹 페이지, 데이터베이스 LOB, 로컬 파일 시스템 등)로부터 데이터가 인출되어 다음 단계의 처리를 위해 데이터 스트림으로 전달됩니다.
2. 필터링: 서로 다른 파일 포맷의 데이터를 일반 텍스트로 변환하기 위해 필터링 프로세스가 적용됩니다. 인덱싱 파이프라인의 다른 컴포넌트들은 일반 텍스트 포맷만을 처리하며 원본 파일 포맷(Microsoft Word, Excel 등)에 대해 알지 못합니다.
3. 섹셔닝(Sectioning): 섹셔너(sectioner)가 원본 데이터 아이템의 구조에 대한 메타데이터를 추가합니다.
4. 렉싱(Lexing): 캐릭터 스트림이 아이템의 언어를 기준으로 단어(word)로 분할됩니다.
5. 인덱싱: 키워드가 실제 인덱스에 추가됩니다.

인덱스가 생성되고 나면, 엔드 유저는 애플리케이션에서 SQL 쿼리를 이용하여 검색을 실행할 수 있습니다.

Oracle Text의 설정

Oracle Text는 기본적으로 Oracle Database XE와 함께 설치됩니다. 다른 데이터베이스 버전의 경우, Oracle Text를 별도로 설치해야 합니다. Oracle Text의 설치를 완료한 다음에는, 데이터베이스 사용자를 생성하고 이 사용자에게 CTXAPP 롤을 할당해 주기만 하면 됩니다. CTXAPP 롤을 할당 받은 사용자는 인덱스 관리 작업을 수행할 수 있는 권한을 가집니다.

CREATE USER ot1 IDENTIFIED BY ot1;

GRANT connect,resource, ctxapp TO ot1;

파일 인덱스

이제 이슈 트래킹 시스템에 저장된 첨부 파일의 인덱싱을 위해 텍스트 테이블을 생성해야 합니다. 첨부 파일은 파일 시스템에 저장되어 있습니다. 애플리케이션의 데이터 모델이 요구하는 컬럼과 별도로, 텍스트 기반 테이블은 절대 파일 경로와 포맷 컬럼 정보를 포함하고 있습니다.

CREATE TABLE files (

id NUMBER PRIMARY KEY,

issue_id NUMBER,

path VARCHAR(255) UNIQUE,

ot_format VARCHAR(6)

);

INSERT INTO files VALUES (1, 1, '/tmp/oracletext/found1.txt', NULL);

INSERT INTO files VALUES (2, 2, '/tmp/oracletext/found2.doc', NULL);

INSERT INTO files VALUES (3, 2, '/tmp/oracletext/notfound.txt', 'IGNORE');

ot_format의 값은 인덱싱 과정에서 Oracle Text에 의해 계산됩니다. NULL 값은 파일을 위한 필터가 자동으로 선택됨을 의미하며, IGNORE는 파일을 완전히 무시함을 의미합니다.

텍스트 인덱스를 생성하기 위한 구문이 아래와 같습니다.

CREATE INDEX file_index ON files(path) INDEXTYPE IS ctxsys.context

PARAMETERS ('datastore ctxsys.file_datastore format column ot_format');

이 구문은 인덱싱 프로세스가 베이스 테이블에 저장된 경로 정보를 이용하여 파일 시스템에서 파일을 인출하고, 그 컨텐트에 대한 필터링, 인덱싱을 수행하도록 지시하고 있습니다. CONTEXT 인덱스는 대소문자를 구분하지 않으며 정확한 단어 단위 매칭을 지원합니다. 여기서 인덱싱 프로세스의 커스터마이즈 작업을 통해 접두어(prefix), 접미어(suffix) 매치 등을 지원할 수 있습니다.

필터링은 대부분의 경우 각 파일의 포맷을 명시하지 않은 상태에서도 올바르게 동작하지만, format 컬럼을 베이스 테이블에 포함시킴으로써 인덱싱 프로세스를 보다 정교하게 제어할 수 있다는 장점이 있습니다. 예를 들어, format 컬럼을 이용하여 특정 파일 타입이 인덱싱되지 않도록 설정하는 것이 가능합니다. 이 옵션은 Oracle Text에서 파일 포맷 중 일부만을 지원하고자 하는 경우에 특히 유용합니다.

Oracle Text는 메타데이터의 전체 텍스트 검색을 위해서도 이용될 수 있습니다. 샘플 애플리케이션에서는 각 이슈에 관련한 메타데이터를 저장하기 위해 issues 테이블이 사용되고 있습니다. 이 테이블은 아래와 같이 정의됩니다.

CREATE TABLE issues (

id NUMBER,

author VARCHAR(80),

summary VARCHAR(120),

description CLOB,

ot_version VARCHAR(10)

);

The ot_version column is the index column, which can be used to force reindexing for certain documents. The table can be populated with test data:

INSERT INTO issues VALUES (1, 'Jane', 'Text does not make tea',

'Oracle Text is unable to make morning tea', 1);

INSERT INTO issues VALUES (2, 'John', 'It comes in the wrong color',

'I want to have Text in pink', 1);

사용자 인덱스

Oracle Text는 서로 다른 데이터 소스로부터의 데이터 인덱싱을 지원합니다. 샘플 애플리케이션에서 이슈 메타데이터의 전체 텍스트 검색을 위해 Oracle Text를 활용할 수 있습니다. 디폴트 상태에서 인덱스 값은 단일 컬럼에 저장됩니다. 하지만 여러 테이블의 데이터를 조합하고자 하는 경우라면 커스텀 PL/SQL 필터 프로시저를 생성해야 합니다. 여기에서는 스토리지 추상화(storage abstraction)의 한 방법으로서 프로시저를 생성하여 활용하기로 합니다. 인덱싱 프로세스는 텍스트 테이블의 모든 로우를 스캔하고, 각 로우에 대해 필터 프로시저를 호출합니다. 그런 다음 필터 프로시저는 해당 이슈에 관련하여 인덱싱 되어야 하는 모든 텍스트를 반환합니다.

-- declare indexing procedure

CREATE PACKAGE ot_search AS

PROCEDURE issue_filter(rid IN ROWID, tlob IN OUT NOCOPY CLOB);

END ot_search;

/

-- define indexing procedure

CREATE PACKAGE BODY ot_search AS

PROCEDURE issue_filter(rid IN ROWID, tlob IN OUT NOCOPY CLOB) IS

BEGIN

FOR c1 IN (SELECT author, summary, description FROM issues WHERE rowid = rid)

LOOP

dbms_lob.writeappend(tlob, LENGTH(c1.summary)+1, c1.summary || ' ');

dbms_lob.writeappend(tlob, LENGTH(c1.author)+1, c1.author || ' ');

dbms_lob.writeappend(tlob, LENGTH(c1.description), c1.description);

END LOOP;

END issue_filter;

END ot_search;

/

-- define datastore preference for issues

BEGIN

ctx_ddl.create_preference('issue_store', 'user_datastore');

ctx_ddl.set_attribute('issue_store', 'procedure', 'ot_search.issue_filter');

ctx_ddl.set_attribute('issue_store', 'output_type', 'CLOB');

END;

/

-- index issues

CREATE INDEX issue_index ON issues(ot_version) INDEXTYPE IS ctxsys.context

PARAMETERS ('datastore issue_store');

검색

CONTAINS 연산자는 CONTEXT 인덱스의 검색을 위해 사용됩니다. 본 예제에서는 키워드의 조합을 위해 간단한 불리언 연산자만을 이용하고 있습니다. 하지만 CONTAINS 연산자는 사운덱스(soundex) 매치와 같은 고급 기능도 함께 제공하고 있음을 참고하시기 바랍니다. Oracle Text에 의해 지원되는 언어가 사용되고 있는 경우, 퍼지(fuzzy) 매칭과 스테밍(stemming)이 디폴트로 활성화됩니다. CONTAINS 연산자와 함께 fuzzy(), $ 등의 쿼리 연산자를 이용하여 이러한 고급 기능을 쉽게 활용할 수 있습니다. 접두어/접미어 매칭을 위해 CONTAINS 쿼리에 와일드카드 문자를 활용할 수도 있습니다. 간단한 검색 쿼리의 예가 아래와 같습니다.

SELECT id FROM issues WHERE CONTAINS(ot_version, 'color AND pink', 1) > 0;

SELECT id FROM issues WHERE CONTAINS(ot_version, 'jane OR john', 1) > 0;

인덱스의 유지 보수

베이스 테이블 데이터는 인덱스에 의해 복제되므로, 인덱스와 데이터를 주기적으로 동기화하는 작업이 필요합니다. 인덱스 유지 보수 프로시저는 CTX_DDL PL/SQL 패키지에 포함되어 있습니다. 베이스 테이블의 변경 사항을 반영하여 인덱스를 업데이트하는 예가 아래와 같습니다.

EXECUTE ctx_ddl.sync_index('issue_index', '2M');

동기화 프로시저는 인덱스 네임과 작업에 사용되는 메모리 사이즈를 입력 매개변수로 받아 들입니다. 데이터베이스에서 이 작업을 일정 간격으로 자동 실행하도록 설정하는 것도 물론 가능합니다. 또 운영 체제 또는 다른 스케줄링 기능을 통해 동기화 작업을 실행할 수도 있습니다. UNIX 시스템의 경우 아래와 같은 쉘 스크립트를 Cron 작업 스케줄링 시스템에 등록하여 동기화를 수행할 수 있습니다.

#!/bin/sh

export ORACLE_SID=orcl

export ORAENV_ASK=NO

source /usr/local/bin/oraenv

sqlplus ot1/ot1@XE > synch.log <<EOF

WHENEVER SQLERROR EXIT 5;

EXECUTE ctx_ddl.sync_index('issue_index', '2M');

EOF

그 밖에도 CTX_DDL 패키지에는 인덱스 최적화, 인덱스 조각 모음, 데이터 폐기 등에 관련한 유용한 프로시저들이 포함되어 있습니다.

문제가 발생한 경우에는 CTX_USER_INDEX_ERROR 뷰에서 인덱싱 에러를 추적할 수 있습니다.

데이터베이스는 인덱스 컬럼의 변경 결과를 기준으로 문서의 변경 여부를 추적합니다. 따라서 Oracle Text에서 특정 문서의 인덱스를 재생성해야 하는 경우, 해당 로우의 인덱스 컬럼을 아래와 같은 방법으로 업데이트할 수 있습니다.

UPDATE files SET path=path WHERE id = 4;

위 구문을 실행하면 인덱스가 동기화될 때 id가 4인 파일의 인덱스가 업데이트됩니다.

저자 - Tom Kyte

오라클 전문가 Tom Kyte가 ROWNUM의 동작 원리와 활용 방법에 대해 설명합니다.

이번 호의 Ask Tom 컬럼은 지금까지와는 조금 다른 내용을 담고 있습니다. 필자는 오라클 데이터베이스에서 Top-N 쿼리와 페이지네이션(pagination) 쿼리를 구현하는 방법에 대해 자주 질문을 받곤 합니다. 하나의 컬럼을 통해 이러한 질문에 한꺼번에 대답하기 위한 방편으로, < Effective Oracle by Design (Oracle Press, 2003)> 의 내용을 인용하기로 했습니다. 컬럼의 포맷에 맞게 책의 내용이 다소 수정되었음을 참고하시기 바랍니다.

결과 셋의 제한

ROWNUM은 오라클 데이터베이스가 제공하는 마술과도 같은 컬럼입니다. 이 때문에 많은 사용자들이 문제를 겪기도 합니다. 하지만 그 원리와 활용 방법을 이해한다면 매우 유용하게 사용할 수 있습니다. 필자는 주로 두 가지 목적으로 ROWNUM을 사용합니다.

• Top-N 프로세싱: 이 기능은 다른 일부 데이터베이스가 제공하는 LIMIT 구문과 유사합니다.
• 쿼리 내에서의 페이지네이션(pagination) – 특히 웹과 같은 "stateless" 환경에서 자주 활용됩니다. 필자는 asktom.oracle.com 웹 사이트에서도 이 테크닉을 사용하고 있습니다.

두 가지 활용 방안을 설명하기 전에, 먼저 ROWNUM의 동작 원리에 대해 살펴 보기로 하겠습니다

ROWNUM의 동작 원리

ROWNUM은 쿼리 내에서 사용 가능한 (실제 컬럼이 아닌) 가상 컬럼(pseudocolumn)입니다. ROWNUM에는 숫자 1, 2, 3, 4, ... N의 값이 할당됩니다. 여기서 N 은 ROWNUM과 함께 사용하는 로우의 수를 의미합니다. ROWNUM의 값은 로우에 영구적으로 할당되지 않습니다(이는 사람들이 많이 오해하는 부분이기도 합니다). 테이블의 로우는 숫자와 연계되어 참조될 수 없습니다. 따라서 테이블에서 "row 5"를 요청할 수 있는 방법은 없습니다. "row 5"라는 것은 존재하지 않기 때문입니다.

또 ROWNUM 값이 실제로 할당되는 방법에 대해서도 많은 사람들이 오해를 하고 있습니다. ROWNUM 값은 쿼리의 조건절이 처리되고 난 이후, 그리고 sort, aggregation이 수행되기 이전에 할당됩니다. 또 ROWNUM 값은 할당된 이후에만 증가(increment) 됩니다. 따라서 아래 쿼리는 로우를 반환하지 않습니다.

select * 
  from t 
 where ROWNUM > 1;

첫 번째 로우에 대해 ROWNUM > 1의 조건이 True가 아니기 때문에, ROWNUM은 2로 증가하지 않습니다. 아래와 같은 쿼리를 생각해 봅시다.

select ..., ROWNUM
  from t
 where <where clause>
 group by <columns>
having <having clause>
 order by <columns>;

이 쿼리는 다음과 같은 순서로 처리됩니다.

1. FROM/WHERE 절이 먼저 처리됩니다.
2. ROWNUM이 할당되고 FROM/WHERE 절에서 전달되는 각각의 출력 로우에 대해 증가(increment) 됩니다.
3. SELECT가 적용됩니다.
4. GROUP BY 조건이 적용됩니다.
5. HAVING이 적용됩니다.
6. ORDER BY 조건이 적용됩니다.

따라서 아래와 같은 쿼리는 에러가 발생할 수 밖에 없습니다.

select * 
  from emp 
 where ROWNUM <= 5 
 order by sal desc;

이 쿼리는 가장 높은 연봉을 받는 다섯 명의 직원을 조회하기 위한 Top-N 쿼리로 작성되었습니다. 하지만 실제로 쿼리는 5 개의 레코드를 랜덤하게(조회되는 순서대로) 반환하고 salary를 기준으로 정렬합니다. 이 쿼리를 위해서 사용되는 가상코드(pseudocode)가 아래와 같습니다.

ROWNUM = 1
for x in 
( select * from emp )
loop
    exit when NOT(ROWNUM <= 5)
    OUTPUT record to temp
    ROWNUM = ROWNUM+1
end loop
SORT TEMP

위에서 볼 수 있듯 처음의 5 개 레코드를 가져 온후 바로 sorting이 수행됩니다. 쿼리에서 "WHERE ROWNUM = 5" 또는 "WHERE ROWNUM > 5"와 같은 조건은 의미가 없습니다. 이는 ROWNUM 값이 조건자(predicate) 실행 과정에서 로우에 할당되며, 로우가 WHERE 조건에 의해 처리된 이후에만 increment 되기 때문입니다.

올바르게 작성된 쿼리가 아래와 같습니다.

select *
  from  
( select * 
    from emp 
   order by sal desc ) 
 where ROWNUM <= 5;

위 쿼리는 salary를 기준으로 EMP를 내림차순으로 정렬한 후, 상위의 5 개 레코드(Top-5 레코드)를 반환합니다. 아래에서 다시 설명되겠지만, 오라클 데이터베이스가 실제로 전체 결과 셋을 정렬하지 않습니다. (오라클 데이터베이스는 좀 더 지능적인 방식으로 동작합니다.) 하지만 사용자가 얻는 결과는 동일합니다.

ROWNUM을 이용한 Top-N 쿼리 프로세싱

일반적으로 Top-N 쿼리를 실행하는 사용자는 다소 복잡한 쿼리를 실행하고, 그 결과를 정렬한 뒤 상위의 N 개 로우만을 반환하는 방식을 사용합니다. ROWNUM은 Top- N쿼리를 위해 최적화된 기능을 제공합니다. ROWNUM을 사용하면 대량의 결과 셋을 정렬하는 번거로운 과정을 피할 수 있습니다. 먼저 그 개념을 살펴보고 예제를 통해 설명하기로 하겠습니다.

아래와 같은 쿼리가 있다고 가정해 봅시다.

select ... 
  from ... 
 where ... 
 order by columns;

또 이 쿼리가 반환하는 데이터가 수천 개, 수십만 개, 또는 그 이상에 달한다고 가정해 봅시다. 하지만 사용자가 실제로 관심 있는 것은 상위 N개(Top 10, Top 100)의 값입니다. 이 결과를 얻기 위한 방법에는 두 가지가 있습니다.

• 클라이언트 애플리케이션에서 쿼리를 실행하고 상위 N 개의 로우만을 가져오도록 명령
• • 쿼리를 인라인 뷰(inline view)로 활용하고, ROWNUM을 이용하여 결과 셋을 제한 (예: SELECT * FROM (your_query_here) WHERE ROWNUM <= N)

두 번째 접근법은 첫 번째에 비해 월등한 장점을 제공합니다. 그 이유는 두 가지입니다. 첫 번째로, ROWNUM을 사용하면 클라이언트의 부담이 줄어듭니다. 데이터베이스에서 제한된 결과 값만을 전송하기 때문입니다. 두 번째로, 데이터베이스에서 최적화된 프로세싱 방법을 이용하여 Top N 로우를 산출할 수 있습니다. Top-N 쿼리를 실행함으로써, 사용자는 데이터베이스에 추가적인 정보를 전달하게 됩니다. 그 정보란 바로 "나는N 개의 로우에만 관심이 있고, 나머지에 대해서는 관심이 없다"는 메시지입니다. 이제, 정렬(sorting) 작업이 데이터베이스 서버에서 어떤 원리로 실행되는지 설명을 듣고 나면 그 의미를 이해하실 수 있을 것입니다. 샘플 쿼리에 위에서 설명한 두 가지 접근법을 적용해 보기로 합시다.

select * 
  from t 
 order by unindexed_column;

여기서 T가 1백만 개 이상의 레코드를 저장한 큰 테이블이라고, 그리고 각각의 레코드가 100 바이트 이상으로 구성되어 있다고 가정해 봅시다. 그리고 UNINDEXED_COLUMN은 인덱스가 적용되지 않은 컬럼이라고, 또 사용자는 상위 10 개의 로우에만 관심이 있다고 가정하겠습니다. 오라클 데이터베이스는 아래와 같은 순서로 쿼리를 처리합니다.

1. T에 대해 풀 테이블 스캔을 실행합니다.
2. UNINDEXED_COLUMN을 기준으로 T를 정렬합니다. 이 작업은 "full sort"로 진행됩니다.
3. Sort 영역의 메모리가 부족한 경우 임시 익스텐트를 디스크에 스왑하는 작업이 수행됩니다.
4. 임시 익스텐트를 병합하여 상위 10 개의 레코드를 확인합니다.
5.쿼리가 종료되면 임시 익스텐트에 대한 클린업 작업을 수행합니다. .

결과적으로 매우 많은 I/O 작업이 발생합니다. 오라클 데이터베이스가 상위 10 개의 로우를 얻기 위해 전체 테이블을 TEMP 영역으로 복사했을 가능성이 높습니다.

그럼 다음으로, Top-N 쿼리를 오라클 데이터베이스가 개념적으로 어떻게 처리할 수 있는지 살펴 보기로 합시다.

select *
  from 
(select * 
   from t 
  order by unindexed_column)
 where ROWNUM < :N;

오라클 데이터베이스가 위 쿼리를 처리하는 방법이 아래와 같습니다.

1. 앞에서와 마찬가지로 T에 대해 풀-테이블 스캔을 수행합니다(이 과정은 피할 수 없습니다).
2. :N 엘리먼트의 어레이(이 어레이는 메모리에 저장되어 있을 가능성이 높습니다)에서 :N 로우만을 정렬합니다.

상위N 개의 로우는 이 어레이에 정렬된 순서로 입력됩니다. N +1 로우를 가져온 경우, 이 로우를 어레이의 마지막 로우와 비교합니다. 이 로우가 어레이의 N +1 슬롯에 들어가야 하는 것으로 판명되는 경우, 로우는 버려집니다. 그렇지 않은 경우, 로우를 어레이에 추가하여 정렬한 후 기존 로우 중 하나를 삭제합니다. Sort 영역에는 최대 N 개의 로우만이 저장되며, 따라서 1 백만 개의 로우를 정렬하는 대신N 개의 로우만을 정렬하면 됩니다.

이처럼 간단한 개념(어레이의 활용, N개 로우의 정렬)을 이용하여 성능 및 리소스 활용도 면에서 큰 이익을 볼 수 있습니다. (TEMP 공간을 사용하지 않아도 된다는 것을 차치하더라도) 1 백만 개의 로우를 정렬하는 것보다 10 개의 로우를 정렬하는 것이 메모리를 덜 먹는다는 것은 당연합니다.

아래의 테이블 T를 이용하면, 두 가지 접근법이 모두 동일한 결과를 제공하지만 사용되는 리소스는 극적인 차이를 보임을 확인할 수 있습니다.

create table t
as
select dbms_random.value(1,1000000) 
id, 
       rpad('*',40,'*' ) data
  from dual
connect by level <= 100000;

begin
dbms_stats.gather_table_stats
( user, 'T');
end;
/

Now enable tracing, via

exec 
dbms_monitor.session_trace_enable
(waits=>true);

And then run your top-N query with ROWNUM:

select *
  from
(select *
   from t
  order by id)
where rownum <= 10;
 

마지막으로 상위 10 개의 레코드만을 반환하는 쿼리를 실행합니다.

declare
cursor c is
select *
  from t
 order by id;
l_rec c%rowtype;
begin
    open c;
    for i in 1 .. 10
    loop
        fetch c into l_rec;
        exit when c%notfound;
    end loop;
    close c;
end;
/

이 쿼리를 실행한 후, TKPROF를 사용해서 트레이스 결과를 확인할 수 있습니다. 먼저 Top-N 쿼리 수행 후 확인한 트레이스 결과가 Listing 1과 같습니다.

Code Listing 1: ROWNUM을 이용한 Top-N 쿼리

select *
  from
(select *
   from t
  order by id)
where rownum <= 10

call         count     cpu	elapsed   disk     query      current    rows
--------     --------  -------  -------   -------  --------   --------   ------ 
Parse        1         0.00     0.00      0          0        0           0
Execute      1         0.00     0.00      0          0        0           0
Fetch        2         0.04     0.04      0        949        0          10
--------     --------  -------  -------   -------  --------   --------   ------ 
total        4         0.04     0.04      0        949        0          10

Rows                         Row          Source Operation
-----------------            ---------------------------------------------------
10                           COUNT STOPKEY (cr=949 pr=0 pw=0 time=46997 us)
10                           VIEW  (cr=949 pr=0 pw=0 time=46979 us)
10                           SORT ORDER BY STOPKEY (cr=949 pr=0 pw=0 time=46961 us)
100000                       TABLE ACCESS FULL T (cr=949 pr=0 pw=0 time=400066 us)

이 쿼리는 전체 테이블을 읽어 들인 후, SORT ORDER BY STOPKEY 단계를 이용해서 임시 공간에서 사용되는 로우를 10 개로 제한하고 있습니다. 마지막 Row Source Operation 라인을 주목하시기 바랍니다. 쿼리가 949 번의 논리적 I/O를 수행했으며(cr=949), 물리적 읽기/쓰기는 전혀 발생하지 않았고(pr=0, pw=0), 불과 400066 백만 분의 일초 (0.04 초) 밖에 걸리지 않았습니다. 이 결과를 Listing 2의 실행 결과와 비교해 보시기 바랍니다.

Code Listing 2: ROWNUM을 사용하지 않은 쿼리

SELECT * FROM T ORDER BY ID
call         count     cpu	elapsed   disk     query      current    rows
--------     --------  -------  -------   -------  --------   --------   ------ 
Parse         1        0.00     0.00        0        0        0           0
Execute       2        0.00     0.00        0        0        0           0
Fetch        10        0.35     0.40      155      949        6          10
--------     --------  -------  -------   -------  --------   --------   ------ 
total        13        0.36     0.40      155      949        6          10

Rows                         Row          Source Operation
-----------------            ---------------------------------------------------
10                           SORT ORDER BY (cr=949 pr=155 pw=891 time=401610 us)
100000                       TABLE ACCESS FULL T (cr=949 pr=0 pw=0 time=400060 us)

Elapsed times include waiting for the following events:

Event waited on                  Times
------------------------------   ------------
direct path write temp           33
direct path read temp             5

결과가 완전히 다른 것을 확인하실 수 있습니다. "elapsed/CPU time"이 크게 증가했으며, 마지막 Row Source Operation 라인을 보면 그 이유를 이해할 수 있습니다. 정렬 작업은 디스크 상에서 수행되었으며, 물리적 쓰기(physical write) 작업이 "pw=891"회 발생했습니다. 또 다이렉트 경로를 통한 읽기/쓰기 작업이 발생했습니다. (10 개가 아닌) 100,000 개의 레코드가 디스크 상에서 정렬되었으며, 이로 인해 쿼리의 실행 시간과 런타임 리소스가 급증하였습니다.

ROWNUM을 이용한 페이지네이션

필자가 ROWNUM을 가장 즐겨 사용하는 대상이 바로 페이지네이션(pagination)입니다. 필자는 결과 셋의 로우 N 에서 로우 M까지를 가져오기 위해 ROWNUM을 사용합니다. 쿼리의 일반적인 형식이 아래와 같습니다.

select * 
  from ( select /*+ FIRST_ROWS(n) */ 
  a.*, ROWNUM rnum 
      from ( your_query_goes_here, 
      with order by ) a 
      where ROWNUM <= 
      :MAX_ROW_TO_FETCH ) 
where rnum  >= :MIN_ROW_TO_FETCH;

where

여기서,

• FIRST_ROWS(N)는 옵티마이저에게 "나는 앞부분의 로우에만 관심이 있고, 그 중 N 개를 최대한 빨리 가져오기를 원한다"는 메시지를 전달하는 의미를 갖습니다.
• :MAX_ROW_TO_FETCH는 결과 셋에서 가져올 마지막 로우로 설정됩니다. 결과 셋에서 50 번째 – 60 번째 로우만을 가져오려 한다면 이 값은 60이 됩니다.
• :MIN_ROW_TO_FETCH는 결과 셋에서 가져올 첫 번째 로우로 설정됩니다. 결과 셋에서 50 번째 – 60 번째 로우만을 가져오려 한다면 이 값은 50이 됩니다.

이 시나리오는 웹 브라우저를 통해 접속한 사용자가 검색을 마치고 그 결과를 기다리고 있는 상황을 가정하고 있습니다. 따라서 첫 번째 결과 페이지(그리고 이어서 두 번째, 세 번째 결과 페이지)를 최대한 빨리 반환해야 할 것입니다. 쿼리를 자세히 살펴 보면, (처음의 :MAX_ROW_TO_FETCH 로우를 반환하는) Top-N 쿼리가 사용되고 있으며, 따라서 위에서 설명한 최적화된 기능을 이용할 수 있음을 알 수 있습니다. 또 네트워크를 통해 클라이언트가 관심을 갖는 로우만을 반환하며, 조회 대상이 아닌 로우는 네트워크로 전송되지 않습니다.

페이지네이션 쿼리를 사용할 때 주의할 점이 하나 있습니다. ORDER BY 구문은 유니크한 컬럼을 대상으로 적용되어야 합니다. 유니크하지 않은 컬럼 값을 대상으로 정렬을 수행해야 한다면 ORDER BY 조건에 별도의 조건을 추가해 주어야 합니다. 예를 들어 SALARY를 기준으로 100 개의 레코드를 정렬하는 상황에서 100 개의 레코드가 모두 동일한 SALARY 값을 갖는다면, 로우의 수를 20-25 개로 제한하는 것은 의미가 없을 것입니다. 여러 개의 중복된 ID 값을 갖는 작은 테이블을 예로 들어 설명해 보겠습니다.

SQL> create table t
  2  as
  3  select mod(level,5) id, 
     trunc(dbms_random.value(1,100)) data 
  4    from dual
  5  connect by level <= 10000;
Table created.

ID 컬럼을 정렬한 후 148-150 번째 로우, 그리고 148–151 번째 로우를 쿼리해 보겠습니다.

SQL> select *
  2    from
  3  (select a.*, rownum rnum
  4     from
  5  (select id, data
  6     from t
  7   order by id) a
  8   where rownum <= 150
  9  )
 10   where rnum >= 148;

 ID           DATA           RNUM
-------       ----------     -----------
0             38             148
0             64             149
0             53             150

SQL>
SQL> select *
  2    from
  3  (select a.*, rownum rnum
  4     from
  5  (select id, data
  6     from t
  7   order by id) a
  8   where rownum <= 151
  9  )
 10   where rnum >= 148;

 ID           DATA           RNUM
-------       ----------     -----------
0             59             148
0             38             149
0             64             150
0             53             151

로우 148의 경우 DATA=38의 결과가 반환되었습니다. 두 번째 쿼리에서는 DATA=59의 결과가 반환되었습니다. 두 가지 쿼리 모두 올바른 결과를 반환하고 있습니다. 쿼리는 데이터를 ID 기준으로 정렬한 후 앞부분의 147 개 로우를 버린 후 그 다음의 3 개 또는 4 개의 로우를 반환합니다. 하지만 ID에 중복값이 너무 많기 때문에, 쿼리는 항상 동일한 결과를 반환함을 보장할 수 없습니다. 이 문제를 해결하려면 ORDER BY 조건에 유니크한 값을 추가해 주어야 합니다. 위의 경우에는 ROWID를 사용하면 됩니다.

SQL> select *
  2    from
  3  (select a.*, rownum rnum
  4     from
  5  (select id, data
  6     from t
  7   order by id, rowid) a
  8   where rownum <= 150
  9  )
 10   where rnum >= 148;

 ID           DATA           RNUM
-------       ----------     -----------
0             45             148
0             99             149
0             41             150

SQL>
SQL> select *
  2    from
  3  (select a.*, rownum rnum
  4     from
  5  (select id, data
  6     from t
  7   order by id, rowid) a
  8   where rownum <= 151
  9  )
 10   where rnum >= 148;

 ID           DATA           RNUM
-------       ----------     -----------
0             45             148
0             99             149
0             41             150
0             45             151

이제 쿼리를 반복 실행해도 동일한 결과를 보장할 수 있게 되었습니다. ROWID는 테이블 내에서 유니크한 값을 가집니다. 따라서 ORDER BY ID 조건과 ORDER BY ROWID 기준을 함께 사용함으로써 사용자가 기대한 순서대로 페이지네이션 쿼리의 결과를 확인할 수 있습니다.

출처 : http://www.oracle.com/technology/global/kr/oramag/oracle/06-sep/o56asktom.html

Oracle BPEL Process Manager를 이용한 PeopleSoft CRM과 Oracle E-Business Suite의 통합
저자: Lawrence Pravin

BPEL을 이용하여 PeopleSoft 8.9 CRM과 Oracle Applications 11i를 통합하는 방법을 단계별로 설명합니다

샘플 코드

invalid-file

많은 기업들이 서로 다른 부서, 지역, 지사 별로 다양한 이기종 애플리케이션을 운영하고 있습니다. 비즈니스 조직의 요구사항을 만족하기 위해서는 다수의 ERP 시스템이 필요할 수 있으며, 이로 인해 데이터의 파편화(fragmentation)가 발생할 수 있습니다. 이러한 시스템의 통합 작업은 복잡할 뿐 아니라 비표준적인 방법으로 처리되는 것이 일반적입니다. 또 의사결정을 위해 다수의 ERP 시스템에 존재하는 분산된 정보를 수집하는 과정에서 많은 시간과 노력이 소모되곤 합니다.

BPEL은 이기종 시스템의 통합을 위한 표준적, 프로세스 중심적 방법론을 제공합니다. Oracle BPEL Process Manager는 SOA(service-oriented architecture)의 구현을 위한 Oracle Fusion Middleware의 핵심 툴로써, Microsoft, IBM, SAP, BEA 등에 의해 제안된 이후 통합 프로젝트의 비용, 복잡성 절감 및 유연성 개선을 위한 엔터프라이즈 청사진으로써 활용되고 있는 BPEL 표준을 지원합니다.

이번 BPEL Cookbook 시리즈에서는 BPEL을 이용하여 PeopleSoft 8.9 CRM과 Oracle Application 11i를 통합하는 방법을 설명하기로 합니다. 특히, 샘플 비즈니스 시나리오를 통해 PeopleSoft의 모듈을 웹 서비스의 형태로 공개하고, 오라클 애플리케이션과의 연동을 위해 BPEL Applications 어댑터를 설정하는 방법을 예시하게 될 것입니다.

일반적으로 주문 관리 비즈니스 시나리오에서는, 주문이 CRM 시스템에 입력된 후 백-오피스 ERP에 의해 처리되는 과정을 거칩니다. 본 문서의 예제에서는, PeopleSoft를 프론트 애플리케이션으로 사용하여 마케팅, 세일즈, 서비스 업무를 관리하고 Oracle E-Business Suite를 ERP (주문 관리, 인벤토리, 재무) 솔루션으로 사용하고 있습니다. 여기에서는 Quote-to-Order(주문 견적) 프로세스를 중심적으로 다루기로 합니다.

견적 및 주문 입력을 위한 비즈니스 프로세스는 CRM 시스템 상에서 실행되며, 주문 처리 작업은 ERP 시스템을 통해 실행됩니다. 전체 Quote-to-Order 비즈니스 프로세스는 작업 능률의 최적화를 위해 완전 자동화됩니다.

비즈니스 프로세스의 통합 과정에서 구현되는 기능이 다음과 같습니다. (그림 1 참고):

• PeopleSoft에서 견적을 세일즈 주문으로 변환하거나 Order Capture 스크린을 이용하는 방법으로 세일즈 주문(sales order)가 생성됩니다.
• 주문이 입력되면, 시스템은 필요한 정보를 점검하고 상태(status)를 OPEN으로 변경합니다. (그렇지 않은 경우 HOLD 상태가 유지됩니다.)
• Sales Order Process는 주문 정보를 호출하여 Integration Process에 제출합니다. 이 과정에서 BPEL Process Manager가 호출됩니다.
• BPEL Process Manager는 메시지 데이터를 Oracle ERP Order Management 모듈에서 요구하는 포맷으로 변환합니다.
• 세일즈 주문 생성 작업은 Oracle ERP 애플리케이션 상에서 실행되며, 주문의 실행 결과가 PeopleSoft로 전달.

• 주문 생성 프로세스가 진행되는 동안, 세일즈 담당자는 발송 일자를 확정하기 위해 재고를 점검할 수 있습니다.
• PeopleSoft CRM은 ERP 애플리케이션에 동기식 호출을 발생시키고, Item/Product Availability 질의 컴포넌트를 이용하여 현재 사용 가능한 물량을 확인합니다.
• BPEL Process Manager는 ATP Check 요청을 Oracle ERP에 전달합니다.
• Oracle ERP는 해당 아이템의 수량을 인벤토리에서 확인한 후, 상세 정보를 BPEL Process Manager에 전달합니다.
• BPEL Process Manager는 ATP 응답 정보를 PeopleSoft CRM에 전달합니다. 이 작업 결과에 따라, 고객에게 발송 가능 일자를 전달합니다.

• 세일즈 주문이 ERP 애플리케이션으로 전달되면, 주문은 ERP에서 예약 처리되고, 그 결과가 BPEL Process를 거쳐 PeopleSoft CRM 시스템으로 전달됩니다. PeopleSoft CRM 시스템은 주문 상태(order status)를 “In Process"로 변경합니다.
• Oracle ERP는 주문 상태가 변경될 때마다 그 내역을 CRM으로 전달합니다. ERP의 상태 정보는 CRM 상의 대응되는 상태 정보로 매핑됩니다.

 

그림 1

여기에서는 주문 생성(order creation) 단계에 초점을 맞추기로 합니다. 디자인 타임 viewlet을 통해 실제 설정 및 실행 방법을 확인하실 수 있습니다. 자세한 방법은 Oracle Apps Integration Cookbook을 참고하시기 바랍니다.

비즈니스 프로세스를 개략적으로 이해했다면, 이제 아키텍처에 대해 살펴보기로 합시다. 그림 2는 Oracle BPEL Process Manager를 플랫폼으로 하여 PeopleSoft CRM과 오라클 애플리케이션을 통합한 환경의 하이 레벨 아키텍처를 예시하고 있습니다.

 

그림 2

Enterprise Integration Point (EIP)란 PeopleSoft 애플리케이션이 써드 파티 시스템 또는 다른 PeopleSoft 소프트웨어와 연동하기 위해 사용되는 웹 서비스 연결입니다. PeopleSoft CRM에 주문이 입력되면, PeopleSoft의 EIP가 주문을 XML 포맷으로 변환합니다. 그런 다음 Order XML이 PeopleCode 메소드(WSDL_ORDER)로 전달됩니다. (PeopleCode는 비즈니스 룰 구현, 또는 기타 커스터마이즈 작업을 위해 사용되는 PeopleSoft의 프로그래밍 언어입니다.) WSDL_ORDER는 수신된 Order XML을 SOAP XML로 변환하고 PeopleSoft 원격 노드에 요청을 전달합니다. 원격 노드(remote node)란 BPEL Process Manager의 웹 서비스와 핸드쉐이크(handshake) 작업을 수행하는 노드를 의미합니다.

WSIF(Web Service Invocation Framework) 바인딩을 이용하여 원격 노드를 WSDL로 매핑함으로써 PeopleSoft의 웹 서비스 호출 작업이 수행됩니다. BPEL Process Manager는 WSIF 바인딩을 완벽하게 지원합니다. SOAP XML을 수신한 PeopleSoft 노드는 해당 노드에 임포트 및 설정된 WSDL을 기준으로 웹 서비스를 호출합니다. 그런 다음, 웹 서비스가 BPEL Process Manager에서 호출 및 실행됩니다.

BPEL Process Manager는 데이터를 SOAP XML로 처리하고 오라클 애플리케이션에 전달합니다. 이때 Oracle Applications (OA) Adapter를 이용하여 11i와의 커뮤니케이션을 수행합니다. OA Adapter는 퓨어 JCA 1.5 Resource Adapter로, E-Business Suite 환경의 메시지 전송/수신을 위해 사용됩니다. 오라클 애플리케이션은 이 어댑터를 통해 외부 애플리케이션으로 API 및 테이블의 일부를 노출합니다.

오라클 애플리케이션이 주문을 처리한 후 결과를 전송하면, BPEL Process Manager가 이를 수신하여 PeopleSoft 노드에 전달합니다. PeopleSoft 노드는 웹 서비스를 요청한 PeopleCode에 결과를 전달합니다. PeopleCode는 XML 데이터를 인출하여 PeopleSoft에 구성된 컴포넌트 인터페이스에 전달합니다. 컴포넌트 인터페이스(component interface)란 (Java 또는 PeopleCode로 작성된) 다른 애플리케이션으로부터의 동기식 접근을 위해 PeopleSoft 컴포넌트를 노출하는 인터페이스를 말합니다.

PeopleSoft와 오라클 애플리케이션 간에 이루어지는 Order 데이터의 하이 레벨 플로우가 지금까지 설명한 바와 같습니다. 다음으로, PeopleSoft CRM 모듈을 웹 서비스의 형태로 노출하고, BPEL 프로세스를 구성하고, OA Adapter를 설정하는 방법에 대해 알아 보겠습니다.

주문이 PeopleSoft CRM에 입력되고 나면, 해당 주문에 대한 정보 오라클 애플리케이션으로 전달되어야 합니다. 이 과정에서 세 단계의 작업이 수행됩니다.

1. Oracle BPEL Process Manager에서 비즈니스 프로세스를 설계합니다.
2. Oracle Applications Adapter를 설정합니다.
3. PeopleSoft를 설정합니다.

각 단계별로 자세히 살펴보도록 합시다:

이 단계에서는 BPEL Designer를 이용하여 프로세스를 생성합니다. BPEL Process Manager는 세일즈 주문 정보를 포함한 SOAP XML을 PeopleSoft로부터 수신하고, 이를 OA Adapter의 XML 포맷으로 변환합니다. (스키마는 호출 API를 위한 파트너 링크가 생성되는 시점에 OA Adapter에 의해 자동으로 생성됩니다.) 그런 다음, OA Adapter Partner 링크가 호출되어 변환 작업을 거친 Order XML이 오라클 애플리케이션으로 전달됩니다. Oracle API는 주문을 처리하고 결과 확인(output acknowledgement) XML을 통해 주문 번호(order number)를 반환합니다.

BPEL Process Manager는 원격 폴트(remote fault) 및 바인딩 폴트(binding fault)를 처리합니다. 연결이 끊어진 경우, 5 차례에 걸쳐 접속이 재시도된 후 익셉션(exception)을 발생시킵니다. 바인딩 익셉션이 발생하고 나면 바인딩 폴트가 자동으로 처리됩니다.

BPEL Process Manager를 위해 설계된 통합 비즈니스 프로세스의 예가 아래와 같습니다.

(이미지를 클릭하면 큰 그림을 보실 수 있습니다)

이 프로세스에서 실행되는 작업이 아래와 같습니다:

1. Applications > New Application Workspace > New Project > BPEL Process Project 메뉴를 선택합니다.
2. Schema를 임포트하고 BPEL Process의 입력/출력 변수를 정의합니다.
   1. Structure 윈도우에서 project schemas > import Schema를 선택합니다.
      Input schema name(createorder.xsd)을 입력합니다.
   2. Structure 윈도우에서 Message Types를 선택합니다.
      • CreateOrderRequestMessage를 선택하고 createorder.xsd의 CreateOrderIn루트 엘리먼트를 매핑합니다.
      • CreateOrderResponseMessage를 선택하고 createorder.xsd의 CreateOrderOut 루트 엘리먼트를 매핑합니다.
   3. Structure 윈도우에서 Variables를 선택합니다.
      • InputVariable이 CreateOrderRequestMessage로 매핑되었음을 확인합니다.
      • OutputVariable이 CreateOrderResponseMessage로 매핑되었음을 확인합니다.
3. OA Adapter를 위한 파트너 링크를 생성합니다 .
   1. 컴포넌트 팔레트에서 partner link activity를 CreateOrder Process에 추가하고 “CreateOrderPL”이라 명명합니다.
   2. Define Adapter Service를 클릭하고 아래 작업을 수행합니다.
   3. 파트너 링크를 위한 BPEL Process Manager와 OA Adapter를 설정합니다(뒷부분에서 자세히 설명합니다).
4. OA Adapter를 호출하는 Invoke 액티비티를 추가합니다.
   1. invoke 액티비티를 프로세스 위에 드래그-앤-드롭하고 더블클릭 합니다.
      
      
      
   2. 파트너 링크를 CreateOrderPL로 매핑하고 입력/출력 변수를 생성합니다.
5. invoke 액티비티 위에 transform 액티비티를 추가하고 PeopleSoft outbound XML을 Oracle Apps inbound XML로 변환합니다.
   1. transform 액티비티를 더블클릭 합니다. inputVariable 을 source variable로 선택하고, invokeCreateOrderInputVariable을 target variable로 선택한 뒤 “create mapping” 아이콘을 클릭합니다.
      
      
      
   2. source/target schema를 아래와 같이 매핑합니다.
      
      
      
6. invoke 액티비티 아래에 transform 액티비티를 추가하고 Oracle Apps outbound XML을 PeopleSoft 인바운드 XML로 변환합니다.
   1. transform 액티비티를 더블클릭 합니다. invokeCreateOrderOutputVariable 를 source variable로 outputVariable을 target variable로 선택하고 “create mapping” 아이콘을 클릭합니다.
      
      
      
   2. 소스/타겟 스키마를 아래와 같이 매핑합니다.
      
      
      

샘플 코드 다운로드에 포함된 아래 파일들을 이용하면, BPEL Designer를 통해 위에서 설명된 프로세스를 재구성할 수 있습니다.

이것으로 BPEL Process의 설계 작업을 완료했습니다. 다음으로 PeopleSoft 환경의 설정에 대해 자세히 살펴보기로 합니다.

OA Adapter는 매니지드 모드(managed mode)로 Oracle Containers for J2EE에 deploy되며, E-Business Suite로부터 메시지를 전송/수신하는데 사용됩니다. 어댑터의 설정 방법이 아래와 같습니다 .

1. Adapter Service를 정의하고 Oracle Applications Adapter를 선택합니다.
   
   
   
2. 서비스 네임을 입력합니다. 이 서비스 네임은 선택된 API/테이블을 위한 웹 서비스로서 사용됩니다.
   
   
   
3. 프로젝트에 정의된 데이터베이스 연결을 선택합니다. DB 연결이 현재 사용 불가능한 경우, New를 클릭하고 마법사를 실행합니다.
   
   
   
4. Oracle Application Data에 대한 Table/Views/APIs 인터페이스를 선택합니다.
   
   
   
5. BPEL Process로부터 호출할 PROC_ORDERENTRY_ARRAY API를 검색하여 선택합니다.
   
   
   

   참고: 위의 경우, BPEL Process Manager가 오라클 레코드 타입을 지원하지 않기 때문에, PROC_ORDERENTRY_ARRAY가 PROCESS_ORDER pre-seeded API를 위한 래퍼(wrapper) API로 사용됩니다. 따라서 PROCESS_ORDER에서 사용된 레코드 타입과 유사한 오브젝트 타입을 래퍼 프로시저(wrapper procedure)에서 사용해야 합니다. 이 인터페이스는 오라클 애플리케이션에서 세일즈 주문을 처리하는 작업을 담당합니다.

6. API를 Adapter Service에 추가합니다.
   
   
   
7. 오라클 애플리케이션 파트너 링크를 BPEL Process에 추가하고, APPS_PROC_ORDERENTRY_ARRAY.xsd를 생성합니다.
   
   
   

   Adapter Service에 의해 PROC_ORDERENTERY_ARRAY API를 위한 WSDL 파일이 생성됩니다. 이 파일은 웹 서비스와 같은 형태로 동작하며 WSIF를 이용하여 ERP를 바인딩하는 역할을 담당합니다.

   샘플 코드 다운로드에 포함된 아래 파일들은 BPEL Designer에서 OA Adapter를 설정하는데 사용됩니다.

이것으로 BPEL 프로세스의 설계 및 OA Adapter의 설정을 완료하였습니다. 마지막 단계로, PeopleSoft를 설정하기로 합니다.

BPEL 프로세스의 생성을 완료하였다면, 이제 네 단계에 걸쳐 PeopleSoft의 설정 작업을 수행할 차례입니다.

• 첫 번째 단계로, BPEL 프로세스 WSDL을 PeopleSoft에 임포트합니다. 그런 다음 임포드된 WSDL을 이용하여 BPEL 프로세스와 커뮤니케이션을 수행할 노드의 설정 작업을 수행합니다.
• 두 번째 단계로, Sales Order EIP를 설정하여 새로운 세일즈 주문이 생성될 때마다 노드를 호출하도록 합니다.
• 마지막으로, 노드가 BPEL 프로세스에 주문 정보를 전달하기 전에, 주문 정보를 SOAP XML로 변환합니다. 세 번째, 그리고 네 번째 단계에서는 간단한 PeopleCode 함수를 작성하여 변환 작업을 수행하고 노드와의 관계를 설정하는 작업을 수행하게 됩니다.

각 단계별 설명이 아래와 같습니다.

이 단계에서는, BPEL Process와 커뮤니케이션을 수행할 노드를 설정하게 됩니다. 요청/응답 메시지와 메시지가 전달되는 채널을 정의합니다.

먼저, URL 옵션을 사용하여 (앞에서 생성한) CreateOrder.wsdl을 PeopleSoft에 임포트합니다. CreateOrder는 프로세스로 정의되며, 새로운 주문이 PeopleSoft에서 생성될 때 호출됩니다. WSDL을 PeopleSoft에 임포트하는 시점에, Integration Broker는 WSDL을 WSDL 리포지토리에 추가합니다.

CreateOrder.wsdl을 임포트하려면, PeopleTools > Integration Broker > Web Services > Import WSDL를 선택합니다.

이제 WSDL Repository 페이지를 이용하여 WSDL 리포지토리의 WSDL에 액세스할 수 있습니다.

CreateOrder WSDL을 PeopleSoft에 임포트한 다음에는, BPEL 프로세스와의 커뮤니케이션을 위해 원격 노드를 설정해야 합니다. 이를 위해 request message, response message, and message channel를 아래와 같이 추가합니다.

새로운 원격 노드를 생성하는 방법이 아래와 같습니다:

1. Create a New Node Definition 버튼을 선택합니다.
   
   
   
2. Node Name 필드에서, BPEL_CREATEORDER를 입력하여 새로운 노드를 정의합니다.
3. Node Description 필드에 노드에 대한 설명을 입력합니다.
4. Authentication 드롭다운 리스트에서 인증 방법(None, Certificate, Password)을 선택합니다. (디폴트는 None입니다.)
5. (옵션) Password 필드에 암호를 입력합니다.
6. (옵션) Confirm Password 필드에 암호를 다시 입력합니다.

Next 버튼을 클릭하여 WSDL Operation Wizard의 다음 페이지로 진행하여 서비스의 요청/응답 메시지(request/response message)를 선택합니다. 요청/응답 메시지는 PeopleSoft 내부에서 비구조형 메시지로 정의되며 SOAP Request / Response 메시지로 활용됩니다.

새로운 요청/응답 메시지를 생성하는 방법이 아래와 같습니다:

1. 해당 섹션에서 Create a New Message 옵션을 선택합니다.
2. 새로운 요청 메시지를 생성하기 위해 Request Message 섹션의 옵션을 선택합니다. message name 필드에는 BPEL_ORDER_REQ를 입력합니다.
3. 새로운 응답 메시지를 생성하기 위해 Response Message 섹션의 옵션을 선택합니다. message name 필드에는 BPEL_ORDER_RES를 입력합니다.
4. d. 새로 생성된 메시지의 버전은 디폴트로 VERSION_1로 정의됩니다. PeopleSoft Integration Broker는 이 값을 이용하여 Message Version 필드를 채웁니다.
   
   
   

   PeopleSoft Integration Broker는 새로운 메시지 채널에 자동으로 메시지를 할당합니다. 마법사 실행이 완료되면, 새로운 메시지를 이용하여 노드에 아웃바운드 동기식 트랜잭션이 생성됩니다.

5. channel name 필드의 New Message Channel Name을 BPEL_SERVICES로 입력합니다.
    
   

이로써 1 단계가 완료되었습니다. WSDL을 임포트함으로써 어떤 웹 서비스를 호출할 것인지 PeopleSoft에 알리고, 웹 서비스(CreateOrder BPEL Process)에 정보를 전달하기 위한 노드, 메시지, 채널 등을 설정하였습니다. 다음 단계에서는 Sales Order EIP와 새로 설정된 노드 간의 관계를 설정합니다.

이 단계에서는 CRM_SALES_ORDER EIP와 새로운 노드 간의 링크를 생성합니다. CRM_SALES_ORDER EIP가 Integration Broker를 통해 공개되고, 생성된 링크를 통해 CRM_SALES_ORDER 요청 메시지를 노드에 전달합니다.

• People Tools > Integration Setup에서 Node Definition을 선택하고, BPEL_CREATEORDER 노드를 검색합니다.
  
  
  
• 선택한 노드에서, Transaction Type을 “Outbound Asynchronous”로 변경하고 Request Message에 "CRM_SALES_ORDER"를 입력합니다.

이 단계에서는 WSDL_ORDER Application Engine 프로그램을 생성합니다. (Application Engine은 PeopleSoft의 대용량 애플리케이션 프로세서입니다. Application Engine 프로그램은 Application Designer를 통해 작성되며, 레코드, PeopleCode, SQL 오브젝트와 같은 PeopleTool의 기능을 활용할 수 있습니다.) WSDL_ORDER는 EIP로부터 수신된 세일즈 주문 메시지를 BPEL 요청 메시지(SOAP 메시지)로 변환하고 변환된 메시지를 노드 채널로 배포합니다.

아래 코드를 추가하여 요청 메시지를 변환한 후 노드에 전송합니다. 요청이 노드에 전송되고 나면, 노드가 웹 서비스를 호출하는 작업을 수행하게 됩니다. 웹 서비스가 호출되면, XML 메시지를 PeopleCode 메소드로 전송하고 응답 메시지를 노드에 전달하는 작업이 수행됩니다.

/* Get the data from the AE Runtime */
Local TransformData &transformData = %TransformData;
 
Local File &logFile = GetFile("TestSyncReqResStep3.log", "W", %FilePath_Absolute);
 
Local string &destNode = &transformData.DestNode;

&logFile.WriteLine("DestNode: " | &destNode);
 
/* Set a temp object to contain the incoming document */
Local XmlDoc &xmlDoc = &transformData.XmlDoc;

Local string &xmlStr = &xmlDoc.GenXmlString();
 
&logFile.WriteLine("Transformed XML : " | &xmlStr);

/* Maps the &xmlDoc  to the BPEL_ORDER_REQ and publish to the BPEL_CREATEORDER node. 
   Node will invoke BPEL CreateOrder process. 
   Response will be assigned to &response variable. */

Local XmlDoc &response = SyncRequestXmlDoc(&xmlDoc, Message.BPEL_ORDER_REQ, Node.BPEL_CREATEORDER);
 

&logFile.WriteLine("Response XML Data: " | &response.GenXmlString());
 
&logFile.Close();

이 단계에서는, 변환 코드를 CreateOrder 노드에 연결하는 작업을 수행합니다. 이와 같이 함으로써, BPEL Process Manager가 호출될 때마다 변환 코드가 실행되게 할 수 있습니다.

새로운 WSDL_ORDER 관계(relationship)를 생성하고, 생성된 관계를 WSDL_ORDER 노드에 연결합니다:

People Tools > Integration Setup에서 Relationships을 선택하고 Add New Value를 선택합니다.

• Initial Node를 BPEL_CREATEORDER로, CRM_SALES_ORDER를 Request Message로, Transaction Type을 OA로, Result Node를 BPEL_CREATEORDER로, Request Message Name을 CRM_SALES_ORDER로 설정하고 디폴트 버전을 그대로 사용합니다. Add를 클릭합니다.

• Transformation Request를 WSDL_ORDER로 설정합니다.

T이로써 Sales Order 생성 작업을 완료하였습니다. 디자인 타임 viewlet을 통해 설정 과정을 다시 한 번 확인하실 수 있습니다.
또 런타임 viewlet에서는 PeopleSoft에 입력된 세일즈 주문이 오라클 애플리케이션으로 전달되는 과정을 확인할 수 있습니다.

각 부서별로 서로 다른 애플리케이션을 사용하는 기업 환경에서 정보를 통합하는 문제가 중요한 이슈로 떠오르고 있습니다. 시스템의 통합에는 많은 비용 투자가 필요합니다. 본 문서에서 제시한 것과 같은 방법을 이용하여 BPEL 표준 기반의 애플리케이션 통합을 수행함으로써 문제를 쉽게 해결하는 것이 가능합니다.

이기종 EAI 환경에 BPEL 추가하기
저자: Praveen Chandran and Arun Poduval

Oracle BPEL Process Manager의 통합(orchestration) 기능을 이용하여 고전적인 EAI 미들웨어를 아우르는 표준 기반 비즈니스 프로세스 통합 환경을 구현하는 방법에 대해 알아봅니다.

이처럼 벤더 종속적인 통합 환경을 유지하고 관리하는 데에는 만만치 않은 비용이 듭니다. 비용과 안정성 문제를 해결하기 위해서는 전문적인 스킬이 요구될 뿐 아니라, 기존 EAI 솔루션을 다른 대안으로 완전하게 대체하려면 막대한 비용을 EAI 영역에 쏟아 부어야 할 수도 있습니다.

BPEL이 제공하는 표준 기반, 플랫폼 중립적인 솔루션을 이용함으로써 이러한 문제를 해결하는 것이 가능합니다. 느슨하게 커플링된(loosely coupled) BPEL 프로세스는 벤더 종속 요소를 제거하고, 통합 비용을 절감하고, 호환성을 개선하는 효과를 제공합니다. 또 보안, 예외 관리, 로깅 등을 위한 계층 구현을 가능하게 합니다. 가장 중요한 사실은 기업들이 기존 인프라스트럭처를 활용하여 서비스를 구현하고 BPEL을 이용하여 전체 서비스를 통합할 수 있다는 점입니다.

“BPEL Cookbook” 시리즈의 이번 연재에서는 Oracle BPEL Process Manager를 이용하여 새로운 통합 솔루션을 개발하고 기존 인프라스트럭처와 인터페이스 하기 위한 아키텍처의 청사진을 제시합니다. 또 TIBCO BusinessWorks와 webMethods에 기반한 이기종 EAI 솔루션을 웹 서비스와 통합하는 사례를 함께 살펴보기로 합니다.

또, 완벽한 비즈니스 프로세스의 구현을 위해서는 용의주도한 에러 관리, 보안, 로깅 프레임워크가 필수적입니다. 여기에서는 BPEL scope와 compensation handler를 이용하여 프로세스의 안정성을 높이고 BPEL 프로세스 및 서비스의 보안을 보장하는 방법에 대해 함께 알아보도록 하겠습니다.

사례 연구 배경정보

EAI는 기존 애플리케이션의 서비스화(service-enabling)을 위한 훌륭한 촉매제 역할을 합니다. 기존 미들웨어 프로세스를 웹 서비스로 공개하고, 이를 다시 BPEL을 통해 통합하는 것이 가능합니다.

아래 다이어그램은 Oracle BPEL Process Manager를 이용하여 기존 EAI 인터페이스와 새로운 애플리케이션을 통합하는 일반적인 접근법을 보여주고 있습니다. 아래에서는 미들웨어가 비즈니스 프로세스를 웹 서비스 형태로 공개할 수 있으며, 애플리케이션 서버 내부적으로 웹 서비스 인터페이스가 구현되어 있다고 가정하고 있습니다.

그림 1 EAI 환경의 Oracle BPEL Process Manager — 전체 개념도

본 문서에서는 두 가지 고전적인 EAI 미들웨어 제품에 대한 사례 연구를 통해, BPEL이 제품간 통합 과정에서 어떤 역할을 담당하는지 알아보기로 하겠습니다.

특정 고객의 “기록 시스템(system of record)”이 고객 데이터가 관리되고 있는 다른 시스템으로 원활하게 전달되지 않는 경우를 자주 찾아볼 수 있습니다. 이러저러한 이유로 기업이 두 가지 벤더의 미들웨어 제품(예: Siebel CRM과 TIBCO BusinessWorks, SAP R/3와 webMethods 등)을 동시에 사용하고 있는 경우를 고려해 봅시다. (그림 2 참고).

그림 2 Customer Details Management Module과 미들웨어 인터페이스

이러한 모델에서는, SAP 시스템과 Siebel 시스템 간의 고객 데이터 불일치로 인한 고객 서비스 수준의 저하, 또는 기업 매출의 저하가 발생할 소지가 높습니다. 따라서, TIBCO 및 webMethods와 다수의 인터페이스 포인트(interfacing point)를 갖는 공통 Customer Details Management Module을 구현함으로써 데이터 일관성을 보장하는 것이 바람직합니다. 예를 들어, Siebel 시스템에 고객 데이터가 접수된 경우, 시스템은 고객이 신규 고객인지 아니면 기존 고객인지 확인한 후 SAP 시스템에 새로운 데이터를 추가하거나 기존 데이터를 업데이트하는 작업을 수행하게 됩니다.

이러한 통합 목표의 달성을 위해 기존 미들웨어 툴(TIBCO와 webMethods)를 이용할 수도 있습니다. 하지만 이 경우 벤더 종속성이 더욱 심화될 뿐 아니라 표준 기반의 통합이 불가능하다는 문제가 있습니다. 따라서 이와 같은 환경을 애플리케이션의 서비스화(service-enabling)를 위한 새로운 기회로 활용하고, 표준 기반, 벤더 중립적인 솔루션을 구현하는 것이 바람직할 것입니다.

표준 기반의 EAI 인터페이스를 구현하기 위한 첫 단계로서, 프로세스를 웹 서비스로 공개하는 작업이 필요합니다. 대부분의 미들웨어 플랫폼은 웹 서비스를 이용한 다른 플랫폼 간의 커뮤니케이션을 지원합니다. 하지만 일련의 인터페이스 서비스를 관련된 비즈니스 로직과 연계해야 하는 경우에는 문제가 복잡해질 수 있습니다.

또 다른 미들웨어 프로세스, 또는 복잡한 Java 코드를 이용하여 웹 서비스를 통합하는 방법을 고려해 볼 수도 있습니다. 하지만 이 경우에도 아래와 같은 기능을 프로세스를 통해 직접 구현해야 한다는 문제가 남습니다:

• 병렬 웹 서비스 호출
• 비동기식 웹 서비스 호출
• 서비스 간의 느슨한 커플링(loose coupling) 및 호환성
• 표준 기반 인터페이스를 이용하여 전체 통합(orchestration) 환경을 공개
• 통합 모니터링 (orchestration monitoring)

그림 3 웹 서비스 인터페이스를 이용한 Customer Details Management Module

이와 같은 시나리오에 BPEL을 도입함으로써 기대할 수 있는 이점이 다음과 같습니다:

• BPEL은 느슨하게 커플링된(loosely coupled) 웹 서비스 통합을 지원합니다.
• 비즈니스 로직을 (심지어 병렬 플로우까지도!) 단순한 XML 태그를 이용해 표현할 수 있습니다.
• 간단한 assign (copy 룰),invoke 구문을 이용하여 서비스 간의 데이터 라우팅을 수행할 수 있습니다.
• 다른 통합 툴, 미들웨어 툴, 또는 웹 애플리케이션으로부터 Customer Details Management Module을 독립적인 웹 서비스 컴포넌트로 호출할 수 있습니다.
• Oracle BPEL Process Manager 등의 제품이 제공하는 단순한 GUI를 통해 프로세스를 쉽게 관리할 수 있습니다.

이제, Oracle BPEL Process Manager를 이용하여 SAP 시스템과 Siebel 시스템의 고객 데이터를 동기화하는 방법에 대해 알아보기로 합시다.

Customer Details Management Module의 구현

BPEL은 SAP 시스템과 Siebel 시스템 간의 고객 데이터 동기화 프로세스를 자동화하는 과정에서 매우 중요한 역할을 담당합니다. BPEL 프로세스를 구현하기 위한 작업 단계가 아래와 같습니다:

1. TIBCO, webMethods 프로세스를 웹 서비스로 공개합니다.
2. BPEL 프로세스를 이용하여 웹 서비스를 통합(orchestrate)합니다.
3. BPEL 프로세스에 예외 관리(exception management) 기능을 추가합니다.
4. Oracle BPEL Process Manager, 애플리케이션 어댑터, EAI 툴 간의 커뮤니케이션 보안 환경을 구현합니다.
5. 로깅, 통보 프로세스를 중앙집중화 합니다.

BusinessWorks 프로세스를 웹 서비스로 공개하기 위한 방법이 아래와 같습니다:

1. BusinessWorks 프로세스가 제공하는 기능을 분석하고, 해당 기능이 통합 시나리오 내에서 독립적인 컴포넌트로써 구현될 수 있는지 확인합니다.
2. 프로세스 입력 및 출력을 정의합니다.
3. input과 output이 복잡한 경우, W3C XML 스키마(XSD)를 이용하여 정의합니다. XSD를 이용하여 커스텀 폴트 스키마(custom fault schema)를 정의할 수도 있습니다.
4. WSDL 팔레트를 이용하여 WSDL을 생성하고input과 output 메시지 포맷을 정의합니다 (이때 필요한 경우, 메시지 포맷을 사전정의된 XSD와 연결합니다). 기존 WSDL을 임포트(import)할 수도 있습니다.
5. HTTP Connection 리소스를 설정합니다.
6. SOAP Event Source 를 첫 번째 액티비티로 사용하고, 비즈니스 로직, SOAP Send Reply 등의 순으로 프로세스를 서비스 형태로 공개합니다.
7. HTTP Connection 리소스와 Event Source를 연결(associate)합니다.
8. WSDL과 Send Reply를 Event Source와 연결합니다.
9. 발생 가능한 예외(exception)을 처리하고 SOAP Send Fault를 이용하여 서비스 클라이언트에 예외를 전달합니다.
10. 10. 머신 네임이 mymachine이고, HTTP Connection 리소스를 위해 8000번 포트를 사용하며, 프로세스 네임이 SOAPService인 경우, 다음 URL을 통해 서비스에 접근할 수 있습니다. http://mymachine:8000/SOAPService.
11. Event Source 액티비티의 WSDL 탭에서 서비스의 WSDL을 가져옵니다.

1. webMethods Flow Service가 통합 시나리오에서 독립적인 컴포넌트로써 구현될 수 있는지 확인합니다.
2. 해당 웹 서비스를 별도의 인증 과정 없이 webMethods 외부에서 호출하고자 하는 경우 Permissions 탭의 "Execute ACL to Anonymous"를 체크합니다.
3. webMethods Developer에서 해당 Flow Service를 선택하고, Tools 메뉴에서 Generate WSDL을 클릭합니다.
4. WSDL 다큐먼트를 생성하는 과정에서 프로토콜(SOAP-RPC/SOAP-MSG/HTTP-GET/HTTP-POST)과 전송 메커니즘(HTTP/HTTPS)을 정의합니다.
5. WSDL 다큐먼트의 타겟 네임스페이스(target namespace)를 정의합니다.
6. webMethods Integration Server가 실행되는 호스트명 또는 IP 주소를 Host 필드에 입력합니다.
7. 현재 Integration Server에 연결할 때 사용하는 포트 넘버를 Port 필드에 입력합니다.
8. WSDL 다큐먼트를 로컬 파일 시스템에 저장합니다. 생성된 WSDL 다큐먼트에서 서비스 엔드포인트(service endpoint)를 확인할 수 있습니다.

이제, 아래와 같은 3 개의 TIBCO 웹 서비스가 존재한다고 가정해 봅시다 (TIBCO BusinessWorks 프로세스와 TIBCO Adapter for Siebel을 사용하여 구현).

• Siebel Add
• Siebel Update
• Siebel Query

• SAP Add
• SAP Update

그림 4 솔루션 아키텍처

위 그림에서 확인할 수 있는 것처럼, BPEL 프로세스는 EAI 툴을 이용한 프론트 오피스 콜 센터와 백엔드 SAP 및 Siebel CRM 애플리케이션 간의 커뮤니케이션을 중개하는 역할을 담당합니다.

미들웨어 프로세스를 웹 서비스로 공개하기 위한 몇 가지 베스트 프랙티스가 아래와 같습니다:

• 가능한 한 WS-I 표준을 준수합니다.
• 서비스를 리터럴 인코딩(literal encoding)을 사용한 “document” 스타일로 공개합니다. 이것이 불가능한 경우에는, 리터럴 인코딩을 이용한 “rpc” 스타일로 공개합니다. 두 가지 스타일 모두 WS-I 표준에 의해 권장되고 있지만, document 스타일이 좀 더 사용하기 쉬운 것이 사실입니다 (특히 BPEL assign 액티비티에 대한 copy 룰을 생성하는 경우에 특히 그러합니다). rpc 스타일의 경우 모든 스키마 엘리먼트가 별도의 메시지 파트로 구성되는 반면, document 스타일에서는 전체 메시지가 하나로 전달됩니다. 따라서 전체 스키마를 하나의 copy 룰을 사용하여 복사할 수 있기 때문에, 개발 작업이 단순화되고 최종 WSDL 다큐먼트의 스타일과 인코딩을 검증하기 쉽다는 장점이 있습니다.
• SOAP 인코딩의 사용을 피합니다. WSDL의 SOAP Action 속성은 빈 문자열(empty string)으로 구성됩니다.
• 미들웨어가 웹 서비스의 인터페이스 기술을 위해 WSDL 1.1을 사용하고 있는지 확인합니다.
• SOAP의 HTTP 바인딩을 사용합니다.
• 스키마 기술에 사용되는 모든 XSD가 W3C가 제안한 XML Schema Specification을 준수하는지 확인합니다. 예를 들어, 글로벌 엘리먼트 선언에 다른 글로벌 엘리먼트에 대한 참조가 포함되어서는 안됩니다 (다시 말해, ref 속성 대신 type 속성이 사용되어야 합니다).

앞부분에서 Customer Details Management Module이 SAP 시스템 및 Siebel 시스템의 고객 데이터를 동기화하는 역할을 담당한다고 설명한 바 있습니다. 이 프로세스를 BPEL Designer의 비주얼 인터페이스를 이용하여 생성한 결과가 아래 그림과 같습니다.

그림 5 Oracle BPEL Process Manager를 이용한 Customer Details Management Module의 통합

프로세스 플로우는 아래와 같이 요약될 수 있습니다:

1. receive 액티비티가 customer detail 정보를 접수합니다 (enterprise schema).
2. Detail 정보가 assign, invoke (Siebel Query 서비스) 액티비티를 통해 Siebel에 전달됩니다.
3. pick 액티비티에서 Siebel Query의 결과를 통해 고객이 신규 고객인지 기존 고객인지를 확인합니다.
4. 신규 고객인 경우 병렬 플로우가 호출되고, flow 액티비티가 Siebel와 SAP에 고객 정보를 동시에 추가합니다. 또는 기존 고객인 경우, 양쪽 애플리케이션에 고객 정보를 업데이트하기 위한 병렬 플로우가 호출됩니다.
5. 고객 정보가 SAP에 존재하지 않는 경우, Siebel Query 를 통해 필요한 필드를 가져옵니다. 업데이트가 필요한 SAP 필드는 일련의 assign copy 룰을 통해 SAP Update로 전달됩니다.
6. reply 액티비티를 통해 Customer Update/Addition의 최종 결과가 반환됩니다.

이 BPEL 프로세스는 고객 관에 관련한 비즈니스 요구사항을 해결하고 있지만, 여전히 예외 사항을 처리하고 있지 못하다는 문제가 있습니다. 예를 들어, 특정 고객의 레코드가 Siebel에서는 성공적으로 추가되었지만 SAP에서 실패한 경우에는 어떻게 해야 할까요? 이러한 문제를 해결하기 위해서는 비즈니스 프로세스에 예외 관리(exception management)가 구현되어야 합니다.

3 단계: 예외 관리(Exception Management) 기능의 추가. 예외 관리 기능을 구현함으로써 BPEL 프로세스 또는 웹 서비스 외부에서 반환되는 에러 메시지 및 기타 예외 사항을 처리하고 비즈니스 에러 또는 런타임 에러 발생시 대응되는 에러 메시지를 생성할 수 있습니다.

아래 표는 고객 관리 BPEL 프로세스를 보다 안정적으로 구현하기 위한 예외 상황을 요약하고 있습니다.

1, 2, 6 번을 제외한 나머지 케이스는 별도의 예외 처리를 필요로 하지 않음을 확인할 수 있습니다.

Exception을 캐치하고 적절한 대응 조치를 취하기 위해서는 웹 서비스의 상태를 추적하는 것이 중요합니다. 케이스 1, 2, 6이 어떻게 처리되는지 논의 하기 전에, 특정 웹 서비스의 상태를 어떻게 추적할 수 있는지 설명해 보기로 하겠습니다.

BPEL 프로세스에는 아래와 같은 reply 스키마 속성이 포함됩니다:

• Siebel_Add_Status
• Siebel_Update_Status
• SAP_Add_Status
• SAP_Update_Status

이제 각 케이스 별로 예외가 어떻게 관리되는지 살펴보기로 합시다:

Case 1
Siebel 쿼리가 실패한 경우, 프로세스를 종료하고 클라이언트 호출 과정을 재시도합니다.

Case 2
고객 상세정보의 INSERT 작업이 Siebel에서는 실패하고 SAP에서는 성공한 경우 (두 가지 작업은 병렬적으로 실행됩니다), 데이터 일관성이 훼손될 수 있습니다. 또, 같은 작업을 재시도하는 경우 아래와 같은 문제가 발생할 수 있습니다:

• BPEL 프로세스를 호출하여 Siebel에 고객 상세정보를 INSERT하려 시도하는 과정에서, SAP에 중복된 정보가 입력될 수 있습니다.
• 해당 고객의 정보를 업데이트하는 BPEL 프로세스를 호출하려 시도하는 경우, Siebel에서의 업데이트가 (해당 레코드가 존재하지 않기 때문에) 실패하게 됩니다.

scope와 compensate 액티비티는 가장 핵심적인 BPEL 개발 툴의 하나입니다. 스코프(scope)는 다른 액티비티에 대한 container 겸 context로써 활용됩니다. 스코프 액티비티(scope activity)는 프로그래밍 언어의 {} 블록에 대응됩니다. 스코프는 BPEL 플로우를 기능적으로 유사한 구조로 그룹화하고, 에러, 이벤트, 보정(compensation) 및 데이터 변수, correlation set 등을 위한 핸들러(handler)를 제공합니다.

Oracle BPEL Process Manager는 보정 처리를 위해 두 가지 컨스트럭트를 제공합니다:

• Compensation handler—롤백 작업을 위한 비즈니스 로직을 처리합니다. 프로세스 및 스코프 별로 핸들러를 정의할 수 있습니다.
• Compensate activity—이 액티비티는 성공적으로 완료된 inner scope 액티비티를 통해 compensation handler를 호출합니다. 그리고 이 액티비티는 fault handler 또는 다른 compensation handler 내부에서만 호출이 가능합니다.

다시 예제로 돌아가, BPEL 프로세스가 내부(inner), 외부(outer)의 두 가지 스코프를 갖는다고 가정해 봅시다. SAP Add 및 Siebel Add 서비스의 호출은 outer scope를 통해 수행되며, SAP Add 서비스 만이 inner scope를 통해 수행됩니다. compensation handler는 inner scope와 연계가 가능하며, SAP Delete 서비스를 위한 액티비티를 호출합니다.

BusinessWorks 웹 서비스가 전송한 SiebelAddfault 를 캐치하기 위해 catch block을 outer scope와 연계할 수 있습니다. SiebelAddfault가 발생할 때마다, compensate activity는 inner scope에 대한 보정작업을 수행하고 SAP 고객 레코드를 삭제합니다. 이때 inner scope의 모든 액티비티가 성공적인 경우에만 보정 작업이 성공적으로 완료될 수 있음을 참고하시기 바랍니다.

scope와 compensation handler를 수정한 BPEL 프로세스가 그림 6과 같습니다.

그림 6 SAP 고객 레코드 삭제를 위한 compensation logic

Case 6
Siebel 업데이트가 실패하고 SAP 업데이트만 성공한 경우에도 트랜잭션은 실패합니다. 이로 인해 데이터 일관성에 문제가 발생할 수 있습니다. 따라서, SAP에서 발생한 트랜잭션을 롤백 하기 위한 compensation logic이 필요합니다. Compensation handler는 SAP Update 서비스와 연계되어 SAP Rollback 서비스를 호출합니다. BPEL 프로세스의 수정 작업은 위에서 설명한 가이드라인을 준수하는 형태로 수행됩니다.

compensate activity를 명시적으로 호출할 수 있는 기능은 BPEL 에러 핸들링 프레임워크의 핵심으로 활용됩니다. 고전적인 EAI 보정 메커니즘과 달리, BPEL은 표준화된 롤백 방법론을 제공하고 있습니다.

TIBCO 및 webMethods 서비스의 통합을 위한 BPEL 프로세스를 생성했다면, 이제 BPEL 어댑터와 EAI 툴 간의 커뮤니케이션을 보다 효과적으로 수행할 수 있는 방법을 알아보기로 합시다.

4 단계: 비즈니스 커뮤니케이션의 보안. 보안은 아웃바운드(TIBCO, webMethods 서비스 호출의 보안)와 인바운드(BPEL 프로세스의 보안)의 두 레벨로 나누어 구현됩니다.

아웃바운드 보안 (Outbound Security)
먼저 TIBCO 및 webMethods 서비스에 대한 불법적인 액세스를 차단하기 위한 보안 대책이 필요합니다. Oracle BPEL Process Manager는 외부 서비스 호출 시 HTTP basic authentication 또는 WS-Security authentication을 지원합니다. 본 문서의 예제에서는 HTTP 인증을 사용하여 TIBCO 및 webMethods 서비스 및 BPEL 프로세스로부터의 호출 메커니즘의 보안을 구현하는 방법을 설명합니다.

TIBCO BusinessWorks와 webMethods Integration Server에 구현된 웹 서비스는 기본적으로 HTTP basic authentication을 지원합니다. TIBCO 웹 서비스를 설계하는 과정에서 SOAP event source activity의 Transport Details 탭에서 Use Basic Authentication 체크박스를 체크해야 합니다. TIBCO Administrator를 이용하여 웹 서비스를 구축하는 과정에서 사용자/역할 별로 서비스 액세스 레벨을 설정할 수 있습니다. 또 webMethods Developer를 이용하여 웹 서비스를 설정하는 과정에서 각각의 operation 별로 ACL(Access Control List)을 작성할 수 있습니다.

TIBCO, webMethods 서비스를 구축하는 과정에서 basic authentication을 사용하였다면, 서비스에 대한 호출이 수행될 때마다 인증정보가 BPEL 프로세스에 전달되어야 합니다. 두 개의 Partner Link 속성(httpUsername과 httpPassword)을 이용하면 이 작업을 쉽게 수행할 수 있습니다. 이 속성의 값은 아래와 같이 정적으로 설정될 수 있습니다.

그림 7 httpUsername과 httpPassword의 설정

인증정보를 다이내믹하게 전달하고자 하는 경우에는 copy rule을 사용합니다.

  
<copy>
  <from variable="varUsername"/>
  <to partnerLink="p1" bpelx:property="httpUsername"/>
</copy>

인바운드 보안 (Inbound Security)
HTTP 인증을 이용하면 BPEL 프로세스가 불법적인 사용자에 의해 차단되는 것을 방지할 수 있습니다. 또 각 BPEL 프로세스 별로 다른 인증 정보를 설정하는 것이 가능합니다.

이 기능을 사용하려면, 애플리케이션 서버 레벨에서 HTTP basic authentication을 활성화해야 합니다. 그런 다음 인증 정보는 BPEL 프로세스에 의해 호출되고 Partner Link 속을 통해 TIBCO, webMethods 웹 서비스로 전달됩니다.

BPEL 보안에 대한 보다 자세한 정보가 필요하신 경우 OTN의 "Securing BPEL Processes & Services" Webinar를 확인하시기 바랍니다.

5 단계: 중앙집중적 로깅 및 에러 핸들링. 비즈니스 프로세스의 보안과 안정성을 확보하는 것만큼이나, 중앙집중적인 로깅, 통보 기능을 구현하는 것 또한 중요합니다. 중앙집중적인 로깅, 에러 핸들링 프레임워크를 구현함으로써 애플리케이션의 안정성을 한층 더 강화하고, 재활용성을 증가시키고, 개발 비용을 절감할 수 있습니다. BPEL 프로세스의 웹 서비스 또는 미들웨어를 이용하여 이러한 프레임워크를 구축하는 것이 가능합니다. Oracle BPEL Process Manager의 파일 어댑터를 이용하여 서비스에 로깅 기능을 추가하고 에러 발생시 이메일 통보 기능을 구현할 수 있습니다.

프레임워크에 사용하게 될 샘플 스키마가 아래와 같습니다:

이 BPEL 프로세스는 비동기식 단방향 웹 서비스로서 공개되므로, 서비스의 클라이언트에는 별도의 시간 지연이 수반되지 않습니다. 중앙집중적 로깅 및 통보를 위한 LogNotify 프로세스가 아래 그림과 같습니다.

그림 8 Oracle BPEL Process Manager를 이용한 로깅 및 에러 핸들링 프레임워크

그림 8에서 확인할 수 있는 것처럼, LogNotify 프로세스는 아래와 같은 작업을 수행합니다:

1. 외부 프로세스로부터 전달되는 정보를 로그에 저장합니다
2. Log Service를 호출하여 데이터를 로그 파일에 저장합니다. 이 과정에서 Log Service는 파일 어댑터를 활용합니다.
3. 로깅이 성공적인 경우 프로세스가 완료됩니다. 또 ROLE 필드에ERROR가 포함된 경우, 서비스는 이메일을 통해 담당자에게 통보를 수행합니다. 이메일 정보는 수신된 메시지 원본으로부터 추출됩니다 (샘플 스키마를 참고하십시오.).
4. 이메일 통보 작업이 실패한 경우, 에러가 로그 파일에 저장된 후 프로세스가 종료됩니다.

오늘날의 통합 시장은 강력한 EAI 제품으로 넘치고 있으며, 많은 통합 기능이 구현되어 제공되고 있습니다. BPEL은 기존 EAI 솔루션의 서비스 전환을 위한 독자적인 대안을 제공합니다. 기존 미들웨어 프로세스를 웹 서비스로 공개하고, 웹 서비스들을 Oracle BPEL Process Manager로 통합함으로써, 기업은 SOA를 위한 첫 걸음을 내디딜 수 있을 것입니다.

View는 사용자가 실제로 테이블을 만들고 데이터를 집어넣어서 사용하는 것이 아니라 이미 만들어진 Table에서 자신이 원하는 일부를 마치 테이블 처럼 정의 한 것이다.

뷰를 만드는 형식은 아래와 같다.

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW viewname  [(alias[, alias..)]
AS subqurey
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];
     
각각의 옵션을 보자

OR REPLACE : 만약에 기존에 만들어 놓은 똑같은 이름의 View가 있다면 덮어 써라.
         FORCE : DB에 View로 사용할 테이블이 존재하지 않더라도 View를 만들어라.
     NOFORCE : DB에 View로 사용할 테이블이 존재하지 않으면 만들지 말아라.
WITH CHECK OPTION : View에서 insert, update 작업을 할때 사용되는 제약사항을 기술한다.
WITH READ ONLY : 이 옵션을 사용하면 View는 오직 읽기만 가능하다.      


ex) EMPLOYEE 테이블의 EMPNO와 NAME을 VIEW로 만들고 싶은 경우
    1. 기본 문법
      CREATE VEIW EMPLOYEE_VIEW AS
      SELECT EMPNO, NAME
         FROM EMPLOYEE;

사용방법은 SELECT 문과 같다.
 
     SELECT *
        FROM EMPLOYEE_VIEW;

  2. 옵션을 사용한 문법
      CREATE OR REPLACE VIEW EMPLOYEE_VIEW AS
      SELECT EMPNO, NAME
         FROM EMPLOYEE
           WITH READ ONLY;