UPDATE statement not working in stored procedure; No error & no exception is happening

Question

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' & 'TBLHOTELFARE'.

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.

Problem is no exception is happening and also no update is happening to the database.

Here is my code:

Schemas:

TBLHOTEL Schema: {DATE1 (DATE) , ACROOMS (NUMBER) , NACROOMS (NUMBER), HOTELID (VARCHAR2(10)) }

TBLHOTELFARE Schema: {HOTELID (VARCHAR2(10)), CLASS (VARCHAR2(5)), FARE (NUMBER)}

TBLHOTELDETAIL Schema: {HOTELID (VARCHAR2(10)) , PLACE (VARCHAR2(15)) , HOTELNAME (VARCHAR2(15)) }

Procedure:

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;

CALLING STATEMENT

DECLARE 
 C  number;

BEGIN

   TableUpdateByParameter (140,200,TO_DATE('03/24/2013','MM/DD/YYYY'),'H1',3000,'GANGTOK','TRIPTI','AC',C);
END;

DBMS OUTPUT:

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

Please help me to identify the problem. If extra information needed, please let me know.

Answer 1

The UPDATE statement is working, but updates 0 rows, as shown per your log (Row Updated =0). This is not an error, your updates evaluate the where clause, finds 0 rows matching, and performs 0 modification. In Oracle, an update that doesn't match any row in the where clause is still a success.

Now why does it happen. Let's take your first update:

UPDATE tblhotel
   SET acrooms = acrooms, nacrooms = nacrooms
 WHERE date1 = (to_date(date1, 'mm/dd/yyyy'))
   AND hotelid = 'hotelid' ;

I think you want to update the row that have the column hotelid with the value passed as parameter. There are a few problems with that:

• First of all, you're not comparing the column and the parameter, but the column with a constant. Parameters don't use quotes. Constants (VARCHAR2) do.
• Secondly, you shouldn't call a parameter with the same name as your column, this causes confusion and could even cause variable shadowing. I recommend using a prefix that no column whatsoever uses in your schema. One common prefix for parameters is p_.
• Finally, you don't need conversion functions if your parameter are of the good type (since your parameter p_date1 is of type date, you don't need the to_date function).

So if you rename your parameters p_hotelid and p_date1, your statement should read:

UPDATE tblhotel
   SET acrooms = acrooms, nacrooms = nacrooms
 WHERE date1 = p_date1
   AND hotelid = p_hotelid;

In this case there is no confusion nor conversion error possible.

---

On an unrelated note:

• there seems to be a mismatch between your procedure name and the log it generates: no insertion will ever be done by update statements.
• Don't catch when others, let the error propagate. PL/SQL will rollback the procedure changes if the error propagates. PL/SQL statements (DML and blocks) are atomic by nature, they either fail completely or succeed entirely.