7 Creating and Managing Custom Objects and Properties

Using OMB*Plus Scripts to Specify UDOs and UDPs

OMB*Plus scripts enable you to define new objects, add and remove properties, as well as view the attributes for existing objects. The syntax is case sensitive and must be in upper case. While creating UDOs and UDPs, follow the guidelines in "Naming Conventions for UDOs and UDPs".

OMBDEFINE

OMBDEFINE CLASS_DEFINITION enables you to create new object definitions in the workspace.

To create a new module definition, use the following command:

OMBDEFINE MODULE CLASS_DEFINITION 'UD_TABLEMODULE' SET PROPERTIES 
   (BUSINESS_NAME, PLURAL_NAME) VALUES ('Table Module', 'Table Modules')

This creates a new module definition called UD_TABLEMODULE.

OMBREDEFINE

OMBREDEFINE CLASS_DEFINITION enables you to redefine a UDO.

To create a UDP on the Dimension object, use the following command:

OMBREDEFINE CLASS_DEFINITION 'DIMENSION' 
     ADD PROPERTY_DEFINITION 'UD_DOCID' SET PROPERTIES (TYPE, DEFAULT_VALUE)
     VALUES ('INTEGER', '100')

This adds a new property definition called UD_DOCID to class definition DIMENSION.

The following command adds a new property definition for notes for the COLUMN type. Because columns exist in tables, views, materialized view, external tables and sequences, the following command adds the definition of this property to columns for all of those metadata objects:

OMBREDEFINE CLASS_DEFINITION 'COLUMN' 
     ADD PROPERTY_DEFINITION 'UD_COL_NOTE' SET PROPERTIES (TYPE, DEFAULT_VALUE)
     VALUES ('STRING', 'notes')

When you create and save a new property definition, OMB*Plus performs the following validations:

• A user access check ensuring that you have single-user access to the current workspace.

• A name space check ensuring that you did not define two identically named property definitions within the same class hierarchy.

• A property value check ensuring that you defined default values consistent with the data types that you specified.

To change the name or the default value of a given property definition, use a command as follows:

OMBREDEFINE CLASS_DEFINITION 'TABLE' MODIFY PROPERTY_DEFINITION 'UD_TBL_NOTE'
     SET PROPERTIES (DEFAULT_VALUE, BUSINESS_NAME) 
     VALUES ('99', 'Table Note') 

To delete a UDP, use a command such as

OMBREDEFINE CLASS_DEFINITION 'TABLE' DELETE PROPERTY_DEFINITION 'UD_TBL_NOTE'

which deletes the UD_TBL_NOTE property definition from the Table class. Deleting a UDP is a destructive action and should generally be done with caution because it cannot be undone. It renders irretrievable all custom property values made for this property definition in your workspace.

OMBDESCRIBE

You can use OMBDESCRIBE on any class definition to view the attributes for a metadata element. Among other tasks, use OMBDESCRIBE to list all the property definitions including the UDPs for a given object type. For instance, the following command lists the UDPs for dimensions:

OMBDESCRIBE CLASS_DEFINITION 'DIMENSION' GET USER_DEFINED PROPERTY_DEFINITIONS

You can also use OMBDESCRIBE to inspect the properties of a property definition. For instance, for a UDP called UD_DOCID, you can discover its data type, default value, and business name using the following command:

OMBDESCRIBE CLASS_DEFINITION 'DIMENSION' PROPERTY_DEFINITION  'UD_DOCID' 
     GET PROPERTIES (TYPE, DEFAULT_VALUE, BUSINESS_NAME)

Naming Conventions for UDOs and UDPs

It is mandatory to include the prefix UD_ while naming UDOs and UDPs. This ensures that the names of UDOs and UDPs are not identical to the names of predefined objects in the workspace.

Creating UDPs: An Example

To create UDPs for an object, complete the following steps:

1. Log in to the client as an administrator.

2. Open the OMB*Plus view from the main menu of the Design Center. To do this, click View, and select OMB*Plus.

3. Ensure that you are in single user mode. You can verify this with the command OMBDISPLAYCURRENTMODE. If you are in multiple user mode, then switch to single user mode by using the command:OMBREDEFINE CLASS_DEFINITION 'VIEW

   OMBSWITCHMODE SINGLE_USER_MODE
   

