Oracle Forms에서 조회조건의 설정은 블록 속성에서 WHERE Clause를 이용하거나 PRE-QUERY 트리거에서 SET_BLOCK_PROPERTY를 사용한다. 어느 방법을 사용하든 결국은 SQL문의 where에 해당하는 내용을 만들어내는 것이다. 이것이 조회 화면을 작성할 때 가장 많은 시간을 들이는 부분이다. 조회조건을 입력할 아이템의 수가 한 두 개일 경우에는 그리 어렵지 않게 작성할 수 있으나 조건의 수가 많아지면 경우의 수가 그만큼 많아지면서 에러 없이 정확하게 코딩 하기가 매우 어려워진다. 더구나, 실제 개발 현장에서는 유저의 요구가 수시로 바뀐다. 하나의 화면을 개발하는데 새로운 조건을 추가하거나 빼는 일이 수십 번 발생하기도 한다. 요구사항이 변경될 때마다 프로그램 로직을 바꾸는 일은 많은 시간과 노력이 요구된다. 이러한 문제를 쉽게 해결하기 위해 조회조건 정보를 읽어서 자동으로 조회조건을 생성하는 프로그램을 만들게 되었다.

아래에서 실제로 Oracle Forms에서 조회용 화면을 만드는 예제를 통해 이를 구현해 보기로 한다.

시나리오: 오라클 DB에 제공되는 HR 샘플 스키마의 Employees 테이블을 가지고 다양한 조건으로 검색하는 아래 그림과 같은 화면을 개발하고자 한다. 개발자의 환경에 따라 Employees 테이블이 아닌 실제 사용하는 테이블 가지고 해도 무방하다.

1. PL/SQL로 작성된 아래의 패키지 헤더를 Forms의 program unit에 만든다.

				 
PACKAGE K_QueryAutomation IS
PROCEDURE P_execute( ControlBlockName in VARCHAR2
,QueryBlockName in VARCHAR2 );
END K_QueryAutomation;
				  
                

2. PL/SQL로 작성된 아래의 패키지 바디를 Forms의 program unit에 만든다.

				
PACKAGE BODY K_QueryAutomation IS
--------------------------------------------------------------------
-- Forward Declaration
--------------------------------------------------------------------
FUNCTION F_GetCondition( BlockItem_Name in VARCHAR2 ) RETURN VARCHAR2;

--------------------------------------------------------------------
-- Common Variable
--------------------------------------------------------------------
   c_controllable_itemtype constant varchar2(100) := 'TEXT ITEM, CHECKBOX, LIST';
   c_controllable_datatype constant varchar2(100) := 'CHAR ALPHA NUMBER RNUMBER 
INT RINT DATE DATETIME';

--------------------------------------------------------------------
-- Main Program
--------------------------------------------------------------------
PROCEDURE P_Execute( ControlBlockName in VARCHAR2
                    ,QueryBlockName   in VARCHAR2 ) IS
   c_block      constant varchar2(40) := upper( ControlBlockName );
   c_last_item  constant varchar2(40) := upper( get_block_property(c_block,last_item) );

   v_item       varchar2(40) := upper( get_block_property(c_block,first_item) );
   v_datatype   varchar2(40);
   v_itemtype   varchar2(40);
   v_block_item varchar2(100);

   where_cond   varchar2(2000) := null;
BEGIN
   loop
      v_block_item := upper( c_block||'.'||v_item );
      v_itemtype := get_item_property( v_block_item, item_type );
      if instr( c_controllable_itemtype, v_itemtype ) > 0 AND
         substrb( v_Block_Item, -2 ) <> '_X' then
         v_datatype := get_item_property( v_block_item, datatype );
         if name_in( v_block_item ) is not null then
            if instr( c_controllable_datatype, v_datatype ) > 0 then
               where_cond := where_cond||' AND '||F_GetCondition( v_block_item );
            else
               null;
            end if;
         end if;
      else
         null;
      end if;
      exit when v_item = c_last_item;
      v_item := get_item_property(v_block_item,nextitem);
   end loop;

/* 특수한 부가조건있으면 여기에 추가하세요.
          example : where_cond := where_cond || 'AND ' || '조건문';
*/

