5 Creating PL/SQL Mappings

Types of Mappings

Warehouse Builder mappings can be classified according to the supported data extraction technologies used in the mapping. The code generated by Warehouse Builder depends on the sources and targets used in the mapping.

The different types of mappings include:

• PL/SQL Mappings

• SQL*Loader Mappings

• SAP ABAP Mappings

• Code Template (CT) Mappings

Warehouse Builder generates ETL code in different languages for the different mapping types. The generated code is deployed to a target location, where it executes. By selecting the appropriate data extraction technology for each mapping, you can access a wide range of data source types, and satisfy different technical requirements, such as performance and security.

Mapping Editor Canvas

The Mapping Editor canvas is the area that you use to graphically design your mappings. Mappings define how data extracted from the source is transformed before being loaded into the targets.

The Mapping Editor canvas contains two tabs: Logical View and Execution View.

Logical View

The Logical view of the Mapping Editor enables you to design the data flows that define your mapping. You first drag and drop operators representing the source objects, the target objects, and the transformations. Next you establish a data flow between these operators that represents the data transformation by drawing data flow connections between operators.

Execution View

Use the Execution View of the Mapping Editor to define execution units for Code Template (CT) mappings. The Execution View is available only when you create CT mappings.

An execution unit represents the set of related tasks that are to be performed using a code template. A code template contains the logic to perform a particular ETL processing on a particular platform during runtime, such as moving data between two Oracle Database instances, or unloading data from a DB2 database into a flat file. An execution unit can be implemented by a single generated script such as a PL/SQL package or by a code template.

Execution units enable you to break up your mapping execution into smaller related units. Each execution unit may be associated with a code template that contains the template to perform the required data integration task on the specified platform. An execution unit can be implemented by a single generated script such as a PL/SQL package or by a code template.

The Execution View tab of the Mapping Editor displays the operators and data flows from the Logical View in an iconized form. You cannot edit operators or create data flows in the Execution View. You can only perform these tasks using the Logical View.

The contents of the Execution Unit view are based on the selected configuration. Thus, you can use different code templates for different configurations. For example, if you have two configurations, Development and QA, you can use one set of code templates for Development and another for QA.

Execution View Menu and Toolbars

When you select the Execution View tab, the Execution menu is displayed in the Design Center and an Execution toolbar is displayed at the top of the Mapping Editor canvas. Use the options in the Execution menu or the Execution toolbar to:

• Create and delete execution units

• Define default execution units

• Associate code templates with execution units

Mapping Editor Display Options

You can control how the editor displays the mappings on the canvas by selecting Graph from the menu bar and then selecting Options. Warehouse Builder displays the Options dialog box that enables you to set display options for the Mapping Editor canvas.

The Options dialog box contains the following options. You can either select or deselect any of these options.

• Input Connector: Select this option to display an arrow icon on the left of attributes that you can use as input attributes.

• Key Indicator: Select this option to display a key icon to the left of the attribute that is a foreign key attribute in an operator.

• Data Type: Select this option to display the data type of attributes in all operators.

• Output Connector: Select this option to display an arrow icon on the right of attributes that you can use as output attributes.

• Enable Horizontal Scrolling: Select this option to enable horizontal scrolling for operators.

• Automatic Layout: Select this option to use an automatic layout for the mapping.

Rules for Naming Mappings

The rules for naming mappings depend on the naming mode that you select in the Naming Preferences section of the Preferences dialog box. Warehouse Builder maintains a business and a physical name for each object in the workspace. The business name is a unique descriptive name that makes sense to a business-level user of the data. The physical name is the name Warehouse Builder uses when generating code.

