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
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
OWA
OWA_COOKIE
OWA_CUSTOM
OWA_CX
OWA_SEC
OWA_TEXT
OWA_UTIL
Packages

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-2014 Daniel A. Morgan All Rights Reserved