Re: Seeking Equivalent of InterBase Domain

In message <1169574758.230266_at_bubbleator.drizzle.com>, DA Morgan <damorgan_at_psoug.org> writes
>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.

Oh give it up. Providing domains in a case tool is entirely different from providing them in the dbms.

-- 
Jim Smith
Ponder Stibbons Limited <http://oracleandting.blogspot.com/>
RSS <http://oracleandting.blogspot.com/atom.xml>