SQL Power Software Forum

Hi,
I recently tried to build existing Oracle database structure in Power*Architect.

All tables with all relationships were imported into diagram correctly.

However, Power*Architect currently has problem with recognition of column PRECISION and SCALE

For example,

column VARCHAR2(100) is imported as VARCHAR2(400)
column TIMESTAMP(9) is imported as TIMESTAMP(11, 9)

More over,

when dialog "Column Properties" is opened for column with type VARCHAR2 the column type is displayed as VARCHAR and column precision is wrong there too(VARCHAR2(100) is displayed as VARCHAR(400)

It seems like a bug for me.

I used Oracle 10g express edition.
Oracle JDBC driver version is 10.2.0.1

Did I configure something incorrectly?

Any help appreciated...
Cheers

This message was edited 1 time. Last update was at 2008-01-07 11:40:35

Hi alexr,

I've tried reproducing the issues you've described on the latest code base.

I could not reproduce the issue with VARCHAR2. I imported a table with VARCHAR2(100) and it came back as VARCHAR2(100). There may be some configuration that I am overlooking.

As for the TIMESTAMP issue, I ran into the same issue, but also another one as well. TIMESTAMP(9) became reverse engineered as TIMESTAMP(9)(11,9). The problem here is worse because the data type is not set to TIMESTAMP, but to 'Other'.

I used DbVisualizer to examine the table's column definitions, and I noticed Oracle sets the datatype to TIMESTAMP(n) (where n is the precision you entered when you created the table) instead of just TIMESTAMP. Additionally, I noticed that 'column_size' was set to 11 and 'decimal_digits' was set to 9, which would explain where the 11 and 9 are coming from.

I will look into getting this fixed.

By the way, what version of Power*Architect are you using?

I used Power*Architect of version 0.9.9 and version 0.9.10-alpha.

In both version the "Column Properties" dialog for VARCHAR2 columns displays column type as VARCHAR .

There is no VARCHAR2 in the list of type.

I guess that type list displays only JDBC types and does not give any Oracle custom types.

Perhaps, it would be great if you can add functionality to import DB custom types like VARCHAR2 etc.

Concerning my VARCHAR2 column I guess, that jdbc driver gives the size in bytes.

I looked into table definition again and found that column is declared as VARCHAR2(100

Database is configured to use char semantic for columns:
(alter system set nls_length_semantics='char' scope=both;)

So, my column is actually declared as VARCHAR2(100 CHAR) in this case.

The encoding of the database is UTF8.

I am guessing that Oracle char in UTF8 encoding occupies 4bytes.

So, jdbc metadata returned correct size in bytes.

I am curious is it possible in Power*Architect to use char in columns definition for Oracle instead of bytes?

Perhaps it depends from Oracle jdbc drivers...

Hi!

You've brought up several points here, so I'll address them one at a time.

About VARCHAR versus VARCHAR2: The official Oracle documentation since at least version 8i has said the same thing:

The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype. Oracle recommends that you use VARCHAR2 rather than VARCHAR. In future releases, VARCHAR might be defined as a separate datatype used for variable-length character strings compared with different comparison semantics.

Additionally, one of the Architect's distinguishing features as a data modeling tool is its platform neutrality. Your data model itself is always database platform-independent. At any time, you can choose to forward-engineer it to any of the supported database types. Hence (as you said yourself) the data types in your model are the SQL standard type names, not platform-specific names.

Based on those two points, I think the Architect's behaviour is correct: forward engineer VARCHAR columns in Oracle as VARCHAR2. When you pick the Oracle DDL generator, you will never get VARCHAR (without the 2) columns.

When and if Oracle produces a release where VARCHAR semantics differ from those of VARCHAR2, we will consider adding some sort of flag for VARCHAR columns so you can decide which to use.

About reverse engineering coming up with the wrong column size: This does sound like a bytes-vs-characters problem, doesn't it? You can't really say how many bytes it takes to represent a character in UTF-8. It's a variable-length encoding scheme. Each character will take up at least one byte, and at most (according to Wikipedia) 4 bytes. It sounds like Oracle's JDBC driver is reporting the "worst-case scenario" for the amount of storage the column requires.

I agree with you: the Architect's default behaviour should be to specify the number of characters allowed in a field as opposed to the storage size in bytes. A quick fix would be to always declare character-type columns using the TYPE(n CHAR) notation. On reverse engineering, we can tweak the values Oracle gives us to represent the number of characters instead of bytes. Such a tweak might be difficult to make 100% reliable, but hopefully we'll be able to come up with something that reliably covers the common cases.

I've entered this into our bug tracking database. It's split into bugs 1451 and 1452 (for reverse engineering and forward engineering respectively).

-Jonathan

Cheers for detailed answers and raising the bugs.

I am looking forward for next release.