Oracle | OWA_OPT

Oracle OWA_OPT_LOCK Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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

      /