Oracle Update confirmation

From your description I assume that at one time the request is only handle 1 record for update. If this is the case you can define a  Function that return the message instanously. like below (I also assume all columns are varchar2, you just have to replace it accordingly).


Function sfc_upd_emp (p_empid varchar2, street_address varchar2, city varchar2, state varchar2, zip varchar2) return varchar2 is
  vchEmpID varchar2(10);
begin
  select empID into vchEmpID
    from emp where empid=p_empid;
 
 update emp
       set .......
  where empId = vchEmpID;
  commit;
  return 'Update Success !';
exception when no_data_found then
  return 'Invalid Emp ID !';
end;

I used it personally for some of my projects...

I wish it helps.
 

I forgot to mention that this is a mod_plsql procedure. I only use IN arameters and print the output using htp.print statements.

I also do not use "select for update". I think this is only for client/server app and nto web app. For web app you need to use optimistic locking. I may be wrong here. but http is stateless and using select for update will lock the record for a long time.

Looking at the above code, You are saying that if the update runs then it is successful. However , the update can run and I can get
0 rows updated
which means the row was not really updated but the program will say "update Successful".

It seems to me that there should be more checking on whether update occured successfully or not.
1.  check the number of rows updated.
2. select the data after commit and verify that it is what the update called for.

munzer,
In the function I wrote, if there's nothing to update, it will said 'Invalid Emp ID', because before the update proceed, the function check whether the record thal will be updated exists (that why there's a select statement before the update).

When this select statement failed (Find no rows in return), the procedure jump to exception right away without making any updation, and give return message 'Invalid Emp ID'

If you want to check whether the commit is failed, you can add more exception on that function.

I think what you are saying is that

if select works the update will always work.
if select fails then record was not found and update failed.

But is not there a case where you find a record and then update fails (0 rows updated)?
Also, if select fails then I would say the record did not exist. I am trying to tie the success/failure for the function of the update statement.

your solution is excellent.

one more thing

1.  Do you worry about lost updates and how you handle those? optimstic locking

2.  I am thinking of adding another exception
"when others then
  get error code and error message
save it to a table

do you think this is enough and appropriate to have two exceptions for his kind of program

one for no data found
one for other general errors.

1. No, I never worry about unsuccessful update, in most of my update/insert/delete procedures/functions, I mostly give a return value to user, and I always add when others exception to handle any unexpected result. Mostly it only rollback the previous command, and return the 'SQLERRMS' error text to user user, and for a long functions/procedures I usually put some comments to mark, where the error come from.

2. Besides No Data Found and Others, I usually also put a DUP_VAL_ON_INDEX to notify if an insert/update failed because of duplicate values in some indexes.

prast

what i meant by lost updates is that when you update do u lock the record or not.

If you do not, then you are taking a chance that someone else may have read the record and update it then you overwrite his changes with your update.

This is what "select for update" = pessimistic lock is all about.

For web apps I think the best is to use optimistic locking  where you read the values before your update and then update only when the values are still the same to make sure no one has changed any values.
You can also do it based on a flag or reord checksum value.