19 Oracle Warehouse Builder Transformations

As you design mappings and process flows, you may want to use specialized transformations to transform data. This chapter how to create your own transformations as well as how to use the predefined transformations provided by Warehouse Builder.

This chapter contains the following topics:

Defining Custom Transformations

Custom transformations include procedures, functions, and packages. Warehouse Builder provides wizards to create each type of custom transformation. Custom transformations can belong to the Global Shared Library or to a module in a project.

Custom Transformations in the Global Shared Library

Custom transformations that are part of the Global Shared Library can be used across all projects of the workspace in which they are defined. For example, you create a function called ADD_EMPL in the Global Shared Library of the workspace REP_OWNER. This procedure can be used across all the projects in REP_OWNER.

Use the Custom node of the Public Transformations node in the Global Explorer to define custom transformations that can be used across all projects in the workspace. Figure 19-1 displays the Global Explorer used to create such transformations.

To create a custom transformation in the Global Shared Library:

  1. From the Global Explorer, expand the Public Transformations node and then the Custom node.

    Warehouse Builder displays the type of transformations that you can create. This includes functions, procedures, and packages. Note that PL/SQL types can be created only as part of a package.

  2. Right-click the type of transformation you want to define and select New.

    For example, to create a function, right-click Functions and select New. To create PL/SQL types, expand the package in which you want to create the PL/SQL type, right-click PL/SQL Types and select New.

  3. For functions and procedures, Warehouse Builder displays the Welcome page of the Create Function Wizard or the Create Procedure wizard respectively. For PL/SQL types, Warehouse Builder displays the Welcome page of the Create PL/SQL Type Wizard.

    Click Next to proceed. See "Defining Functions and Procedures" for more information about the other pages in the wizard. For more information about creating PL/SQL types, see "Defining PL/SQL Types".

    For packages, Warehouse Builder displays the Create Transformation Library dialog box. Provide a name and an optional description for the package and click OK. The new package is added to the Packages node. You can subsequently create procedures, functions, or PL/SQL types that belong to this package.

Custom Transformations in a Project

Sometimes, you may need to define custom transformations that are required only in the current module or project. In this case, you can define custom transformations in an Oracle module of a project. Such custom transformations are accessible from all the projects in the current workspace. For example, consider the workspace owner called REP_OWNER that contains two projects PROJECT1 and PROJECT2. In the Oracle module called SALES of PROJECT1, you define a procedure called CALC_SAL. This procedure can be used in all modules belonging to PROJECT1, but is not accessible in PROJECT2.

Figure 19-1 displays the Project Explorer from which you can create custom transformations that are accessible within the project in which they are defined.

Figure 19-1 Creating Custom Transformations in an Oracle Module

Description of Figure 19-1 follows
Description of "Figure 19-1 Creating Custom Transformations in an Oracle Module"

To define a custom transformation in an Oracle module:

  1. From the Project Explorer, expand the Oracle warehouse module node and then the Transformations node.

  2. Right-click the type of transformation you want to create and select New.

    For example, to create a package, right-click Packages and select New. To create PL/SQL types, expand the package node under which you want to create the type, right-click PL/SQL Types and select New.

    For functions or procedures, Warehouse Builder displays the Welcome page of the Create Function Wizard or the Create Procedure Wizard respectively. For PL/SQL Types, the Welcome page of the Create PL/SQL Type Wizard is displayed. Click Next to proceed.

    See "Defining Functions and Procedures" for information about the remaining wizard pages. For more information about creating PL/SQL types, see "Defining PL/SQL Types".

    For packages, Warehouse Builder opens the Create Transformation Library dialog box. Provide a name and an optional description for the package and click OK. The package is added under the Packages node. You can subsequently create procedures, functions, or PL/SQL types that belong to this package.

Defining Functions and Procedures

Use the following pages of the Create Function Wizard or Create Procedure Wizard to define a function or procedure.

Name and Description Page

You use the Name and Description page to describe the custom transformation. Specify the following details on this page:

  • Name: Represents the name of the custom transformation. For more information about naming conventions, see "Naming Conventions for Data Objects".

  • Description: Represents the description of the custom transformation. This is an optional field.

  • Return Type: Represents the data type of the value returned by the function. You select a return type from the available options in the list. This field is applicable only for functions.

Parameters Page

Use the Parameters page to define the parameters, both input and output, of the transformation. Specify the following details for each parameter:

  • Name: Enter the name of the parameter.

  • Type: Select the data type of the parameter from the list.

  • I/O: Select the type of parameter. The options available are Input, Output, and Input/Output.

  • Required: Select Yes to indicate that a parameter is mandatory and No to indicate that it is not mandatory.

  • Default Value: Enter the default value for the parameter. The default value is used when you do not specify a value for the parameter at the time of executing the function or procedure.

Implementation Page

Use the Implementation page to specify the implementation details, such as the code, of the transformation. To specify the code used to implement the function or procedure, click Code Editor. Warehouse Builder displays the Code Editor window. This editor contains two panels. The upper panel displays the code and the lower panel displays the function signature and messages.

When you create a function, the following additional options are displayed:

  • Function is deterministic: This hint helps to avoid redundant function calls. If a stored function was called previously with the same arguments, the previous result can be used. The function result should not depend on the state of session variables or schema objects. Otherwise, results might vary across calls. Only DETERMINISTIC functions can be called from a function-based index or a materialized view that has query-rewrite enabled.

  • Enable function for parallel execution: This option declares that a stored function can be used safely in the child sessions of parallel DML evaluations. The state of a main (logon) session is never shared with child sessions. Each child session has its own state, which is initialized when the session begins. The function result should not depend on the state of session (static) variables. Otherwise, results might vary across sessions.

Summary Page

The Summary page provides a summary of the options that you chose on the previous wizard pages. Click Finish to complete defining the function or procedure. Warehouse Builder creates the function or procedure and displays it under the corresponding folder under the Public Transformations and Custom nodes in the Global Explorer.

Defining PL/SQL Types

Use the Create PL/SQL Type Wizard to create PL/SQL types. PL/SQL types must be defined within a package and they cannot exist independently.

About PL/SQL Types

PL/SQL types enable you to create collection types, record types, and REF cursor types in Warehouse Builder. You use PL/SQL types as parameters in subprograms or as return types for functions. Using PL/SQL types as parameters to subprograms enables you to process arbitrary number of elements. Use collection types to move data into and out of database tables using bulk SQL. For more information about PL/SQL types, see Oracle Database PL/SQL Language Reference.

Warehouse Builder enables you to create the following PL/SQL types:

  • PL/SQL Record types

    Record types enable you to define records in a package. A record is a composite data structure that contains multiple fields. Use records to hold related items and pass them to subprograms using a single parameter.

    For example, an EMPLOYEE record can contain details related to an employee such as ID, first name, last name, address, date of birth, date of joining, and salary. You can create a record type based on the EMPLOYEE record and use this record type to pass employee data between subprograms.

  • REF Cursor types

    REF cursor types enable you to define REF cursors within a package. REF cursors are not bound to a single query and can point to different result sets. Use REF cursors when you want to perform a query in one subprogram and process the results in another subprogram. REF cursors also enable you to pass query result sets between PL/SQL stored subprograms and various clients such as an OCI client or an Oracle Forms application.

    REF cursors are available to all PL/SQL clients. For example, you can declare a REF cursor in a PL/SQL host environment such as an OCI or Pro*C program, then pass it as an input host variable (bind variable) to PL/SQL. Application development tools such as Oracle Forms, which have a PL/SQL engine, can use cursor variables entirely on the client side. Or, you can pass cursor variables back and forth between a client and the database server through remote procedure calls.

  • Nested Table types

    Use nested table types to define nested tables within a package. A nested table is an unordered set of elements, all of the same data type. They are similar to one-dimensional arrays with no declared number of elements. Nested tables enable you to model multidimensional arrays by creating a nested table whose elements are also tables.

    For example, you can create a nested table type that can hold an arbitrary number of employee IDs. This nested table type can then be passed as a parameter to a subprogram that processes only the employee records contained in the nested table type.

Usage Scenario for PL/SQL Types

The SALES table stores the daily sales of an organization that has offices across the world. This table contains the sale ID, sale date, customer ID, product ID, amount sold, quantity sold, and currency in which the sale was made. Management wants to analyze global sales for a specified time period using a single currency, for example the US Dollar. Thus all sales values must be converted to US Dollar. Since the currency exchange rates can change every day, the sales amounts must be computed using the exchange rate of the sale currency on the sale date.

Solution Using PL/SQL Record Types 

Figure 19-2 displays the mapping that you use to obtain the sales amount in a specified currency using PL/SQL record types

Figure 19-2 PL/SQL Record Type in a Mapping

Description of Figure 19-2 follows
Description of "Figure 19-2 PL/SQL Record Type in a Mapping"

The mapping takes the individual sales data stored in different currencies, obtains the sales value in the specified currency, and loads this data into a target table. Use the following steps to create this mapping.

  1. In the Global Explorer, create a package. In this package, create a procedure called CURR_CONV_PROC.

    This procedure obtains the currency conversion values on each date in a specified time interval from a Web site. The input parameters of this procedure are the sales currency, the currency to which the sale value needs to be converted, and the time interval for which the currency conversion is required. This data is stored in a PL/SQL record type of type CURR_CONV_REC. This record type contains two attributes: date and conversion value.

    You create the PL/SQL record type as part of the package.

  2. Create a mapping that contains a Transformation operator. This operator is bound to the CURR_CONV_PROC procedure.

  3. Use a Mapping Input Parameter operator to provide values for the input parameters of the Transformation operator.

    The output group of the Transformation operator is a PL/SQL record type of type CURR_CONV_REC.

  4. Use an Expand Object operator to obtain the individual values stored in this record type and store these values in the table CURRENCY_TAB.

  5. Use an Aggregator operator to aggregate sales details for each order.

    The SALES table is a transactional table and stores data in normalized form. To obtain the aggregate sales for each order, use an Aggregator operator to aggregate sales data.

  6. Use a Joiner operator to join the aggregated sales details, which is the output of the Aggregator operator, with the data in the CURRENCY_TAB table. The sale date is used as the join condition.

  7. Use the Expression operator to multiply the sales amount with the currency exchange rate to get the total sales in the required currency. Load the converted sales data into the CONV_SALES_TAB table.

Creating PL/SQL Types

