Re: Seeking Equivalent of InterBase Domain

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Tue, 23 Jan 2007 08:28:04 -0500
Message-ID: <51mgr5F1l1mi1U1@mid.individual.net>

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

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Received on Tue Jan 23 2007 - 07:28:04 CST