Monday, November 10, 2014

Use Domains in SQL Developer Data Modeler

How long is an email address? 30 characters should be enough, I thought. And I was right for about three weeks or so until one of the smartest Oracle experts in Europe tried to submit an abstract at our call for paper site. His email address had 32 chars and therefore failed already at registration. A bit embarrassing.

The data model was created in SQL Developer Data Modeler (SDDM) and I had done something right. In the data model EMAIL was defined as a domain and not simply as VARCHAR2(30) in different places in the data model.

What I needed to do was to update the properties of this domain. Before you change a domain  you may want to know where the domain has been used so you get an idea of the consequences of the change. Just go to the Browser and expand the stuff under Domains, and by right clicking on the domain you select Properties and in the window that pops up you can see where the domain has been used by clicking on Used In:

You can change the definition of the domain in one place, of course, under Tools -> Domain Administration:

Simply select the domain, you have to click on the Modify button before you can update the Domain Properties on the right. 

The fix in the schema was to perform a few ALTER TABLE statements on the involved tables followed by some statements to compile objects that became INVALID afterwards.