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 DBMS_IOT
Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/dbmsiotc.sql
Dependencies dbms_iot_lib
Object Privileges execute on dbms_iot
 
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 / as sysdba

@? dbms\admin\dbmsiotc.sql

GRANT execute ON dbms_iot TO uwclass;

conn uwclass/uwclass

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');
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;
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;
TBD
Overload 2 dbms_iot.number_to_urowid(n IN NUMBER) RETURN VARCHAR2;
TBD
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
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [249 users online]    © 2010 psoug.org