Most of the tables in our system (over 90%) do not have Metadata describing the foreign keys.
When you run the Foreign key discovery, it only finds foreign keys from the Metadata.
We have several table that keep the foreign key information about the rest fo the tables.
Is there an automated way that we can set the Foreign key and primary key information on these tables?
We can write code to do this if there is a way that something we write will add the foreign key information to the datamodeler.
The problem description is not quite clear for me.
From what I understand is you want to add additional information to a Data Modeler design. This information is kept in some tables in a database.
What you can do is write a java script which creates the pk and fk on tables in DM. You can several examples on java script in this forum.
Once you have the java script for one pk/fk, you can generate a java script for all pk/fk's based on the database tables containing this information.
I have been looking for documentation and examples on how to write the Java script that may be able to import our information.
However, I haven't been able to find documentation on the subject.
Is there any place that is known that has samples of scripts, and documentation on the Oracle Data Modeler API?
You can find the API in the installation directory of Data Modeler in datamodeler\datamodeler\xmlmetadata\doc\index.html.
On the Data Modeler forum you can search for 'transformation script' and you will find several discussions on this topic.
There is also a tutorail on: http://download.oracle.com/otn_hosted_doc/sqldev/UserDefinedDesignRules/UserDefinedDesignRules.html
I hope this enough to start with.
Data Modeler also provides Discover Foreign Keys functionality. You can call it by right-clicking on the node for the Relational Model in the Browser tree, and selecting Discover Foreign Keys from the drop-down menu.
This can be used to find hidden foreign key relationships in the model, based on column names, and create the relevant Foreign Key. There's more detail on the discovery options in the Help for the "Create discovered foreign keys" panel.
I can see that the Discover Foreign keys may be able to find foreign keys if you select a column name.
However, with over 3000 tables, this becomes a cumbersom proctice.
We already have the Foreign key data, but it is in a form that the data modeler can't recognise.
Thus writing a script that would pul in the information and set it on the display seems the most likely of ways to do this.
You shouldn't call from java script a procedure which returns data.
So for looping through all fk's, you fill out each time the template. The filled out templates you send to a file, and this file you process in Data Modeler using the transformation tool.
How then do I get the FK information (child table name, child column name) etc, when that information is in a seperate SQL table that is accesed through a stored procedure.
I cannot fill in the FK or PK fields without accessing the database,
A Java program may be exactly what we need.
Our other option is to write an external program that would update the metadata, however we may not want to do that (changing customer databases may not be desired).
Is there any documentation on making a filter script (as an example) that is a Java executible?
I also have not found anything in the Oraacle documentation that mentions how to implement a Java app in the Data Modeler.
My email is firstname.lastname@example.org