Using Experts to Automate Warehouse Builder Tasks

This tutorial shows you how using Experts you open up Oracle Warehouse Builder (OWB) to the inexperienced user. Experts make the design of a complex system easier and repeatable by guiding the (novice) designer or developer. Experts provide an extremely powerful method for building a sequence of tasks in which part of the steps can be automated.

Note: The screenshots in this tutorial may appear slightly different from yours depending on the order in which you perform the other tutorials in this OBE Series. You can ignore the irrelevant items you see on your screens.

Approximately 1 hour

Topics

This tutorial covers the following topics:

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

Overview

This tutorial covers creating a simple expert first. Then learn how to create a nested expert. You also learn how to execute an expert from the OWB User Interface (UI) and from outside the OWB UI. You create an advanced expert and research with the various property settings.

A common requirement within Experts is an error handler – for example, trapping the fact that a user hit the ‘Cancel’ button on a dialog and exiting gracefully with a message.

Back to Topic List

Before starting this tutorial, you should:

1.

Complete the Setting Up the Oracle Warehouse Builder Project tutorial.

Back to Topic List

Creating a Simple Expert

Creating the ERROR_HANDLER Expert
Validating the Expert
Executing an Expert

Back to Topic List

Creating the ERROR_HANDLER Expert

In this section you create the error handler expert, which is executed as a stand alone and then is re-used in various other experts in this tutorial.

1.

Assuming that you logged in as OWB user, from the Design Center expand HANDSON project, click on the Experts node.

 

2.

Now create a private expert module within the HANDSON project.

Right-click the Experts node, from the short-cut menu select New.

 

3.

In the Create Expert Module dialog, enter expert_module (OWB automatically converts to uppercase) as the name. In the Description field enter The EXPERT_MODULE expert module contains three experts, ERROR_HANDLER, NESTED_EXPERT and ADVANCED_EXPERT. Ensure the Proceed to Expert Wizard checkbox is clicked. Click OK.

 

4.

In the Create Expert dialog, enter error_handler (not case sensitive. OWB automatically converts to uppercase) as the name. In the Description field enter An Expert to handle errors. Click OK.

The Expert Editor launches. The Expert Editor canvas by default contains two tasks, START_TASK and an END_TASK.

 

5.

Add a Custom Dialog task onto the canvas. A custom dialog helps to get some information from the user. You can also use this dialog to provide some instructions to users.

In the Expert Editor window, from the Palette (lower left hand panel), drag and drop onto the Expert (canvas) a Custom Dialog.

 

6.

Now, connect the tasks thereby specifying the sequence for the execution of the tasks.

Draw a transition line from the START_TASK to the CUSTOM_DIALOG_TASK. (Click in the center of the START_TASK. With the left-mouse button pressed, drag a line from the START_TASK and drop the line in the center of the CUSTOM_DIALOG_TASK. Release the left-mouse button)

Draw a transition line from the CUSTOM_DIALOG_TASK to the END_TASK. You canvas must look as follows:

 

7.

Customize the Custom Dialog task as per your requirement. You specify a title, and a message to be displayed in the Custom Dialog.

On the canvas, click the CUSTOM_DIALOG_TASK custom dialog task.

 

8.

With the CUSTOM_DIALOG_TASK task clicked, click the Dialog Builder tabbed page from the Task Editor panel.

 

9.

On the Dialog Builder tabbed page, in the Title field enter Error Handler as the title for the custom dialog task.

Make note of the two buttons OK and Cancel, these buttons appear in the custom dialog.

 

10.

To specify some information message to be displayed to the user, create a label. Click the Add button located on the bottom right hand corner of the Task Editor panel.

Clicking on the Add button adds a new row in the Task Editor panel for the selected custom dialog task.

 

11.

On the Task Editor panel, in the Content field for the newly created row enter Hello from the Error Handler.

To get an early preview of how this appears when the expert is run, click the checkbox Show Preview. The preview of the dialog is shown inside the Custom Dialog Preview panel. Note: Sometimes, the Custom Dialog Preview panel could appear on the right of the Task Editor panel.

 

