Oracle OWA_OPT_LOCK
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
Purpose Contains subprograms that impose optimistic locking strategies, so as to prevent lost updates. It checks if the row that the user is interested in updating has been changed by someone else.
AUTHID CURRENT_USER
Data Types TYPE VCARRAY IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;

vcarray(1) = table_owner
vcarray(2) = table_name
vcarray(3) = rowid
Dependencies
DBMS_ASSERT DBMS_STANDARD HTP
DBMS_SQL HTF  
Documented Yes
Exceptions
Error Code Reason
ORA-01007 last_column
First Available 8.1.7
Security Model Owned by SYS with no privileges granted.
Source {ORACLE_HOME}/rdbms/admin/privoolk.sql
Subprograms
 
CHECKSUM
Returns a checksum value for a specified string, or for a row in a table. For a row in a table, the function calculates the checksum value based on the values of the columns in the row.

Overload 1
owa_opt_lock.checksum(p_buff IN VARCHAR2) RETURN NUMBER;
CREATE TABLE t AS
SELECT owner, object_name, object_id
FROM all_objects;

SELECT rowid, owner, object_name, object_id
FROM t
WHERE object_name = 'ALL_TABLES'
AND owner = 'SYS';

set serveroutput on

DECLARE
  s  VARCHAR2(50);
  x  NUMBER;
BEGIN
  SELECT owner || object_name || TO_CHAR(object_id)
  INTO s
  FROM t
  WHERE object_name = 'ALL_TABLES'
  AND owner = 'SYS';

  x := owa_opt_lock.checksum(s);
  dbms_output.put_line(x);
END;
/
Overload 2 owa_opt_lock.checksum(
p_owner IN VARCHAR2,
p_tname IN VARCHAR2,
p_rowid IN ROWID)
RETURN NUMBER;
CREATE TABLE t AS
SELECT owner, object_name, object_id
FROM all_objects;

SELECT rowid, owner, object_name, object_id
FROM t
WHERE object_name = 'ALL_TABLES'
AND owner = 'SYS';

set serveroutput on

DECLARE
 x  NUMBER;
 u  ROWID;
BEGIN
  SELECT rowid
  INTO u
  FROM t
  WHERE object_name = 'ALL_TABLES'
  AND owner = 'SYS';

  x := owa_opt_lock.checksum('UWCLASS', 'T', u);
  dbms_output.put_line(x);
END;
/

UPDATE t
SET object_id = 99998
WHERE object_id = 2423;

DECLARE
 x  NUMBER;
 u  ROWID;
BEGIN
  SELECT rowid
  INTO u
  FROM t
  WHERE object_name = 'ALL_TABLES'
  AND owner = 'SYS';

  x := owa_opt_lock.checksum('UWCLASS', 'T', u);
  dbms_output.put_line(x);
END;
/
 
GET_ROWID
Returns the ROWID data type from the specified VCARRAY data type owa_opt_lock.get_rowid(p_old_values VCARRAY) RETURN ROWID;
CREATE TABLE t AS
SELECT owner, object_name, object_id
FROM all_objects;

set serveroutput on

DECLARE
 v owa_opt_lock.vcarray;
BEGIN
  v(1) := 'UWCLASS';
  v(2) := 'T';

  SELECT rowid
  INTO v(3)
  FROM t
  WHERE object_name = 'ALL_TABLES'
  AND owner = 'SYS';

  dbms_output.put_line(owa_opt_lock.get_rowid(v));
END;
/
 
STORE_VALUES
Stores, as hidden HTML form elements, the column values of a row pending an update owa_opt_lock.store_values(
p_owner  VARCHAR2,
p_tname  VARCHAR2,
p_rowid  ROWID);
See VERIFY_VALUES procedure below
 
VERIFY_VALUES
Verifies whether values in the specified row have been updated since the last query owa_opt_lock.verify_values(p_old_values VCARRAY) RETURN BOOLEAN;
CREATE TABLE t AS
SELECT owner, object_name, object_id
FROM all_objects;

set serveroutput on

DECLARE
 v  owa_opt_lock.vcarray;
 x  NUMBER;
 b  BOOLEAN;
BEGIN
  v(1) := 'UWCLASS';
  v(2) := 'T';

  SELECT rowid
  INTO v(3)
  FROM t
  WHERE object_name = 'ALL_TABLES'
  AND owner = 'SYS';

  v(4) := 'OWNER';
  v(5) := 'OBJECT_NAME';
  v(6) := 'OBJECT_ID';

  x := owa_opt_lock.checksum(v(1), v(2), v(3));
  dbms_output.put_line('Initial: ' || TO_CHAR(x));

  owa_opt_lock.store_values(v(1), v(2), v(3));

  UPDATE t
  SET object_id = 99999
  WHERE object_name = 'ALL_TABLES'
  AND owner = 'SYS';
  COMMIT;

  dbms_output.put_line(v(1));
  dbms_output.put_line(v(2));
  dbms_output.put_line(v(3));

  IF owa_opt_lock.verify_values(v) THEN
    dbms_output.put_line('No Change');
  ELSE
    dbms_output.put_line('Changed');
  END IF;
END;
/

Related Topics
Built-in Functions
Built-in Packages
OWA
OWA_COOKIE
OWA_CUSTOM
OWA_CX
OWA_SEC
OWA_TEXT
OWA_UTIL
What's New In 12cR2
What's New In 18cR3

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved