Re: Seeking Equivalent of InterBase Domain

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. Received on Tue Jan 23 2007 - 05:21:50 CST