4. In the OMB*Plus client, enter the following command to create four UDPs for the object View:

   • OMBREDEFINE CLASS_DEFINITION 'VIEW' \
     ADD PROPERTY_DEFINITION 'UD_OWNER' SET PROPERTIES \ 
     (TYPE, DEFAULT_VALUE, BUSINESS_NAME) VALUES \ 
     ('STRING', 'REP_OWNER', 'Object Owner')  
     

   • OMBREDEFINE CLASS_DEFINITION 'VIEW' \ 
     ADD PROPERTY_DEFINITION 'UD_FILE' SET PROPERTIES \ 
     (TYPE, DEFAULT_VALUE) VALUES ('FILE', 'C:\\vw.sql')
     

   • OMBREDEFINE CLASS_DEFINITION 'VIEW' \ 
     ADD PROPERTY_DEFINITION 'UD_LINK' SET PROPERTIES \ 
     (TYPE, DEFAULT_VALUE) VALUES ('URL', 'http://www.oracle.com')
     

   • OMBREDEFINE CLASS_DEFINITION 'VIEW' \ 
     ADD PROPERTY_DEFINITION 'UD_VERSION' SET PROPERTIES \ 
     (TYPE, DEFAULT_VALUE) VALUES ('DATE', '2006-1-7')
     

   • OMBSAVE
     

   This creates the following UDPs: UD_OWNER, UD_FILE, UD_LINK, and UD_VERSION.

   Note that the valid UDP types are: integer, string, float, double, date, timestamp, boolean, long, file, and url.

5. From the Projects Navigator create a view in any module.

6. Open the property inspector for the view. To do this, select the view and from the main menu, click View, and then select Property Inspector.

7. The user defined properties are listed on the property inspector.

   You can modify the values of any of the UDPs from the property inspector.

To remove a UDP from the workspace, use the DELETE clause. For example, to delete UD_VERSION, use the following command:

OMBREDEFINE CLASS_DEFINITION 'VIEW' DELETE PROPERTY_DEFINITION 'UD_VERSION' 

Writing Scripts to Define UDOs

To define a UDO, write a script that completes the following steps:

• Create a user defined module: This will be the parent module.

• Define the object type: Define the module to be a folder. This folder is an object that can hold other objects.

• Define FCOs and SCOs: Create user-defined FCOs and SCOs for the UDO, define the parent-child relationship between the FCOs and the SCOs, and assign physical names to these objects. For example, UD_WORKBOOK is a valid physical name. You can also indicate a business name and plural name, both of which are displayed in the Design Center and in editors. Continuing the previous example, Workbook and Workbooks are likely entries for the business name and plural name, respectively. If you do not specify these values, then they default to the physical name.

• Define object properties: Define the properties for all the objects you create. Some properties, such as Name, Business_Name, Plural_Name, and Description, are assigned automatically to any newly created object.

• Add component definition: All parent objects need to be assigned a component definition. The child elements have to be added to the component definition. The component definition determines the lifetime of child elements.For example, a column cannot exist if the parent table is deleted. Note that a component definition includes all the SCOs for the parent FCO, and all the SCOs owned by the first level SCOs and so on. If the child type is a FCO, then it must be added to the FOLDER_DEFINITION of the parent.

• Define association types: Create association types to indicate the types of relationships a UDO can have with workspace objects and other UDOs. You need to perform this step only if you want end users to later relate the UDO to specific instances of objects. For instance, in your script you could associate the UDO with tables and views. In the Design Center, end users could then relate instances of the UDO with specific tables and views. Warehouse Builder displays these relationships in impact and lineage analysis reports.

• Assign icons (optional): See "Creating New Icons for Workspace Objects".

• Save the changes.

Creating UDOs: An Example

This section provides an example to create UDOs modeled on a Java application. The Java application acts as a module. This module contains classes (FCOs), and those classes contain methods (SCOs). Within a method, you can model the lines of code. From a business standpoint, this is of interest because a particular line of code in an application may be impacted by a change in a database table if it is used within a SQL (JDBC) statement.

Figure 7-1 Structure of the UDOs

Description of "Figure 7-1 Structure of the UDOs"

To create the UDOs, perform the following steps:

1. Log in to the Warehouse Builder client as an administrator and open the OMB*Plus window. Make sure that you are logged in single user mode.

2. First create a module definition and set properties for this module:

   OMBDEFINE MODULE CLASS_DEFINITION 'UD_JAVA_APP' \ 
   SET PROPERTIES (BUSINESS_NAME, PLURAL_NAME) \ 
   VALUES ('Java Application', 'Java Applications')
   

   This defines the module definition and sets certain properties common to all objects. BUSINESS_NAME is the user-friendly name for an object. If the Naming mode preference for the Design Center is switched to Business mode, then the value set for BUSINESS_NAME is displayed for the object. PLURAL_NAME is the label that is used to show where multiple instances of an object are shown, such as the label used for a tree node in the Design Center that contains several instances of the object.

3. Now create a folder definition with the same name as the module so that the module assumes the role of a folder:

   OMBDEFINE FOLDER_DEFINITION 'UD_JAVA_APP'
   

