Re: user defined data type: does they exist ?
Date: Sat, 26 Jan 2008 19:00:05 -0600
Message-ID: <DGQmj.9270$EZ3.3865@nlpi070.nbdc.sbc.com>
Mark D Powell wrote:
> On Jan 18, 5:39 am, alberto.rol..._at_gmail.com 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 --
Mark,
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:
SQL> CREATE DOMAIN ADDRESS_DATA2_DOM VARCHAR2(20) DEFAULT NULL; SQL> CREATE TABLE X (ADDRESS_2 ADDRESS_DATA2_DOM); SQL> CREATE TABLE y (ADDRESS_1 ADDRESS_DATA2_DOM);
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