PROCEDURE upd_account
            (   p_acc_id      accounts.acc_id%type
              , p_acc_name    accounts.acc_name%type
              , p_acc_amount  accounts.acc_amount%type
              , p_acc_date    accounts.acc_date%type
              , p_version     accounts.version%type
            )
             IS
          BEGIN
              UPDATE accounts
              set acc_name    = p_acc_name
            , acc_amount  = p_acc_amount
            , acc_date    = p_acc_date
            , version = p_version + 1
     where version = p_version;
     DBMS_OUTPUT.PUT_LINE ('Number of updated records: ' || TO_CHAR(SQL%ROWCOUNT));
  if(SQL%ROWCOUNT = 0)
  THEN
  RAISE_APPLICATION_ERROR( -20001, 'Oops, the row has changed since you read it.' );
  END IF;
   END;

SQL> CREATE TABLE bob (ID NUMBER, b VARCHAR2(10), tcn NUMBER);

Table created


SQL> CREATE TRIGGER bob_bur
  2  before update on bob FOR EACH ROW
  3  begin
  4  if(:NEW.TCN != :OLD.TCN+1) THEN
  5  RAISE_APPLICATION_ERROR(-20000, 'Concurrency Failure');
  6  end if;
  7  :NEW.TCN := DBMS_UTILITY.GET_TIME;
  8  END;
  9  /

Trigger created


SQL> create or replace trigger bob_ibr
  2  before insert on bob
  3  for each row
  4  begin
  5    :NEW.TCN := DBMS_UTILITY.GET_TIME;
  6  end;
  7  /

Trigger created


SQL> insert into bob (id, b) values (1, 'text1');

1 row inserted


SQL> commit;

Commit complete

SQL> select * from bob;

        ID B                 TCN
---------- ---------- ----------
         1 text1       429969331

SQL> update bob set b = 'text2', tcn = 429969331 + 1 where id = 1;

1 row updated

SQL> select * from bob;

        ID B                 TCN
---------- ---------- ----------
         1 text1       429969331

SQL> update bob set b = 'text3', tcn = 429969331 + 1 where id = 1;

<<HANGS>>

SQL> commit;

Commit complete

update bob set b = 'text3', tcn = 429969331 + 1 where id = 1
       *
ERROR at line 1:
ORA-20000: Concurrency Failure
ORA-06512: at "LIVE.BOB_BUR", line 3
ORA-04088: error during execution of trigger 'LIVE.BOB_BUR'

SQL> select * from bob;

        ID B                 TCN
---------- ---------- ----------
         1 text2       429978188
         

SQL> select * from bob;

        ID B                 TCN
---------- ---------- ----------
         1 text2       429978188

SQL> update bob set b = 'text4', tcn = 429978188 + 1 where id = 1;

1 row updated

SQL> commit;

Commit complete

SQL> update bob set b = 'text5', tcn = 429978188 + 1 where id = 1;
update bob set b = 'text5', tcn = 429978188 + 1 where id = 1
       *
ERROR at line 1:
ORA-20000: Concurrency Failure
ORA-06512: at "LIVE.BOB_BUR", line 3
ORA-04088: error during execution of trigger 'LIVE.BOB_BUR'

CREATE OR REPLACE PROCEDURE change_balance(p_account_id, p_amount)

l_new_balance NUMBER;

BEGIN
  
  UPDATE accounts
  SET balance = balance + p_amount --p_amount will be negative for withdrawels)
  WHERE coount_id = p_account_id
  RETURNING balance INTO l_new_balance;
  
  IF l_new_balance < 0 THEN
    raise_application_error(-20001, 'Sorry you're overdrawn');
  END IF;
  
END;

CREATE OR REPLACE PROCEDURE change_balance(p_account_id, p_amount)

l_current_balance NUMBER;
l_new_balance NUMBER;

BEGIN
  
  SELECT balance INTO l_current_balance
  FROM accounts
  WHERE account_id = p_account_id;
  
  l_new_balance := l_current_balance + p_amount;
  
  IF l_new_balance < 0 THEN
      raise_application_error(-20001, 'Sorry you're overdrawn');
  END IF;
  
  UPDATE accounts
  SET balance = l_new_balance
  WHERE account_id = p_account_id;
    
END;

CREATE OR REPLACE PROCEDURE change_balance(p_account_id, p_amount)

l_current_balance NUMBER;
l_new_balance NUMBER;

BEGIN
  
  SELECT balance INTO l_current_balance
  FROM accounts
  WHERE account_id = p_account_id
  FOR UPDATE;
  
  l_new_balance := l_current_balance + p_amount;
  
  IF l_new_balance < 0 THEN
      raise_application_error(-20001, 'Sorry you're overdrawn');
  END IF;
  
  UPDATE accounts
  SET balance = l_new_balance
  WHERE account_id = p_account_id;
    
END;

begin
account.api.upd_account (1, 'user', 1000);
end;

CREATE OR REPLACE PACKAGE account_api AS
PROCEDURE upd_account
          (
            p_acc_id     accounts.acc_id%type
          , p_acc_name   accounts.acc_name%type
          , p_acc_amount accounts.acc_amount%type
      );
end account_api;
 /

CREATE OR REPLACE PACKAGE BODY account_api AS
PROCEDURE upd_account (
            p_acc_id     accounts.acc_id%type
          , p_acc_name   accounts.acc_name%type
          , p_acc_amount accounts.acc_amount%type
)
is
l_current_balance NUMBER;
l_new_balance NUMBER;

BEGIN

  SELECT acc_amount INTO l_current_balance
  FROM accounts
  WHERE acc_id = p_acc_id
  FOR UPDATE;

  l_new_balance := l_current_balance + p_acc_amount;

  IF l_new_balance < 0 THEN
      raise_application_error(-20001, 'Sorry you re overdrawn');
  END IF;

  UPDATE accounts
  SET acc_amount = l_new_balance,
      acc_name = p_acc_name
  WHERE acc_id = p_acc_id;
COMMIT;
end;
end account_api;
/

begin account_api.upd_account(1, 'user', 1000) end;