4. Now create an FCO:

   OMBDEFINE FIRST_CLASS_OBJECT CLASS_DEFINITION \ 
   'UD_JCLASS' SET PROPERTIES (BUSINESS_NAME, PLURAL_NAME) \ 
   VALUES ('Java Class File', 'Java Class Files')
   

5. Add the FCO as a child of the folder class:

   OMBREDEFINE CLASS_DEFINITION 'UD_JAVA_APP' \ 
   ADD CHILD_TYPE 'UD_JCLASS'
   

6. Create a component definition for the FCO:

   OMBDEFINE COMPONENT_DEFINITION 'UD_JCLASS'
   

7. Add the component definition to the folder definition:

   OMBREDEFINE FOLDER_DEFINITION 'UD_JAVA_APP' \ 
   ADD 'UD_JCLASS'
   

8. Create an SCO and set its properties:

   OMBDEFINE SECOND_CLASS_OBJECT \ 
   CLASS_DEFINITION 'UD_JMETHOD' \ 
   SET PROPERTIES (BUSINESS_NAME, PLURAL_NAME) \ 
   VALUES ('Method', 'Methods')
   

9. Add the SCO as a child of the FCO:

   OMBREDEFINE CLASS_DEFINITION 'UD_JCLASS' \ 
   ADD CHILD_TYPE 'UD_JMETHOD'
   

10. Add the SCO to the component definition:

    OMBREDEFINE COMPONENT_DEFINITION 'UD_JCLASS' \ 
    ADD 'UD_JMETHOD' 
    

11. Create an SCO and set its properties:

    OMBDEFINE SECOND_CLASS_OBJECT \ 
    CLASS_DEFINITION 'UD_JMETHOD_LINE' \ 
    SET PROPERTIES (BUSINESS_NAME, PLURAL_NAME) \ 
    VALUES ('Java Method Line', 'Java Method Lines') 
    

12. Add this SCO as a child of the initially created SCO:

    OMBREDEFINE CLASS_DEFINITION 'UD_JMETHOD' \ 
    ADD CHILD_TYPE 'UD_JMETHOD_LINE' 
    

13. Add this SCO to the component definition:

    OMBREDEFINE COMPONENT_DEFINITION 'UD_JCLASS' \ 
    ADD 'UD_JMETHOD_LINE'
    

This creates the following UDOs:

• A module folder called UD_JAVA_APP

• An FCO named UD_JCLASS, within the module

• An SCO named UD_JMETHOD, which is the child of UD_JCLASS

• Another SCO named UD_JMETHOD_LINE, which is the child of UD_JMETHOD

You can access the UDOs from the Projects Navigator under the User Defined Modules icon. To create a new instance of the UDO, right-click the UDO and select New. You can create new modules and FCOs as well as edit these modules and FCOs.

Associating UDOs with Objects

UDOs can be associated with other objects. By creating these associations, UDOs become a part of Lineage and Impact Analysis diagram just like any other object.

Associating a Java Application with a Table

This example associates the SCO, UD_JMETHOD, with one or more tables. This is modeling the fact that a method could be referencing tables in JDBC calls.

To associate the Java method to table, use the command:

OMBDEFINE ASSOCIATION_DEFINITION 'UD_XJMETHOD2TABLE' \ 
SET PROPERTIES (CLASS_1,CLASS_2,ROLE_1,ROLE_2 \ 
,ROLE_1_MAX_CARDINALITY,ROLE_1_NAVIGABLE) \ 
VALUES ('UD_JMETHOD','TABLE','TABLEUSED','JM2TABLE' \ 
,'INFINITE','true') ADD DEPENDENCY_DEFINITION 'DATAFLOW' 

CLASS_1 and CLASS_2 can be any classes (FCO or SCO). At least one of the classes should be a user defined class. The other class can be either a user defined class or one of the main Warehouse Builder classes, such as table or column. In this example, the association is between the UDO UD_JMETHOD, and table.

Role_1 and Role_2 are the names you use to identify Class_1 from the point of view of this association. A class may have multiple associations and it plays a role in each one of them.

MAX_CARDINALITY allows you to limit the number of objects of that class which participate in that association. For example, consider the association between uniqueKey and foreignKey. The max_cardinality of uniqueKey is 1, because a given foreignKey object can be associated with at most one uniqueKey object. MAX_CARDINALITY can be set to any positive integer, or the reserved word INFINITE.

ROLE_1_NAVIGABLE is used by Lineage/Impact analyzer. If set to TRUE, it means that the analyzer can traverse the association in either direction between Class_1 and Class_2. If the property is set to FALSE, it means that the analyzer can traverse the relationship from Class_1 to Class_2, but not the other way around.