When you name objects while working in one naming mode, Warehouse Builder creates a default name for the other mode. Therefore, when working in the business name mode, if you assign a name to a mapping that includes mixed cases, special characters and spaces, Warehouse Builder creates a default physical name for you. For example, if you save a mapping with the business name My Mapping (refer to doc#12345), the default physical name is MY_MAPPING_REFER_TO_DOC#12345.

When you name or rename objects in the Mapping Editor, use the following naming rules.

Naming and Describing Mappings

In the physical naming mode, a mapping name can be from 1 to 30 alphanumeric characters, and blank spaces are not allowed. In the business naming mode, the limit is 200 characters and blank spaces and special characters are allowed. In both naming modes, the name should be unique across the project.

Note for scheduling mappings: If you intend to schedule the execution of the mapping, there is an additional consideration. For any ETL object that you want to schedule, the limit is 25 characters for physical names and 1995 characters for business names. Follow this additional restriction to enable Warehouse Builder to append, to the mapping name, the suffix _job and other internal characters required for deployment and execution.

After you create the mapping definition, you can view its physical and business name in the Property Inspector.

Edit the description of the mapping as necessary. The description can be up to 4,000 alphanumeric characters and can contain blank spaces.

Rules for Naming Attributes and Groups

You can rename groups and attributes independent of their sources. Attribute and group names are logical. Although attribute names of the object are often the same as the attribute names of the operator to which they are bound, their properties remain independent of each other.

Rules for Naming Operators

Business names for the operators must meet the following requirements:

• The length of the operator name can be any string of 200 characters.

• The operator name must be unique within its parent group. The parent group could be either a mapping or its parent pluggable mapping container.

Physical names for operators must meet the following requirements:

• The length of the operator name must be between 1 and 30 characters.

• The operator name must be unique within its parent group. The parent group could be either a mapping or its parent pluggable mapping container.

• The operator name must conform to the syntax rules for basic elements as defined in the Oracle Database SQL Language Reference.

In addition to physical and business names, some operators also have bound names. Every operator associated with a workspace object has a bound name. During code generation, Warehouse Builder uses the bound name to reference the operator to its workspace object. Bound names have the following characteristics:

• Bound names need not be unique.

• Bound names must conform to the general Warehouse Builder physical naming rules, except if the object was imported and contains restricted characters such as spaces.

• Typically, you do not change bound names directly. Instead, you change these by synchronizing with a workspace object outside the mapping.

• In physical naming mode, when you modify the physical name of an operator attribute, Warehouse Builder propagates the new physical name as the bound name when you synchronize.

Using the Add Operator Dialog Box to Add Operators

The Add Operator dialog box enables you to add operators to a mapping. When you add an operator that you can bind to a workspace object, the Mapping Editor displays the Add operator_name Operator dialog box.

Select one of the following options on this dialog box:

• Create Unbound Operator with No Attributes

• Select from Existing Repository Object and Bind

Using Pseudocolumns ROWID and ROWNUM in Mappings

You can use the pseudocolumns ROWID and ROWNUM in mappings. The ROWNUM pseudocolumn returns a number indicating the order in which a row was selected from a table. The ROWID pseudocolumn returns the rowid (binary address) of a row in a database table.

You can use the ROWID and ROWNUM pseudocolumns in Table, View, and Materialized View operators in a mapping. These operators contain an additional column called COLUMN USAGE that is used to identify attributes used as ROWID or ROWNUM. For normal attributes, this column defaults to TABLE USAGE. To use an attribute for ROWID or ROWNUM values, set the COLUMN USAGE to ROWID or ROWNUM respectively.

You can map a ROWID column to any attribute of data type ROWID, UROWID, or VARCHAR2. You can map ROWNUM column to an attribute of data type NUMBER or to any other data type that allows implicit conversion from NUMBER.

Note that ROWID and ROWNUM pseudocolumns are not displayed in the object editors since they are not real columns.

Connecting Operators

You can connect one operator to another if there are no existing connections between the operators. Both of the operators that you want to connect must be displayed in their icon form.

You can also connect from a group to an operator. Hold down the left-mouse button on the group, drag and then drop on the title of the operator.

To connect one operator to another:

1. Select the operator from which you want to establish a connection.

2. Click and hold down the left mouse button while the pointer is positioned over the operator icon.

3. Drag the mouse away from the operator and toward the operator icon to which you want to establish a connection.

   As you drag, a line appears indicating the connection.

4. Release the mouse button over the target operator.

   The Mapping Connection dialog box is displayed. Use this dialog box to specify connections between groups and attributes within these groups as described in "Using the Mapping Connection Dialog Box".

Connecting Groups

When you connect groups, the Mapping Editor assists you by either automatically copying the attributes or prompts you for more information as described in "Using the Mapping Connection Dialog Box".

To connect one group to another:

1. Select the group from which you want to establish a connection.

2. Click and hold down the left mouse button while the pointer is positioned over the group.

3. Drag the mouse away from the group and towards the group to which you want to establish a connection.

   As you drag, a line appears indicating the connection.

4. Release the mouse button over the target group.

   If you connect from an operator group to a target group containing attributes, the Mapping Connection Dialog Box is displayed. Use this dialog box to specify connections between attributes as described in "Using the Mapping Connection Dialog Box".

   If you connect from one operator group to a target group with no existing attributes, the Mapping Editor automatically copies the attributes and connects the attributes. This is useful for designing mappings such as the one shown in "Example: Using the Mapping Editor to Create Staging Area Tables".

Connecting Attributes

You can draw a line from a single output attribute of one operator to a single input attribute of another operator.

To connect attributes:

1. Click and hold down the left mouse button while the pointer is positioned over an output attribute.

2. Drag the mouse away from the output attribute and toward the input attribute to which you want data to flow.

   As you drag the mouse, a line appears on the Mapping Editor canvas to indicate a connection.

3. Release the mouse over the input attribute.

4. Repeat Steps 1 through 3 until you create all the required data flow connections.

You can also select more than one attribute in Step 1. To select more than one attribute, hold down the Ctrl key and select attributes by clicking them. If you select multiple source attributes you can only release the mouse over a group and not over an output attribute. The Mapping Connection dialog box is displayed. Use this dialog box to define the data flow between the source attributes and target attributes.

As you connect attributes, remember the following rules:

• You cannot connect to the same input or inout attribute twice.

• You cannot connect attributes within the same operator.

• You cannot connect out of an input-only attribute nor can you connect into an output-only attribute.

• You cannot connect operators in such a way as to contradict an established cardinality. Instead, use a Joiner operator.

Using the Mapping Connection Dialog Box

The Mapping Connection dialog box enables you to define connections between operators in the mapping. Typically, mappings contain numerous operators that implement the complex transformation logic required for your data loading process. The operators that you want to connect may be situated far away from each other on the mapping and thus require scrolling. Warehouse Builder provides an efficient method to connect operators, groups, and attributes by using the Mapping Connection dialog box.

Figure 5-3 displays the Mapping Connection Dialog box.

Figure 5-3 Mapping Connection Dialog Box

Description of "Figure 5-3 Mapping Connection Dialog Box"

Complete the following sections to define connections between operators:

• (Optional) Attribute Group to Connect

• Connection Options

• Messages

• Connections

Name Tab

The Name tab displays the operator name and an optional description. You can rename the operator and add a description. Name the operator according to the conventions listed in "Rules for Naming Mappings".

Groups Tab

Edit group information on the Groups tab.

Each group has a name, direction, and optional description. You can rename groups for most operators but cannot change group direction for any of the operators. A group can have one of these directions: Input, Output, Input/Output.

Depending on the operator, you can add and remove groups from the Groups tab. For example, you add input groups to Joiners and output groups to Splitters.

Input and Output Tabs

The operator editor displays a tab for each type of group displayed on the Groups tab. Each of these tabs displays the attribute name, data type, length, precision, scale, seconds precision, and optional description. Certain operators such as the Table or View operators have only the Input/Output tab, instead of separate Input and Output tabs. Edit attribute information on these tabs.

Figure 5-4 shows an Output Attributes tab on the operator editor. In this example, the operator is an Aggregator, with separate Input and Output tabs.

Figure 5-4 Output Tab on the Operator Editor

Description of "Figure 5-4 Output Tab on the Operator Editor"

The tab contains a table that you can use to define output attributes. Each row on this tab represents an attribute. The Mapping Editor disables properties that you cannot edit. For example, if the data type is NUMBER, you can edit the precision and scale but not the length.

You can add, remove, and edit attributes. To add an attribute, click on the Attribute column of an empty row, enter the attribute name and then provide the other attribute details such as data type, length, and description. To delete an attribute, right-click the grey cell to the left of the attribute and select Remove.

To assign correct values for data type, length, precision, and scale in an attribute, follow PL/SQL rules. When you synchronize the operator, Warehouse Builder checks the attributes based on SQL rules.

You can also change the order of the attributes listed in the Input and Output tabs. Select the row representing the attribute and use the arrow buttons to the left of the attributes to reorder attributes in a group. Alternatively, hold down the left-mouse button until you see a cross-hair and then drag the row to the position you want.

Associating Expressions with Operator Attributes

Attributes in certain operators such as Expression, Joiner, Aggregator, and Lookup can have an expression associated with them. For such attributes, use the Expression column of the attribute to specify the expression used to create the attribute value. You can directly enter the expression in the Expression column. Figure 5-4 displays the Expression column for an output attribute in the Aggregator operator. To use the Expression Builder interface to define your expression, click the Ellipsis button to the right of the Expression column.

For example, in an Aggregator operator, you create output attributes that store the aggregated source columns. Use the Expression column for an output attribute to specify the expression used to create the attribute value.

Using Display Sets

A display set is a graphical representation of a subset of attributes. Use display sets to limit the number of attributes visible in an operator and simplify the display of a complex mapping.

By default, operators contain three predefined display sets: ALL, MAPPED, and UNMAPPED. Table 5-1 describes the default display sets.

Specifying the Order in Which Target Objects in a Mapping Are Loaded

If your mapping includes only one target or is a SQL*Loader or ABAP mapping, target load ordering does not apply. Accept the default settings and continue with your mapping design.

When you design a PL/SQL mapping with multiple targets, Warehouse Builder calculates a default ordering for loading the targets. If you define foreign key relationships between targets, Warehouse Builder creates a default order that loads the parent and then the child. If you do not create foreign key relationships or if a target table has a recursive relationship, Warehouse Builder assigns a random ordering as the default.

You can override the default load ordering by setting the mapping property Target Load Order. If you make a mistake when reordering the targets, you can restore the default ordering by selecting the Reset to Default option. Or you can select Cancel to discard your changes to the target order.

To specify the loading order for multiple targets:

1. Click whitespace in the mapping canvas to view the mapping properties in the Property Inspector.

   If the Property Inspector is not displayed, select Property Inspector from the View menu.

2. Go to the Target Load Order property and click the Ellipsis button on the right of this property.

   Warehouse Builder displays the Target Load Order dialog box in which TARGET2 is listed before TARGET1.

3. To change the loading order, select a target and use the buttons to move the target up or down on the list.

Steps to Configure Mappings

Use the following steps to configure mappings.

1. In the Projects Navigator, right-click the mapping and select Configure.

   Warehouse Builder displays the Configuration tab that contains configuration parameters for the mapping.

   This tab contains the Deployable, Language, Generation Comments, and Referred Calendar parameters. It also contains the Runtime Parameters and Code Generation Options nodes. Additionally, each operator on the mapping is listed under the node representing the object or operator type. For example, if your mapping contains two tables and a Filter operator, the Table Operators node displays the configuration parameters for the two tables and the Filter Operator node displays the configuration parameters for the Filter operator.

2. Set the Deployable parameter to True.

3. Set Language to the type of code that you want to generate for the selected mapping.

   The options from which you can choose depend upon the design and use of the operators in the mapping. Warehouse Builder provides the following options: PL/SQL, SQL*PLUS, SQL*Loader, and ABAP (for an SAP source mapping).

4. If you want to schedule the mapping to run based on a previously defined schedule, click the Ellipsis button on the Referred Calendar parameter.

   The Referred Calendar dialog box is displayed. Any schedules created are listed here. Select the schedule that you want to associate with the current mapping.

   For instructions on creating and using schedules, see Chapter 11, "Scheduling ETL Jobs".

5. Expand Code Generation Options to enable performance options that optimize the code generated for the mapping.

   For a description of each option, see "Code Generation Options".

6. Expand Runtime Parameters to configure your mapping for deployment.

   For a description of each runtime parameter, see "Runtime Parameters".

7. Go to the node for each operator in the mapping to set their physical properties. The properties displayed under a particular node depend on the type of object. For example, for tables listed under the Table Operators node, you can configure the table parameters listed in "Configuring Tables".

   See Also:

   • "Sources and Targets Reference" for information about configuring sources and targets in a mapping

   • "Configuring Flat File Operators" for information about configuring mappings with flat file sources and targets

Synchronizing a Mapping Operator with its Associated Workspace Object

After you begin using mappings in a production environment, changes may be made to the sources or targets that affect your ETL designs. Typically, the best way to manage these changes is through the Metadata Dependency Manager described in Chapter 14, "Managing Metadata Dependencies". Use the Metadata Dependency Manager to automatically evaluate the impact of changes and to synchronize all affected mappings at one time.

The Mapping Editor enables you to manually synchronize objects as described in this section.

When Do You Synchronize from a Workspace Object to an Operator?

In the Mapping Editor, you can synchronize from a workspace object to an operator for any of the following reasons:

• To manually propagate changes: Propagate changes you made in a workspace object to its associated operator. Changes to the workspace object can include structural changes, attribute name changes, or attribute data type changes.

  To automatically propagate changes in a workspace object across multiple mappings, see Chapter 14, "Managing Metadata Dependencies".

• To synchronize an operator with a new workspace object: You can synchronize an operator with a new workspace object if, for example, you migrate mappings from one version of a data warehouse to a newer version and maintain different object definitions for each version.

• To create a prototype mapping using tables: When working in the design environment, you could choose to design the ETL logic using tables. However, for production, you may want to the mappings to source other workspace object types such as views, materialized views, or cubes.

Synchronizing Physical and Business Names

While synchronizing from a workspace object to an operator, you can specify if the physical and business names of the operator, groups, and attributes should be synchronized. By default, the bound name of the operator, groups and attributes will be derived from the physical name of the corresponding workspace object. The synchronization behavior is controlled by a preference called Synchronize Name Changes listed under the Naming node of the Preferences dialog box.

If you select the Synchronize Name Changes preference under the Naming node of the Preferences dialog box, a synchronize operation on any operator synchronizes the physical and business names of the operator, groups, and attributes. If you deselect the Synchronize Name Changes preference, the physical and business names of the operator, groups, and attributes are not synchronized.

Steps to Synchronize from a Workspace Object to an Operator

Use the following steps to synchronize an operator with the workspace object to which it is bound.

1. On the Mapping Editor canvas, select the operator that you want to synchronize. When the operator is displayed in maximized form, select the operator by clicking the operator name.

2. Right-click and select Synchronize.

   The Synchronize dialog box is displayed.

3. In the Repository Object with which to Synchronize field, select the workspace object with which you want to synchronize the mapping operator.

   By default, the workspace object to which the mapping operator was originally bound is displayed in this field.

4. Under Direction of Synchronization, select Inbound.

5. In the Matching Strategy field, select the matching strategy to be used during synchronization.

   For more information about the matching strategy, see "Matching Strategies".

6. In the Synchronize strategy field, select the synchronization strategy.

   Select Replace to replace the mapping operator definition with the workspace object definition. Select Merge to add any new metadata definitions and overwrite existing metadata definitions if they differ from the ones in the workspace object.

7. Click OK to complete the synchronization.

Synchronizing a Workspace Object with a Mapping Operator

As you make changes to operators in a mapping, you may want to propagate those changes to a workspace object. By synchronizing, you can propagate changes from the following operators: Table, View, Materialized View, Transformation, and Flat File.

Synchronize from the operator to a workspace object for any of the following reasons:

• To propagate changes: Propagate changes that you made in an operator to its associated workspace object. When you rename the business name for an operator or attribute, Warehouse Builder propagates the first 30 characters of the business name as the bound name.

• To replace workspace objects: Synchronize to replace an existing workspace object.

Synchronizing from an operator has no impact on the dependent relationship between other operators and the workspace object. Table 5-2 lists the operators from which you can synchronize.

Advanced Options for Synchronizing

Use the Synchronization Plan dialog box to view and edit the details of how Warehouse Builder synchronizes your selected objects. After you select from the Matching Strategies, click Refresh Plan to view the actions that Warehouse Builder takes.

In the context of synchronizing, source refers to the object from which to inherit differences and target refers to the object to be changed.

For example, in Figure 5-6, the flat file PAYROLL_WEST is the source and the Flat File operator PAYROLL is the target. Therefore, Warehouse Builder creates new attributes for the PAYROLL operator to correspond to fields in the flat file PAYROLL_WEST.

Figure 5-6 Advanced Synchronizing Options

Description of "Figure 5-6 Advanced Synchronizing Options "

Creating Pluggable Mappings

Pluggable mappings are usually predefined and used when required. You can create pluggable mappings either from within a mapping by using the Mapping Editor, or from the navigation tree by using the wizard. The wizard is the faster way to create a pluggable mapping because it makes some default choices and guides you through fewer choices. You can make additional choices later in the Pluggable Mapping Editor. The editor presents you with all the settings in a series of tabs.

The Pluggable Mappings node in the navigation tree contains the following two nodes:

• Standalone: Contains standalone pluggable mappings

• Pluggable Mapping Libraries: Contains a set of pluggable mappings providing related functionality that you would like to publish as a library.

You can create pluggable mappings under either of these nodes.

Limitations of Copying Operators, Groups, and Attributes

Following are the limitations of copying operators, groups, and attributes:

• You can copy operators, groups, or attributes from a mapping and paste them into another mapping within the same project only.

• Any connections that existed between the operator, group, or attributes in the source mapping are not copied to the target mapping. Only the operator, group, or attributes are pasted into the target mapping.

• For pluggable mappings, the connections between child operators within the source pluggable mapping are copied to the target mapping.

• Group properties of the source operator are not copied to the group in the target operator. However, all the attributes contained in the group are copied.

• Before you copy and paste a group from a source operator to a target operator, you must create the group in the target operator. The group is not created when you perform a paste operation.

Steps to Group Operators in Mappings and Pluggable Mappings

Use the following steps to group operators in mappings and pluggable mappings.

1. Open the mapping in which you want to group operators.

   See "Steps to Open a Previously Created Mapping" .

2. Select the operators that you want to group.

   To select multiple objects, hold down the Ctrl key while selecting objects. Or, hold down the left-mouse button and draw a rectangle that includes the objects you want to select.

3. In the toolbar at the top of the Mapping Editor canvas, click the Group Selected Objects icon. Or, from the Graph menu, select Group Selected Objects.

   The selected operators are grouped into a folder, and the collapsed folder is displayed in the Mapping Editor. A default name, such as Folder1, is used for the folder.

4. (Optional) Rename the folder so that the name is more intuitive and reflects the task that the group of operators performs.

   To rename the folder:

   1. Right-click the folder and select Open Details.

   2. In the Edit Folder dialog box, enter the name of the folder in the Name field and click OK.

Steps to Ungroup Operators in Mappings and Pluggable Mappings

Use the following steps to ungroup operators.

1. Open the mapping in which you want to ungroup operators.

   See "Steps to Open a Previously Created Mapping" .

2. Select the folder that you want to ungroup.

   You can select multiple folders by holding down the Ctrl key and selecting all the folders.

3. In the toolbar, click the Ungroup Selected Objects icon. Or, from the Graph menu, select Ungroup Selected Objects.

   The operators that were grouped are now displayed individually on the Mapping Editor.

Spotlighting Selected Operators

Spotlighting enables you to view only selected operators and their connections. All other operators and their connections are temporarily hidden. You can perform spotlighting on a single operator, a group of operators, a single folder, a group of folders, or any combination of folders and operators. When you select objects for spotlighting, the Mapping Editor layout is redisplayed such that the relationships between the selected objects are displayed clearly. The edges between spotlighted components are visible. However, edges between spotlighted components and other nonselected components are hidden.

To spotlight a folder containing grouped operators, in the Mapping Editor, select the folder and click the Spotlight Selected Items icon in the toolbar. The folder is expanded and all the operators it contains are displayed. Click the Spotlight Selected Objects icon again to toggle the spotlighting mode. The folder will appear collapsed again and the operators it contains are hidden.

When in Spotlight mode, you can perform all normal mapping operations, such as moving or resizing operators, modifying operator properties, deleting spotlighted operators, creating new operators, and creating connections between operators. You can perform any operation that does not affect temporarily hidden operators.

If you create an operator in Spotlight mode, the operator will remain visible in the Mapping Editor when you toggle out of Spotlight mode.

Steps to Perform a Regular Search

Performing regular search involves the following steps:

1. Specifying the Object to Locate

2. Specifying the Method Used to Display Search Results

3. Clicking Find.

Steps to Perform an Advanced Search

Performing an advanced search involves the following steps:

1. Specifying the Object to Locate

2. Specifying the Method Used to Display Search Results

3. Specifying the Search Scope

4. Specifying the Search Criteria

5. Clicking Find.

Advanced Find Dialog Box

The Advanced Find dialog box enables you to search for operators, groups, or attributes within a mapping or pluggable mapping. By default, this dialog box displays the options required to perform a regular search. In a regular search, you search for an operator, group, or attribute using its display name. You can also specify how the search results should be displayed.

An advanced search provides techniques to define the scope of the search and the search criteria. To perform an advanced search, click Show Advanced. The additional parameters that you must define for an advanced search are displayed.

Specifying the Object to Locate

Use the Find field to specify the object that you want to locate. A regular search locates objects containing the same display name as the one specified in the Find field.

While performing an advanced search, in addition to the display name, you can specify a string that you provided in the Description property, physical name, business name, or name of the workspace object to which an operator is bound.

You can use wildcards in the search criteria. For example, specifying "C*" in the Find field searches for an object whose name begins with C or c.

Specifying the Method Used to Display Search Results

Use the Turn On Highlighting button on the Advanced Find dialog box to specify the method used to display the search results. This is a toggle button, and you can turn highlighting on or off.

Turn on Highlighting 

When you turn highlighting on, all objects located as a result of the search operation are highlighted in yellow in the Mapping Editor. Highlighting enables you to easily spot objects that are part of the search results.

Turn off Highlighting 

This is the default behavior. When you turn off highlighting, objects located as a result of the search operation are identified by the control being moved to these objects. When control is on a particular object, the border around the object is blue. When highlighting is off, the search results are presented one at a time.

For example, if an operator is found as a result of the search, the borders of the node representing the operator are blue.

Specifying the Search Scope

You can restrict the objects searched by specifying the scope of the search. The In Selected field in the Scope section enables you to limit the scope of the search to within the objects selected on the canvas.

To locate an object within a specific set of operators, you first select all the operators on the canvas and then choose In Selected. You can select multiple operators by holding down the Shift key while selecting operators.

Specifying the Search Criteria

To perform an advanced search, you must specify additional search criteria that will further refine the search.

Find By

Use the Find By list to specify which name should be used to search for an object. The options you can select are:

• Display Name: Search either the physical names or the business names, depending on the Naming Mode set.

• Physical Name: Search for an object containing the same physical name as the one specified in the Find field.

• Business Name: Search for an object containing the same business name as the one specified in the Find field.

• Bound Name: Search for an object containing the same bound name as the one specified in the Find field, if this property is available for the object.

• Description: Search for an object containing the same description as the one specified in the Find field, if this property is defined for the object.

Match Options

Use the Match Options section to specify the matching options used while finding the object. The options that you can select are:

• Match Case: Locates objects whose name and case match the search string specified in the Find field.

  When searching by physical name, match case is set to false by default.

  Note that in physical name mode, everything a user creates will be uppercase, but the imported objects may be in mixed case.

  When searching by logical name, match case is set to true by default.

• Whole Word Only: Restricts matches to exclude those objects that do not match the entire search string, unless specifically overridden by a wildcard.

• Regular Expression: Supports the specification of a pattern, used for a Java regular expression as the search string. If Regular Expression is combined with Whole Word Only, a boundary matcher "$" is appended to the search string pattern.

  For more information about regular expression support, see Oracle Database SQL Language Reference.

Find Options

Specifies options for managing the search operation. Select one of the following options:

• Incremental: Performs a search after any character is typed or if characters are removed from the search string. Use the Find button to find additional objects that match the search string

• Wrap Find: Continues the search operation with the first object when the last object in the set has been reached.

• Find from Beginning: Continues the search with the first object in the set.

Scope

Use this section to restrict the scope of the search.

In Selected: Select this option to locate the search string only among the objects currently selected in the Mapping Editor.

Direction

Use the Direction section to step through the objects in the search result set either forward or backward. Select Next to step forward through the result set and Previous to step backward through the result set.

General Restrictions in the Mapping Debugger

The following restrictions and limitations apply to the Mapping Debugger.

• Mappings run using the debug mode in the Mapping Editor are intended to be used for debug purposes only. Mappings run from the Mapping Editor do not perform as well as mappings that are run from the Control Center. This is attributed to the setup of temporary objects necessary to support the debugging capabilities. Use the Control Center to run mappings.

• You cannot pause an active debug run using the Pause button on the toolbar or the associated item in the debug menu.

• You cannot use the Repository Browser to view the results of a mapping run in debug mode.

• Only mappings that can be implemented as a PL/SQL package can currently be run in debug mode. ABAP mappings are not supported in the debugger.

• The Advanced Queue operator is not supported when you run mappings in debug mode.

Starting a Debug Session

To start a debug session, open the mapping that you want to debug in the Mapping Editor. From the Debug menu, select Start. Or, click the Start icon on the Mapping Editor toolbar. The Mapping Editor switches to debug mode with the debug panels appearing in the Log window, and the debugger connects to the appropriate Control Center for the project. The debug-generated code is deployed to the target schema specified by the location of the module that contains the map being debugged.

After the connection has been established, a message appears, indicating that you may want to define test data. When you have previously defined test data, then you are asked if you want to continue with initialization.

To debug a mapping, each source or target operator must be bound to a database object. Defining test data for the source and target operators is optional. By default, the debugger uses the same source and target data that is currently defined for the non-debug deployment of the map.

Debug Panels of the Design Center

When the Mapping Editor is opened in Debug mode, the Log window displays two new panels: Info Panel and Data Panel.

Defining Test Data

Every source or target operator in the mapping is listed on the Test Data tab in the lower Info tab panel. It also contains the object type, the source, and a check mark that indicates whether the database object has already been bound to the source or target operator.

The object type listed on the tab is determined by whether the column names in the data source that you select (for example, a table) matches the columns in the mapping operators. There are two possible types:

• Direct Access. When there is an exact match, the type is listed as Direct Access.

• Deployed as View. When you choose a data source with columns that do not match the mapping operator columns, you can choose how you want the columns mapped. This object is deployed as a view when you run the mapping and the type is listed as Deployed as View.

Click Edit to add or change the binding of an operator as well as the test data in the bound database objects. Before you can run the mapping in debug mode, each listed source or target operator must be bound and have a check mark. The need to have test data defined and available in the bound database object depends on what aspect of the data flow you are interested in focusing on when running the debug session. Typically, you will need test data for all source operators. Test data for target operators is usually necessary if you want to debug loading scenarios that involve updates or target constraints.

To define or edit test data:

1. From the Test Data tab in the Mapping Editor, select an operator from the list and click Edit. The Define Test Data dialog box is displayed.

2. In the Define Test Data dialog box, specify the characteristics of the test data that you want Warehouse Builder to use when it debugs. There are many characteristics that you can specify. For example, you can specify that the test data be from a new or existing database object or that you can or cannot manually edit the test data. Click Help on the Define Test Data dialog box for more information.

Setting Breakpoints

If you are interested in how a specific operator is processing data, you can set a breakpoint on that operator to cause a break in the debug session. This enables you to proceed quickly to a specific operator in the data flow without having to go through all the operators step by step. When the debug session gets to the breakpoint, you can run data through the operator step by step to ensure that it is functioning as expected.

To set or remove a breakpoint:

1. From the Mapping Editor, click an operator, select Debug, and then select Set Breakpoint. You can also click the Set Breakpoint button on the toolbar to toggle the breakpoint on and off for the currently highlighted operator.

   If you are setting the breakpoint, the name of the operator set as a breakpoint appears in the list on the Breakpoints tab on the Info panel. If you are removing the breakpoint, the name is removed. Use the Clear button on the Breakpoint tab to remove breakpoints.

2. Deselect or select the breakpoints on the Breakpoint tab to disable or enable them.

Setting Watches

The Step Data tab on the Data Panel always shows the data for the current operator. To keep track of data that has passed through any other operator irrespective of the active operator, you can set a watch.

Use watches to track data that has passed through an operator or for sources and targets, the data that currently resides in the bound database objects. You can also set watches on operators after the debug run has already passed the operator and look back to see how the data was processed by an operator in the data flow.

To set a watch:

From the Mapping Editor, select an operator. From the Debug menu, select Set Watch. You can also select the operator and click the Set Watch button on the Mapping Editor toolbar to toggle the watch on and off.

A separate Watch panel is displayed to view data for Constant, Mapping Input, Mapping Output, Pre Mapping, Post Mapping, and Sequence operators. Since these operators contain lesser information than other operators, information regarding more than one of these operators is displayed in the Watch panel. Thus, only one instance of Watch panel is displayed for these operators if you choose to watch values.

To remove a watch:

To remove a watch, select the operator on the Mapping Editor canvas. Then, click the Set Watch icon on the Mapping Editor toolbar or select Set Watch from the Debug menu.

If a watch panel consists of non-data based operators such as Constant, Mapping Input, Mapping Output, Pre Mapping, Post Mapping, and Sequence, you can remove these operators by right-clicking the operator and selecting Remove. You can remove all these operators at one time by closing the Watch panel.

Saving Watches

When you set a watch for an operator, Warehouse Builder automatically saves the watch points for this operator, unless you close the watch panels. When you end the debug session and start up again, the tabs for operator watches that you created are displayed.

If you do not want to save watch points, click the Set Watch icon in the Mapping Debugger toolbar. Or close the tab related to the Watch point.

Running the Mapping

After you have defined the test data connections for each of the data operators, you can initially generate the debug code by selecting Reinitialize from the Debug menu, or by clicking Reinitialize on the Mapping Editor toolbar. Warehouse Builder generates the debug code and deploys the package to the target schema that you specified.

You can run the debug session in one of the following modes:

• Continue processing until the next breakpoint or until the debug run finishes by using the Resume button on the toolbar or the associated menu item.

• Process row by row using the Step button on the toolbar or the associated menu item.

• Process all remaining rows for the current operator by using the Skip button on the toolbar or the associated menu item.

• Reset the debug run and go back to the beginning by using the Reset button or the associated item from the Debug menu.

ReInitializing a Debug Session

When you have made changes to the mapping, or have bound source or target operators to different database objects, then you must reinitialize the debug session to continue debugging the mapping with the new changes. To reinitialize, click the reinitialize button on the toolbar or select the reinitialize menu item in the debug menu. Reinitializing both regenerates and redeploys the debug code. After reinitialization, the mapping debug session starts from the beginning.

Scalability

Scalability when debugging a mapping applies both to the amount of data that is passed as well as to the number of columns displayed in the Step Data panel. The Define Test Data dialog box provides a row limit that you can use to limit the amount of data that flows through the mapping. Also, you can define your own data set by creating your own table and manipulating the records manually.

To restrict the number of columns displayed on the Step Data tab, or on a watch tab, you can use display sets. By default, every operator has a display set ALL and a display set MAPPED (to display only the mapped attributes). You can manually add display sets on sources by using the Mapping Editor directly. Select the Use Display Set option under the right mouse button on an input or output group to select the display set.