12.

Uncheck the Show Preview checkbox.

 

13.

In the Expert Editor click on the Save All toolbar icon to save your work. Click Yes in the Warehouse Builder Warning dialog.

It is a good practice to periodically save your work.

 

Back to Topic

Validating the Expert

Validating the Expert helps to ensure there are no syntax errors and you can correct any errors before execution. Validate is an optional step.

1.

In the Expert Editor, from the Expert menu, select Validate. Ensure there are no errors. You can ignore warnings about goals and instructions not specified.

 

2.

In the Compilation Results panel, the validation results are displayed. You can ignore the warnings.

In this example you receive one warning. You can ignore any warnings about goals and instructions not being specified.

Note: If you receive any errors, rectify the errors and repeat step 1.

 

Back to Topic

Executing an Expert

After creating and validating an error free expert you are ready to run this expert. Now, run this expert to verify if it works.

1.

Click the Start button from the toolbar in the Expert Editor window.

When you run an expert the Expert Warning dialog displays, click Yes to proceed with executing the expert.

 

2.

When the Expert runs, this is what you see on your screen.

The title that you specified appears in the title bar of the custom dialog, the message that you specified appears as the main dialog text, and the two buttons

Click OK in the Error Handler dialog.

 

3.

The Expert Information dialog appears. This dialog displays everytime an expert completes successfully. Click OK in the Expert Information Dialog to go back to the Design Center.

 

Back to Topic

Back to Topic List

Creating a Nested Expert

You can create experts to perform relatively simple tasks and use these experts as building blocks in building complex experts. When you add an expert to the canvas, it is called a nested expert.

Creating the NESTED_EXPERT Expert
Adding a Condition to a Transition
Passing Values Between Experts

Back to Topic List

Creating the NESTED_EXPERT Expert

In this topic, first you create a simple expert to display the ORACLE logo inside the custom dialog. When you click on the logo the website opens in a browser window. Then, edit the expert to include the ERROR_HANDLER expert you created earlier. This is an example of creating a nested expert. After you complete this topic you will appreciate how it is useful to write an expert once and re-use it in various experts.

1.

From the Project Explorer the Design Center, expand the HANDSON project. Right-click on the EXPERT_MODULE node, from the shortcut menu select New.

 

2.

In the Create Expert dialog, enter NESTED_EXPERT (can be entered in uppercase or lowercase, OWB will convert to uppercase) as the name of the expert. Click OK.

 

3.

In the Expert Editor window, from the Palette (lower left hand panel), drag and drop onto the Expert panel (canvas) a Custom Dialog. Place it in between the START_TASK and the END_TASK.

 

4.

On the canvas, click (if not already selected) the CUSTOM_DIALOG_TASK custom dialog task.

5.

Click the Dialog Builder tabbed page on the Task Editor panel. In the Title field enter Intro as the title for the custom dialog task.

Specify the width and the height for the custom dialog. Unselect the Auto Size option. In the Width field enter 290 and in the Height field enter 170.

 

6.

Click the Add button located on the bottom right hand corner of the Task Editor panel.

 

7.

In the new row provide the following information:

In the Type field from the drop-down select Hyperlink (to add a field of type hyperlink to the custom dialog)

In the Message field type <img src=http://www.oracle.com/admin/images/ocom/oralogo_small.gif>

In the Content field type http://www.oracle.com

 

8.

Click the Add button located on the bottom right hand corner of the Task Editor panel.

In the newly created row, in the Content field enter Hit <b>OK</b> to end<br><b>Cancel</b> to terminate with Error. Press the ENTER key. Note: If the enter key is not pressed and you go to the next step, whatever you typed is lost.

 

9.

Add transition lines to connect the tasks thereby specifying the sequence for the execution of the tasks.

Connect the tasks as shown in the screenshot:

 

10.