When you set the DEPENDENCY_DEFINITION parameter to DATAFLOW, the association participates in the dataflow impact and lineage analysis.

To associate the UDO to a table, complete the following steps:

1. In the Projects Navigator, expand the node User Defined Modules.

2. Right-click UD_JAVA_APP and select New UD_JAVA_APP.

3. Specify a name for the application.

4. Right-click UD_JCLASS and select New UD_JCLASS.

5. Specify a name for the Java class. In the example, it is CUSTOMERUPDATE.

   
   Description of the illustration udo003_customerupdate.gif
   
   

6. Right-click CUSTOMERUPDATE, and select Open to open the UDO Editor.

7. Click the Object and Association Tree tab and select CUSTOMERUPDATE. In the right panel, you can view the properties for the user defined object CUSTOMERUPDATE.

   
   Description of the illustration udo004_jclasseditor.gif
   
   

8. On the Object and Association Tree tab, right-click UD_JMethod and select Create.

   An SCO called JMETHOD_1 is created.

9. JMETHOD_1 contains two nodes: UD_JMETHOD_LINE, which is the child SCO, and TABLEUSED, which is the value specified for ROLE_1 when the association UD_XJMETHOD2TABLE was created.

10. Right-click TABLEUSED and select Reference.

    The Object Selector dialog box is displayed, and allows you to select the table to which you want to connect the UDO.

    
    Description of the illustration udo006_objectselector.gif
    
    

Propagating UDOs and UDPs to Other Workspaces

The primary method for propagating changes from one workspace to another is by using MDL. MDL enables you to export and import the metadata definition of the UDP and its contents.

Exporting UDOs and UDPs

You can export UDOs and UDPs as any other object.

In the MDL Control file, the option is DEFINITIONFILE=filename to export the metadata definition. For example:

## Sample Export file 
USERID=UserName/Password@HostName:PortID:OracleServiceName
#
DEFINITIONFILE=Drive:\DirectoryName\filename.mdd

FILE=Drive:\DirectoryName\filename.mdl
LOG=Drive:\DirectoryName\filename.log

Importing UDOs and UDPs

You can import UDPs from the command line as well as from the user interface. During import, MDL updates the UDPs for all objects. In the MDL Control file, the option is DEFINITIONFILE=filename to import the metadata definition. For example:

## Sample Import file 
USERID=UserName/Password@HostName:PortID:OracleServiceName
#
DEFINITIONFILE=Drive:\DirectoryName\filename.mdd

FILE=Drive:\DirectoryName\filename.mdl
LOG=Drive:\DirectoryName\filename.log

You can import UDPs using one of the following search criteria:

• Universal ID: The metadata definition contains a Universal Object ID (UOID). The UOID uniquely identifies objects across workspaces. If you import the MDL file by UOID, then MDL looks up the metadata definition by UOID. If the metadata definition name in the source MDL file is different from the metadata definition in the workspace, then MDL renames it when it is running in the Update/Replace mode.

• Physical Name: MDL looks up the metadata definition by physical name.

Regardless of the import mode, MDL either adds the metadata definition if it does not exist in the workspace, or updates the metadata definition if it already exists. MDL does not delete metadata definitions in the workspace.

When updating the metadata definition, MDL only renames the object if the names are different (search criteria is by UOID), and updates the default value. MDL does not change the data type.

Creating Icon Sets

To create a new icon set, complete the following steps:

1. Log in to the client as an administrator.

2. In the Globals Navigator, right-click the Icon Sets node and select New Icon Set.

3. The Create Icon Set dialog box is displayed. For details on the values to be entered on this page, see "Create Icon Set Dialog Box" .

Assigning New Icon Sets to Objects

To assign a new icon set to an object, you must use the OMB*Plus scripting clause SET REFERENCE ICONSET. To remove an icon set, use the clause UNSET REFERENCE ICONSET.

To assign an icon set named ICON1 to an FCO such as Table, use the following command:

OMBREDEFINE CLASS_DEFINITION 'TABLE' SET REF ICONSET 'ICON1'

To remove an icon set assigned to this FCO, use the following command:

OMBREDEFINE CLASS_DEFINITION 'TABLE' UNSET REF ICONSET

To assign an icon set to a specific instance of a table TAB1, use the following command:

OMBALTER TABLE 'TAB1' SET REFERENCE ICONSET 'ICON1'

To remove this icon set, use the following command:

OMBALTER TABLE 'TAB1' UNSET REFERENCE ICONSET

To know what icon set has been assigned to an object, use the following command:

OMBRETRIEVE TABLE 'TAB1' GET REFERENCE ICONSET