CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle OWA_OPT_LOCK
Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/privoolk.sql
First Availability 8.1.7
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_SQL HTF SYS
DBMS_SYS_SQL HTP  
Exceptions
Name SQLCODE Description
last_column -1007  last_column
Security In 11g the source code was modified to use DBMS_ASSERT to prevent SQL Injection
 
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 VARCHAR2) RETURN NUMBER;
CREATE TABLE t AS
SELECT owner, object_name, object_id
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';

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 VARCHAR2,
p_tname VARCHAR2,
p_rowid ROWID) RETURN NUMBER;
CREATE TABLE t AS
SELECT owner, object_name, object_id
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';

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
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';

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 STORE_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
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';

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;
/
 
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [285 users online]    © 2010 psoug.org