NHibernate, Oracle 환경에서 StoreProcedure 호출

Oracle에서 REFCURSOR 타입을 사용하는 StoreProcedure를 OUT parameter로 사용하는 경우에는 2가지 제약 사항이 있다.

1. Table Cursor를 사용하는 것이 아닌, SYS_REFCURSOR 를 사용해야지 된다.
2. 1개의 Cursor만 return되는 것을 지원하며, in parameter의 순서가 중요하다. 반드시 SYS_REFCURSOR를 처음에 return시켜줘야지 된다.

이와 같은 규칙을 따르면 다음과 같은 StoreProcedure를 사용하는 경우 다음과 같이 HBM이 구성 가능하다.

CREATE OR REPLACE PROCEDURE FWKUSER.sp_XMMenu_MenuName_Test_q
  (
   spcur        OUT sys_refcursor, 
   p_menu_name    IN VARCHAR2) IS
BEGIN
  OPEN spcur FOR
    select   
    *         
   
    from   xm_menu 
    where   menu_name like '%' || p_menu_name || '%'
    and        fold_yn = 'N'
    and        hidden_yn = 'N'
    and        use_yn = 'Y'
    and        del_yn = 'N'
    order by instr(menu_name, p_menu_name, 1, 1), menu_name;
END;
/

이렇게 구성된 hbm을 이용해서 다음과 같이 StoreProcedure를 호출 할 수 있다.

using(var session = sessionFactory.OpenSession())
{
    var query = session.GetNamedQuery("GetMenus");
    query.SetParameter("menuName", "%");
    var output = query.List();

    foreach(var p in output)
    {
        Console.WriteLine(p.MenuName);
    }
}