CREATE OR REPLACE PROCEDURE proc_today_dut (
   dut_cur      OUT   pack_today_dut.p_dut_cur,
   i_dut_date   IN       VARCHAR,
   i_dept_code  IN       VARCHAR
)
IS

v_dut_org_code VARCHAR(11); -- 변수선언

BEGIN

   -- 기관코드 가져온다
   SELECT dut_org_code into v_dut_org_code from SLFANDUTORGDEPT
   WHERE dept_code = i_dept_code;
 
   OPEN dut_cur
   FOR
            SELECT d.dept_nm, b.nm, dut_gbn
   FROM slfandutord a, cmmanuser b, cmmandptmnt d, slfandutpst f
   WHERE b.dep_code = d.dept_code(+)
   AND   decode((select y.code_ctn                             
      from icuanapvstat x, cmmandcode y                        
      where y.code_id = 'ICU010'                   
      and x.apv_state_code =y.code          
      and x.tbl_nm = 'SLFANDUTORD'           
      and a.apv_no = x.apv_doc_vlu), '완료', a.dut_wrk_sid, null, a.dut_wrk_sid, a.old_sid)=b.usr_id(+)
   AND a.dut_date = i_dut_date
   AND a.dut_posit = f.dut_pst_code
   AND a.ord_gubun = 'Y'
   AND a.dut_org_code = f.dut_org_code
   AND a.dut_org_code = v_dut_org_code
   ORDER BY a.dut_date DESC, a.dut_gbn, a.dut_posit, b.usr_order;

END proc_today_dut;
/


프로시져입니다.

v_dut_org_code 를 변수선언하여,

첫번째 select 문에서 기관코드를 얻어와서 
두번째 select 문에 조건절로 넣어줍니다.

문제는, 첫번째 select 문에서 데이터가 없을경우입니다.

jsp에서 프로시져호출을 하는데, 404 에러가 나타납니다.

    String arrRtnName = "";

     String arrRtnDept = "";

     String arrRtnResult = "";

     String[] retArr = null;

     String[] retArrDept = null;

     String[] retArrName = null;

     String[] retArrDutDay = null;

cstmt = conn.prepareCall("{call PACK_TODAY_DUT.proc_today_dut(?, ?, ?)}");

 cstmt.registerOutParameter(1, OracleTypes.CURSOR);

 cstmt.setString(2, strCurrDate);

 cstmt.setString(3, strDeptId);

 cstmt.execute();

 rs = (ResultSet)cstmt.getObject(1);  

 Vector v = new Vector();

 Vector v2 = new Vector();

 Vector v3 = new Vector();

 Vector v4 = new Vector();

 while(rs.next()){

  arrRtnDept = rs.getString(1);

  arrRtnName = rs.getString(2);

  strDutGbn  = rs.getString(3);

  arrRtnResult = arrRtnName+"("+arrRtnDept+")";

  v.addElement( arrRtnResult );  

  v2.addElement( arrRtnDept );  

  v3.addElement( arrRtnName );  

  v4.addElement( strDutGbn );

 }

 retArr = new String[v.size()];

 retArrDept = new String[v2.size()];

 retArrName = new String[v3.size()];

 retArrDutDay = new String[v4.size()];

 v.copyInto( retArr );

 v2.copyInto( retArrDept );

 v3.copyInto( retArrName );

 v4.copyInto( retArrDutDay );