Oracle DBMS_IOT
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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 any version greater than 8.0.

I am documenting it here in the library solely because for reasons beyond my comprehension Oracle won't drop it from the distribution. If they keep it ... I keep it.
AUTHID DEFINER
Dependencies
COL$ DBMS_STANDARD ODCINUMBERLIST
DBMS_IOT_LIB ICOL$ ODCIRIDLIST
DBMS_OUTPUT 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
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
Constraints
Index Organized Tables
Packages
What's New In 12cR1
What's New In 12cR2

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