| 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$ |
DBA_COLL_TYPES |
TYPE$ |
| ALL_COLL_TYPES |
DBA_DEPENDENCIES |
USER_COLL_TYPES |
| ALL_DEPENDENCIES |
DBA_SOURCE |
USER_DEPENDENCIES |
| ALL_SOURCE |
DBA_TYPES |
USER_SOURCE |
| ALL_TYPES |
DBA_VARRAYS |
USER_TYPES |
| ALL_VARRAYS |
SOURCE$ |
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 <schema_name>.<type_name> FORCE
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 |
| 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 |
| 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, '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; |