You can create PL/SQL types in the Project Explorer and Global Explorer of the Design Center. For more details about creating PL/SQL types, see "Defining PL/SQL Types".

Use the Create PL/SQL Types Wizard to create PL/SQL types. The wizard guides you through the following pages:

Name and Description Page

Use the Name and Description page to provide the name and an optional description for the PL/SQL type. Also use this page to select the type of PL/SQL type you want to create.

You can create any of the following PL/SQL types:

  • PL/SQL record type

  • REF cursor type

  • Nested table type

For more information about each PL/SQL type, see "About PL/SQL Types".

After specifying the name and selecting the type of PL/SQL type to create, click Next.

Attributes Page

Use the Attributes page to define the attributes of the PL/SQL record type. You specify attributes only for PL/SQL record types. A PL/SQL record must have at least one attribute.

For each attribute, define the following:

  • Name: The name of the attribute. The name should be unique within the record type.

  • Type: The data type of the attribute. Select the data type from the list.

  • Length: The length of the data type, for character data types.

  • Precision: The total number of digits allowed for the attribute, for numeric data types.

  • Scale: The total number of digits to the right of the decimal point, for numeric data types.

  • Seconds Precision: The number of digits in the fractional part of the datetime field. It can be a number between 0 and 9. The Seconds Precision is used only for TIMESTAMP data types.

Click Next to proceed to the next step.

Return Type Page

Use the Return Type page to select the return type of the PL/SQL type. You must specify a return type when you create REF cursors and nested tables.

To define REF cursors:

The return type for a REF cursor can only be a PL/SQL record type. If you know the name of the PL/SQL record type, you can search for it by typing the name in the Search For field and clicking Go.

The area below the Search For field displays the available PL/SQL types. These PL/SQL types are grouped under the two nodes: Public and Private. Expand the Public node to view the PL/SQL types that are part of the Oracle Shared Library. The types are grouped by package name. The Private node contains PL/SQL types that are created as part of a package in an Oracle module. Only PL/SQL types that belong to the current project are displayed. Each Oracle module is represented by a node. Within the module, the PL/SQL types are grouped by the package to which they belong.

To define nested tables:

For nested tables, the return type can be a scalar data type or a PL/SQL record type. Select one of the following options based on what the PL/SQL type returns:

  • Select a scalar type as return type

    This option enables you to create a PL/SQL type that returns a scalar type. Use the list to select the data type.

  • Select a PL/SQL record as return type

    This option enables you to create a PL/SQL type that returns a PL/SQL record type. If you know the name of the PL/SQL record type that is returned, type the name in the Search For field and click Go. The results of the search are displayed in the area below the option.

    You can also select the return type from the list of available types displayed. The area below this option contains two nodes: Public and Private. The Public node contains PL/SQL record types that are part of the Oracle Shared Library. The PL/SQL record types are grouped by the package to which they belong. The Private node contains the PL/SQL record types created as transformations in each Oracle module in the current project. These are grouped by module. Select the PL/SQL record type that the PL/SQL type returns.

Click Next to proceed with the creation of the PL/SQL type.

Summary Page

The Summary page displays the options that you have chosen on the wizard pages. Review the options. Click Back to modify any options. Click Finish to create the PL/SQL type.

Editing Custom Transformations

You can edit the definition of a custom transformation using the editors. Make sure you edit properties consistently. For example, if you change the name of a parameter, then you must also change its name in the implementation code.

Editing Function or Procedure Definitions

The Edit Function dialog box enables you to edit function definitions. To edit a procedure definition, use the Edit Procedure dialog box.

Use the following steps to edit functions, procedures, or packages:

  1. From the Project Explorer, expand the Oracle module in which the transformation is created. Then expand the Transformations node.

    To edit a transformation that is part of the Global Shared Library, from the Global Explorer, expand the Public Transformations node, and then the Custom node.

  2. Right-click the name of the function, procedure, or package you want to edit and select Open Editor.

    For functions or procedures, the Edit Function or Edit Procedure dialog box is displayed. Use the following tabs to edit the function or procedure definition:

    For packages, Warehouse Builder displays the Edit Transformation Library dialog box. You can only edit the name and description of the package. You can edit the functions and procedures contained within the package using the steps used to edit functions or packages.

Name Tab

Use the Name tab to edit the name and description of the function or procedure. For functions, you can also edit the return data type.

Parameters Tab

Use the Parameters tab to edit, add, or delete new parameters for a function or procedure. You can also edit and define the attributes of the parameters. The contents of the Parameters tab are the same as that of the Parameters page of the Create Transformation Wizard. For more information about the contents of this page, see "Parameters Page".

Implementation Tab

Use the Implementation tab to review the PL/SQL code for the function or procedure. Click Code Editor to edit the code. The contents of the Implementation tab are the same as that of the Implementation page of the Create Transformation Wizard. For more information on the contents of the Implementation page, see "Implementation Page".

Editing PL/SQL Types

The Edit PL/SQL Type dialog box enables you to edit the definition of a PL/SQL type. Use the following steps to edit a PL/SQL type:

  1. From the Project Explorer, expand the Oracle module that contains the PL/SQL type. Then expand the Transformations node.

    To edit a PL/SQL type stored in the Global Shared Library, expand the Public Transformations node in the Global Explorer, and then the Custom node.

  2. Expand the package that contains the PL/SQL type and then the PL/SQL Types node.

  3. Right-click the name of the PL/SQL type that you want to edit and select Open Editor.

    The Edit PL/SQL Type dialog box is displayed. Use the following tabs to edit the PL/SQL type:

Name Tab

The Name tab displays the name and the description of the PL/SQL type. Use this tab to edit the name or the description of the PL/SQL type.

To rename a PL/SQL type, select the name and enter the new name.

Attributes Tab

The Attributes tab displays details about the existing attributes of the PL/SQL record type. This tab is displayed for PL/SQL record types only. You can modify existing attributes, add new attributes, or delete attributes.

To add a new attribute, click the Name column of a blank row specify the details for the attribute. To delete an attribute, right-click the gray cell to the left the row that represents the attribute and select Delete.

Return Type Tab

Use the Return Type tab to modify the details of the return type of the PL/SQL type. For a REF cursor type, the return type must be a PL/SQL record. For a nested table, the return type can be a PL/SQL record type or a scalar data type.

Administrative Transformations

Administrative transformations provide pre-built functionality to perform actions that are regularly performed in ETL processes. The main focus of these transformations is in the DBA related areas or to improve performance. For example, it is common to disable constraints when loading tables and then to re-enable them after loading has completed.

The administrative transformations in Warehouse Builder are custom functions. The Administrative transformation that Warehouse Builder provides are:

WB_ABORT

Syntax

WB_ABORT(p_code, p_message)

where p_code is the abort code, and must be between -20000 and -29999; and p_message is an abort message you specify.

Purpose

WB_ABORT enables you to terminate the application from a Warehouse Builder component. You can run it from a post mapping process or as a transformation within a mapping.

Example

Use this administration function to terminate an application. You can use this function in a post mapping process to terminate deployment if there is an error in the mapping.

WB_COMPILE_PLSQL

Syntax

WB_COMPILE_PLSQL(p_name, p_type)

where p_name is the name of the object that is to be compiled; p_type is the type of object to be compiled. The legal types are:

'PACKAGE'
'PACKAGE BODY'
'PROCEDURE'
'FUNCTION'
'TRIGGER'

Purpose

This program unit compiles a stored object in the database.

Example

The following hypothetical example compiles the procedure called add_employee_proc:

EXECUTE WB_COMPILE_PLSQL('ADD_EMPLOYEE_PROC', 'PROCEDURE');

WB_DISABLE_ALL_CONSTRAINTS

Syntax

WB_DISABLE_ALL_CONSTRAINTS(p_name)

where p_name is the name of the table on which constraints are disabled.

Purpose

This program unit disables all constraints that are owned by the table as stated in the call to the program.

For faster loading of data sets, you can disable constraints on a table. The data is now loaded without validation. This is mainly done on relatively clean data sets.

Example

The following example shows the disabling of the constraints on the table OE.CUSTOMERS:

SELECT constraint_name
,      DECODE(constraint_type,'C','Check','P','Primary') Type
,      status
FROM user_constraints
WHERE table_name = 'CUSTOMERS';
CONSTRAINT_NAME                TYPE    STATUS
------------------------------ ------- --------
CUST_FNAME_NN                  Check   ENABLED
CUST_LNAME_NN                  Check   ENABLED
CUSTOMER_CREDIT_LIMIT_MAX      Check   ENABLED
CUSTOMER_ID_MIN                Check   ENABLED
CUSTOMERS_PK                   Primary ENABLED

Perform the following in SQL*Plus or Warehouse Builder to disable all constraints:

EXECUTE WB_DISABLE_ALL_CONSTRAINTS('CUSTOMERS');

CONSTRAINT_NAME                TYPE    STATUS
------------------------------ ------- --------
CUST_FNAME_NN                  Check   DISABLED
CUST_LNAME_NN                  Check   DISABLED
CUSTOMER_CREDIT_LIMIT_MAX      Check   DISABLED
CUSTOMER_ID_MIN                Check   DISABLED
CUSTOMERS_PK                   Primary DISABLED

Note:

This statement uses a cascade option to allow dependencies to be broken by disabling the keys.

WB_DISABLE_ALL_TRIGGERS

Syntax

WB_DISABLE_ALL_TRIGGERS(p_name)

where p_name is the table name on which the triggers are disabled.

Purpose

This program unit disables all triggers owned by the table as stated in the call to the program. The owner of the table must be the current user (in variable USER). This action stops triggers and improves performance.

Example

The following example shows the disabling of all triggers on the table OE.OC_ORDERS:

SELECT trigger_name
,      status
FROM user_triggers
WHERE table_name = 'OC_ORDERS';

TRIGGER_NAME                   STATUS
------------------------------ --------
ORDERS_TRG                     ENABLED
ORDERS_ITEMS_TRG               ENABLED

Perform the following in SQL*Plus or Warehouse Builder to disable all triggers on the table OC_ORDERS.

EXECUTE WB_DISABLE_ALL_TRIGGERS ('OC_ORDERS');
TRIGGER_NAME                   STATUS
------------------------------ --------
ORDERS_TRG                     DISABLED
ORDERS_ITEMS_TRG               DISABLED

WB_DISABLE_CONSTRAINT

Syntax

WB_DISABLE_CONSTRAINT(p_constraintname, p_tablename)

