ER models to DDL script - problems

[Ramas]

We are facing problems when using Visio 2003 Enterprise Architect edition
with respect to the creation of DDL scripts.

We have defined an ORM Source Model where we have used USER DEFINED DATA
TYPES. The TIMESTAMP data type has also been used.

We are able to convert the ORM Source Model to an ER model without any
problems.

But, when generating the DDL scripts from the ER model, by connecting to an
Oracle DSN using the Oracle driver, we find that the USER DEFINED DATA TYPES
are replaced by the underlying oracle database data-types. (For example a
user defined data-type by name dtAmount which is defined as numeric(38,0) is
being replaced by numeric(38,0) in the DDL script instead of referring to
dtAmount.

Also, the TIMESTAMP datatype is being replaced by the DATE datatype.

This works OK when connecting to a MS SQL Server DSN though.

How can we resolve these two issues, as the option of not using UDTs and the
TIMESTAMP data-type is not a solution we can pursue.

 

[Chang Oh]

I do not believe that Oracle supports UDT in such a way that SQL Server
does. As such, it is doing what it's supposed to do. If Oracle does
support UDT, can you tell me the syntax to create a UDT and a way for
referencing such a UDT from create table statement? Also, Oracle has a
different way to define a new type based on existing type, and to be able to
use it, you have to use Object Relational feature.

Chang Oh
Visual Studio Team Architect.

 

[Ramas]

You are right, and the nearest equivalent of the UDT in Oracle would be to
use an abstract user defined type. A sample DDL script would have been as
follows.

CREATE TYPE emp_t AS OBJECT (emp_type CHAR(11));
CREATE TABLE emp
(
per_id NUMBER(10),
per_emp emp_t
);

So I suppose that Visio cannot perform this conversion as the concept of a
User Defined Type in Oracle is fundamentally different from the
interpretation of an UDT in Visio or SQL Server.

Do you have any inputs on the other question viz. use of the Oracle
TIMESTAMP datatype for modeling in Visio ? Is this supported?

 

[Chang Oh]

In ORM source model, you can use the Type mapping feature to create Oracle
abstract types.

Chang Oh
Visual Studio Team System