Oracle Types and Subtypes
Version 20c

General Information
Library Note Morgan's Library Page Header
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;

desc user_types

col typecode format a10

SELECT type_name, typecode, attributes, methods, predefined, incomplete,
final, instantiable
FROM user_types;

-- examine type definition
col text format a60

SELECT *
FROM user_source
WHERE name = 'SSN_T';

CREATE TABLE ssn (
per_id NUMBER(10),
per_ssn ssn_t);

desc ssn

-- examine table columns
col data_type format a30

SELECT column_name, data_type
FROM user_tab_cols
WHERE table_name = 'SSN';

INSERT INTO ssn VALUES (1, '123-45-6789');

set describe depth all linenum on indent on

desc ssn

INSERT INTO ssn VALUES (1, ssn_t('123-45-6789'));

SELECT * FROM ssn;

UPDATE ssn
SET per_ssn = ssn_t('111-22-3333');

SELECT * FROM ssn;

INSERT INTO ssn (per_id, per_ssn) VALUES (1, ssn_t('999-88-7777'));

SELECT * FROM ssn;

UPDATE ssn
SET per_ssn = ssn_t('456-56-0841')
WHERE per_ssn = ssn_t('111-22-3333');

SELECT * FROM ssn;

DROP TYPE ssn_t;

DROP TABLE ssn PURGE;

DROP TYPE ssn_t;
Multicolumn Object Declaration CREATE OR REPLACE TYPE <type_name> AUTHID <CURRENT USER | DEFINER> AS OBJECT (
<column_name> <data_type>,
...,
<column_name> <data_type>);
/
CREATE OR REPLACE TYPE phone_t AUTHID CURRENT USER AS OBJECT (
a_code   CHAR(3),
p_number CHAR(8));
/

SELECT type_name, typecode, attributes, methods, predefined, incomplete,
final, instantiable
FROM user_types;

-- examine type definition
col text format a40

SELECT *
FROM user_source
WHERE name = 'PHONE_T';

CREATE TABLE phone (
per_id    NUMBER(10),
per_phone phone_t);

desc phone

set describe depth all

desc phone

set describe depth all linenum on indent on

desc phone

-- examine table columns
col data_type format a15
col data_type_owner format a15

SELECT column_name, data_type, data_type_mod, data_type_owner
FROM user_tab_cols
WHERE table_name = 'PHONE';

INSERT INTO phone
(per_id, per_phone)
VALUES
(1, phone_t('206', '555-1212'));

INSERT INTO phone
(per_id, per_phone)
VALUES
(2, phone_t('212', '123-4567'));

COMMIT;

SELECT * FROM phone;

col per_phone format a30

SELECT per_id, PER_PHONE
FROM phone;

SELECT per_id, per_phone
FROM phone;

-- selective select
SELECT *
FROM phone p
WHERE p.per_phone.a_code = '206';

SELECT p.per_phone.p_number
FROM phone p
WHERE p.per_phone.a_code = '206';

-- selective update
UPDATE phone p
SET p.per_id = 9
WHERE p.per_id = 1;

SELECT * FROM phone;

UPDATE phone p
SET p.per_phone.a_code = '303'
WHERE p.per_phone.a_code = '206';

SELECT * FROM phone;

COMMIT;

-- selective delete
DELETE FROM phone p
WHERE p.per_id = 2;

SELECT * FROM phone;

ROLLBACK;

SELECT * FROM phone;

DELETE FROM phone p
WHERE p.per_phone.a_code = '303';

SELECT * FROM phone;
 
Create Subtype
Scalar Subtypes Declared in a Package Header SUBTYPE <type_name> IS <data_type>
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;
/

SELECT type_name, typecode, attributes, methods, predefined, incomplete,
final, instantiable
FROM user_types;

-- 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;
/

SELECT type_name, typecode, attributes, methods, predefined, incomplete,
final, instantiable
FROM user_types;

-- derive collection type from subtype
CREATE OR REPLACE TYPE student_tab_typ AS TABLE OF student_typ;
/

SELECT type_name, typecode, attributes, methods, predefined, incomplete,
final, instantiable
FROM user_types;

-- 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';

  l_test2.EXTEND(2);
  l_test2(1) := 'foo';
  l_test2(2) := 'woo';

  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;

Related Topics
Accessible By Clause
Collections
Nested Tables
Object Tables
REF Cursors
VArrays
What's New In 21c
What's New In 23c

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