Skip to Main Content

SQL Developer Data Modeler

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Getting an "Integer" logical type to stick

dbompApr 9 2013 — edited Apr 9 2013
I created my identity-style primary keys in the logical model as logical type "Integer". On the relational model it's still "Integer", and for Oracle it created them as "number(38,0)" (or as SQL Developer tells me, "number(*,0)"). This is a good result, and I like it. My trouble is how to compare my model to a created database without it reporting that the data types are different and wanting to recreate the tables.

Maybe it's a problem with how I'm doing the comparison, so I'll spell out my steps:

1) File->Import->Data Dictionary
2) I pick my connection, and [Next >].
3) I pick my schema, click "Swap Target Model" to on, and leave the "Import To" as my relational model and "Oracle Database 11g" as the database. [Next >]
4) My tables are already clicked, so I leave them. [Next >]
5) It reports that I have changes in my tables, and there are three in the list:
a) The "Data Type" for the column in question in the model is "INTEGER", and the table is "NUMBER". It's checkmarked as a difference.
b) The "Data Type Kind" for the model is "Logical Type(Integer)", and the table is "Logical Type(NUMERIC)". (This one isn't checkmarked because I turned off the "Compare Option" for "Use 'Data Type Kind' Property".)
c) Under "Indexes", it wants "To Drop" my primary key (index). That's odd, because it still lists the primary key under "PK and UK Constraints". It's not really a problem and I just ignore it, but I wonder if it's related to the data type issue.

If I do "DDL Preview", it's (leaving out some of the storage and logging options):
ALTER TABLE CAM.ADDRESSES DROP CONSTRAINT ADDRESSES_PK CASCADE ;

DROP INDEX CAM.ADDRESSES_PK
;
DROP TRIGGER CAM.ADDRESSES_ROW_ID_TRG 
;
ALTER TABLE CAM.ADDRESSES RENAME TO bcp_ADDRESSES 
;
CREATE TABLE CAM.ADDRESSES 
    ( 
     ROW_ID INTEGER  NOT NULL , 
[...]
    );
INSERT INTO CAM.ADDRESSES 
    (ROW_ID ,[...] )
SELECT 
    ROW_ID , [...]
FROM 
    bcp_ADDRESSES 
;
CREATE UNIQUE INDEX CAM.ADDRESSES_PK ON CAM.ADDRESSES 
    ( 
     ROW_ID ASC 
    ) [...]
;

ALTER TABLE CAM.ADDRESSES 
    ADD CONSTRAINT ADDRESSES_PK PRIMARY KEY ( ROW_ID  ) 
    USING INDEX CAM.ADDRESSES_PK ;
So it looks like it'd create the column as an "INTEGER" again, so on the next attempt I'd be back where I started.

In the "Types Administration" I tried changing the logical type mapping to "number(38,0)" and then "number(38)", but that didn't help. I didn't write down how it reported the difference -- I think it was that "number(38)" was different from "number". If I changed the type mapping to "number", then it stopped reporting the data type as a difference -- but then it wanted to create the columns as "number", which has a scale, and I'd rather not have one.

Any suggestions on how to get it to leave this alone?

(Version 3.3.0.747. I think that when I was running 3.1.4, it just wanted to
alter column addresses modify (row_id integer)
, which was annoying but I could just delete all of those lines. Now that it wants to recreate the table altogether, it's harder to work around. But maybe the table recreation is due to the index business, which it didn't bother me about before.)
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 7 2013
Added on Apr 9 2013
2 comments
614 views