Re: user defined data type: does they exist ?
Date: Mon, 28 Jan 2008 12:48:31 -0800 (PST)
On Jan 26, 8:00 pm, Michael Austin <maus..._at_firstdbasource.com> wrote:
> 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 --
> 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
> 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.- Hide quoted text -
> - Show quoted text -
Michael, yes they can but the point of my reply was to state that such definitions do not belong being used as column definitions in permanent tables. If the type is varchar2(20) just use varchar2(20).
Adhoc query tool, ODBC, etc... just do not deal well with stored objects in realtional databases.
HTH -- Mark D Powell -- Received on Mon Jan 28 2008 - 14:48:31 CST