ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Notes
Object types and other user-defined types allow for the definition of data types that model the structure and behavior of the data in an application.
Related Data Dictionary Objects
COLLECTION$
CDB_SOURCE
SOURCE$
ALL_COLL_TYPES
CDB_TYPES
TYPE$
ALL_DEPENDENCIES
CDB_VARRAYS
USER_COLL_TYPES
ALL_SOURCE
DBA_COLL_TYPES
USER_DEPENDENCIES
ALL_TYPES
DBA_DEPENDENCIES
USER_SOURCE
ALL_VARRAYS
DBA_SOURCE
USER_TYPES
CDB_COLL_TYPES
DBA_TYPES
USER_VARRAYS
CDB_DEPENDENCIES
DBA_VARRAYS
Related System Privileges
ALTER ANY TYPE
CREATE TYPE
EXECUTE ANY TYPE
CREATE ANY TYPE
DROP ANY TYPE
UNDER ANY TYPE
Create Type Header
Single Column Object Declaration
CREATE OR REPLACE TYPE <schema_name>.<type_name> FORCE
AUTHID <CURRENT USER | DEFINER>
AS OBJECT (
<attribute> <attribute data_type>,
<inheritance clause>,
<subprogram spec>)
<[NOT] FINAL | INSTANTIABLE | PERSISTABLE>;
/
CREATE OR REPLACE TYPE ssn_t
AUTHID DEFINER AS OBJECT (
ssn_type CHAR(11));
/
desc ssn_t
SELECT object_name, object_type
FROM user_objects
ORDER BY 2;
CREATE OR REPLACE PACKAGE app_type IS
SUBTYPE t_return_code IS INTEGER;
SUBTYPE t_system_timeout IS NUMBER;
SUBTYPE t_guid IS CHAR(36);
SUBTYPE t_plsql_block IS CLOB;
SUBTYPE t_table_name IS VARCHAR2(30);
SUBTYPE t_column_name IS VARCHAR2(30);
SUBTYPE t_primary_id IS RAW(16);
SUBTYPE t_secondary_id IS VARCHAR2(255);
SUBTYPE t_customer_id IS RAW(16);
SUBTYPE t_object_type_enum IS NUMBER(10);
SUBTYPE t_language_code IS VARCHAR2(12);
SUBTYPE t_enum IS NUMBER(10);
END app_type;
/
Scalar Subtypes with Range Limit
SUBTYPE <type_name> IS <data_type> [RANGE <value> .. <value>];
set serveroutput on
DECLARE
SUBTYPE flagtype IS PLS_INTEGER RANGE 0..1;
x flagtype;
BEGIN
FOR i IN -2 .. 2 LOOP
BEGIN
x := i;
dbms_output.put_line('Success: ' || TO_CHAR(x));
EXCEPTION
WHEN others THEN
dbms_output.put_line('Can not assign ' || TO_CHAR(i) || ' To Flagtype');
END;
END LOOP;
END;
/
Non-Scalar Subtypes
CREATE OR REPLACE TYPE <type_name>
AUTHID <CURRENT USER | DEFINER>
UNDER <supertype_name>,
<attribute> <data_type>,
<inheritance clause> <subprogram spec>, <pragma clause>)
<FINAL | NOT FINAL> <INSTANTIABLE | NOT INSTANTIABLE>;
/
-- create object supertype
CREATE OR REPLACE TYPE person_typ AS OBJECT (
ssn NUMBER(9), name VARCHAR2(30), address VARCHAR2(100))
NOT FINAL;
/
-- derive collection type from supertype
CREATE OR REPLACE TYPE person_tab_typ AS TABLE OF person_typ;
/
SELECT type_name, typecode, attributes, methods, predefined, incomplete,
final, instantiable
FROM user_types;
-- for more information about collections
-- see the collections page of the library
-- derive object subtype from object supertype
CREATE OR REPLACE TYPE student_typ
UNDER person_typ (
deptid NUMBER, major VARCHAR2(30))
NOT FINAL;
/
-- create nested table from the two collection
CREATE TABLE test (
regular_field DATE,
person_nested_tab person_tab_typ,
student_nested_tab student_tab_typ)
NESTED TABLE person_nested_tab STORE AS per_tab
NESTED TABLE student_nested_tab STORE AS stu_tab;
-- for more information on nested tables
-- see the nested tables page of the library
desc test
desc per_tab
desc stu_tab
Subtype Limitation
DECLARE
SUBTYPE Xtype IS NUMBER(1,0);
var_num Xtype(2,0); -- note subtype definition is ignored
BEGIN
var_num := 10;
dbms_output.put_line('var_num = ' || TO_CHAR(var_num));
END;
/
Create Type Body (always as a database object)
Create Function
CREATE OR REPLACE FUNCTION validate_ssn(ssn_in IN VARCHAR2)
RETURN BOOLEAN IS
BEGIN
IF TRANSLATE(ssn_in, 'A0123456789', 'BAAAAAAAAAA') = 'AAA-AA-AAAA' THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END
validate_ssn;
/
Create Type Specification (Header)
CREATE OR REPLACE TYPE ssn AS OBJECT (
n_ CHAR(11),
CONSTRUCTOR FUNCTION ssn(ssn_in IN VARCHAR2) RETURN self AS result,
MEMBER FUNCTION get_ssn RETURN CHAR);
/
desc ssn
Create Type Body
CREATE OR REPLACE TYPE BODY ssn IS
CONSTRUCTOR FUNCTION ssn(ssn_in IN VARCHAR2)
RETURN self AS RESULT IS
BEGIN
IF validate_ssn(ssn_in) THEN
n_ := ssn_in;
RETURN;
ELSE
RAISE_APPLICATION_ERROR(-20001, 'INVALID SSN');
END IF;
END;
MEMBER FUNCTION get_ssn RETURN CHAR IS
BEGIN
RETURN n_;
END;
END;
/
SELECT object_name, object_type
FROM user_objects
ORDER BY 2;
desc ssn
Create Object Table
CREATE TABLE person (
per_name VARCHAR2(20),
per_ssn SSN);
desc person
set describe depth all linenum on indent on
desc person
desc user_tab_cols
col data_type format a20
SELECT column_name, data_type, data_type_mod, data_type_owner
FROM user_tab_cols
WHERE table_name = 'PERSON';
Test SSN Data Type
DECLARE
myssn ssn;
BEGIN
myssn := ssn(ssn_in=>'232-22-5678');
INSERT INTO person VALUES ('Morgan', myssn);
myssn := ssn(ssn_in=>'444=55-6789');
INSERT INTO person VALUES ('Morgan', myssn);
myssn := ssn(ssn_in=>'123-45-6789');
INSERT INTO person VALUES ('Kyte', myssn);
COMMIT;
END;
/
DECLARE
myssn ssn;
BEGIN
myssn := ssn(ssn_in=>'232-22-5678');
INSERT INTO person VALUES ('Morgan', myssn);
/*
myssn := ssn(ssn_in=>'444=55-6789');
INSERT INTO person VALUES ('Morgan', myssn);
*/
myssn := ssn(ssn_in=>'123-45-6789');
INSERT INTO person VALUES ('Kyte', myssn);
COMMIT;
END;
/
Alter Type
Alter Type Demo
ALTER TYPE <type name> ADD ATTRIBUTE (<atribute name> <data type>) CASCADE;
CREATE OR REPLACE TYPE phone_t AS OBJECT (
a_code CHAR(3),
p_number CHAR(8)) NOT FINAL;
/
desc phone_t
CREATE OR REPLACE TYPE phone_t_tab AS TABLE OF phone_t;
/
desc phone_t_tab
--produces an error
CREATE OR REPLACE TYPE phone_t AS OBJECT (
country_code CHAR(3),
area_code CHAR(3),
phone_number CHAR(8));
/
ALTER TYPE phone_t ADD ATTRIBUTE (country_code CHAR(3)) CASCADE;
desc phone_t
desc phone_t_tab
Drop Type
Dropping a Type
DROP TYPE <type_name>;
DROP TABLE person;
-- fails
DROP TYPE phone_t;
DROP TYPE phone_t_tab;
DROP TYPE phone_t;
Dropping a Type with dependencies
DROP TYPE <type_name> FORCE;
CREATE OR REPLACE TYPE phone_t AS OBJECT (
a_code CHAR(3),
p_number CHAR(8)) NOT FINAL;
/
CREATE OR REPLACE TYPE phone_t_tab AS TABLE OF phone_t;
/
-- will fail;
DROP TYPE phone_t;
DROP TYPE phone_t FORCE;
Type Inheritance
Supertype Creation
CREATE OR REPLACE TYPE person_typ AS OBJECT (
ssn NUMBER(9), name VARCHAR2(30), address VARCHAR2(100))
NOT FINAL;
/
SELECT object_name, object_type
FROM user_objects;
SELECT type_name, supertype_owner, supertype_name
FROM user_types;
Create Type From Supertype
CREATE OR REPLACE TYPE person_tab_typ AS TABLE OF person_typ;
/
SELECT object_name, object_type
FROM user_objects;
SELECT type_name, supertype_owner, supertype_name
FROM user_types;
Create Subtype
CREATE OR REPLACE TYPE student_typ UNDER person_typ (
deptid NUMBER, major VARCHAR2(30))
NOT FINAL;
/
SELECT object_name, object_type
FROM user_objects;
SELECT type_name, supertype_owner, supertype_name
FROM user_types;
Create Type From Subtype
CREATE OR REPLACE TYPE student_tab_typ AS TABLE OF student_typ;
/
SELECT object_name, object_type
FROM user_objects;
SELECT type_name, supertype_owner, supertype_name
FROM user_types;
Create Table With Two Nested Tables
CREATE TABLE test (
regular_field DATE,
person_nested_tab person_tab_typ,
student_nested_tab student_tab_typ)
NESTED TABLE person_nested_tab STORE AS per_tab
NESTED TABLE student_nested_tab STORE AS stu_tab;
desc test
desc per_tab
desc stu_tab
Insert A Row
INSERT INTO test
VALUES
(SYSDATE, person_tab_typ(), student_tab_typ(student_typ(987654321, 'Kyte', 'PO Box 0', 101, 'Computer Science')));
INSERT INTO test
VALUES
(SYSDATE, person_tab_typ(), student_tab_typ(student_typ(987654321, 'Kyte', 'PO Box 0', 101, 'Computer Science'),
student_typ(12345, 'Starr', '123 Main St', 102, 'Agricultural Science')));
INSERT INTO test
VALUES
(TRUNC(SYSDATE), person_tab_typ(), student_tab_typ());
SELECT * FROM test;
INSERT INTO TABLE (
SELECT person_nested_tab
FROM test
WHERE regular_field = TRUNC(SYSDATE))
VALUES (111223456, 'Morgan', '123 Main Street');
SELECT * FROM test;
INSERT INTO TABLE (
SELECT student_nested_tab
FROM test
WHERE regular_field = TRUNC(SYSDATE))
VALUES (987654321, 'Kyte', 'PO Box 123', 101, 'Oracle Scientist');
INSERT INTO TABLE (
SELECT student_nested_tab
FROM test
WHERE regular_field = TRUNC(SYSDATE))
VALUES (987654321, 'Kyte', 'PO Box 123', 299, 'Advanced SQL and PL/SQL');
SELECT * FROM test;
set head off
SELECT t1.*, t2.*, t3.*
FROM test t1,
TABLE(person_nested_tab) t2,
TABLE(student_nested_tab) t3;
set head on
Type Demos
Set operations with Types
Thank you Jim Kennedy for this code
DECLARE
TYPE l_test_type IS TABLE OF VARCHAR2(10);
l_test1 l_test_type := l_test_type();
l_test2 l_test_type := l_test_type();
l_test_result l_test_type;
BEGIN
l_test1.EXTEND(3);
l_test1(1) := 'foo';
l_test1(2) := 'moo';
l_test1(3) := 'too';
dbms_output.put_line('===== Union Sets Keep Duplicates =====');
l_test_result := l_test1 MULTISET UNION l_test2;
FOR i IN 1 .. l_test_result.COUNT LOOP
dbms_output.put_line(TO_CHAR(i) || ' ' || l_test_result(i));
END LOOP;
dbms_output.put_line('========================================');
dbms_output.put_line('===== Union Sets Remove Duplicates =====');
l_test_result := l_test1 MULTISET UNION DISTINCT l_test2;
FOR i IN 1 .. l_test_result.COUNT LOOP
dbms_output.put_line(TO_CHAR(i) || ' ' || l_test_result(i));
END LOOP;
dbms_output.put_line('========================================');
END TEST_PROC;
/
Type declaration
set serveroutput on
DECLARE
TYPE demo IS TABLE OF VARCHAR2(32767);
x demo;
BEGIN
x := demo(RPAD('X', 32766, 'Z'));
dbms_output.put_line(x(1));
END;
/
Type and type body creation with member function
CREATE OR REPLACE TYPE data_typ AS OBJECT
(year NUMBER, MEMBER FUNCTION prod(invent NUMBER) RETURN NUMBER);
/
CREATE OR REPLACE TYPE BODY data_typ IS
MEMBER FUNCTION prod (invent NUMBER) RETURN NUMBER IS
BEGIN
RETURN (year + invent);
END;
END;
/
Type based on a type
CREATE OR REPLACE TYPE person_t AS OBJECT (name VARCHAR2(100), ssn NUMBER) NOT FINAL;
/
CREATE OR REPLACE TYPE employee_t UNDER person_t
(department_id NUMBER, salary NUMBER) NOT FINAL;
/
CREATE OR REPLACE TYPE part_time_emp_t UNDER employee_t(num_hrs NUMBER);
/
A user defined data type used by a function
CREATE OR REPLACE TYPE InStrTab
IS TABLE OF VARCHAR2(4000);
/
CREATE OR REPLACE FUNCTION contains_all (useridin dms_user.id%TYPE, stringin VARCHAR2, checkint INTEGER) RETURN INTEGER IS
i BINARY_INTEGER;
my_table dbms_utility.uncl_array;
stringary InStrTab;
BEGIN
-- convert stringin of domain ids into a PL/SQL table
dbms_utility.comma_to_table(stringin, i, my_table);
-- initialize a collection
stringary := InStrTab('');
-- extend the collection to the size of the PL/SQL table
stringary.EXTEND(my_table.COUNT);
-- for each element in the PL/SQL table
FOR j IN 1 .. my_table.COUNT LOOP
-- remove the double-quotes
my_table(j) := TRANSLATE(my_table(j), 'A"', 'A');
-- assign it to an element in the array
stringary(j) := my_table(j);
END LOOP;
-- check the count of array elements found in the user_domain_map table
SELECT COUNT(*)
INTO i
FROM zuser_domain_map
WHERE user_id = useridin
AND domain_id IN (
SELECT column_value
FROM TABLE(CAST(stringary AS InStrTab)));
-- compare the number found against the checksum
IF i >= checkint THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END contains_all;
/
-- how to test:
SELECT contains_all(121, '"200","201","207"',3)
FROM dual;