where p_constraintname is the constraint name to be disabled; p_tablename is the table name on which the specified constraint is defined.

Purpose

This program unit disables the specified constraint that is owned by the table as stated in the call to the program. The user is the current user (in variable USER).

For faster loading of data sets, you can disable constraints on a table. The data is then loaded without validation. This reduces overhead and is mainly done on relatively clean data sets.

Example

The following example shows the disabling of the specified constraint on the table OE.CUSTOMERS:

SELECT constraint_name
, DECODE(constraint_type
, 'C', 'Check'
, 'P', 'Primary'
) Type
, status
FROM user_constraints
WHERE table_name = 'CUSTOMERS';

CONSTRAINT_NAME                TYPE    STATUS
------------------------------ ------- --------
CUST_FNAME_NN                  Check   ENABLED
CUST_LNAME_NN                  Check   ENABLED
CUSTOMER_CREDIT_LIMIT_MAX      Check   ENABLED
CUSTOMER_ID_MIN                Check   ENABLED
CUSTOMERS_PK                   Primary ENABLED

Perform the following in SQL*Plus or Warehouse Builder to disable the specified constraint.

EXECUTE WB_DISABLE_CONSTRAINT('CUSTOMERS_PK','CUSTOMERS');

CONSTRAINT_NAME                TYPE    STATUS
------------------------------ ------- --------
CUST_FNAME_NN                  Check   ENABLED
CUST_LNAME_NN                  Check   ENABLED
CUSTOMER_CREDIT_LIMIT_MAX      Check   ENABLED
CUSTOMER_ID_MIN                Check   ENABLED
CUSTOMERS_PK                   Primary DISABLED

Note:

This statement uses a cascade option to allow dependencies to be broken by disabling the keys.

WB_DISABLE_TRIGGER

Syntax

WB_DISABLE_TRIGGER(p_name)

where p_name is the trigger name to be disabled.

Purpose

This program unit disables the specified trigger. The owner of the trigger must be the current user (in variable USER).

Example

The following example shows the disabling of a trigger on the table OE.OC_ORDERS:

SELECT trigger_name, status
FROM user_triggers
WHERE table_name = 'OC_ORDERS';

TRIGGER_NAME                   STATUS
------------------------------ --------
ORDERS_TRG                     ENABLED
ORDERS_ITEMS_TRG               ENABLED

Perform the following in SQL*Plus or Warehouse Builder to disable the specified constraint.

ECECUTE WB_DISABLE_TRIGGER ('ORDERS_TRG');

TRIGGER_NAME                   STATUS
------------------------------ --------
ORDERS_TRG                     DISABLED
ORDERS_ITEMS_TRG               ENABLED

WB_ENABLE_ALL_CONSTRAINTS

Syntax

WB_ENABLE_ALL_CONSTRAINTS(p_name)

where p_name is the name of the table for which all constraints should be enabled.

Purpose

This program unit enables all constraints that are owned by the table as stated in the call to the program.

For faster loading of data sets, you can disable constraints on a table. After the data is loaded, you must enable these constraints again using this program unit.

Example

The following example shows the enabling of the constraints on the table OE.CUSTOMERS:

