title: deadlock occured after execute CallableStatement
body: <p>jdk1.6 , oracle 11g 64bit environment</p>
  
  <p>This following matter is just raised after changing to oracle 11g,
  worked well  on oracle 9i environment.</p>
  
  <p>sorry , my english is very poor</p>
  
  <p>when i execute procedure in toad over and over , working very well</p>
  
  <p>but when i execute procedure by callablestatement in java
  after just one execute well, not working any more!</p>
  
  <p>so,  i use query like this</p>
  
  <pre><code>    SELECT a.serial#,a.SID, a.username, b.id1, a.sql_hash_value
      FROM     v$session a, v$lock b
      WHERE  b.id1 IN (
      SELECT DISTINCT e.id1
      FROM    v$session d, v$lock e  WHERE d.lockwait = e.kaddr) 
      AND       a.SID = b.SID   AND       b.request = 0
  </code></pre>
  
  <p>one deadlock is exist</p>
  
  <p>After Tomcat restart, deadlock is disappeared</p>
  
  <p>here is my java code</p>
  
  <pre><code>private Ss14104Ent ipchulTmpProcedureCall(String accod_dd, String accod_no) throws Exception {
  
      long del_count      = 0; 
      long ipgo_count     = 0;
      long out_count      = 0; 
      String errorCode    = ""; 
      String errorMsg     = ""; 
  
      Connection          conn    = null;
      CallableStatement   cstmt   = null ;
  
      Ss14104Ent result_data = new Ss14104Ent();
  
      String worker =  (String)session.getAttribute("emp_id"); 
      try
      {
          conn = new DBConnector().connectDB();
  
  
          cstmt = conn.prepareCall( "{call out_ipchul_list_proc(?,?,?,?,?)}" ) ;
  
          cstmt.setString(1 , accod_dd    ) ;
          cstmt.setString(2 , accod_no    ) ; 
          cstmt.setString(3 , worker      ) ; 
          cstmt.registerOutParameter( 4, Types.VARCHAR ) ;
          cstmt.registerOutParameter( 5, Types.VARCHAR ) ;
  
          System.out.println("call--before-execute");
  
          cstmt.executeUpdate( ) ;
          System.out.println("call--after-execute");
  
          errorCode   = cstmt.getString( 4 )  ; // errcode
          errorMsg    = cstmt.getString( 5 )  ; // errmsg
  
  
          result_data.errorCode  = errorCode;
          result_data.errorMsg   = errorMsg ;
  
           cstmt.close( ) ;
           conn.close();
  
          System.out.println("AAAXX errorCode:" + errorCode);
          System.out.println("AAAXX errorMsg:" + errorMsg);
  
    }
    catch (Exception ee)
    {
         request.setAttribute("err_Message", "PROCEDURE Error:" + "&lt;br&gt;" +   ee.getMessage());
         forward(ERR_JSP);
    }
   finally
  {
   try {
      if( cstmt != null ) cstmt.close( ) ;
      if (conn != null) conn.close();
  
       }catch( Exception e ){}
        }
  
      return result_data;
     }
  
  
  }
  </code></pre>
  
  <p>After Tomcat restart, 
  "AAAXX errorMsg:" is printed in stdout.log on first execution, 
  but  only by "call--before-execute" is printed  on   next execution.</p>
  
  <p>i can not find the reason why deadlock is occur</p>
  
  <p>please help me..</p>
  
  <p>here is my procedure code</p>
  
  <pre><code>    CREATE OR REPLACE PROCEDURE MYDBX.out_ipchul_list_proc(
                      p_accod_dd          in  VARCHAR2,
                      p_accod_no          in  VARCHAR2,
                      p_worker            in  VARCHAR2,
                      p_errorCode         out VARCHAR2,
                      p_errorMsg          out VARCHAR2
                      )
  
      IS
  
      v_lst_seq               NUMBER;         
      v_up_seq                NUMBER;         
  
  
  
      v_accod_dd              VARCHAR2(6);    
      v_accod_no              NUMBER;         
      v_color_clf             VARCHAR2(5);    
      v_design_no             VARCHAR2(15);   
      v_accod_qty             NUMBER;         
      v_prod_dd               VARCHAR2(8);    
      v_prod_qty              NUMBER;         
      v_chul_dd               VARCHAR2(8);    
      v_chul_qty              NUMBER;         
      v_out_chul_dd           VARCHAR2(8);    
      v_out_chul_qty          NUMBER;         
      v_out_chul_cust         VARCHAR2(300);  
      v_out_in_dd             VARCHAR2(8);    
      v_out_in_qty            NUMBER;         
      v_out_in_cust           VARCHAR2(300);  
  
  
  
      LIST_NOT_FOUND         EXCEPTION;   
  
  
      CURSOR c_ipchul_list
      IS
          select a.accod_dd               accod_dd
          , a.accod_no                    accod_no
          , b.color_clf                   color_clf
          , nvl(b.design_no,'-')          design_no
          , b.accod_qty_yad               accod_qty
          , null                          prod_dd
          , null                          prod_qty
          , null                          chul_dd
          , null                          chul_qty
          , null                          out_chul_cust
          , null                          out_chul_dd
          , null                          out_chul_qty
          , null                          out_in_cust
          , null                          out_in_dd
          , null                          out_in_qty
          from SS1ORDEMST a
          , SS1ORDEDET b
          where a.accod_dd = b.accod_dd
          and a.accod_no = b.accod_no
          and a.accod_dd = p_accod_dd
          and a.accod_no = p_accod_no
          --  order by a.accod_dd, a.accod_no, b.color_clf, b.design_no
          union
          select a.accod_dd
          , a.accod_no
          , a.color_clf
          , nvl(a.design_no,'-') design_no
          , null      accod_qty
          , to_char(a.i_str_dd,'YYYYMMDD') prod_dd
          , sum(a.item_qty) prod_qty
          , null  chul_dd
          , null  chul_qty
          , null out_chul_cust
          , null out_chul_dd
          , null out_chul_qty
          , null out_in_cust
          , null out_in_dd
          , null out_in_qty
          from SS2PROCMST a
          , SS1ORDEDET b
          where a.accod_dd = b.accod_dd
          and a.accod_no = b.accod_no
          and a.accod_seq = b.accod_seq
          and a.accod_dd  = p_accod_dd
          and a.accod_no  = p_accod_no
          and a.dec_clf = '1'
          group by a.accod_dd, a.accod_no, a.color_clf, to_char(a.i_str_dd,'YYYYMMDD'), a.design_no
          union
          select a.accod_dd
          , a.accod_no
          , b.color_clf
          , nvl(b.design_no,'-')
          , null  accod_qty
          , null  prod_dd
          , null  prod_qty
          , a.out_str_dd chul_dd
          , sum(b.color_qty) chul_qty
          , null out_chul_cust
          , null out_chul_dd
          , null out_chul_qty
          , null out_in_cust
          , null out_in_dd
          , null out_in_qty
          from SS2SENDMST a
          , SS2SENDDET b
          where a.item_clf not in ('5', '6')
          and a.out_str_dd = b.out_str_dd
          and a.out_str_no = b.out_str_no
          and a.accod_dd = p_accod_dd
          and a.accod_no = p_accod_no
          group by a.accod_dd, a.accod_no, b.color_clf, b.design_no, a.out_str_dd
          union
          select a.accod_dd
          , a.accod_no
          , b.color_clf
          , nvl(b.design_no,'-')
          , null  accod_qty
          , null  prod_dd
          , null  prod_qty
          , null chul_dd
          , null chul_qty
          , (select cust_name from MP1CUSTMST where cust_code = a.cust_code) out_chul_cust
          , a.out_str_dd out_chul_dd
          , sum(b.color_qty) out_chul_qty
          , null out_in_cust
          , null out_in_dd
          , null out_in_qty
          from SS2SENDMST a
          , SS2SENDDET b
          where a.item_clf = '5'
          and a.out_str_dd = b.out_str_dd
          and a.out_str_no = b.out_str_no
          and a.accod_dd = p_accod_dd
          and a.accod_no = p_accod_no
          group by a.accod_dd, a.accod_no, b.color_clf, b.design_no, a.cust_code, a.out_str_dd
          union
          select a.accod_dd
          , a.accod_no
          , b.color_clf
          , nvl(b.design_no,'-')
          , null  accod_qty
          , null  prod_dd
          , null  prod_qty
          , null chul_dd
          , null chul_qty
          , null out_chul_cust
          , null out_chul_dd
          , null out_chul_qty
          , (select cust_name from MP1CUSTMST where cust_code = a.cust_code) out_in_cust
          , a.ipgo_dd out_in_dd
          , sum(b.surang_qty) out_in_qty
          from SS2IPGOMST a
          , SS2IPGODET b
          where a.ipgo_dd = b.ipgo_dd
          and a.ipgo_seq = b.ipgo_seq
          and a.accod_type = '5'
          and a.accod_dd = p_accod_dd
          and a.accod_no = p_accod_no
          group by a.accod_dd, a.accod_no, b.color_clf, b.design_no, a.cust_code, a.ipgo_dd;
  
  
      BEGIN
  
          v_lst_seq   := 1; 
          v_up_seq    := 0;
  
  
  
  
          BEGIN
  
  
  
              BEGIN
                  delete from SS2IPCHULTMP
                  where accod_dd = p_accod_dd
                  and accod_no    = p_accod_no;
                  EXCEPTION
                      WHEN OTHERS THEN
                          p_errorCode := '0001';
                          p_errorMsg  := 'SS2IPCHULTMP Delete::' || SUBSTR(SQLERRM, 1, 100);
                          rollback;
                  return;
              END;
  
  
  
              OPEN c_ipchul_list;
  
              LOOP
              FETCH c_ipchul_list INTO  v_accod_dd
                                      , v_accod_no
                                      , v_color_clf
                                      , v_design_no
                                      , v_accod_qty
                                      , v_prod_dd
                                      , v_prod_qty
                                      , v_chul_dd
                                      , v_chul_qty
                                      , v_out_chul_cust
                                      , v_out_chul_dd
                                      , v_out_chul_qty
                                      , v_out_in_cust
                                      , v_out_in_dd
                                      , v_out_in_qty;
  
              IF c_ipchul_list%ROWCOUNT = 0 THEN
                  RAISE LIST_NOT_FOUND;
              END IF;
  
              EXIT WHEN c_ipchul_list%NOTFOUND;
  
  
  
                  IF v_accod_qty is not null THEN
  
                      BEGIN
                          insert into SS2IPCHULTMP(accod_dd
                                                  ,accod_no
                                                  ,lst_seq
                                                  ,color_clf
                                                  ,design_no
                                                  ,accod_qty
                                                  ,prod_dd
                                                  ,prod_qty
                                                  ,chul_dd
                                                  ,chul_qty
                                                  ,out_chul_dd
                                                  ,out_chul_qty
                                                  ,out_chul_cust
                                                  ,out_in_dd
                                                  ,out_in_qty
                                                  ,out_in_cust
                                                  ,rgi_dd
                                                  ,rgi_mno)
                                          values ( v_accod_dd
                                                  ,v_accod_no
                                                  ,v_lst_seq
                                                  ,v_color_clf
                                                  ,v_design_no
                                                  ,v_accod_qty
                                                  ,v_prod_dd
                                                  ,v_prod_qty
                                                  ,v_chul_dd
                                                  ,v_chul_qty
                                                  ,v_out_chul_dd
                                                  ,v_out_chul_qty
                                                  ,v_out_chul_cust
                                                  ,v_out_in_dd
                                                  ,v_out_in_qty
                                                  ,v_out_in_cust
                                                  ,sysdate
                                                  ,p_worker);
                          EXCEPTION
                          WHEN VALUE_ERROR THEN
                              p_errorMsg       := 'SS2IPCHULTMP insert value error';
                              p_errorCode      := '0002';
                              rollback;
                              return;
                          WHEN PROGRAM_ERROR THEN
                              p_errorMsg       := 'SS2IPCHULTMP insert program error';
                              p_errorCode      := '0003';
                              rollback;
                              return;
                          WHEN DUP_VAL_ON_INDEX THEN
                              p_errorMsg       := 'SS2IPCHULTMP insert duplication error';
                              p_errorCode      := '0004';
                              rollback;
                              return;
                          WHEN OTHERS THEN
                              p_errorMsg       := 'SS2IPCHULTMP insert other error ('|| SUBSTR(SQLERRM, 1, 100)||')';
                              p_errorCode      := '0005';
                              rollback;
                              return;
                      END;
  
  
                      v_lst_seq := v_lst_seq + 1; 
  
                  END IF;
  
                  IF v_prod_dd is not null THEN
  
                      BEGIN
                          select nvl(min(lst_seq),9999)
                          INTO v_up_seq
                          from SS2IPCHULTMP
                          where accod_dd  = v_accod_dd
                          and accod_no    = v_accod_no
                          and color_clf   = v_color_clf
                          and design_no   = v_design_no
                          and prod_dd     is null ; 
                          EXCEPTION
                              WHEN OTHERS THEN
                                  v_up_seq := 9999; 
                      END;
  
  
                      IF v_up_seq = 9999 THEN
  
                          BEGIN
                              insert into SS2IPCHULTMP(accod_dd
                                                      ,accod_no
                                                      ,lst_seq
                                                      ,color_clf
                                                      ,design_no
                                                      ,accod_qty
                                                      ,prod_dd
                                                      ,prod_qty
                                                      ,chul_dd
                                                      ,chul_qty
                                                      ,out_chul_dd
                                                      ,out_chul_qty
                                                      ,out_chul_cust
                                                      ,out_in_dd
                                                      ,out_in_qty
                                                      ,out_in_cust
                                                      ,rgi_dd
                                                      ,rgi_mno)
                                              values ( v_accod_dd
                                                      ,v_accod_no
                                                      ,v_lst_seq
                                                      ,v_color_clf
                                                      ,v_design_no
                                                      ,v_accod_qty
                                                      ,v_prod_dd
                                                      ,v_prod_qty
                                                      ,v_chul_dd
                                                      ,v_chul_qty
                                                      ,v_out_chul_dd
                                                      ,v_out_chul_qty
                                                      ,v_out_chul_cust
                                                      ,v_out_in_dd
                                                      ,v_out_in_qty
                                                      ,v_out_in_cust
                                                      ,sysdate
                                                      ,p_worker);
                              EXCEPTION
                              WHEN VALUE_ERROR THEN
                                  p_errorMsg       := 'SS2IPCHULTMP  insert value error !!';
                                  p_errorCode      := '0006';
                                  rollback;
                                  return;
                              WHEN PROGRAM_ERROR THEN
                                  p_errorMsg       := 'SS2IPCHULTMP insert program error !!';
                                  p_errorCode      := '0007';
                                  rollback;
                                  return;
                              WHEN DUP_VAL_ON_INDEX THEN
                                  p_errorMsg       := 'SS2IPCHULTMP insert duplication error !!';
                                  p_errorCode      := '0008';
                                  rollback;
                                  return;
                              WHEN OTHERS THEN
                                  p_errorMsg       := 'SS2IPCHULTMP other insert error ('|| SUBSTR(SQLERRM, 1, 100)||')';
                                  p_errorCode      := '0009';
                                  rollback;
                                  return;
                          END;
  
  
                          v_lst_seq := v_lst_seq + 1; 
  
  
                      ELSE
  
  
                          BEGIN
                              update SS2IPCHULTMP
                              set prod_dd     = v_prod_dd
                              , prod_qty      = v_prod_qty
                              where accod_dd  = v_accod_dd
                              and accod_no    = v_accod_no
                              and color_clf   = v_color_clf
                              and design_no   = v_design_no
                              and lst_seq     = v_up_seq ;
                              EXCEPTION
                              WHEN VALUE_ERROR THEN
                                  p_errorMsg       := 'SS2IPCHULTMP update value error !!';
                                  p_errorCode      := '0010';
                                  rollback;
                                  return;
                              WHEN PROGRAM_ERROR THEN
                                  p_errorMsg       := 'SS2IPCHULTMP update program error !!';
                                  p_errorCode      := '0011';
                                  rollback;
                                  return;
                              WHEN DUP_VAL_ON_INDEX THEN
                                  p_errorMsg       := 'SS2IPCHULTMP update duplication error !!';
                                  p_errorCode      := '0012';
                                  rollback;
                                  return;
                              WHEN OTHERS THEN
                                  p_errorMsg       := 'SS2IPCHULTMP update other error ('|| SUBSTR(SQLERRM, 1, 100)||')';
                                  p_errorCode      := '0013';
                                  rollback;
                                  return;
                          END;
  
                      END IF;
  
                  END IF;
  
                  IF v_chul_dd is not null THEN
  
                      BEGIN
                          select nvl(min(lst_seq),9999)
                          INTO v_up_seq
                          from SS2IPCHULTMP
                          where accod_dd  = v_accod_dd
                          and accod_no    = v_accod_no
                          and color_clf   = v_color_clf
                          and design_no   = v_design_no
                          and chul_dd     is null ;
                          EXCEPTION
                              WHEN OTHERS THEN
                                  v_up_seq := 9999; 
                      END;
  
  
                      IF v_up_seq = 9999 THEN
  
                          BEGIN
                              insert into SS2IPCHULTMP(accod_dd
                                                      ,accod_no
                                                      ,lst_seq
                                                      ,color_clf
                                                      ,design_no
                                                      ,accod_qty
                                                      ,prod_dd
                                                      ,prod_qty
                                                      ,chul_dd
                                                      ,chul_qty
                                                      ,out_chul_dd
                                                      ,out_chul_qty
                                                      ,out_chul_cust
                                                      ,out_in_dd
                                                      ,out_in_qty
                                                      ,out_in_cust
                                                      ,rgi_dd
                                                      ,rgi_mno)
                                              values ( v_accod_dd
                                                      ,v_accod_no
                                                      ,v_lst_seq
                                                      ,v_color_clf
                                                      ,v_design_no
                                                      ,v_accod_qty
                                                      ,v_prod_dd
                                                      ,v_prod_qty
                                                      ,v_chul_dd
                                                      ,v_chul_qty
                                                      ,v_out_chul_dd
                                                      ,v_out_chul_qty
                                                      ,v_out_chul_cust
                                                      ,v_out_in_dd
                                                      ,v_out_in_qty
                                                      ,v_out_in_cust
                                                      ,sysdate
                                                      ,p_worker);
                              EXCEPTION
                              WHEN VALUE_ERROR THEN
                                  p_errorMsg       := 'SS2IPCHULTMP insert value error';
                                  p_errorCode      := '0014';
                                  rollback;
                                  return;
                              WHEN PROGRAM_ERROR THEN
                                  p_errorMsg       := 'SS2IPCHULTMP insert program error';
                                  p_errorCode      := '0015';
                                  rollback;
                                  return;
                              WHEN DUP_VAL_ON_INDEX THEN
                                  p_errorMsg       := 'SS2IPCHULTMP insert duplication error';
                                  p_errorCode      := '0016';
                                  rollback;
                                  return;
                              WHEN OTHERS THEN
                                  p_errorMsg       := 'SS2IPCHULTMP insert other error ('|| SUBSTR(SQLERRM, 1, 100)||')';
                                  p_errorCode      := '0017';
                                  rollback;
                                  return;
                          END;
  
  
                          v_lst_seq := v_lst_seq + 1;
  
  
                      ELSE
  
  
                          BEGIN
                              update SS2IPCHULTMP
                              set chul_dd     = v_chul_dd
                              , chul_qty      = v_chul_qty
                              where accod_dd  = v_accod_dd
                              and accod_no    = v_accod_no
                              and color_clf   = v_color_clf
                              and design_no   = v_design_no
                              and lst_seq     = v_up_seq ;
                              EXCEPTION
                              WHEN VALUE_ERROR THEN
                                  p_errorMsg       := 'SS2IPCHULTMP update value error !!';
                                  p_errorCode      := '0018';
                                  rollback;
                                  return;
                              WHEN PROGRAM_ERROR THEN
                                  p_errorMsg       := 'SS2IPCHULTMP update program error!!';
                                  p_errorCode      := '0019';
                                  rollback;
                                  return;
                              WHEN DUP_VAL_ON_INDEX THEN
                                  p_errorMsg       := 'SS2IPCHULTMP update duplication error !!';
                                  p_errorCode      := '0020';
                                  rollback;
                                  return;
                              WHEN OTHERS THEN
                                  p_errorMsg       := 'SS2IPCHULTMP update other error ('|| SUBSTR(SQLERRM, 1, 100)||')';
                                  p_errorCode      := '0021';
                                  rollback;
                                  return;
                          END;
  
                      END IF;
  
                  END IF;
  
                  IF v_out_chul_dd is not null THEN
  
                      BEGIN
                          select nvl(min(lst_seq),9999)
                          INTO v_up_seq
                          from SS2IPCHULTMP
                          where accod_dd  = v_accod_dd
                          and accod_no    = v_accod_no
                          and color_clf   = v_color_clf
                          and design_no   = v_design_no
                          and out_chul_dd is null ; 
                          EXCEPTION
                              WHEN OTHERS THEN
                                  v_up_seq := 9999; 
                      END;
  
  
                      IF v_up_seq = 9999 THEN
  
                          BEGIN
                              insert into SS2IPCHULTMP(accod_dd
                                                      ,accod_no
                                                      ,lst_seq
                                                      ,color_clf
                                                      ,design_no
                                                      ,accod_qty
                                                      ,prod_dd
                                                      ,prod_qty
                                                      ,chul_dd
                                                      ,chul_qty
                                                      ,out_chul_dd
                                                      ,out_chul_qty
                                                      ,out_chul_cust
                                                      ,out_in_dd
                                                      ,out_in_qty
                                                      ,out_in_cust
                                                      ,rgi_dd
                                                      ,rgi_mno)
                                              values ( v_accod_dd
                                                      ,v_accod_no
                                                      ,v_lst_seq
                                                      ,v_color_clf
                                                      ,v_design_no
                                                      ,v_accod_qty
                                                      ,v_prod_dd
                                                      ,v_prod_qty
                                                      ,v_chul_dd
                                                      ,v_chul_qty
                                                      ,v_out_chul_dd
                                                      ,v_out_chul_qty
                                                      ,v_out_chul_cust
                                                      ,v_out_in_dd
                                                      ,v_out_in_qty
                                                      ,v_out_in_cust
                                                      ,sysdate
                                                      ,p_worker);
                              EXCEPTION
                              WHEN VALUE_ERROR THEN
                                  p_errorMsg       := 'SS2IPCHULTMP insert value error';
                                  p_errorCode      := '0022';
                                  rollback;
                                  return;
                              WHEN PROGRAM_ERROR THEN
                                  p_errorMsg       := 'SS2IPCHULTMP insert program error!!';
                                  p_errorCode      := '0023';
                                  rollback;
                                  return;
                              WHEN DUP_VAL_ON_INDEX THEN
                                  p_errorMsg       := 'SS2IPCHULTMP insert duplication error!!';
                                  p_errorCode      := '0024';
                                  rollback;
                                  return;
                              WHEN OTHERS THEN
                                  p_errorMsg       := 'SS2IPCHULTMP insert other error('|| SUBSTR(SQLERRM, 1, 100)||')';
                                  p_errorCode      := '0025';
                                  rollback;
                                  return;
                          END;
  
  
                          v_lst_seq := v_lst_seq + 1; 
  
  
                      ELSE
  
  
                          BEGIN
                              update SS2IPCHULTMP
                              set out_chul_dd = v_out_chul_dd
                              , out_chul_qty  = v_out_chul_qty
                              , out_chul_cust = v_out_chul_cust
                              where accod_dd  = v_accod_dd
                              and accod_no    = v_accod_no
                              and color_clf   = v_color_clf
                              and design_no   = v_design_no
                              and lst_seq     = v_up_seq ;
                              EXCEPTION
                              WHEN VALUE_ERROR THEN
                                  p_errorMsg       := 'SS2IPCHULTMP  update value error !!';
                                  p_errorCode      := '0026';
                                  rollback;
                                  return;
                              WHEN PROGRAM_ERROR THEN
                                  p_errorMsg       := 'SS2IPCHULTMP update program error!!';
                                  p_errorCode      := '0027';
                                  rollback;
                                  return;
                              WHEN DUP_VAL_ON_INDEX THEN
                                  p_errorMsg       := 'SS2IPCHULTMP update duplication error!!';
                                  p_errorCode      := '0028';
                                  rollback;
                                  return;
                              WHEN OTHERS THEN
                                  p_errorMsg       := 'SS2IPCHULTMP update other error ('|| SUBSTR(SQLERRM, 1, 100)||')';
                                  p_errorCode      := '0029';
                                  rollback;
                                  return;
                          END;
  
                      END IF;
  
                  END IF;
  
                  IF v_out_in_dd is not null THEN
  
                      BEGIN
                          select nvl(min(lst_seq),9999)
                          INTO v_up_seq
                          from SS2IPCHULTMP
                          where accod_dd  = v_accod_dd
                          and accod_no    = v_accod_no
                          and color_clf   = v_color_clf
                          and design_no   = v_design_no
                          and out_in_dd   is null ; 
                          EXCEPTION
                              WHEN OTHERS THEN
                                  v_up_seq := 9999; 
                      END;
  
  
                      IF v_up_seq = 9999 THEN
  
                          BEGIN
                              insert into SS2IPCHULTMP(accod_dd
                                                      ,accod_no
                                                      ,lst_seq
                                                      ,color_clf
                                                      ,design_no
                                                      ,accod_qty
                                                      ,prod_dd
                                                      ,prod_qty
                                                      ,chul_dd
                                                      ,chul_qty
                                                      ,out_chul_dd
                                                      ,out_chul_qty
                                                      ,out_chul_cust
                                                      ,out_in_dd
                                                      ,out_in_qty
                                                      ,out_in_cust
                                                      ,rgi_dd
                                                      ,rgi_mno)
                                              values ( v_accod_dd
                                                      ,v_accod_no
                                                      ,v_lst_seq
                                                      ,v_color_clf
                                                      ,v_design_no
                                                      ,v_accod_qty
                                                      ,v_prod_dd
                                                      ,v_prod_qty
                                                      ,v_chul_dd
                                                      ,v_chul_qty
                                                      ,v_out_chul_dd
                                                      ,v_out_chul_qty
                                                      ,v_out_chul_cust
                                                      ,v_out_in_dd
                                                      ,v_out_in_qty
                                                      ,v_out_in_cust
                                                      ,sysdate
                                                      ,p_worker);
                              EXCEPTION
                              WHEN VALUE_ERROR THEN
                                  p_errorMsg       := 'SS2IPCHULTMP insert value error!!';
                                  p_errorCode      := '0022';
                                  rollback;
                                  return;
                              WHEN PROGRAM_ERROR THEN
                                  p_errorMsg       := 'SS2IPCHULTMP insert program error!!';
                                  p_errorCode      := '0023';
                                  rollback;
                                  return;
                              WHEN DUP_VAL_ON_INDEX THEN
                                  p_errorMsg       := 'SS2IPCHULTMP insert duplication error!!';
                                  p_errorCode      := '0024';
                                  rollback;
                                  return;
                              WHEN OTHERS THEN
                                  p_errorMsg       := 'SS2IPCHULTMP insert other error ('|| SUBSTR(SQLERRM, 1, 100)||')';
                                  p_errorCode      := '0025';
                                  rollback;
                                  return;
                          END;
  
  
                          v_lst_seq := v_lst_seq + 1; 
  
  
                      ELSE
  
  
                          BEGIN
                              update SS2IPCHULTMP
                              set out_in_dd   = v_out_in_dd
                              , out_in_qty    = v_out_in_qty
                              , out_in_cust   = v_out_in_cust
                              where accod_dd  = v_accod_dd
                              and accod_no    = v_accod_no
                              and color_clf   = v_color_clf
                              and design_no   = v_design_no
                              and lst_seq     = v_up_seq ;
                              EXCEPTION
                              WHEN VALUE_ERROR THEN
                                  p_errorMsg       := 'SS2IPCHULTMP update value error!!';
                                  p_errorCode      := '0026';
                                  rollback;
                                  return;
                              WHEN PROGRAM_ERROR THEN
                                  p_errorMsg       := 'SS2IPCHULTMP update program error!!';
                                  p_errorCode      := '0027';
                                  rollback;
                                  return;
                              WHEN DUP_VAL_ON_INDEX THEN
                                  p_errorMsg       := 'SS2IPCHULTMP update duplication error !!';
                                  p_errorCode      := '0028';
                                  rollback;
                                  return;
                              WHEN OTHERS THEN
                                  p_errorMsg       := 'SS2IPCHULTMP update other error ('|| SUBSTR(SQLERRM, 1, 100)||')';
                                  p_errorCode      := '0029';
                                  rollback;
                                  return;
                          END;
  
                      END IF;
  
                  END IF;
  
  
  
  
              END LOOP; 
  
              CLOSE c_ipchul_list;
  
  
  
          END;
  
  
         COMMIT;
  
      p_errorMsg  := 'complete!!';
      p_errorCode := '0000';
  
      EXCEPTION
          WHEN LIST_NOT_FOUND THEN
              p_errorCode := '0000';
              p_errorMsg  := p_accod_dd || '-' || p_accod_no ||'no in-out data exist ';
              rollback;
          WHEN OTHERS THEN
              p_errorCode := SQLCODE;
              p_errorMsg  := SUBSTR(SQLERRM, 1, 100);
              rollback;
  
  
      END out_ipchul_list_proc;
      /
  </code></pre>