Click the Validate icon from the toolbar. Validation before executing the expert is an optional step. You can ignore any warnings about goals and instructions not being specified.

 

11.

Save your work. From the Expert menu select Save All. Click Yes in the Warehouse Builder Warning dialog.

 

12.

From the Expert menu select Close. You go back to the Design Center.

 

13.

Run the expert to see the result of your work. From the Project Explorer, right-click on the NESTED_EXPERT node and from the short-cut menu select Start.

 

14.

Click on the ORACLE logo, a browser window launches and opens the Oracle.com webpage.

 

15.

Click OK in the Intro dialog.

Click OK in the Expert Information Dialog, to go back to the Design Center.

 

16.

Now, add the ERROR_HANDLER expert to the NESTED_EXPERT expert.

From the Project Explorer, in the Design Center, expand HANDSON project. Expand Experts > EXPERT_MODULE. Double-click the NESTED_EXPERT node. The Expert Editor launches.

 

17.

In the Expert Editor, from the Explorer window (in the upper left corner) click the Available Objects tab.

Expand EXPERT_MODULE located under the Private Experts node. Drag and drop onto the canvas the ERROR_HANDLER expert. Position it as shown in the screenshot below.

 

18.

Draw a transition line from CUSTOM_DIALOG_TASK to ERROR_HANDLER and from ERROR_HANDLER to END_TASK.

 

Back to Topic

Adding a Condition to a Transition

If you want execution of the next task to be conditional, then assign a condition to the transition. If the user clicks the CANCEL button run the ERROR_HANDLER expert. To assign a condition for the transition, perform the following steps:

1.

Click the transition line starting from the CUSTOM_DIALOG_TASK to ERROR_HANDLER. In the Object Details panel click on the field next to the Condition property and click the : button .

 

2.

In the Transition Condition Editor from the Enumerated Condition drop-down, select CANCEL and click OK.

 

3.

Click the transition line starting from the CUSTOM_DIALOG_TASK to END_TASK. In the Object Details panel click on the field next to the Condition property. Click the : button .

In the Transition Condition Editor from the Enumerated Condition drop-down, select SUCCESS and click OK.

 

4.

From the Expert menu click Validate. Ensure there are no errors. You can ignore warnings about goals and instructions not specified.

From the Expert menu click Start.

Click Yes in the Expert Warning dialog.

 

5.

In the Intro dialog, click Cancel. The Error Handler dialog that you created in the ERROR_HANDLER expert appears.

 

6.

Click OK in the Error Handler dialog.

Click OK in the Expert Information Dialog.

 

Back to Topic

Passing Values between Experts

Typically, in the case of error handler, you want to pass information either into or out of (or both) of a nested expert. In this topic, you learn how to pass values between experts.

You first need to create variables and parameters, which are used to pass values between experts. Perform the following steps:

1.

From the Design Center, expand HANDSON project. Expand Experts > EXPERT_MODULE. Double-click the ERROR_HANDLER node. The Expert Editor launches.

 

2.

You now learn, how to create a variable. In the Expert Editor, from the Explorer panel (upper left corner) click the Selected Objects tab (if not already selected), click the Variables node and click the Create button. A variable named VARIABLE is created.

 

3.

In the Object Details panel, click in the field next to the Name property. Rename VARIABLE to ERROR_TO_DISPLAY and hit the Enter key.

 

4.

Now you learn how to create a parameter. From the Explorer panel (upper left corner) in the Expert Editor, click the Parameters node and click the Create button.

 

5.

In the Object Details panel, click to the right of the Name property. Rename PARAM to INPUT_ERROR_MESSAGE and hit the Enter key. Ensure IN is selected in the Direction field.

 

6.

Repeat step 4.

In the Object Details panel, click in the field on the right of the Name property. Rename PARAM_1 to OUTPUT_ERROR_MESSAGE and hit the Enter key. Click in the field to the right of the Direction property. From the drop-down list select OUT.

 

7.

You now learn how to bind a parameter to a variable.

