Re: Seeking Equivalent of InterBase Domain

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 23 Jan 2007 09:52:42 -0800
Message-ID: <1169574758.230266@bubbleator.drizzle.com>

Serge Rielau wrote:
> William Robertson wrote:

>> Jim Smith wrote:
>>> In message <1169531413.53689_at_bubbleator.drizzle.com>, DA Morgan
>>> <damorgan_at_psoug.org> writes
>>>> hasta_l3_at_hotmail.com wrote:
>>>>
>>>>> you have to repeat the type constraints for every column
>>>>> defined with that type.  It's a development nigthmare...
>>>>>  Monte, FWIW we are using SQL*Plus DEFINE ...
>>>>>  --- Raoul
>>>> Possibly but not necessarily. Types have type bodies that
>>>> contain methods. What is it that you would want to do, or
>>>> the OP wants to do, that could not be defined in a type
>>>> body.
>>>>
>>>> I'm not going to say that a method is or is not going to
>>>> do the job. But I'd sure like to see a substantive example
>>>> of what is being asked to see if it is or is not the case
>>>> before just agreeing that a domain can do something that
>>>> can not be done in Oracle.
>>> To use your own exanp
>>> SQL> CREATE OR REPLACE TYPE first_name AS OBJECT (
>>>    2  first_name VARCHAR2(25));
>>>    3  /
>>>
>>> Type created.
>>>
>>> SQL> create table names (
>>>    2   fname first_name);
>>>
>>> Table created.
>>>
>>> SQL> insert into names(fname) values ('adsfads');
>>> insert into names(fname) values ('adsfads')
>>>                                   *
>>> ERROR at line 1:
>>> ORA-00932: inconsistent datatypes: expected JIM.FIRST_NAME got CHAR
>>>
>>> The domain as described by the OP defines types which can be used like
>>> native types.
>>
>> I suppose you could work around the syntax using views and INSTEAD OF
>> triggers to fake a scalar datatype, but even so, unless I'm missing
>> something, one limitation of object types is that a user-defined
>> constructor must differ in signature from the system-defined one,
>> otherwise you get something like this:
>>
>> SQL> SELECT BOOL_DOM('Y') FROM dual;
>> SELECT BOOL_DOM('Y') FROM dual
>>        *
>> ERROR at line 1:
>> ORA-06553: PLS-307: too many declarations of 'BOOL_DOM' match this call
>>
>> Here I created a BOOL_DOM type with a constructor function that checked
>> whether the attribute self.truth is 'Y' or 'N'. You could add a dummy
>> attribute of course.
>>

> You would need to build a constructor for the structured type.
> The constructor will contain the constraint.
> Then is all boils down whether Oracle supports implicit casting for
> structured types.
> I.e. Can you just pass a string and it will automagically invoke the
> constructor?
> IF that hurdle is overcome the next problem is indexing.
> Instead of normal indexing I presume one would need to define indexes on
> the observer methods of the type.
> Very quickly this whole solution is far, far away from the vanilla plan
> the DBMS (any DBMS!) is tuned to cope with well.
> At the end of the day: Yes Oracle may be able to "get teh job done" but
> by the same token any job a DBMS does can be done with pen and paper. :-)
>
> Daniel, I have no problem accepting that DB2 can't do it (and we have
> the same OO features Oracle has). Given that you claim to be non biased
> what is so hard about accepting the fact that there exist features in
> other DBMSs your favorite doesn't have that are still useful? I'm sure
> if Oracle felt the need, they (just like us) could implement it.
> It ain't exactly innovative... just work.
>
> Cheers
> Serge

Oh I'm open to the fact that I may be incorrect. I am just looking for someone to explicitly state something that can not be done.

I'm not claiming a constructor or a method isn't required. Rather I looking for something I am precluded from doing for lack of them.

In fact anyone using Oracle Designer has full access to domains and has had that for years. I can remember defining them in Oracle Designer back in the early 90s.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue Jan 23 2007 - 11:52:42 CST