Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
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;
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;
/
PL/SQL procedure successfully completed.
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;
/
PL/SQL procedure successfully completed.
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;
/