/* default 조건이 있으면 여기에 추가하세요.
          example : where_cond := where_cond || 'AND ' || '조건문';
*/
-- 최종조건 SETTING
     WHERE_COND := LTRIM(LTRIM( WHERE_COND ),'AND');
     WHERE_COND := LTRIM(LTRIM( WHERE_COND ),'and');
     SET_BLOCK_PROPERTY( QueryBlockName, DEFAULT_WHERE, WHERE_COND );

END P_Execute;

--------------------------------------------------------------------
-- 
--------------------------------------------------------------------
FUNCTION F_GetCondition( BlockItem_Name in VARCHAR2 ) RETURN VARCHAR2 IS

   ItemName_Length    number := lengthb( BlockItem_Name );
   Point_Location     number := instrb( BlockItem_Name, '.');
   Item_Entered_Value varchar2(1000) := name_in( BlockItem_Name );
   ItemName_Postfix   varchar2(2)    := substrb( BlockItem_Name, -2 );

   BaseTable_FieldName varchar2(40);
   ComparisonOperator  varchar2(10);
BEGIN
   if ItemName_Postfix in ('_X') then
      return( NULL );
   end if;
   
   if ItemName_Postfix in ('_S','_E') then
      BaseTable_FieldName := substrb( BlockItem_Name, Point_Location + 1
                                    , ItemName_Length - Point_Location - 2 );
      if ItemName_Postfix = '_S' then
         ComparisonOperator := '>=';
      else
         ComparisonOperator := '<=';
      end if;
   else
      BaseTable_FieldName := substrb( BlockItem_Name, Point_Location + 1
                                    , ItemName_Length - Point_Location  );
      if INSTR( Item_Entered_Value, '%' ) > 0 or INSTR( Item_Entered_Value, '_' ) > 0 then -- Wildcard
         ComparisonOperator := ' like';
      else
         ComparisonOperator := '=';
      end if;
   end if;
   return( BaseTable_FieldName || ComparisonOperator || ' :' || BlockItem_Name );
END F_GetCondition;

END K_QueryAutomation;
               
               

3. 조회조건 입력용 블록을 만든다. (블록 이름을 “QUERY_COND”라고 하자.)
  조회조건의 아이템 이름은 다음과 같은 규칙으로 작성한다:

• 조회결과 블록에서 설정한 베이스 테이블의 컬럼과 동일하게 작성하여야 한다.
• 범위 조건을 지정하고자 하는 아이템의 경우에는 컬럼의 이름 뒤에 “_S” 또는 “_E”를 붙인다. “_S”를 붙이면 “크거나 같다”에 해당하는 조건을 생성한다. “_E”를 붙이면 “작거나 같다”인 조건을 생성한다.
• ( 예을 들면 EMPLOYEE 테이블에서 HIREDATE을 기준으로 질의한다고 한다면 프로그램을 작성시엔 HIREDATE_S , HIREDATE_E 아이템을 만들고, 이에 대한 결과는 실행시에 ‘HIREDATE >= HIREDATE_S and HIREDATE <= HIREDATE_E’ 을 만들어내어 질의가 이루어진다. )
• 조회조건에 포함시키지 않은 아이템은 “_X”를 아이템 이름에 붙이거나 Display Item으로 설정한다.

4. 조회결과 블록을 만든다.
   (베이스 테이블은 Employees로 하였고 블록 이름은 “EMPLOYEES”로 하자)

5. 조회결과 나타나는 EMPLOYEES 블록에 다음의 PRE-QUERY 트리거를 작성한다.
   K_QueryAutomation.P_execute( ControlBlockName => 'QUERY_COND' ,QueryBlockName => :SYSTEM.TRIGGER_BLOCK );
   주) ControlBlockName 파라메터 값은 조회조건 블록의 이름이다. QueryBlockName 파라메터 값은 조회결과 블록의 이름을 넘긴다.

6. Forms를 실행시켜 결과를 확인해보자.

위에서 설명한 패키지를 사용하면 유저의 빈번한 질의 요구사항 변경에도 큰 부담 없이 쉽게 대응할 수 있게 된다. 실제로 위의 조회조건 블록에 새로운 아이템을 추가하거나 삭제한 후에도 프로그램은 아무런 변경을 하지 않아도 작동된다.