IF UPDATE is success how to get acknowledegment whether it is sucess or failure in procedure

Question

Possible Duplicate:
Number of rows affected by an UPDATE in PL/SQL

CREATE PROCEDURE P_Update(in_termid IN VARCHAR2,StmntType IN VARCHAR2)
AS
BEGIN
IF StmntType = 'UpdateCS'
BEGIN
update OP_TTER_MAPPING set TXN_STATUS = 'N' where  TERMINAL_ID = in_termid;
END  

Else If StmntType = 'UpdateHS' 
BEGIN
update OP_TTERMINALMASTER set TXN_STATUS = 'N' where  TERMINAL_ID =  in_termid;
END  
end P_UpdateTIDStatus;

1. In the above procedure if the UPDATE is success i want to know it is successful or failure, how to do that, i should get some acknowledgement, how to modify the above proc to get acknowledgement?

2. can i assign like this set TXN_STATUS = 'N' in procedure?

3. Is this the correct way IF StmntType = 'UpdateCS' can i compare like this, if its correct v are going to create the procedure in backend and im going to execute from frontend, how ill i know what the "StmntType " are

Thanks in advance.

Answer 1

sql%rowcount will give you count and if update is not successful then you need not worry, it will give you error... :P Normally people use expection of catch a error like this. Hope this helps..

Answer 2

Try as follows, let me know if you get any compilation errors because I have not tested it.

CREATE OR REPLACE PROCEDURE P_Update(in_termid IN VARCHAR2,
                          StmntType IN VARCHAR2,
                                     returnval IN OUT NUMBER)
IS 
BEGIN
 IF (StmntType = 'UpdateCS') then
         update OP_TTER_MAPPING set TXN_STATUS = 'N' 
         where  TERMINAL_ID = in_termid;
   END IF;
  returnval := 0;
 EXCEPTION
 WHEN OTHERS THEN
   returnval := 1;
   raise_application_error
      (-20140                                      
      ,'Exception is raised'
      );
END P_Update;

When you call your procedure from front end, you can check with return value, if it is 1 then update has failed.