Oracle DBMS_IOT
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose Creates a table used for identifying chained rows in index organized tables. This package is not created by the DBCA installation because it is not required with database versions greater than 8.0.

I am documenting it here in the library because, for reasons beyond my comprehension, Oracle won't drop it from the distribution. If they keep it ... The Library keeps it.
AUTHID DEFINER
Dependencies
COL$ DBMS_STANDARD ODCINUMBERLIST
DBMS_ASSERT ICOL$ ODCIRIDLIST
DBMS_IOT_LIB IND$ PLITBLM
DBMS_SQL OBJ$ USER$
Documented Yes but only the first two procedures. The final three are officially undocumented
First Available No known
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsiotc.sql
SQL> @?/rdbms/admin/dbmsiotc.sql

SP2-0808: Package created with compilation warnings

Synonym created.

Grant succeeded.

Library created.

SP2-0810: Package Body created with compilation warnings

SQL> sho err
Errors for PACKAGE BODY DBMS_IOT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
92/21 PLW-07203: parameter 'C_NAM' may benefit from use of the NOCOPY compiler hint
518/3 PLW-06002: Unreachable code
Subprograms
 
BUILD_CHAIN_ROWS_TABLE
Creates a table into which references to the chained rows for an index-organized table can be placed using the ANALYZE command dbms_iot.build_chain_rows_table(
owner               IN VARCHAR2,
iot_name            IN VARCHAR2,
chainrow_table_name IN VARCHAR2 DEFAULT 'IOT_CHAINED_ROWS');
conn sys@pdbdev as sysdba

@?\rdbms\admin\dbmsiotc.sql

GRANT execute ON dbms_iot TO uwclass;

conn uwclass/uwclass@pdbdev

CREATE TABLE iottab (
person_id   NUMBER(5),
dept_name   VARCHAR2(20),
project_no  VARCHAR2(20),
time_worked NUMBER(4,2),
PRIMARY KEY(person_id, dept_name, project_no))
ORGANIZATION INDEX pctthreshold 10 OVERFLOW;

exec dbms_iot.build_chain_rows_table('UWCLASS','IOTTAB');

desc iottab

desc iot_chained_rows
 
BUILD_EXCEPTIONS_TABLE
Creates an exception table into which rows of an index-organized table that violate a constraint can be placed dbms_iot.build_exceptions_table(
owner                 IN VARCHAR2,
iot_name              IN VARCHAR2,
exceptions_table_name IN VARCHAR2 DEFAULT 'IOT_EXCEPTIONS');
conn uwclass/uwclass@pdbdev

exec dbms_iot.build_exceptions_table('UWCLASS', 'IOTTAB');

desc iottab

desc iot_exceptions

INSERT INTO iottab
(person_id, dept_name, project_no, time_worked)
VALUES
(1, 'IT', '110A', 6.5);

INSERT INTO iottab
(person_id, dept_name, project_no, time_worked)
VALUES
(1, 'IT', '201C', 1.5);

INSERT INTO iottab
(person_id, dept_name, project_no, time_worked)
VALUES
(2, 'IT', '110A', 8.5);

COMMIT;

SELECT * FROM iottab;

ALTER TABLE iottab
ADD CONSTRAINT cc_iottab_max_time_worked
CHECK (time_worked <= 8.0);

SELECT * FROM iot_exceptions;

ALTER TABLE iottab
ADD CONSTRAINT cc_iottab_max_time_worked
CHECK (time_worked <= 8.0)
EXCEPTIONS INTO iot_exceptions;

SELECT * FROM iot_exceptions;
 
CHECK_REDUNDANT_PKEY
Check for redundant primary key entry dbms_iot.check_redundant_pkey(
table_owner IN VARCHAR2,
table_name  IN VARCHAR2,
index_owner IN VARCHAR2,
index_name  IN VARCHAR2,
uniqueness  IN VARCHAR2,
nblk_uniq   IN BINARY_INTEGER DEFAULT NULL)
RETURN VARCHAR2;
conn uwclass/uwclass@pdbdev

CREATE TABLE labor_hour (
WORK_DATE   DATE,
EMPLOYEE_NO VARCHAR2(8),
CONSTRAINT pk_labor_hour
PRIMARY KEY (work_date, employee_no))
ORGANIZATION INDEX;

INSERT INTO labor_hour VALUES (TRUNC(SYSDATE), 1);
INSERT INTO labor_hour VALUES (TRUNC(SYSDATE), 2);
INSERT INTO labor_hour VALUES (TRUNC(SYSDATE), 3);
INSERT INTO labor_hour VALUES (TRUNC(SYSDATE), 1);

set serveroutput on

DECLARE
 retval VARCHAR2(100);
BEGIN
  retval := dbms_iot.check_redundant_pkey('UWCLASS', 'LABOR_HOUR', 'UWCLASS', 'PK_LABOR_HOUR', 'UNIQUE');
  dbms_output.put_line(retval);
END;
/
NUMBER_TO_UROWID
Undocumented
Overload 1
dbms_iot.number_to_urowid(
n   IN  NUMBER,
len OUT INTEGER)
RETURN VARCHAR2;
DECLARE
 vLen INTEGER;
 vRet VARCHAR2(30);
BEGIN
  vRet := dbms_iot.number_to_urowid(42, vLen);
  dbms_output.put_line(vRet);
  dbms_output.put_line(TO_CHAR(vLen));
END;
/
Overload 2 dbms_iot.number_to_urowid(n IN NUMBER)
RETURN VARCHAR2;
SELECT dbms_iot.number_to_urowid(42)
FROM dual;
Overload 3 dbms_iot.number_to_urowid(n sys.ODCINumberList)
RETURN sys.ODCIRidList;
TBD
 
REPAIR_REDUNDANT_PKEY
Repair redundant primary key entry dbms_iot.repair_redundant_pkey(schema IN VARCHAR2);
exec dbms_iot.repair_redundant_pkey('UWCLASS');

Related Topics
Built-in Functions
Built-in Packages
Constraints
Index Organized Tables
What's New In 19c
What's New In 20c-21c

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