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 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
Collections
Nested Tables
REF Cursors
VArrays
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [203 users online]    © 2010 psoug.org