There are tons of properties you can set in your design objects.

In fact, I discovered a ‘new’ one this week thanks to my friend David.

Display as…what’s he talking about?

Oh, this.

Looks perfect for doing APEX design work, yes?
Looks perfect for doing APEX design work, yes?

But what if the set of values we give you for a table or column property aren’t enough? What if you want to document something else?

Enter the User Defined Properties. Note: this is new for version 4.1

Right-click on your design and open your design properties.

Click the + button to get started.
Click the + button to get started.

And now we can define our custom properties for our design.

Note I have 'Column' selected below.
Note I have ‘Column’ selected below.

I went with a custom list here. But I could have also went with simpler inputs for the property, namely:

  • text
  • numbers
  • dates

Hit save.

Open your table properties, go to the Columns page.

Ta-da!
Ta-da!

So that’s for columns. But I can set custom properties for tables as well.

Displaying Custom Table Properties in your Diagrams

Just enable it.

Note I set the display name to 'Why' for the 'DEMO' property.
Note I set the display name to ‘Why’ for the ‘DEMO’ property.

So I just showed you this for tables and columns, but you have a lot more flexibility than just that.

If you assign a color, the property text in the diagram will display in that color.
If you assign a color, the property text in the diagram will display in that color.

Search too!

So once you set these, you can search on them as well.

Advanced Search, and add the property to your search parameters.

You can also hit the properties button and change the user defined property value right there in the search results dialog.
You can also hit the properties button and change the user defined property value right there in the search results dialog.

And of course, you’ll want this new user defined property in your reports. Just create a custom report template and add your property.

This is a search results report, but you can add these to your regular reports as well.
This is a search results report, but you can add these to your regular reports as well.
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.

9 Comments

  1. Neetesh Lodhi Reply

    Hi Jeff,

    Thanks for the great article.

    We can set the dynamic properties using below commands-
    table.setProperty(prop_name, prop_value)
    table.getProperty(prop_name)

    Could you please suggest any method to set/get User defined properties?

    Thanks,
    Neetesh

  2. Nilangi Dalvi Reply

    Can the User defined properties be set on Views as well? I can see the UDP on tables but not on my views.

  3. Hi Jeff,

    Is it possible to get User Defined Properties and their values through the API to use them into Transformation scripts and User DDL Transformation scripts

    • From the developer:
      Every object has methods to get/set UDp/Dynamic properties:

      • table.setProperty(prop_name, prop_value)
      • table.getProperty(prop_name)

      That works for UDP and dynamic properties. In “Table template – uses column name” script there is an example of setting property:
      col.setProperty(p_name,column.getObjectID());

      For follow-ups, please consult the forums.

  4. Hi Jeff, I am trying to use the Naming Standard Templates for generating the name of the Surrogate Key Column. For this template when you look at the available variables (by clicking on on Add Variable), there is a variable {table singular} for which the description is ‘singularName’ dynamic/UD property of Table. I therefore created a User Defined Property names ‘singularName’ and populated this with the singularName for the entity/table. However when I engineer to a relational model, this singularName is not being used to generate the Surrogate Key Column name… Any idea why?

  5. Hi Jeff, thanks for this post. I am trying to generate a data dictionary with additional user defined properties and found this post very useful. However, the recipe here doesn’t seem to work on 17.2. Can you update the post with 17.2 version?

  6. Jim Campbell Reply

    How about a demo of using SQLcl instead of Sqlplus for running scripts after patching with OPatch.

Write A Comment