Oracle 12c OWA_OPT

Oracle OWA_OPT_LOCK Version 12.1.0.2

General Information

Library Note

Morgan's Library Page Header

The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.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.

Also important ... there are rumors on the web about SPARC and Solaris being discontinued. Welcome to the age of fake news. There is absolutely not a byte of truth to these irresponsible rumors.

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	HTF
DBMS_SQL	DBMS_SYS_SQL	HTP

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;

      /