| 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_ASSERT |
DBMS_SQL |
DBMS_SYS_SQL |
HTF |
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
This package is owned by SYS and no privs are granted |
| 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 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;
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;
/ |
