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.

Answer 2

If hotelid is a column in your database, you almost certainly do not want to overload the name to use it as the name of an argument to the procedure. Doing so makes using the parameter in a SQL statement rather complicated. Most people develop some sort of convention to differentiate the two. I prefer prefixing parameter names with a p_ which is a relatively common convention.

CREATE OR REPLACE PROCEDURE TableUpdateByParameter (p_acrooms   in number,
                                           p_nacrooms  in number,
                                           p_date1      in date,
                                           p_hotelid   in varchar2,
                                           p_fare in number,
                                           p_place in varchar2,
                                           p_hotelname in varchar2,
                                           p_class in varchar2,
                                           p_successCnt  out number) 
IS

Your SQL statement would then become

 WHERE tblhoteldetail.hotelid = p_hotelid

If you really want to overload the parameter name, you'd have to prefix the parameter name with the procedure name. But that will generally cause you no end of grief as you inadvertently write code like

 WHERE tblhoteldetail.hotelid = hotelid

and inadvertently update every row in your table.

Answer 3

Each of your where statements is similar to this:

AND hotelid = 'hotelid'

This will match the hotelid column on the table against that string literal "hotelid" each time, not the value passed into the proc.

To get them to reference the parameter you need to use something like:

AND tblhotel.hotelid = TableUpdateByParameter.hotelid

where the left hand side is the column on the table and the right hand side is your proc parameter.

An alternative would be to have a naming convention that all params are prefixed with "p", so that you can use:

AND tblhotel.hotelid = photelid