SELECT constraint_name
, DECODE(constraint_type
, 'C', 'Check'
, 'P', 'Primary)
Type
, status
FROM user_constraints
WHERE table_name = 'CUSTOMERS';

CONSTRAINT_NAME                TYPE    STATUS
------------------------------ ------- --------
CUST_FNAME_NN                  Check   DISABLED
CUST_LNAME_NN                  Check   DISABLED
CUSTOMER_CREDIT_LIMIT_MAX      Check   DISABLED
CUSTOMER_ID_MIN                Check   DISABLED
CUSTOMERS_PK                   Primary DISABLED

Perform the following in SQL*Plus or Warehouse Builder to enable all constraints.

EXECUTE WB_ENABLE_ALL_CONSTRAINTS('CUSTOMERS');

CONSTRAINT_NAME                TYPE    STATUS
------------------------------ ------- --------
CUST_FNAME_NN                  Check   ENABLED
CUST_LNAME_NN                  Check   ENABLED
CUSTOMER_CREDIT_LIMIT_MAX      Check   ENABLED
CUSTOMER_ID_MIN                Check   ENABLED
CUSTOMERS_PK                   Primary ENABLED

WB_ENABLE_ALL_TRIGGERS

Syntax

WB_ENABLE_ALL_TRIGGERS(p_name)

where p_name is the table name on which the triggers are enabled.

Purpose

This program unit enables all triggers owned by the table as stated in the call to the program. The owner of the table must be the current user (in variable USER).

Example

The following example shows the enabling of all triggers on the table OE.OC_ORDERS:

SELECT trigger_name
,      status
FROM user_triggers
WHERE table_name = 'OC_ORDERS';

TRIGGER_NAME                   STATUS
------------------------------ --------
ORDERS_TRG                     DISABLED
ORDERS_ITEMS_TRG               DISABLED

Perform the following in SQL*Plus or Warehouse Builder to enable all triggers defined on the table OE.OC_ORDERS.

EXECUTE WB_ENABLE_ALL_TRIGGERS ('OC_ORDERS');

TRIGGER_NAME                   STATUS
------------------------------ --------
ORDERS_TRG                     ENABLED
ORDERS_ITEMS_TRG               ENABLED

WB_ENABLE_CONSTRAINT

Syntax

WB_ENABLE_CONSTRAINT(p_constraintname, p_tablename)

where p_constraintname is the constraint name to be disabled and p_tablename is the table name on which the specified constraint is defined.

Purpose

This program unit enables the specified constraint that is owned by the table as stated in the call to the program. The user is the current user (in variable USER). For faster loading of data sets, you can disable constraints on a table. After the loading is complete, you must re-enable these constraints. This program unit shows you how to enable the constraints one at a time.

Example

The following example shows the enabling of the specified constraint on the table OE.CUSTOMERS:

SELECT constraint_name
,      DECODE(constraint_type
       , 'C', 'Check'
       , 'P', 'Primary'
       ) Type
,      status
FROM user_constraints
WHERE table_name = 'CUSTOMERS';

CONSTRAINT_NAME                TYPE    STATUS
------------------------------ ------- --------
CUST_FNAME_NN                  Check   DISABLED
CUST_LNAME_NN                  Check   DISABLED
CUSTOMER_CREDIT_LIMIT_MAX      Check   DISABLED
CUSTOMER_ID_MIN                Check   DISABLED
CUSTOMERS_PK                   Primary DISABLED

Perform the following in SQL*Plus or Warehouse Builder to enable the specified constraint.

EXECUTE WB_ENABLE_CONSTRAINT('CUSTOMERS_PK', 'CUSTOMERS');

CONSTRAINT_NAME                TYPE    STATUS
------------------------------ ------- --------
CUST_FNAME_NN                  Check   DISABLED
CUST_LNAME_NN                  Check   DISABLED
CUSTOMER_CREDIT_LIMIT_MAX      Check   DISABLED
CUSTOMER_ID_MIN                Check   DISABLED
CUSTOMERS_PK                   Primary ENABLED

WB_ENABLE_TRIGGER

Syntax

WB_ENABLE_TRIGGER(p_name)

where p_name is the trigger name to be enabled.

Purpose

This program unit enables the specified trigger. The owner of the trigger must be the current user (in variable USER).

Example

The following example shows the enabling of a trigger on the table OE.OC_ORDERS:

SELECT trigger_name
,      status
FROM user_triggers
WHERE table_name = 'OC_ORDERS';

TRIGGER_NAME                   STATUS
------------------------------ --------
ORDERS_TRG                     DISABLED
ORDERS_ITEMS_TRG               ENABLED

Perform the following in SQL*Plus or Warehouse Builder to enable the specified constraint.

EXECUTE WB_ENABLE_TRIGGER ('ORDERS_TRG');

TRIGGER_NAME                   STATUS
------------------------------ --------
ORDERS_TRG                     ENABLED
ORDERS_ITEMS_TRG               ENABLED

WB_TRUNCATE_TABLE

Syntax

WB_TRUNCATE_TABLE(p_name)

where p_name is the table name to be truncated.

Purpose

This program unit truncates the table specified in the command call. The owner of the trigger must be the current user (in variable USER). The command disables and re-enables all referencing constraints to enable the truncate table command. Use this command in a pre-mapping process to explicitly truncate a staging table and ensure that all data in this staging table is newly loaded data.

Example

The following example shows the truncation of the table OE.OC_ORDERS:

SELECT COUNT(*) FROM oc_orders;

  COUNT(*)
----------
       105

Perform the following in SQL*Plus or Warehouse Builder to enable the specified constraint.

EXECUTE WB_TRUNCATE_TABLE ('OC_ORDERS');

  COUNT(*)
----------
         0

Character Transformations

Character transformations enable Warehouse Builder users to perform transformations on Character objects. The custom functions provided with Warehouse Builder are prefixed with WB_.

Character transformations include implementations of basic Oracle Database SQL functions or procedures and custom transformations provided by Warehouse Builder.

Table 19-1 lists the character transformations that are based on Database SQL functions. The transformations are listed in a columnar table that reads down the columns from left to right to conserve space. For descriptions and examples of these transformations, refer to section "Character Functions" in the Oracle Database SQL Language Reference.

Table 19-1 Character Transformations Based on SQL character functions

Character Transformation Name Character Transformation Name (Contd.) Character Transformation Name (Contd.)
  • ASCII

  • CHR

  • CONCAT

  • INITCAP

  • INSTR

  • INSTR2

  • INSTR4

  • INSTRB

  • INSTRC

  • LENGTH

  • LENGTH2

  • LENGTH4

  • LENGTHB

  • LENGTHC

  • LOWER

  • LPAD

  • LTRIM

  • NLSSORT

  • NLS_INITCAP

  • NLS_LOWER

  • NLS_UPPER

  • REPLACE

  • REGEXP_INSTR

  • REGEXP_REPLACE

  • REGEXP_SUBSTR

  • RPAD

  • RTRIM

  • SOUNDEX

  • SUBSTR

  • SUBSTR2

  • SUBSTR4

  • SUBSTRB

  • SUBSTRC

  • TRANSLATE

  • TRIM

  • UPPER


Following is the list of custom character transformations.

WB_LOOKUP_CHAR (number)

Syntax

WB.LOOKUP_CHAR (table_name
, column_name
, key_column_name
, key_value
)

where table_name is the name of the table to perform the lookup on and column_name is the name of the VARCHAR2 column that will be returned. For example, the result of the lookup key_column_name is the name of the NUMBER column used as the key to match on in the lookup table, key_value is the value of the key column mapped into the key_column_name with which the match will be done.

Purpose

To perform a key lookup on a number that returns a VARCHAR2 value from a database table using a NUMBER column as the matching key.

Example

Consider the following table as a lookup table LKP1:

KEY_COLUMN    TYPE    COLOR
10            Car     Red
20            Bike    Green

Using this package with the following call:

WB.LOOKUP_CHAR ('LKP1'
, 'TYPE'
, 'KEYCOLUMN'
, 20
)

returns the value of 'Bike' as output of this transform. This output would then be processed in the mapping as the result of an inline function call.

Note:

This function is a row-based key lookup. Set-based lookups are supported when you use the lookup operator.

WB_LOOKUP_CHAR (varchar2)

Syntax

WB.LOOKUP_CHAR (table_name
, column_name
, key_column_name
, key_value
)

where table_name is the name of the table to perform the lookup on; column_name is the name of the VARCHAR2 column that will be returned, for instance, the result of the lookup; key_column_name is the name of the VARCHAR2 column used as the key to match on in the lookup table; key_value is the value of the key column, for instance, the value mapped into the key_column_name with which the match will be done.

Purpose

To perform a key lookup on a VARCHAR2 character that returns a VARCHAR2 value from a database table using a VARCHAR2 column as the matching key.

Example

Consider the following table as a lookup table LKP1:

KEYCOLUMN  TYPE  COLOR
ACV        Car   Red
ACP        Bike  Green

Using this package with the following call:

WB.LOOKUP_CHAR ('LKP1'
, 'TYPE'
, 'KEYCOLUMN'
, 'ACP'
)

returns the value of 'Bike' as output of this transformation. This output is then processed in the mapping as the result of an inline function call.

Note:

This function is a row-based key lookup. Set-based lookups are supported when you use the lookup operator.

WB_IS_SPACE

Syntax

WB_IS_SPACE(attibute)

Purpose

Checks whether a string value only contains spaces. This function returns a Boolean value. In mainframe sources, some fields contain many spaces to make a file adhere to the fixed length format. This function provides a way to check for these spaces.

Example

WB_IS_SPACE returns TRUE if attribute contains only spaces.

Control Center Transformations

Control Center transformations are used in a process flow or in custom transformations to enable you to access information about the Control Center at execution time. For example, you can use a Control Center transformation in the expression on a transition to help control the flow through a process flow at execution time. You can also use Control Center transformations within custom functions. These custom functions can in turn be used in the design of your process flow.

All Control Center transformations require an audit ID that provides a handle to the audit data stored in the Control Center workspace. The audit ID is a key into the public view ALL_RT_AUDIT_EXECUTIONS. The transformations can be used to obtain data specific to that audit ID at execution time. When run in the context of a process flow, you can obtain the audit ID at execution time using the pseudo variable audit_id in a process flow expression. This variable is evaluated as the audit ID of the currently executing job. For example, for a map input parameter, this represents the map execution and for a transition this represents the job at the source of the transition.

The Control Center transformations are:

WB_RT_GET_ELAPSED_TIME

Syntax

WB_RT_GET_ELAPSED_TIME(audit_id)

Purpose

This function returns the elapsed time, in seconds, for the job execution given by the specified audit_id. It returns null if the specified audit ID does not exist. For example, you can use this function on a transition if you want to make a choice dependent on the time taken by the previous activity.

Example

The following example returns the time elapsed since the activity represented by audit_id was started:

declare
   audit_id NUMBER := 1812;
   l_time NUMBER;
begin
   l_time:= WB_RT_GET_ELAPSED_TIME(audit_id);
end;

WB_RT_GET_JOB_METRICS

Syntax

WB_RT_GET_JOB_METRICS(audit_id, no_selected, no_deleted, no_updated, no_inserted, no_discarded, no_merged, no_corrected)

where no_selected represents the number of rows selected, no_deleted represents the number of rows deleted, no_updated represents the number of rows updated, no_inserted represents the number of rows inserted, no_discarded represents the number of rows discarded, no_merged represents the number of rows merged, and no_corrected represents the number of rows corrected during the job execution.

Purpose

This procedure returns the metrics of the job execution represented by the specified audit_id. The metrics include the number of rows selected, deleted, updated, inserted, discarded, merged, and corrected.

Example

The following example retrieves the job metrics for the audit ID represented by audit_id.

declare
   audit_id NUMBER := 16547;
   l_nselected NUMBER;
   l_ndeleted NUMBER;
   l_nupdated NUMBER;
   l_ninserted NUMBER;
   l_ndiscarded NUMBER;
   l_nmerged NUMBER;
   l_ncorrected NUMBER;
begin
   WB_RT_GET_JOB_METRICS(audit_id, l_nselected, l_ndeleted, l_nupdated,
                         l_ninserted, l_ndiscarded, l_nmerged, l_ncorrected);
   dbms_output.put_line('sel=' || l_nselected || ', del=' l_ndeleted ||
                        ', upd=' || l_nupdated);
   dbms_output.put_line('ins='|| l_ninserted || ' , dis=' || l_ndiscarded );
   dbms_output.put_line('mer=' || l_nmerged || ', cor=' ||l_ncorrected);
 end;

WB_RT_GET_LAST_EXECUTION_TIME

Syntax

WB_RT_GET_LAST_EXECUTION_TIME(objectName, objectType, objectLocationName)

where objectName represents the name of the object, objectType represents the type of the object (for example MAPPING, DATA_AUDITOR, PROCESS_FLOW, SCHEDULABLE), and objectLocationName represents the location to which the object is deployed.

Purpose

This transformation gives you access to time-based data. Typically, you can use this in a Process Flow to model some design aspect that is relevant to "time". For example you can design a path that may execute different maps if the time since the last execution is more than 1 day.

You can also use this transformation to determine time-synchronization across process flows that are running concurrently. For example, you can choose a path in a process flow according to whether another Process Flow has completed.

Example

The following example retrieves the time when the mapping TIMES_MAP was last executed and the if condition determines whether this time was within 1 day of the current time. Based on this time, it can perform different actions.

declare
    last_exec_time DATE;
begin
    last_exec_time:=WB_RT_GET_LAST_EXECUTION_TIME('TIMES_MAP','MAPPING','WH_LOCATION');
    if last_exec_time < sysdate - 1 then
--       last-execution was more than one day ago
--       provide details of action here
          NULL;
    Else
--       provide details of action here
          NULL;
    end if;
end;

WB_RT_GET_MAP_RUN_AUDIT

Syntax

WB_RT_GET_MAP_RUN_AUDIT(audit_id)

Purpose

This function returns the map run ID for a job execution that represents a map activity. It returns null if audit_id does not represent the job execution for a map. For example, you can use the returned ID as a key to access the ALL_RT_MAP_RUN_<name> views for more information.

Example

The following example retrieves the map run ID for a job execution whose audit ID is 67265. It then uses this map run ID to obtain the name of the source from the ALL_RT_MAP_RUN_EXECUTIONS public view.

declare
  audit_id NUMBER := 67265;
  l_sources VARCHAR2(256);
  l_run_id NUMBER;
begin
  l_run_id := WB_RT_GET_MAP_RUN_AUDIT_ID(audit_id);
  SELECT source_name INTO l_sources FROM all_rt_map_run_sources
         WHERE map_run_id = l_run_id;
end;

WB_RT_GET_NUMBER_OF_ERRORS

Syntax

WB_RT_GET_NUMBER_OF_ERRORS(audit_id)

Purpose

This function returns the number of errors recorded for the job execution given by the specified audit_id. It returns null if the specific audit_id is not found.

Example

The following example retrieves the number of errors generated by the job execution whose audit ID is 8769. You can then perform different actions based on the number of errors.

declare
   audit_id NUMBER := 8769;
   l_errors NUMBER;begin   l_errors := WB_RT_GET_NUMBER_OF_ERRORS(audit_id);
   if l_errors < 5 then
      .....
   else
      .....
   end if;
end;

WB_RT_GET_NUMBER_OF_WARNINGS

Syntax

WB_RT_GET_NUMBER_OF_WARNINGS(audit_id)

Purpose

This function returns the number of warnings recorded for the job executions represented by audit_id. It returns null if audit_id does not exist.

Example

The following example returns the number of warnings generated by the job execution whose audit ID is 54632. You can then perform different actions based on the number of warnings.

declare   audit_is NUMBER := 54632;
   l_warnings NUMBER;begin   l_ warnings:= WB_RT_GET_NUMBER_OF_WARNINGS (audit_id);
   if l_warnings < 5 then
      .....
   else
      .....
   end if;
end;

WB_RT_GET_PARENT_AUDIT_ID

Syntax

WB_RT_GET_PARENT_AUDIT_ID(audit_id)

Purpose

This function returns the audit id for the process that owns the job execution represented by audit_id. It returns null if audit_id does not exist. You can then use the returned audit id as a key into other public views such as ALL_RT_AUDIT_EXECUTIONS, or other Control Center transformations if further information is required.

Example

The following example retrieves the parent audit ID for a job execution whose audit ID is 76859. It then uses this audit ID to determine the elapsed time for the parent activity. You can perform different actions based on the elapsed time of the parent activity.

declare
   audit_id NUMBER := 76859;
   l_elapsed_time NUMBER;
   l_parent_id NUMBER;
begin
   l_parent_id := WB_RT_GET_PARENT_AUDIT_ID(audit_id);
   l_elapsed_time := WB_RT_GET_ELAPSED_TIME(l_parent_id);
   if l_elpased_time < 100 then
      .....
   else
      .....
   end if;
end;

WB_RT_GET_RETURN_CODE

Syntax

WB_RT_GET_RETURN_CODE(audit_id)

Purpose

This function returns the return code recorded for the job execution represented by audit_id. It returns null if audit_id does not exist. For a successful job execution, the return code is greater than or equal to 0. A return code of less than 0 signifies that the job execution has failed.

Example

The following example retrieves the return code for the job execution whose audit ID is represented by audit_id.

declare   audit_id NUMBER:=69;
   l_code NUMBER;begin   l_code:= WB_RT_GET_RETURN_CODE(audit_id);end;

WB_RT_GET_START_TIME

Syntax

WB_RT_GET_START_TIME(audit_id)

Purpose

This function returns the start time for the job execution represented by audit_id. It returns null if audit_id does not exist. For example, you can use this in a transition if you wanted to make a choice dependent on when the previous activity started.

Example

The following example determines the start time of the job execution whose audit ID is 354.

declare   audit_id NUMBER:=354;
   l_date TIMESTAMP WITH TIMEZONE;begin   l_date := WB_RT_GET_START_TIME(audit_id);end;

Conversion Transformations

Conversion transformations enable Warehouse Builder users to perform functions that allow conditional conversion of values. These functions achieve "if -then" constructions within SQL.

Conversion transformations in Warehouse Builder are implementations of the Oracle Database SQL conversion functions. For descriptions and examples of these transformations, see "Conversion Functions" in the Oracle Database SQL Language Reference.

  • ASCIISTR

  • COMPOSE

  • CONVERT

  • HEXTORAW

  • NUMTODSINTERVAL

  • NUMTOYMINTERVAL

  • RAWTOHEX

  • RAWTONHEX

  • SCN_TO_TIMESTAMP

  • TIMESTAMP_TO_SCN

  • TO_BINARY_DOUBLE

  • TO_BINARY_FLOAT

  • TO_CHAR (character), TO_CHAR (datetime), TO_CHAR (number)

  • TO_CLOB

  • TO_DATE

  • TO_DSINTERVAL

  • TO_MULTIBYTE

  • TO_NCHAR (character), TO_NCHAR (datetime), TO_NCHAR (number)

  • TO_NCLOB

  • TO_NUMBER

  • TO_SINGLE_BYTE

  • TO_TIMESTAMP

  • TO_TIMESTAMP_TZ

  • TO_YMINTERVAL

  • UNISTR

Date Transformations

Date transformations provide Warehouse Builder users with functionality to perform transformations on date attributes. These transformations include Oracle Database SQL functions that are implemented by Warehouse Builder and custom functions provided with Warehouse Builder. The custom function are in the format WB_<function name>.

Following are the date transformations that are implementations of Oracle Database SQL functions. For descriptions and examples of these transformations, refer to the section "Datetime Functions" in the Oracle Database SQL Language Reference.

  • ADD_MONTHS

  • CURRENT_DATE

  • DBTIMEZONE

  • FROM_TZ

  • LAST_DAY

  • MONTHS_BETWEEN

  • NEW_TIME

  • NEXT_DAY

  • ROUND

  • SESSIONTIMEZONE

  • SYSDATE

  • SYSTIMESTAMP

  • SYS_EXTRACT_UTC

  • TRUNC

The custom Warehouse Builder Date transformations are:

WB_CAL_MONTH_NAME

Syntax

WB_CAL_MONTH_NAME(attribute)

Purpose

The function call returns the full-length name of the month for the date specified in attribute.

Example

The following example shows the return value on the sysdate and on a specified date string:

SELECT WB_CAL_MONTH_NAME(sysdate)
   FROM DUAL;

WB_CAL_MONTH_NAME(SYSDATE)
----------------------------
March

SELECT WB_CAL_MONTH_NAME('26-MAR-2002')
   FROM DUAL;

WB_CAL_MONTH_NAME('26-MAR-2002')
----------------------------------
March

WB_CAL_MONTH_OF_YEAR

Syntax

WB_CAL_MONTH_OF_YEAR(attribute)

Purpose

WB_CAL_MONTH_OF_YEAR returns the month (1-12) of the year for date in attribute.

Example

The following example shows the return value on the sysdate and on a specified date string:

SELECT WB_CAL_MONTH_OF_YEAR(sysdate) month
   FROM DUAL;

     MONTH
----------
         3

SELECT WB_CAL_MONTH_OF_YEAR('26-MAR-2002') month
FROM DUAL;

     MONTH
----------
         3

WB_CAL_MONTH_SHORT_NAME

Syntax

WB_CAL_MONTH_SHORT_NAME(attribute)

Purpose

WB_CAL_MONTH_SHORT_NAME returns the short name of the month (for example 'Jan') for date in attribute.

Example

The following example shows the return value on the sysdate and on a specified date string:

SELECT WB_CAL_MONTH_SHORT_NAME (sysdate) month
FROM DUAL;

MONTH
---------
Mar

SELECT WB_CAL_MONTH_SHORT_NAME ('26-MAR-2002') month
FROM DUAL;

MONTH
---------
Mar

WB_CAL_QTR

Syntax

WB_CAL_QTR(attribute)

Purpose

WB_CAL_QTR returns the quarter of the Gregorian calendar year (for example Jan - March = 1) for the date in attribute.

Example

The following example shows the return value on the sysdate and on a specified date string:

SELECT WB_CAL_QTR (sysdate) quarter
FROM DUAL;

   QUARTER
----------
         1

SELECT WB_CAL_QTR ('26-MAR-2002') quarter
FROM DUAL;

   QUARTER
----------
         1

WB_CAL_WEEK_OF_YEAR

Syntax

WB_CAL_WEEK_OF_YEAR(attribute)

Purpose

WB_CAL_WEEK_OF_YEAR returns the week of the year (1-53) for the date in attribute.

Example

The following example shows the return value on the sysdate and on a specified date string:

SELECT WB_CAL_WEEK_OF_YEAR (sysdate) w_of_y
FROM DUAL;

    W_OF_Y
----------
        13

SELECT WB_CAL_WEEK_OF_YEAR ('26-MAR-2002') w_of_y
FROM DUAL;

    W_OF_Y
----------
        13

WB_CAL_YEAR

Syntax

WB_CAL_YEAR(attribute)

Purpose

WB_CAL_YEAR returns the numerical year component for the date in attribute.

Example

The following example shows the return value on the sysdate and on a specified date string:

SELECT WB_CAL_YEAR (sysdate) year
FROM DUAL;

      YEAR
----------
      2002

SELECT WB_CAL_YEAR ('26-MAR-2002') w_of_y
FROM DUAL;

      YEAR
----------
      2002

WB_CAL_YEAR_NAME

Syntax

WH_CAL_YEAR_NAME(attribute)

Purpose

WB_CAL_YEAR_NAME returns the spelled out name of the year for the date in attribute.

Example

The following example shows the return value on the sysdate and on a specified date string:

select WB_CAL_YEAR_NAME (sysdate) name
from dual;

NAME
----------------------------------------------
Two Thousand Two

select WB_CAL_YEAR_NAME ('26-MAR-2001') name
from dual;

NAME
----------------------------------------------
Two Thousand One

WB_DATE_FROM_JULIAN

Syntax

WB_DATE_FROM_JULIAN(attribute)

Purpose

WB_DATE_FROM_JULIAN converts Julian date attribute to a regular date.

Example

The following example shows the return value on a specified Julian date:

select to_char(WB_DATE_FROM_JULIAN(3217345),'dd-mon-yyyy') JDate 
from dual;

JDATE
-----------
08-sep-4096

WB_DAY_NAME

Syntax

WB_DAY_NAME(attribute)

Purpose

WB_DAY_NAME returns the full name of the day for the date in attribute.

Example

The following example shows the return value on the sysdate and on a specified date string:

select WB_DAY_NAME (sysdate) name
from dual;

NAME
--------------------------------------------
Thursday

select WB_DAY_NAME ('26-MAR-2002') name
from dual;

NAME
--------------------------------------------
Tuesday

WB_DAY_OF_MONTH

Syntax

WB_DAY_OF_MONTH(attribute)

Purpose

WB_DAY_OF_MONTH returns the day number within the month for the date in attribute.

Example

The following example shows the return value on the sysdate and on a specified date string:

select WB_DAY_OF_MONTH (sysdate) num
from dual;

       NUM
----------
        28

select WB_DAY_OF_MONTH ('26-MAR-2002') num
from dual

       NUM
----------
        26

WB_DAY_OF_WEEK

Syntax

WB_DAY_OF_WEEK(attribute)

Purpose

WB_DAY_OF_WEEK returns the day number within the week for date attribute based on the database calendar.

Example

The following example shows the return value on the sysdate and on a specified date string:

select WB_DAY_OF_WEEK (sysdate) num
from dual;

       NUM
----------
         5

select WB_DAY_OF_WEEK ('26-MAR-2002') num
from dual;


       NUM
----------
         3

WB_DAY_OF_YEAR

Syntax

WB_DAY_OF_YEAR(attribute)

Purpose

WB_DAY_OF_YEAR returns the day number within the year for the date attribute.

Example

The following example shows the return value on the sysdate and on a specified date string:

select WB_DAY_OF_YEAR (sysdate) num
from dual;

       NUM
----------
        87

select WB_DAY_OF_YEAR ('26-MAR-2002') num
from dual;

       NUM
----------
        85

WB_DAY_SHORT_NAME

Syntax

WB_DAY_SHORT_NAME(attribute)

Purpose

WB_DAY_SHORT_NAME returns the three letter abbreviation or name for the date attribute.

Example

The following example shows the return value on the sysdate and on a specified date string:

select WB_DAY_SHORT_NAME  (sysdate) abbr
from dual;

ABBR
-------------------------------------
Thu

select WB_DAY_SHORT_NAME  ('26-MAR-2002') abbr
from dual;

NUM
-------------------------------------
Tue

WB_DECADE

Syntax

WB_DECADE(attribute)

Purpose

WB_DECADE returns the decade number within the century for the date attribute.

Example

The following example shows the return value on the sysdate and on a specified date string:

select WB_DECADE  (sysdate) dcd
from dual;

       DCD
----------
         2

select WB_DECADE  ('26-MAR-2002') DCD
from dual;

       DCD
----------
         2

WB_HOUR12

Syntax

WB_HOUR12(attribute)

Purpose

WB_HOUR12 returns the hour (in a 12-hour setting) component of the date corresponding to attribute.

Example

The following example shows the return value on the sysdate and on a specified date string:

select WB_HOUR12 (sysdate) h12
from dual;

       H12
----------
         9

select WB_HOUR12 ('26-MAR-2002') h12
from dual;

       H12
----------
        12

Note:

For a date not including the timestamp (in the second example), Oracle uses the 12:00 (midnight) timestamp and therefore returns 12 in this case.

WB_HOUR12MI_SS

Syntax

WB_HOUR12MI_SS(attribute)

Purpose

WB_HOUR12MI_SS returns the timestamp in attribute formatted to HH12:MI:SS.

Example

The following example shows the return value on the sysdate and on a specified date string:

select WB_HOUR12MI_SS (sysdate) h12miss
from dual;

H12MISS
-------------------------------------
09:08:52

select WB_HOUR12MI_SS ('26-MAR-2002') h12miss
from dual;

H12MISS
-------------------------------------
12:00:00

Note:

For a date not including the timestamp (in the second example), Oracle uses the 12:00 (midnight) timestamp and therefore returns 12 in this case.

WB_HOUR24

Syntax

WB_HOUR24(attribute)

Purpose

WB_HOUR24 returns the hour (in a 24-hour setting) component of date corresponding to attribute.

Example

The following example shows the return value on the sysdate and on a specified date string:

select WB_HOUR24 (sysdate) h24
from dual;

       H24
----------
         9

select WB_HOUR24 ('26-MAR-2002') h24
from dual;

       H24
----------
         0

Note:

For a date not including the timestamp (in the second example), Oracle uses the 00:00:00 timestamp and therefore returns the timestamp in this case.

WB_HOUR24MI_SS

Syntax

WB_HOUR24MI_SS(attribute)

Purpose

WB_HOUR24MI_SS returns the timestamp in attribute formatted to HH24:MI:SS.

Example

The following example shows the return value on the sysdate and on a specified date string:

select WB_HOUR24MI_SS (sysdate) h24miss
from dual;

H24MISS
------------------------------------
09:11:42

select WB_HOUR24MI_SS ('26-MAR-2002') h24miss
from dual;

H24MISS
------------------------------------
00:00:00

Note:

For a date not including the timestamp (in the second example), Oracle uses the 00:00:00 timestamp and therefore returns the timestamp in this case.

WB_IS_DATE

Syntax

WB_IS_DATE(attribute, fmt)

Purpose

To check whether attribute contains a valid date. The function returns a Boolean value which is set to true if attribute contains a valid date. Fmt is an optional date format. If fmt is omitted, the date format of your database session is used.

You can use this function when you validate your data before loading it into a table. This way the value can be transformed before it reaches the table and causes an error.

Example

WB_IS_DATE returns true in PL/SQL if attribute contains a valid date.

WB_JULIAN_FROM_DATE

Syntax

WB_JULIAN_FROM_DATE(attribute)

Purpose

WB_JULIAN_FROM_DATE returns the Julian date of date corresponding to attribute.

Example

The following example shows the return value on the sysdate and on a specified date string:

select WB_JULIAN_FROM_DATE (sysdate) jdate
from dual;

     JDATE
----------
   2452362

select WB_JULIAN_FROM_DATE ('26-MAR-2002') jdate
from dual;

     JDATE
----------
   2452360

WB_MI_SS

Syntax

WB_MI_SS(attribute)

Purpose

WB_MI_SS returns the minutes and seconds of the time component in the date corresponding to attribute.

Example

The following example shows the return value on the sysdate and on a specified date string:

select WB_MI_SS (sysdate) mi_ss
from dual;

MI_SS
-------------------------------------------
33:23

select WB_MI_SS ('26-MAR-2002') mi_ss
from dual;

MI_SS
-------------------------------------------
00:00

Note:

For a date not including the timestamp (in the second example), Oracle uses the 00:00:00 timestamp and therefore returns the timestamp in this case.

WB_WEEK_OF_MONTH

Syntax

WB_WEEK_OF_MONTH(attribute)

Purpose

WB_WEEK_OF_MONTH returns the week number within the calendar month for the date corresponding to attribute.

Example

The following example shows the return value on the sysdate and on a specified date string:

select WB_WEEK_OF_MONTH (sysdate) w_of_m
from dual;

    W_OF_M
----------
         4

select WB_WEEK_OF_MONTH ('26-MAR-2002') w_of_m
from dual;

    W_OF_M
----------
         4

Number Transformations

Number transformations provide Warehouse Builder users with functionality to perform transformations on numeric values. These include Database SQL functions that are implemented by Warehouse Builder and custom functions defined by Warehouse Builder. The custom functions are prefixed with WB_.

Table 19-2 lists the number transformations that are based on Oracle Database SQL numeric functions. The transformations are listed in a columnar table that reads down the columns from left to right to conserve space.

Table 19-2 List of Number Transformations Based on Database SQL Functions

Number Transformation Name Number Transformation Name (Contd.) Number Transformation Name (Contd.)
  • ABS

  • ACOS

  • ASIN

  • ATAN

  • ATAN2

  • BITAND

  • CEIL

  • COS

  • COSH

  • EXP

  • FLOOR

  • LN

  • LOG

  • MOD

  • NANVL

  • POWER

  • REMAINDER

  • ROUND (number)

  • SIGN

  • SIN

  • SINH

  • SQRT

  • TAN

  • TANH

  • TRUNC (number)

  • WIDTH_BUCKET

 

For descriptions and examples of these transformations, refer to the section titled "Numeric Functions" in the Oracle Database SQL Language Reference.

The custom numeric transformations are:

WB_LOOKUP_NUM (on a number)

Syntax

 WB_LOOKUP_NUM (table_name
, column_name
, key_column_name
, key_value
)

where table_name is the name of the table to perform the lookup on; column_name is the name of the NUMBER column that will be returned, for instance, the result of the lookup; key_column_name is the name of the NUMBER column used as the key to match on in the lookup table; key_value is the value of the key column, for example, the value mapped into the key_column_name with which the match will be done.

Purpose

To perform a key look up that returns a NUMBER value from a database table using a NUMBER column as the matching key.

Example

Consider the following table as a lookup table LKP1:

KEYCOLUMN  TYPE_NO  TYPE
10         100123   Car
20         100124   Bike

Using this package with the following call:

WB_LOOKUP_CHAR('LKP1'
, 'TYPE_NO'
, 'KEYCOLUMN'
, 20
)

returns the value of 100124 as output of this transformation. This output is then processed in the mapping as the result of an inline function call.

Note:

This function is a row-based key lookup. Set-based lookups are supported when you use the lookup operator.

WB_LOOKUP_NUM (on a varchar2)

Syntax:

WB_LOOKUP_CHAR(table_name
, column_name
, key_column_name
, key_value
)

where table_name is the name of the table to perform the lookup on; column_name is the name of the NUMBER column that will be returned (such as the result of the lookup); key_column_name is the name of the NUMBER column used as the key to match on in the lookup table; key_value is the value of the key column, such as the value mapped into the key_column_name with which the match will be done.

Purpose:

To perform a key lookup which returns a NUMBER value from a database table using a VARCHAR2 column as the matching key.

Example

Consider the following table as a lookup table LKP1:

KEYCOLUMN  TYPE_NO  TYPE
ACV        100123   Car
ACP        100124   Bike

Using this package with the following call:

WB_LOOKUP_CHAR ('LKP1'
, 'TYPE'
, 'KEYCOLUMN'
, 'ACP'
)

returns the value of 100124 as output of this transformation. This output is then processed in the mapping as the result of an inline function call.

Note:

This function is a row-based key lookup. Set-based lookups are supported when you use the lookup operator described in Key Lookup Operator.

WB_IS_NUMBER

Syntax

 WB_IS_NUMBER(attibute, fmt)

Purpose

To check whether attribute contains a valid number. The function returns a Boolean value, which is set to true if attribute contains a valid number. Fmt is an optional number format. If fmt is omitted, the number format of your session is used.

You can use this function when you validate the data before loading it into a table. This way the value can be transformed before it reaches the table and causes an error.

Example

WB_IS_NUMBER returns true in PL/SQL if attribute contains a valid number.

OLAP Transformations

OLAP transformations enable Warehouse Builder users to load data stored in relational dimensions and cubes into an analytic workspace.

The OLAP transformations provided by Warehouse Builder are:

The WB_OLAP_LOAD_CUBE, WB_OLAP_LOAD_DIMENSION, and WB_OLAP_LOAD_DIMENSION_GENUK transformations are used for cube cloning in Warehouse Builder. Use these OLAP transformations only if your database version is Oracle Database 9i or Oracle Database 10g Release 1. Starting with Oracle 10g Release 2, you can directly deploy dimensions and cubes into an analytic workspace.

The WB_OLAP_AW_PRECOMPUTE only works with the Oracle Warehouse Builder 10g Release 2.

The examples used to explain these OLAP transformations are based on the scenario depicted in Figure 19-3.

Figure 19-3 Example of OLAP Transformations

Description of Figure 19-3 follows
Description of "Figure 19-3 Example of OLAP Transformations"

The relational dimension TIME_DIM and the relational cube SALES_CUBE are stored in the schema WH_TGT. The analytic workspace AW_WH, into which the dimension and cube are loaded, is also created in the WH_TGT schema.

WB_OLAP_AW_PRECOMPUTE

Syntax

WB_OLAP_AW_PRECOMPUTE(p_aw_name, p_cube_name, p_measure_name, p_allow_parallel_ solve, p_max_job_queues_allocated)

where p_aw_name is the name of the AW where cube is deployed, p_cube_name is the name of the cube to solve, p_measure_name is the optional name of a specific measure to solve (if no measure is specified, then all measures will be solved), p_allow_parallel_solve is the boolean to indicate parallelization of solve based on partitioning (performance related parameter), p_max_job_queues_allocated is the number of DBMS jobs to execute in parallel (default value is 0). If 5 is defined and there are 20 partitions then a pool of 5 DBMS jobs will be used to perform the data load.There is a subtle different between parallel and non-parallel solving. With non-parallel solve, the solve happens synchronously, so when the API call is completed the solve is complete. Parallel solve executes asynchronously, the API call will return with a job id of the job started. The job will control parallel solving using the max job queues parameter to control its processing. The user may then use the job id to query the all_scheduler_* views to check on the status of the activity.

Purpose

WB_OLAP_AW_PRECOMPUTE is used for solving a non-compressed cube (compressed cubes are auto-solved). The load and solve steps can be done independently. By default, the cube map loads data, then solves (precomputes) the cube. You can load data using the map, then perform the solve at a different point of time (since the solve/build time is the costliest operation).

Example

The following example loads data from the relational cubes MART and SALES_CUBE into a cube called SALES and performs a simple solve execution working serially. This example has parameters for parallel solve and max number of job queues. If parallel solve is performed then an ASYNCHRONOUS solve job is started and the master job ID is returned via the return function.

declare
  rslt varchar2(4000);
begin
…
  rslt :=wb_olap_aw_precompute('MART','SALES_CUBE','SALES');
…
end;
/

WB_OLAP_LOAD_CUBE

Syntax

wb_olap_load_cube::=WB_OLAP_LOAD_CUBE(olap_aw_owner, olap_aw_name, olap_cube_owner, olap_cube_name, olap_tgt_cube_name)

where olap_aw_owner is the name of the database schema that owns the analytic workspace; olap_aw_name is the name of the analytic workspace that stores the cube data; olap_cube_owner is the name of the database schema that owns the related relational cube; olap_cube_name is the name of the relational cube; olap_tgt_cube_name is the name of the cube in the analytic workspace.

Purpose

WB_OLAP_LOAD_CUBE loads data from the relational cube into the analytic workspace. This allows further analysis of the cube data. This is for loading data in an AW cube from a relational cube which it was cloned from. This is a wrapper around some of the procedures in the DBMS_AWM package for loading a cube.

Example

The following example loads data from the relational cube SALES_CUBE into a cube called AW_SALES in the AW_WH analytic workspace:

WB_OLAP_LOAD_CUBE('WH_TGT', 'AW_WH', 'WH_TGT', 'SALES_CUBE', 'AW_SALES')

WB_OLAP_LOAD_DIMENSION

Syntax

wb_olap_load_dimension::=WB_OLAP_LOAD_DIMENSION(olap_aw_owner, olap_aw_name, 
     olap_dimension_owner, olap_dimension_name, olap_tgt_dimension_name)

where olap_aw_owner is the name of the database schema that owns the analytic workspace; olap_aw_name is the name of the analytic workspace that stores the dimension data; olap_dimension_owner is the name of the database schema in which the related relational dimension is stored; olap_dimension_name is the name of the relational dimension; olap_tgt_dimension_name is the name of the dimension in the analytic workspace.

Purpose

WB_OLAP_LOAD_DIMENSION loads data from the relational dimension into the analytic workspace. This allows further analysis of the dimension data. This is for loading data in an AW dimension from a relational dimension which it was cloned from. This is a wrapper around some of the procedures in the DBMS_AWM package for loading a dimension.

Example

The following example loads the data from the relational dimension TIME_DIM into a dimension called AW_TIME in the analytic workspace AW_WH:

WB_OLAP_LOAD_DIMENSION('WH_TGT', 'AW_WH', 'WH_TGT', 'TIME_DIM', 'AW_TIME')

WB_OLAP_LOAD_DIMENSION_GENUK

Syntax

wb_olap_load_dimension_genuk::=WB_OLAP_LOAD_DIMENSION_GENUK(olap_aw_owner, 
 olap_aw_name, olap_dimension_owner, olap_dimension_name, olap_tgt_dimension_name)

where olap_aw_owner is the name of the database schema that owns the analytic workspace; olap_aw_name is the name of the analytic workspace that stores the dimension data; olap_dimension_owner is the name of the database schema in which the related relational dimension is stored; olap_dimension_name is the name of the relational dimension; olap_tgt_dimension_name is the name of the dimension in the analytic workspace.

Purpose

WB_OLAP_LOAD_DIMENSION_GENUK loads data from the relational dimension into the analytic workspace. Unique dimension identifiers will be generated across all levels. This is for loading data in an AW dimension from a relational dimension which it was cloned from. This is a wrapper around some of the procedures in the DBMS_AWM package for loading a dimension.

If a cube has been cloned and if you select YES for the Generate Surrogate Keys for Dimensions option, then when you want to reload the dimensions, you should use the WB_OLAP_LOAD_DIMENSION_GENUK procedure. This procedure generates surrogate identifiers for all levels in the AW, because the AW requires all level identifiers to be unique across all levels of a dimension.

Example

Consider an example in which the dimension TIME_DIM has been deployed to the OLAP server by cloning the cube. The parameter generate surrogate keys for Dimension was set to true. To now reload data from the relational dimension TIME_DIM into the dimension AW_TIME in the analytic workspace AW_WH, use the following syntax.

WB_OLAP_LOAD_CUBE('WH_TGT', 'AW_WH', 'WH_TGT', 'TIME_DIM', 'AW_TIME')

Other Transformations

Other transformations included with Warehouse Builder enable you to perform various functions which are not restricted to certain data types. These transformations are implementations of the Oracle Database SQL functions. For descriptions and examples of these transformations, see Oracle Database SQL Language Reference.

  • DEPTH

  • DUMP

  • EMPTY_BLOB

  • EMPTY_CLOB

  • NLS_CHARSET_DECL_LEN

  • NLS_CHARSET_ID

  • NLS_CHARSET_NAME

  • NULLIF

  • NVL

  • NVL2

  • ORA_HASH

  • PATH

  • SYS_CONTEXT

  • SYS_GUID

  • SYS_TYPEID

  • UID

  • USER

  • USERENV

  • VSIZE

Spatial Transformations

Spatial transformations are an integrated set of functions and procedures that enables spatial data to be stored, accessed, and analyzed quickly and efficiently in an Oracle Database.

Spatial transformations included with Warehouse Builder are:

  • SDO_AGGR_CENTROID

  • SDO_AGGR_CONVEXHULL

  • SDO_AGGR_MBR

  • SDO_AGGR_UNION

For descriptions and examples of these transformations, refer to the Oracle Spatial Developer's Guide.

Streams Transformations

The Streams transformations category contains one transformation called REPLICATE. The following section describes this transformation.

REPLICATE

Syntax

REPLICATE(lcr, conflict_resolution)

where lcr stands for Logical Change Record and encapsulates the DML change. Its data type is SYS.LCR$_ROW_RECORD. conflict_resolution is a Boolean variable. If its value is TRUE, any conflict resolution defined for the table will be used to resolve conflicts resulting from the execution of the LCR. For more information about conflict resolution, see Oracle Streams Replication Administrator's Guide.

Purpose

REPLICATE is used to replicate a DML change (INSERT, UPDATE, or DELETE) that has occurred on a table in the source system on an identical table in the target system. The table in the target system should be identical to the table in the source system in the following respects:.

  • The name of the schema that contains the target table should be the same as the name of the schema that contains the source table.

  • The name of the target table should the same as the name of the source table.

  • The structure of the target table should be the same as that of the source table. The structure includes the number, name, and data type of the columns in the table.

Example

Consider a table T1(c1 varchar2(10), c2 number primary key) in schema S on the source system and an identical table in the target system. Consider the following insert operation on the table T1 on the source system

insert into T1 values ('abcde', 10)

An LCR representing the change following the above insert of a row on the table T1 in the source system will have the following details

LCR.GET_OBJECT_OWNER will be 'S'
LCR.GET_OBJECT_NAME will be 'T1'
LCR.GET_COMMAND_TYPE will be 'INSERT'
LCR.GET_VALUE('c1', 'new') will have the value for the column 'c1' - i.e. 'abcde'
LCR.GET_VALUE('c2', 'new') will have the value for the column 'c2' - i.e. 10

Such an LCR will be created and enqueued by a Streams Capture Process on the source system that captures changes on table S.T1

REPLICATE(lcr, true) - will result in a row ('abcde', 10) being inserted into the table T1 on the target system.

Note:

Using this approach will not provide lineage information. If lineage is important, then do not use this function. Use the more direct approach of using an LCRCast operator bound to the source table and a table operator bound to the target table and connecting the attributes of these two operators with the same name ('Match by name'). Further information about LCR (Logical Change Record) is available in Oracle Database 10g Documentation (Information Integration)

XML Transformations

XML transformations provide Warehouse Builder users with functionality to perform transformations on XML objects. These transformations enable Warehouse Builder users to load and transform XML documents and Oracle AQs.

To enable loading of XML sources, Warehouse Builder provides access to the database XML functionality by implementing database XML functions. Warehouse Builder also defines custom functions.

Following are the XML transformations that are implemented based on Oracle Database XML functions:

  • EXISTSNODE

  • EXTRACT

  • EXTRACTVALUE

  • SYS_XMLAGG

  • SYS_XMLGEN

  • XMLCONCAT

  • XMLSEQUENCE

  • XMLTRANSFORM

See Also:

The custom XML transformations are:

WB_XML_LOAD

Syntax:

WB_XML_LOAD(control_file)

Purpose

This program unit extracts and loads data from XML documents into database targets. The control_file, an XML document, specifies the source of the XML documents, the targets, and any runtime controls. After the transformation has been defined, a mapping in Warehouse Builder calls the transformation as a pre-map or post-map trigger.

Example

The following example illustrates a script that can be used to implement a Warehouse Builder transformation that extracts data from an XML document stored in the file products.xml and loads it into the target table called books:

begin
wb_xml_load('<OWBXMLRuntime>' 
||
'<XMLSource>'
||
' <file>\ora817\GCCAPPS\products.xml</file>'
||
'</XMLSource>'
||
'<targets>'
||
' <target XSLFile="\ora817\XMLstyle\GCC.xsl">books</target>'
||
'</targets>'
||
'</OWBXMLRuntime>'
);
end;

For more information about control files, see the Oracle Warehouse Builder User's Guide.

WB_XML_LOAD_F

Syntax

WB_XML_LOAD_F(control_file)

Purpose

WB_XML_LOAD_F extracts and loads data from XML documents into database targets. The function returns the number of XML documents read during the load. The control_file, itself an XML document, specifies the source of the XML documents, the targets, and any runtime controls. After the transformation has been defined, a mapping in Warehouse Builder calls the transformation as a pre-map or post-map trigger.

Example

The following example illustrates a script that can be used to implement a Warehouse Builder transformation that extracts data from an XML document stored in the file products.xml and loads it into the target table books:

begin
wb_xml_load_f('<OWBXMLRuntime>' 
||
'<XMLSource>'
||
' <file>\ora817\GCCAPPS\products.xml</file>'
||
'</XMLSource>'
||
'<targets>'
||
' <target XSLFile="\ora817\XMLstyle\GCC.xsl">books</target>'
||
'</targets>'
||
'</OWBXMLRuntime>'
);
end;

For more information about the types handled and detailed information about control_files, see the Oracle Warehouse Builder User's Guide.

Importing PL/SQL

Use the Import Metadata Wizard to import PL/SQL functions, procedures, and packages into a Warehouse Builder project. You can edit, save, and deploy the imported PL/SQL functions and procedures. You can also view and modify imported packages.

The following steps describe how to import PL/SQL packages from other sources into Warehouse Builder.

To import a PL/SQL function, procedure, or package:

  1. From the Project Explorer, expand the project node and then Databases node.

  2. Right-click an Oracle module node and select Import.

    Warehouse Builder displays the Welcome page of the Import Metadata Wizard.

  3. Click Next.

  4. Select PL/SQL Transformation in the Object Type field of the Filter Information page.

  5. Click Next.

    The Import Metadata Wizard displays the Object Selection page.

  6. Select a function, procedure, or package from the Available Objects list. Move the objects to the Selected Objects list by clicking the right arrow to move a single object or the Move All button to move multiple objects.

  7. Click Next.

    The Import Metadata Wizard displays the Summary and Import page.

  8. Verify the import information. Click Back to revise your selections.

  9. Click Finish to import the selected PL/SQL transformations.

    Warehouse Builder displays the Import Results page.

  10. Click OK proceed with the import. Click Undo to cancel the import process.

    The imported PL/SQL information appears under the Transformations node of the Oracle module into which you imported the data.

Restrictions on Using Imported PL/SQL

The following restrictions apply to the usage of imported PL/SQL:

  • You cannot edit imported PL/SQL packages.

  • Wrapped PL/SQL objects are not readable.

  • You can edit the imported package body but not the imported package specification.

Example: Reusing Existing PL/SQL Code

Scenario

A movie rental company periodically updates the customer rental activity in its CUST_RENTAL_ACTIVITY table, where it stores the rental sales and overdue charges data for each customer. This table is used for different mailing campaigns. For example, in their latest mailing campaign, customers with high overdue charges are offered the company's new pay-per-view service.Currently, the movie rental company uses a PL/SQL package to consolidate their data. The existing PL/SQL package needs to be maintained manually by accessing the database. This code runs on an Oracle 8i database.

CREATE OR REPLACE PACKAGE RENTAL_ACTIVITY AS
  PROCEDURE REFRESH_ACTIVITY(SNAPSHOT_START_DATE IN DATE);
END RENTAL_ACTIVITY;
/
CREATE OR REPLACE PACKAGE BODY RENTAL_ACTIVITY AS
 PROCEDURE REFRESH_ACTIVITY(SNAPSHOT_START_DATE IN DATE) IS
   CURSOR C_ACTIVITY IS
    SELECT
      CUST.CUSTOMER_NUMBER CUSTOMER_NUMBER,
      CUST.CUSTOMER_FIRST_NAME CUSTOMER_FIRST_NAME,
      CUST.CUSTOMER_LAST_NAME CUSTOMER_LAST_NAME,
      CUST.CUSTOMER_ADDRESS CUSTOMER_ADDRESS,
      CUST.CUSTOMER_CITY CUSTOMER_CITY,
      CUST.CUSTOMER_STATE CUSTOMER_STATE,
      CUST.CUSTOMER_ZIP_CODE CUSTOMER_ZIP_CODE,
      SUM(SALE.RENTAL_SALES) RENTAL_SALES,
      SUM(SALE.OVERDUE_FEES) OVERDUE_FEES
    FROM  CUSTOMER CUST, MOVIE_RENTAL_RECORD SALE
    WHERE SALE.CUSTOMER_NUMBER = CUST.CUSTOMER_NUMBER AND
          SALE.RENTAL_RECORD_DATE >= SNAPSHOT_START_DATE 
    GROUP BY
    CUST.CUSTOMER_NUMBER,
    CUST.CUSTOMER_FIRST_NAME,
    CUST.CUSTOMER_LAST_NAME,
    CUST.CUSTOMER_ADDRESS,
    CUST.CUSTOMER_CITY,
    CUST.CUSTOMER_STATE,
    CUST.CUSTOMER_ZIP_CODE;
   
   V_CUSTOMER_NUMBER NUMBER;
   V_CUSTOMER_FIRST_NAME VARCHAR2(20);
   V_CUSTOMER_LAST_NAME VARCHAR2(20);
   V_CUSTOMER_ADDRESS VARCHAR(50);
   V_CUSTOMER_CITY VARCHAR2(20);
   V_CUSTOMER_STATE VARCHAR2(20);
   V_CUSTOMER_ZIP_CODE VARCHAR(10);
   V_RENTAL_SALES NUMBER;
   V_OVERDUE_FEES NUMBER;

BEGIN
   OPEN C_ACTIVITY;
   LOOP
    EXIT WHEN C_ACTIVITY%NOTFOUND;
    FETCH
     C_ACTIVITY
    INTO 
     V_CUSTOMER_NUMBER,
     V_CUSTOMER_FIRST_NAME,
     V_CUSTOMER_LAST_NAME,
     V_CUSTOMER_ADDRESS,
     V_CUSTOMER_CITY,
     V_CUSTOMER_STATE,
     V_CUSTOMER_ZIP_CODE,
     V_RENTAL_SALES,
     V_OVERDUE_FEES;

    UPDATE CUST_ACTIVITY_SNAPSHOT
    SET
     CUSTOMER_FIRST_NAME = V_CUSTOMER_FIRST_NAME,
     CUSTOMER_LAST_NAME = V_CUSTOMER_LAST_NAME,
     CUSTOMER_ADDRESS = V_CUSTOMER_ADDRESS,
     CUSTOMER_CITY = V_CUSTOMER_CITY,
     CUSTOMER_STATE = V_CUSTOMER_STATE,
     CUSTOMER_ZIP_CODE = V_CUSTOMER_ZIP_CODE,
     RENTAL_SALES = V_RENTAL_SALES,
     OVERDUE_FEES = V_OVERDUE_FEES,
     STATUS_UPDATE_DATE = SYSDATE
    WHERE
     CUSTOMER_NUMBER = V_CUSTOMER_NUMBER;

    IF SQL%NOTFOUND THEN
       INSERT INTO CUST_ACTIVITY_SNAPSHOT
       ( CUSTOMER_NUMBER,
         CUSTOMER_FIRST_NAME,
         CUSTOMER_LAST_NAME,
         CUSTOMER_ADDRESS,
         CUSTOMER_CITY,
         CUSTOMER_STATE,
         CUSTOMER_ZIP_CODE,
         RENTAL_SALES,
         OVERDUE_FEES,
         STATUS_UPDATE_DATE )
       VALUES
       ( V_CUSTOMER_NUMBER, 
         V_CUSTOMER_FIRST_NAME,
         V_CUSTOMER_LAST_NAME,
         V_CUSTOMER_ADDRESS,
         V_CUSTOMER_CITY,
         V_CUSTOMER_STATE,
         V_CUSTOMER_ZIP_CODE,
         V_RENTAL_SALES,
         V_OVERDUE_FEES,
         SYSDATE );
     END IF;
   END LOOP;
END REFRESH_ACTIVITY;
END RENTAL_ACTIVITY;
/

Solution

This case study highlights the benefits of importing an existing custom PL/SQL package into Warehouse Builder and using its functionality to automatically maintain, update, and regenerate the PL/SQL code. Warehouse Builder enables you to automatically take advantage of new database features and upgrades by generating code that is optimized for new database versions. For example, if the customer has a PL/SQL package for Oracle 8i, then by importing it into Warehouse Builder they can generate code for both Oracle 8i and Oracle 9i. Also, by importing a custom package and re-creating its operations through a Warehouse Builder mapping, you can transparently run and monitor the operations. Otherwise, you must manually access the database to verify and update the code. Warehouse Builder also enables you to perform lineage and impact analysis on all ETL operations while the Runtime Audit Browser monitors the running of the code and logs errors.

Case Study

You can migrate the PL/SQL code into Warehouse Builder by taking these steps:

Follow these steps to handle a custom PL/SQL package in Warehouse Builder.

Step 1: Import the Custom PL/SQL Package

In the Project Explorer, expand the Transformations node under the Oracle module into which you want to import the PL/SQL package refresh_activity(DATE). Use the Import Metadata Wizard to import the package by right-clicking Transformations and selecting Import. On the Filter Information page of this wizard, indicate that you are importing a PL/SQL Transformation.

After you finish the import, the package refresh_activity(DATE) appears under the Packages node of the Transformations folder.

Step 2: Create a 'Black Box' Mapping

You can use the refresh_activity(DATE) procedure directly in a mapping without making any changes to it. In the mapping, you add a Post-Mapping Process operator to the mapping, with the package refresh_activity(DATE) selected.

In this example, you can immediately take advantage of the existing custom code. The learning curve and investment on resources is minimal. You may decide to maintain all the existing and developed PL/SQL code in this manner, using Warehouse Builder only to develop new processing units. Warehouse Builder enables you to use mappings that use the legacy code along with the new mappings you create. In such a case, although you can generate code for these mappings in Warehouse Builder, they cannot use Warehouse Builder features to maintain, update, or audit the code.

Because the legacy code is used as a 'black box' that is not transparent to Warehouse Builder, you still need to maintain the legacy code manually. Thus, you cannot take advantage of the Warehouse Builder features, such as runtime audit browser, lineage and impact analysis, and optimized code generation, that rely on infrastructure code and metadata available for Warehouse Builder generated mappings.

Follow the next steps to take advantage of these features in Warehouse Builder and to automatically maintain, monitor, and generate your PL/SQL code.

Step 3: Migrate Custom Code into a Mapping

To take advantage of the code generation, maintenance, and auditing features, you can gradually migrate the legacy PL/SQL code functionality into a mapping and phase out the custom 'black box' package. The mapping created to provide the PL/SQL code functionality is called Rental_Activity.

The recommended method is to test out this new mapping by running it side by side with the 'black box' mapping. If the testing is successful and the new mapping can perform all the operations included in the custom code, the 'black box' mappings can be phased out. Warehouse Builder enables you to maintain, update, and generate code from a mapping without performing manual updates in the database. Figure 19–4 shows a sample of code generated from the Rental_Activity mapping that replicates the operations of the custom PL/SQL package for the movie rental company.

Step 4: Generate Code for Oracle 9i

If you upgrade to Oracle 9i version of the database, you only need to re-deploy the Rental_Activity mapping created in Step 3. Warehouse Builder generates code optimized for the new database version. Figure 19-5 shows the MERGE statement from a sample of code generated for the same mapping for Oracle 9i.

Figure 19-5 Sample Code for Oracle 9i

Description of Figure 19-5 follows
Description of "Figure 19-5 Sample Code for Oracle 9i"

No manual steps are required to maintain and generate the new code. Also, you can transparently monitor and maintain their ETL operations. Warehouse Builder enables them to perform lineage and impact analysis on their mappings and the Runtime Audit Browser enables them to track and log errors when running the mappings.