Oracle DBMS_IOT
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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 12.1.0
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

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