From the Explorer panel, expand Parameters node, click INPUT_ERROR_MESSAGE. In the Object Details panel, click in the field to the right of the Binding property. From the drop-down list select ERROR_TO_DISPLAY.

 

8.

From the Explorer panel, select OUTPUT_ERROR_MESSAGE. In the Object Details panel, click in the field to the right of the Binding property. From the drop-down list select ERROR_TO_DISPLAY.

 

9.

Click CUSTOM_DIALOG_TASK on the canvas. In the Task Editor panel, click on Dialog Builder tabbed page. In the Content field type $ERROR_TO_DISPLAY (overwrite the old value Hello from the Error Handler). Press the ENTER key.

 

10.

With the CUSTOM_DIALOG_TASK selected, click the Post-processing tabbed page. Type the following code:

if {$GUI_RETURN_VALUE(DLG1) == "Cancel"} {
set ERROR_TO_DISPLAY "Hit Cancel within Handler"
} elseif {GUI_RETURN_VALUE(DLG1) == "OK"} {
set ERROR_TO_DISPLAY "Hit OK within Handler"
}

ALERT: Tcl scripting is case and space sensitive. This piece of code does not compile successfully or execution could fail because of space issues.

Please Note the following:

1. This code uses tcl scripting language


2. In the above code there are spaces on either side of the == and between } { braces in the IF statement.


3.The above code sets the message in the ERROR_TO_DISPLAY variable based on what button was pressed Cancel or OK.


4. DLG1 is the name of the custom dialog (Click the CUSTOM_DIALOG_TASK on the canvas. Click the Dialog Builder tabbed page. See the Name field)

 

11.

Save your work and close the ERROR_HANDLER expert editor.

 

12.

You have made changes to the ERROR_HANDLER expert. The NESTED_EXPERT must include this updated ERROR_HANDLER expert.

From the Design Center, expand HANDSON project. Expand Experts > EXPERT_MODULE. Double-click the NESTED_EXPERT node. The Expert Editor launches.

 

13.

Click the ERROR_HANDLER expert from the canvas. Delete the ERROR_HANDLER expert. Click OK in the Delete Confirmation dialog.

 

14.

In the Expert Editor, from the Explorer panel (in the upper left corner) click on the Available Objects tab. Expand EXPERT_MODULE located under the Private Experts node. Drag and drop onto the canvas the ERROR_HANDLER expert.

 

15.

Draw a transition line from CUSTOM_DIALOG_TASK to ERROR_HANDLER.

 

16.

Click the transition line starting from the CUSTOM_DIALOG_TASK to ERROR_HANDLER. In the Object Details panel click on the field next to the Condition property. Click the : button

In the Transition Condition Editor from the Enumerated Condition drop-down, select CANCEL and click OK.

 

17.

In the Expert Editor window, from the Palette (lower left hand panel), drag and drop onto the canvas a Custom Dialog. Place it to the right of the ERROR_HANDLER. Compare your design layout with the screenshot and rearrange if necessary.

 

18.

Connect the following tasks:

Draw a transition line from ERROR_HANDLER to CUSTOM_DIALOG_TASK_1.

Draw a transition line from CUSTOM_DIALOG_TASK_1 to END_TASK.

 

19.

In the Expert Editor, from the Explorer panel (upper left corner) click the Selected Objects tab. Click the Variables node and click the Create button. A variables named VARIABLE is created.

 

20.

In the Object Details panel, click in the field next to the Name property. Rename VARIABLE to MESSAGE_BUCKET and hit the Enter key.

 

21.

In the Explorer panel with Selected Object tab selected, expand Tasks > ERROR_HANDLER. Click on the INPUT_ERROR_MESSAGE node.

In the Object Details panel, click on the field next to the Binding from property. From the drop down select MESSAGE_BUCKET.

 

22.

Click on the OUTPUT_ERROR_MESSAGE node. In the Object Details panel, click on the field next to the Binding from property. From the drop down select MESSAGE_BUCKET.

 

23.

In the Expert Editor, click the CUSTOM_DIALOG_TASK_1 on the canvas. Click the Dialog Builder tabbed page from the Task Editor panel.

 

24.

Now, specify some information message to be displayed in the dialog box. Click the Add button located on the bottom right hand corner of the Task Editor panel. Clicking on the Add button adds a new row in the Task Editor panel for the selected custom dialog task.

 

25.

On the Task Editor panel, in the Content field for the newly created row enter $MESSAGE_BUCKET and press the ENTER key.

 

26.

In the Options field, you see two values: OK,Cancel. Delete , (comma) Cancel. This removes the Cancel button from this custom dialog.

 

27.

From the canvas, click the CUSTOM_DIALOG_TASK task. Click on the Post-processing tabbed page from the Task Editor panel. Type the following code:

if {$GUI_RETURN_VALUE(DLG1) == "Cancel"} {
set MESSAGE_BUCKET "Hit Cancel in Main Expert"
}

ALERT: Tcl scripting is case and space sensitive. This piece of code does not compile successfully or execution could fail because of space issues.

 

28.

Save your work.

Validate your work. In the Expert Editor, from the Expert menu, select Validate. Ensure there are no errors. You can ignore warnings about goals and instructions not specified.

Close the Expert Editor.

 

29.

In the Project Explorer, right-click NESTED_EXPERT and select Start.

 

30.

Click Cancel in the Intro dialog. The Error Handler dialog pops up.

 

31.

Click Cancel in the Error Handler dialog.

 

32.

Click OK in the untitled dialog. Click OK in the Expert Information Dialog. You are back in the Design Center.

 

Back to Topic

Back to Topic List

Two Methods to Execute an Expert

So far, you have been running the Experts using the default execution User Interface (UI) settings from within Warehouse Builder. Some Experts are written to run as ‘standalone’ applications (i.e. the user doesn’t see the Design Center). Other Experts however are written to be extensions i.e. augment the functionality available within the Design Center. There are properties and commands that can control the appearance and thus the perception of the experts by end-users. In this topic you perform the following tasks:

Executing an Expert Outside Oracle Warehouse Builder
Executing an Expert Within Oracle Warehouse Builder

Back to Topic List

Executing an Expert Outside Oracle Warehouse Builder

To execute an expert outside Warehouse Builder, perform the following steps:

1.

In this topic you use the existing experts. In notepad or any text editor type the following:

set connectstring [lindex $argv 0]
OMBCONNECT $connectstring
OMBCC '/HANDSON/EXPERT_MODULE'
OMUSTART EXPERT 'NESTED_EXPERT'

ALERT: Tcl scripting is case and space sensitive. Take care while entering this piece of code. Ensure to use the ' single quotation mark wherever used in the above code.

Note: The first line sets the variable ‘connectstring’ to be the first argument passed-in on the command-line to the Tcl script.

 

2.

Save this file to c:\temp\startdialog.tcl.

Note: Save the file as startdialog.tcl and not startdialog.tcl.txt. In the Save As dialog, from the drop down in the Save As type select All Files.

 

3.

In notepad (any text editor) type the following:

c:
cd <owb home>\owb\bin\win32\
call OMBPlus.bat c:/temp/startdialog.tcl <un/pw>@<host:port:servicename>

Note: call OMBPlus.bat c:/temp/startdialog.tcl.................-- On both, windows and linux/unix platforms forward slash (/) should be used. Alternatively on windows you could you use double backslash (\\) (c:\\temp\\startdialog.......)

Save this file as c:\temp\runexpert.bat.

 

4.

Click the Windows Start menu and click Run. In the Open field type c\temp\runexpert.bat. (Optionally, you could create a desktop shortcut to run this batch file)

Minimize the command window and continue running the expert. The expert completes and all the windows close automatically.

Note: This method of execution does not require the user to be familiar with OWB and can still be able to have the benefit of the expert.

Back to Topic

Executing an Expert Within Oracle Warehouse Builder

You now learn how to run experts from within Warehouse Builder.

1.

In the Design Center, expand HANDSON > Experts > EXPERT_MODULE. Double-click NESTED_EXPERT. The Expert Editor launches.

 

2.

Click on NESTED_EXPERT in the Explorer panel in the Expert Editor. You set the properties in the Object Details panel.

 

3.

In the Execution UI Settings section, you see the various properties that control how the Expert is executed (from within OWB UI)

Uncheck the following properties:

Show Progress Graph

Finish Dialog on Completion

Show Task Assistant

Close Assistant on Completion

 

4.

After, you changed the property settings, run the expert. Click Start from the toolbar on the Expert Editor. Click Yes in the Expert Warning dialog.

Note: The Design Center disappears and only the custom dialog appears. Click OK in the Intro dialog.

 

5.

In the Design Center, expand HANDSON > Experts > EXPERT_MODULE. Double-click the NESTED_EXPERT. The Expert Editor launches.

 

6.

From the Execution UI Settings section in the Object Details panel, uncheck the Run Standalone property.

 

7.

After, you changed the property settings, run the expert. Click Start from the toolbar on the Expert Editor. Click Yes in the Expert Warning dialog.

Note: The Expert now executes with the Design Center in the background. Click OK in the Intro dialog. The Expert ends, but the Design Center remains open.

 

Back to Topic

Back to Topic List

Creating an Advanced Expert

In this topic you perform the following tasks:

Attaching an Expert to the Right-mouse Click Menu
Attaching an Expert to a Menu in the Design Center
Completing the ADVANCED_EXPERT

Back to Topic List

Attaching an Expert to the Right-mouse Click Menu

In this topic you create an expert that automates a common task. Each time, instead of creating a mapping and adding a source table, you create an expert, when you right-click on any table, a mapping will automatically be created with the selected source table.

You learn how to attach an expert to the right-mouse click menu (the shortcut menu that appears when you right-click an object). In this example, the ADVANCED_EXPERT expert is available as a menu item when you right-click any table in the OE module.

The Expert needs to know which table the user right-clicked. Use variables to store this information.

1.

In the Design Center, expand the HANDSON project. Right-click on the EXPERT_MODULE node, from the shortcut menu select New.

 

2.

In the Create Expert dialog, enter ADVANCED_EXPERT as the name of the expert. Click OK.

 

3.

In the Expert Editor window, from the Palette (lower left hand panel), drag and drop onto the Expert panel (canvas) a Custom Dialog.

 

4.

From the Palette (lower left hand panel), drag and drop onto the canvas an OMB task.

 

5.

Connect the tasks as shown below in the screenshot:

 

6.

There are some variables that tell the Expert where it was run from. For debug purposes, display the values of these variables.

With the CUSTOM_DIALOG_TASK task selected, click the Dialog Builder tabbed page from the Task Editor panel.

 

7.

In the Title field enter Debug as the title.

Click the Add button located on the bottom right hand corner of the Task Editor panel to add a label. Add four labels (click the Add button four times) and type the following values (each one on a new row) in the Content field:

Initial Launch Context = $EXP_LAUNCH_CONTEXT

Initial Launch Context Type = $EXP_LAUNCH_CONTEXT_TYPE

Initial Operation = $EXP_OPERATION

Initial Type to Create = $EXP_TYPE_TO_CREATE

Note: The variable names are case sensitive and must be in uppercase.

 

8.

Click on ADVANCED_EXPERT node in the Explorer panel. In the Object Details panel, uncheck all the properties in the Execution UI Settings section.

 

9.

On the canvas, click on the OMB task. Click on the Main tabbed page from the Task Editor panel. Type the following code:

puts Hello

Note: For now, you are writing a simple debug message to ensure the task is valid. You will write meaningful code later.

 

10.

In the Expert Editor from the Expert menu click Save All, to save your work. Click Yes in the Warehouse Builder Warning dialog.

To validate the expert, from the Expert menu, select Validate. Ensure there are no errors. You can ignore warnings about goals and instructions not specified.

To close the Expert Editor, from the Expert menu select Close.

 

11.

In the Design Center, expand HANDSON project. Expand Databases > Oracle > OE >Tables.

 

12.

Right mouse click any table in the OE module and select Add/Remove Experts Here.

 

13.

In the Add/Remove Experts dialog, expand HANDSON > EXPERT_MODULE. Click on the checkbox to the left of ADVANCED_EXPERT and click OK.

Save your work. In the Design Center, from the Design menu select Save All. Click Yes in the Warehouse Builder Warning dialog.

 

14.

Now, right-click any table under the OE module. You see the ADVANCED_EXPERT as a menu item. Click ADVANCED_EXPERT.

The Debug custom dialog appears with the variable values displayed. Click OK in the Debug dialog.

Note: The variables captures the full path of the selected object, the type and the operation. In this example the Expert is attached to an instance of the object (tables). If the Expert was attached to the Tables node itself, then the Initial Type to Create would have been set.

 

Back to Topic

Attaching an Expert to a Menu in the Design Center

In addition to attaching Experts to the Right-mouse click menu, Experts may also be attached to the Tools menu in the Design Center. When you are attaching an expert to a menu in the Design Center the expert must be a public expert. Now, create a public expert. All the experts you created so far are private experts.

1.

You create a simple public expert so that you learn how to attach an expert to a menu in the Design Center.

In the Design Center, right-click the Public Experts node from the Global Explorer panel. From the short-cut menu select New.

 

2.

In the Create Expert Module dialog, type test (not case sensitive, OWB automatically changes it to uppercase) as the Name and in the Description field enter The TEST expert module is a public expert module. Ensure the Proceed to Expert Wizard checkbox is clicked and click OK.

 

3.

In the Create Expert dialog, type EXPERT1 (not case sensitive, OWB automatically converts this to uppercase) as the Name and click OK.

 

4.

In the Expert Editor window, from the Palette (lower left hand panel), drag and drop onto the Expert panel (canvas) a Custom Dialog.

 

5.

On the canvas, click the CUSTOM_DIALOG_TASK task. Click the Dialog Builder tabbed page from the Task Editor panel. On the Dialog Builder tabbed page, in the Title field enter Public Expert.

 

6.

Connect the tasks as follows:

 

7.

Save your work and close the Expert Editor.

 

8.

In the Design Center from the Tools menu select Add/Remove Experts Here.

 

9.

In the Add/Remove Experts dialog, expand PUBLIC_PROJECT >TEST. Click the checkbox to the left of EXPERT1 and click OK.

 

10.

Now in the Design Center click the Tools menu, you see EXPERT1 as a menu item.

 

Back to Topic

Completing the ADVANCED_EXPERT

Now, add the code to perform the map creation and drop the selected table in the mapping editor.

1.

From the Project Explorer panel, double-click the ADVANCED_EXPERT. The Expert Editor launches.

 

2.

On the canvas, click the OMB_TASK task.

Click the Main tabbed page from the Task Editor panel. Delete puts hello and type the following code: (tcl script and OMB statements)

if {$EXP_LAUNCH_CONTEXT !="" && $EXP_LAUNCH_CONTEXT_TYPE !=""} {
set thetable [lindex [split $EXP_LAUNCH_CONTEXT /] end]
set thepath [string range $EXP_LAUNCH_CONTEXT 0 [expr [string last $thetable $EXP_LAUNCH_CONTEXT] -1]]

OMBCC '$thepath'

catch {OMBDROP MAPPING 'M1'}

OMBCREATE MAPPING 'M1'

OMBALTER MAPPING 'M1' \
ADD $EXP_LAUNCH_CONTEXT_TYPE OPERATOR '$thetable' \
BOUND TO $EXP_LAUNCH_CONTEXT_TYPE \
'$EXP_LAUNCH_CONTEXT'
}

ALERT: Any line breaks within a single OMB or Tcl statement MUST have the \ (backslash) continuation character for Tcl to interpret the whole thing. Important to note here is spacing, case, and the continuation character.

Please see the comments for the code interpretation.

--Check the variables are not null (which can occur if the expert was NOT executed from the right-mouse click menu)
if {$EXP_LAUNCH_CONTEXT !="" && $EXP_LAUNCH_CONTEXT_TYPE !=""}

--Extract the table name from the INITIAL_LAUNCH_CONTEXT variable
set thetable [lindex [split $EXP_LAUNCH_CONTEXT /] end]

--Extract the path to the table from the INITIAL_LAUNCH_CONTEXT variable
set thepath [string range $EXP_LAUNCH_CONTEXT 0 [expr [string last $thetable $EXP_LAUNCH_CONTEXT] -1]]

--Change location to the module where the table is located
OMBCC '$thepath'

--Drop the mapping M1 if it exists
catch {OMBDROP MAPPING 'M1'}

--Create a mapping called M1
OMBCREATE MAPPING 'M1'

--Add an operator of the same type as the object we clicked on. Then bind this operator to the actual object we clicked on
OMBALTER MAPPING 'M1' \
ADD $EXP_LAUNCH_CONTEXT_TYPE OPERATOR '$thetable' \
BOUND TO $EXP_LAUNCH_CONTEXT_TYPE \
'$EXP_LAUNCH_CONTEXT'

 

3.

To see the map that has been created, drag and drop onto the canvas a Mapping task. Position it after the OMB task.

 

4.

Delete the transition from the OMB_TASK to END_TASK and connect the following tasks as shown:

 

5.

In the Explorer panel, with Selected Objects tab selected, expand MAPPING_TASK. Click on the OBJECT_TO_EDIT node. In the Object Details panel click on the field next to the Value property and type M1.

Note: Here you specify the name of the mapping for the OBJECT_TO_EDIT property.

 

6.

In the Explorer panel, with MAPPING_TASK selected, click on the OPERATION node. In the Object Details panel click on the field next to the Value property and type EDIT.

 

7.

In the Explorer panel, with MAPPING_TASK selected, click on the PARENT_CONTEXT node. In the Object Details panel click on the field next to the Value property and type . (a period/fullstop). This is to say that the mapping is located in the current location.

 

8.

From the Expert menu click Validate. Ensure there are no errors. You can ignore warnings about goals and instructions not specified.

 

9.

From the Explorer panel, click the Selected Objects tab. Click on ADVANCED_EXPERTS node. In the Object Details panel scroll down to the Quick Launch section. Click in the field to the right of Menu Item Display String and type Create map with this table. This is the string that appears on the right-mouse menu instead of ADVANCED_EXPERTS.

 

10.

With the ADVANCED_EXPERT node selected in the Explorer panel, from the Object Details panel check Only Run from Menu checkbox. This prevents the Expert from running as a standalone.

 

11.

Save your work. Close the Expert Editor.

 

12.

In the Design Center, right-click ADVANCED_EXPERT and notice the Start menu item has disappeared.

 

13.

In the Project Explorer expand HANDSON > Databases > Oracle > OE > Tables. Right-click any table and notice that the menu item changed from ADVANCED_EXPERT to Create Map with this table.

Click the Create Map with this table option

 

14.

Click OK in the Debug dialog box that shows the properties of the node that the Expert was invoked from.

The mapping editor launches with the table you right-clicked on. This may take a minute or two.

This simple expert can save you quite some time. Rather than creating the mapping and adding the table, you simply find your table and right mouse click.

 

15.

Close the Mapping Editor. Save your work and close the Design Center.

 

Back to Topic

Back to Topic List

In this tutorial, you learned how to:

Create a simple Error Handler expert

Create Nested Experts

Execute experts from within OWB, and from outside OWB

Create Advanced Experts

Back to Topic List

Place the cursor over this icon to hide all screenshots.