title: UPDATE statement not working in stored procedure; No error & no exception is happening
body: <p>I have created a stored procedure in Oracle. The procedure is compiled successfully with no errors. The procedure has 3 UPDATE queries which updates 3 tables 'TBLHOTEL', 'TBLHOTELDETAIL' &amp; 'TBLHOTELFARE'.</p>
  
  <p>After every Update statement a variable  successCnt1 is incremented to get the number of successful insert queries. At last  successCnt1 is assigned to  successCnt to store the final result. If exception happens then in any query, it is set to 0 , to indicate no insertion happens.</p>
  
  <p><strong>Problem is</strong> no exception is happening and also no update is happening to the database.</p>
  
  <p>Here is my code:</p>
  
  <p><em><strong>Schemas:</em></strong></p>
  
  <p><em><strong>TBLHOTEL Schema:</em></strong> {DATE1 (DATE) , ACROOMS (NUMBER) , NACROOMS (NUMBER), HOTELID (VARCHAR2(10)) }</p>
  
  <p><em><strong>TBLHOTELFARE Schema:</em></strong>  {HOTELID (VARCHAR2(10)), CLASS (VARCHAR2(5)), FARE (NUMBER)}</p>
  
  <p><em><strong>TBLHOTELDETAIL Schema:</em></strong> {HOTELID (VARCHAR2(10)) , PLACE (VARCHAR2(15)) , HOTELNAME (VARCHAR2(15)) }</p>
  
  <p><em><strong>Procedure:</em></strong> </p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE TableUpdateByParameter (acrooms   in number,
                                             nacrooms  in number,
                                             date1      in date,
                                             hotelid   in varchar2,
                                             fare in number,
                                             place in varchar2,
                                             hotelname in varchar2,
                                             class in varchar2,
                                             successCnt  out number) IS
  
  successCnt1 number(6) NOT NULL := 0;
  rowUpdated1 number(6) NOT NULL := 0;
  rowUpdated2 number(6) NOT NULL := 0;
  rowUpdated3 number(6) NOT NULL := 0;
  
  BEGIN
    SAVEPOINT before;
  
    UPDATE tblhotel
       SET acrooms = acrooms, nacrooms = nacrooms
     WHERE date1 = (to_date(date1, 'mm/dd/yyyy'))
       AND hotelid = 'hotelid' ;
  
  rowUpdated1 := SQL%RowCount;
  successCnt1 := successCnt1 + 1;
  
     dbms_output.put_line('Successful Insertion tblhotel. count ='||successCnt1);
     dbms_output.put_line('Successful Insertion tblhotel. Row Updated ='||rowUpdated1);
  
    UPDATE tblhoteldetail
   SET place = 'place', hotelname = 'hotelname'
     WHERE hotelid = 'hotelid' ;
  
  rowUpdated2 := SQL%RowCount;
  successCnt1 := successCnt1 + 1;
  
  dbms_output.put_line('Successful Insertion tblhoteldetail. count ='||successCnt1);
  dbms_output.put_line('Successful Insertion tblhoteldetail. Row Updated= '||rowUpdated2);
  
    UPDATE tblhotelfare
       SET fare = fare
     WHERE hotelid = 'hotelid' 
       AND class = 'class';
  
  rowUpdated3 := SQL%RowCount;
  successCnt1 := successCnt1 + 1;
  successCnt := successCnt1;
  
   COMMIT;
  
   dbms_output.put_line('Successful Insertion tblhotelfare. count ='||successCnt);
  dbms_output.put_line('Successful Insertion tblhotelfare. Row Updated= '||rowUpdated3);
  
  EXCEPTION
    WHEN Others THEN
      successCnt1 := 0;
      successCnt := successCnt1;
      dbms_output.put_line('An error has occured. count ='||successCnt);
  
      ROLLBACK TO before;
  
  END;
  </code></pre>
  
  <p><em><strong>CALLING STATEMENT</em></strong></p>
  
  <pre><code>DECLARE 
   C  number;
  
  BEGIN
  
     TableUpdateByParameter (140,200,TO_DATE('03/24/2013','MM/DD/YYYY'),'H1',3000,'GANGTOK','TRIPTI','AC',C);
  END;
  </code></pre>
  
  <p><em><strong>DBMS OUTPUT:</em></strong></p>
  
  <pre><code>Successful Insertion tblhotel. count =1
  Successful Insertion tblhotel. Row Updated =0
  Successful Insertion tblhoteldetail. count =2
  Successful Insertion tblhotel. Row Updated =0
  Successful Insertion tblhotelfare. count =3
  Successful Insertion tblhotel. Row Updated =0
  </code></pre>
  
  <p>Please help me to identify the problem. If extra information needed, please let me know.</p>