Re: Seeking Equivalent of InterBase Domain

From: William Robertson <williamr2019_at_googlemail.com>
Date: 23 Jan 2007 06:48:14 -0800
Message-ID: <1169563694.668858.327180@m58g2000cwm.googlegroups.com>

Serge Rielau wrote:
> You would need to build a constructor for the structured type.
> The constructor will contain the constraint.

Yes but 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. Why do I bother ;)

> 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 you used the above BOOL_DOM object type for a table column you would have to use constructor syntax to set its value:

INSERT INTO employees (is_director) VALUES (BOOL_DOM('Y'));

Note that in this example if BOOL_DOM has one character string attribute then the above constructor *has* to be the system-defined one, which means it *cannot* include any additional custom processing. You might get around this by adding a dummy attribute so that you could add a custom constructor with only one argument.

> 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.

I'm not sure what an observer method is but you would have to specify the object.attribute as with the column-level constraint suggested earlier:

SQL> CREATE TYPE name_ot AS OBJECT (name VARCHAR2(40));   2 /

Type created.

SQL> CREATE TABLE bananas (id INT PRIMARY KEY, name NAME_OT);

Table created.

SQL> CREATE INDEX banaans_ix ON bananas(name); CREATE INDEX banaans_ix ON bananas(name)

                                   *

ERROR at line 1:
ORA-02327: cannot create index on expression with datatype ADT

SQL> CREATE INDEX banaans_ix ON bananas(name.name);

Index created. Received on Tue Jan 23 2007 - 08:48:14 CST