Re: user defined data type: does they exist ?

From: Michael Austin <>
Date: Sat, 26 Jan 2008 19:00:05 -0600
Message-ID: <DGQmj.9270$>

Mark D Powell wrote:
> On Jan 18, 5:39 am, wrote:

>>> SQL Manual entry CREATE TYPE
>>> PL/SQL User's Guide and Reference  -- contains many examples of using
>>> object types
>> hello, thanks. I've already tried it, but it seems to me that it can
>> "only" create complex types, something like struct in C++.
>> And to use them, I have to use a "constructor" in the insert
>> statement.
>> I need to be able to do only something like
>> insert into table AA (column1) values ('this is a string with maximum
>> 50 chars')
>> and not something like
>> insert into table AA (column1) values (CODE('this is a string with
>> maximum 50 chars'))
>> Maybe I missed something on the create type syntax ?
>> thank you very much!

> If you object type consists of a single varchar2 value why do you need
> an object at all?
> My professional opinion based on experience and common sense is that
> objects do not belong stored in a relational database but should be
> created and manipulated in the application layer.
> Adhoc query tools and end users do not relate to stored objects.
> You can however sometimes use objects to advantage in PL/SQL code that
> manipulates data.
> HTH -- Mark D Powell --


I believe the TYPE could also represent objects with a single definition:

CREATE TYPE demo_typ1 AS OBJECT (a1 VARCHAR2(20));

CREATE TABLE demo_tab1 (b1 NUMBER, b2 demo_typ1);

This is more analogous to the Oracle Rdb (formerly DEC Rdb) DOMAIN. You can create a domain of a specified name, size and default values... for example:


And columns ADDRESS_1 and ADDRESS_2 will assume the characteristics and default value from domain address_data2_dom.

Any where you need a 20 character data type you can use this domain. Cool thing is that when you then alter the domain to be - say varchar2(30) - any where you have used this domain, the column automatically gets modified - so you don't have to find all the places where you used it and alter that table. Received on Sat Jan 26 2008 - 19:00:05 CST

Original text of this message