Oracle DBMS

Oracle OWA_OPT_LOCK Version 12.1.0.1

General Information

Library Note

Morgan's Library Page Footer

The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

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_SYS_SQL	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	STORE_VALUES	VERIFY_VALUES
GET_ROWID

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;
/