Oracle Types User Defined


	CODE Oracle Code Library


	JOBS Find Or Post Oracle Jobs


	FORUM Oracle Discussion & Chat

Search the Reference Library pages:

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Types

Version 11.1

General Information

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$	source$	type$
dba_coll_types	all_coll_types	user_coll_types
dba_dependencies	all_dependencies	user_dependencies
dba_source	all_source	user_source
dba_types	all_types	user_types
dba_varrays	all_varrays	user_varrays

System Privileges Related To Types

ALTER ANY TYPE	DROP ANY TYPE
CREATE ANY TYPE	EXECUTE ANY TYPE
CREATE TYPE	UNDER ANY TYPE

Create Type Header

Single Column Object Declaration

CREATE OR REPLACE TYPE <type_name>
AUTHID <CURRENT USER | DEFINER>
AS OBJECT (
<attribute> <attribute data_type>,
<inheritance clause>,
<subprogram spec>)
<FINAL | NOT FINAL> <INSTANTIABLE | NOT INSTANTIABLE>;
/

CREATE OR REPLACE TYPE ssn_t 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> AS OBJECT (
<column_name> <data_type>,
...,
<column_name> <data_type>);
/

CREATE OR REPLACE TYPE phone_t 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

Subtype Creation

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

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 ('Cline', 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 ('Cline', 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

Create Supertype

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, 'Cline', 'PO Box 0', 101, 'Computer Science')));

INSERT INTO test
VALUES
(SYSDATE, person_tab_typ(), student_tab_typ(student_typ(987654321, 'Cline', 'PO Box 0', 101, 'Computer Science'),
student_typ(12345, 'Starr', '123 Main St', 102, 'Agricultureal 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, 'Cline', 'PO Box 123', 101, 'Frontal Lobotomy Can Be Fun');

INSERT INTO TABLE (
SELECT student_nested_tab
FROM test
WHERE regular_field = TRUNC(SYSDATE))
VALUES (987654321, 'Cline', 'PO Box 123', 299, 'Advanced Basket Weaving');

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

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

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 agains 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

Home : Code Library : Sponsors : Privacy : Terms of Use : Contact Us