Adding DBMS DataTypes (Oracle 9.2)

[purplepangolin]

Hello,

I have a table in my database (Oracle 9.2) that uses a column whose type is a varray of Varchar2 (see DDL statements below).  When I import the schema in EA 6.1.788 the array column is imported as a varchar column of length 255.  There are also other differences in the generated DDL as all colums appear as Varchar(255).

Can anyone please explain:
1)Why the distributor and CONSUMPTION_DATE columns are interpreted as varchar(255)?
2)Whether it is possible to add complex datatypes such as Varrays,PL/SQL tables or objects?

Thanks

Alastair Green


--Original DDL
create or replace type band_array_t as varray(48) of varchar2(10) NOT NULL;
/

create table GSP_SCHEDULE
(
 DISTRIBUTOR      VARCHAR2(30) not null,
 CONSUMPTION_DATE DATE not null,
 TIME_BANDS       BAND_ARRAY_T not null
)
/

--Generated DDL
CREATE TABLE GSP_SCHEDULE (
DISTRIBUTOR varchar(255) NOT NULL,
CONSUMPTION_DATE varchar(255) NOT NULL,
TIME_BANDS varchar(255) NOT NULL
)
;

[«Midnight»]

There has been some recent discussion in this forum of database domains (in the sense of user-defined types). Bottom line seems to be that EA does not support modelling of domains (at least not yet). This would almost certainly extend to Oracle array types and similar.

Some of the forum posters have proposed innovative solutions to the domain limitations. You may want to search this forum for "domain" and see what's there. Perhaps there is enough to get your creative juices flowing and solve this problem. Remember to post your results so others may benefit.

You may also want to post this as a suggestion, once you have a clear idea of just how far EA goes at this point.

[purplepangolin]

Thanks,

I'll have a look.

Alastair

[thomaskilian]

Look here

[purplepangolin]

Thanks for the link.  I am working through the steps in the example and have a further question about step 3.  The post suggests using the ANSI data types as the permissible values for the DataType Tag item.  I am not sure whether it is possible to enforce this in the definition of the Tag:

Type=Enum;
Type=List;
List=<AnsiTypesList>

Can you only access the predefined reference data as a list type or is it possible to extend this?

Maybe the poster just means that this should be done by typing in the appropriate value?

I will carry on using the latter approach for now and post my results.

Thanks for your help so far.  Nice to see that the forums are pretty active.

[thomaskilian]

See the help for User Defined Tagged Values. It gives good examples how to work that out - although you might have to search a bit (AFAIK the printed help is about 1000 pages).