In Oracle SQL Developer Data Modeler, you can create a table in your relational design that stores columns that you want to be added to all of your other tables.

Call it, ‘table_template.’

Whatever columns I add to this particular table can be automatically added to all of my other tables.
Whatever columns I add to this particular table can be automatically added to all of my other tables.

Now call the transformation script.

Tools > Design Rules & Transformations > Transformations.

You can change the name of your template table to anything you want - just modify the highlighted bit of this script before you run the transformation.
You can change the name of your template table to anything you want – just modify the highlighted bit of this script before you run the transformation.

Before

My tables without the columns I want to add via the transformation script.
My tables without the columns I want to add via the transformation script.

After

Notice the script was smart enough to not add the columns to the original template table.
Notice the script was smart enough to not add the columns to the original template table.

These are cool, but they don’t belong in my Logical model!

So let’s say you started with a Relational Model, and you have decided to engineer a Logical one. These template columns may not make any sense in your Logical model.

How do I get rid of them?

These attributes make no sense for my entities - get 'em out of here!
These attributes make no sense for my entities – get ’em out of here!

With the help of a friend, I have this awesome transformation script for you 🙂

By friend I mean, Philip, and by ‘help’ I mean – wrote the script 100% for me.

var t_name = "table_template";
var p_name = "ctemplateID";
template = model.getTableSet().getByName(t_name);
if (template != null) {
    tcolumns = template.getElements();
    tables = model.getTableSet().toArray();
    for (var t = 0; t < tables.length; t++) {
        table = tables[t];
        // compare name ignoring the case
        if (!table.getName().equalsIgnoreCase(t_name)) {
            for (var i = 0; i < tcolumns.length; i++) {
                column = tcolumns[i];
                col = table.getColumnByProperty(p_name, column.getObjectID());
                if (col != null) {
                    attr = col.getEngAttribute();
                    if (attr != null) {
                        ent = attr.getEntity();
                        attr.remove();
                        if (ent != null) {
                            ent.setDirty(true);
                        }
                    }
                    // col.remove();
                    table.setDirty(true);
                }
            }
        }
    }
}

There’s an important line in there.

// col.remove();

I added the comment. If you run this script without that line commented out, you’ll lose your attributes AND your template columns. I just wanted to remove the attributes so I commented out the bit for deleting the columns. You can use this script to JUST remove the columns too, just comment out the logical/attribute bits.

When you add this new script, you need to add it as a ‘relational’ object – it needs access to the table names from the relational model as it traverses the logical.

After running our script –

The attributes derived from the template columns  have been removed.
The attributes derived from the template columns have been removed.

Philip liked this example, so he’s included it as a ‘stock’ template in the 4.1 install and docs.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

14 Comments

  1. Kiril Bojiloff Reply

    The example good, but is about applying the common columns on all tables in the model, if they dont have the common columns. But how can I apply to only few of them or just only one? Or the other way around: Imagine there is already an existing model and I create a new table. Into this new table I want to insert the common columns. Is there a way to do that from within the popup window only for this table?

    • Definitely not via pop-up, no. But between this feature and Domains, you can go pretty far. Especially domains.

    • Kiril Bojiloff

      In fact what is missing is the ability to choose for which tables should the common columns be applied, instead of applying for all, like it is now. would be possible in the script to implement a prompt for which tables sholud be applied?

  2. Shaounak Nasikkar Reply

    Is there any documentation on the usage of the Oracle Nashorn or Mozilla Rhino in the Oracle SQL Developer Data Modeler? I am new to this so I may not be using the right terms but the customization in the DDL and the rules I liked it really. I wanted to get the person name who has modified the data model and include that information in the DDL Changelog.

    https://imgur.com/fDQz4pE

  3. Thanks Jeff, how can we take this to the next level and copy indexes, relationships, and constraints as well?

    That way columns like created_by and modified_by could include foreign key constraints to a USERS table, or range columns could include a start <= end constraint, etc.

    • There’s a DDL transformation script feature that will do EXACTLY what you want. Tools > Design Rules > Table DDL Transformations. Look at the Journal Tables example – that will show you how to reference the object names and create new objects based on them. Then, when you generate your DDL, there’s an option to have those scripts fire on selected objects.

    • I don’t appear to have that sample script or any other sample Table DDL transformations for that matter. I’m running SQL Dev 4.1.2.20 using the embedded data modeler instead of the standalone version.

      Where else can I find these?

  4. Interesting article Jeff. I’ve been using a template table to add common columns to tables that are forward engineered from the logical model.

    Can you tell me if there is a way so that intersection tables that are generated to resolve many to many relationships can inherit the common columns from the template table?

  5. Hi,

    I’m struggling a little with the scripts – what I want to do is remove all the column names in my relational model ,where the “_” character appears with so table_id would become TableId is there a property for this ?

  6. Greg Snider Reply

    in your first screenshot, you have the Oracle Nashorn library loaded. How do we do that to replace the Rhino library?

Write A Comment