Why is oracle spewing bad table metadata?

Question

I'm using DBVisualizer to extract DDL from an Oracle 10.2 DB. I'm getting odd instances of repeated columns in constraints, or repeated constraints in the generated DDL. At first I chalked it up to a bug in DBVisualizer, but I tried using Apache DDLUtils against the DB and it started throwing errors which investigation revealed to be caused by the same problem. The table metadata being returned by Oracle appears to have multiple entries for some FK constraints.

I can find no reference to this sort of thing from my google searches and I was wondering if anyone else had seen the same thing. Is this a bug in the Oracle driver, or does the metadata contain extra information which is being dropped when my tools access it, resulting in confusion on the part of the tools...

Here is an example (truncated) DDL output from

CREATE TABLE ARTIST
(
    ID INTEGER NOT NULL,
    FIRST_NAME VARCHAR2( 128 ),
    LAST_NAME VARCHAR2( 128 ),
    CONSTRAINT ARTIST_ID_PK PRIMARY KEY( ID ),
    CONSTRAINT ARTIST_CONTENT_ID_FK FOREIGN KEY( ID, ID, ID ) REFERENCES CMS_CONTENT( CONTENT_ID, CONTENT_ID, CONTENT_ID )
    -- note the multiple instances of ID and CONTENT_ID in the above line
    -- rest assured there is nothing bizarre about the foreign table CMS_CONTENT
)

I'm attempting to find a Java example which can show the behaviour, and will update the question when I have a concrete example.

Answer 1

You can try the built-in Oracle DBMS_METADATA.GET_DDL('TABLE','ARTIST') and see if that resolves the issue (ie whether it is a bug in the tools or the DB).

You can look at the data_dictionary tables too. In this case, ALL_CONSTRAINTS and ALL_CONS_COLUMNS.

select ac.owner, ac.constraint_name, ac.table_name, ac.r_owner, ac.r_constraint_name,
       acc.column_name, acc.position
from all_constraints ac join all_cons_columns acc on
 (ac.owner = acc.owner and ac.constraint_name = acc.constraint_name)
where ac.table_name = 'ARTIST'
and ac.constraint_type = 'R'

I'd suspect that it is a bug in the tools, and they've missed a join on the owning schema and you are picking up the same table/constraint but in another user's schema.

Answer 2

As far as I can see, dbvis (6.5.7) uses own code when you use the 'DDL' tab and it uses dbms_metadata when using the tab 'DDL with Storage'. Does this make a difference for you ?

Ronald