A Additional and Optional Usages

Keywords Used to Import Metadata

You use keywords to form the parameters specified to selectively import objects using a control file. Table A-1 provides a list of keywords for object types that you use to select objects to import.

Examples of Control Files Used to Import Metadata

You can direct the MDL import utility to import objects from a file by creating a control file with a set of parameters. Example A-2 shows a typical control file for importing objects from a MDL data file.

PROJECT=WH_SALES
ORACLE_MODULES=SALES_SRC
EXTERNAL_TABLES=SALES_DATA_EXT

Control File Used to Create an Action Plan

You can specify an action plan in the control file that will allow you to specifically define what you want to do with each object in the imported file. First you need to indicate that the type of import is an action plan by specifying MODE = ACTION PLAN. Next, you need to specify the type of actions for objects that you want to import or skip. If you want to import the objects, you can set the Action to either CREATE, UPDATE, REPLACE, OR INCREMENTALUPDATE. Otherwise, if you want to skip the object, specify NONE as the Action. For the list of object type keywords that you can use to import objects using the Action Plan, refer Table A-1.

Example A-3 shows an example of an MDL control file that contains an action plan.

MODE=ACTIONPLAN
#
# User-Specified Action Plan
#
ACTION=NONE
ORACLE_MODULES=(DATAWAREHOUSE)
#
ACTION=CREATE
TABLES=(TABLE_3)
FACTS=(FACT1, FACT2, FACT3)
SEQUENCES=(SEQ_A, SEQ_B, SEQ_C)
#
ACTION=REPLACE
TABLES=(TABLE_1, TABLE_2)
DIMENSIONS=(DIM1, DIM2, DIM3)
#
# Switching to a different module
ACTION=REPLACE
FLAT_FILE_MODULES=(FLAT_FILE)
FILES=(FILE_1, FILE_2)
#
ACTION=CREATE
FILES=(FILE_3)
#

Predefined Transformations

Predefined transformations consist of built-in and seeded functions and procedures that are part of the Oracle Library. You can directly use these transformations in any project in your repository.Predefined transformations are grouped into the following categories. Each category contains transformations that pertain to that category.

• Administration

• Character

• Control Center

• Conversion

• Date

• Numeric

• OLAP

• Other

• Spatial

• Streams

• SYS

• XML

Accessing Predefined Transformations Using OMB*Plus

All predefined transformations belong to a transformation module called WB_PREDEFINED_TRANS in the project PUBLIC_PROJECT. Also, every project in the repository contains the WB_PREDEFINED_TRANS. To access predefined transformations, you must change the current context to the WB_PREDEFINED_TRANS transformation module either in PUBLIC_PROJECT or in your project.Each category of predefined transformations is represented by a package in the WB_PREDEFINED_TRANS transformation module. The package contains the transformations, including functions and procedures, that are belong under it. For example, all the predefined numeric transformations belong to the package called NUMERIC under the WB_PREDEFINED_TRANS transformation module.

Examples 

Use the following commands to list the types of public transformation modules.

OMB+> OMBCC '/PUBLIC_PROJECT/'OMB+> OMBLIST TRANSFORMATION_MODULES

To view the types of predefined transformations, use the following command from the context of the WB_PREDEFINED_TRANS transformation module.

OMB+> OMBCC '/PUBLIC_PROJECT/WB_PREDEFINED_TRANS'OMB+> OMBLIST PACKAGES

To view the procedures under the Date category of the predefined transformations, first change context to the DATE package.

OMB+> OMBCC '/PUBLIC_PROJECT/WB_PREDEFINED_TRANS/PACKAGES/DATE'OMB+> OMBLIST PROCEDURES

Use the following command to use the Date transformation TRUNC in your mapping.

OMB+> OMBCREATE MAPPING 'MAP1'\
    > ADD TRANSFORMATION OPERATOR 'TRUNC_OPER' \
    > BOUND TO FUNCTION '/MY_PROJECT/WB_PREDEFINED_TRANS/DATE/TRUNC'

Custom Transformations

Custom transformations are transformations that are defined by the user. Custom transformations include functions, procedures, and packages.Custom transformations are of two types:

• Public custom transformations

  These are part of the global shared library that consists of predefined transformations.

• Custom transformations within a particular project

  These are accessible only in the project in which they are defined.

Public Custom Transformations

Public custom transformations are accessible across all projects in your repository. They belong to the transformation module WB_CUSTOM_TRANS under the project PUBLIC_PROJECT. Also, every project in your repository contains a transformation module called WB_CUSTOM_TRANS. This transformation module contains the public custom transformations.

Examples 

To list the types of custom transformations, you need to be in the context of the custom transformations module.

OMB+> OMBCC '/PUBLIC_PROJECT/WB_CUSTOM_TRANS'OMB+> OMBLIST FUNCTIONS

To view the custom public procedures, use the following command from the context of the WB_CUSTOM_TRANS module in the PUBLIC_PROJECT.

OMB+> OMBLIST PACKAGES

To use a public custom function in a mapping, navigate to the context of the WB_CUSTOM_TRANS transformation module under the project in which you are defining the mapping.

OMB+> OMBCC '/MY_PROJECT/MOD1'
OMB+> OMBCREATE MAPPING 'MAP1'\
    > ADD TRANSFORMATION OPERATOR 'FUNC_OPER' \
    > BOUND TO FUNCTION '/PUBLIC_PROJECT/WB_CUSTOM_TRANS/FUNC1

Custom Transformations that Belong to a Particular Project

You can create custom transformations whose scope is limited to the project in which they are defined. These custom transformations are defined in the context of a particular project and are accessible to all the modules within that project.

For example, the project MY_PROJECT contains two modules MOD1 and MOD2. In MOD1, you define a function called LOCAL_FUNC. This function is accessible from the context of both MOD1 and MOD2.

Custom transformations that belong to a particular project are part of the transformations in that project.

To create a custom transformation in the module MOD1, use the following syntax.

OMB+> OMBCREATE FUNCTION 'LOCAL_FUNC'\
    > ADD PARAMETER PARAM_1\
    > SET PROPERTIES (IN_OUT,DATATYPE) VALUES('in','varchar2')

Use the following command to reference the function LOCAL_FUNC in a mapping that you defined in module MOD2.

OMB+> OMBCREATE MAPPING 'MAP1'\
    > ADD TRANSFORMATION OPERATOR 'TRUNC_OPER' \
    > BOUND TO FUNCTION '/MY_PROJECT/MOD1/LOCAL_FUNC'

Accessing Predefined Transformations Using OMB*Plus

All predefined transformations belong to a transformation module called WB_PREDEFINED_TRANS in the project PUBLIC_PROJECT. Also, every project in the repository contains the WB_PREDEFINED_TRANS. To access predefined transformations, you must change the current context to the WB_PREDEFINED_TRANS transformation module either in PUBLIC_PROJECT or in your project.Each category of predefined transformations is represented by a package in the WB_PREDEFINED_TRANS transformation module. The package contains the transformations, including functions and procedures, that are belong under it. For example, all the predefined numeric transformations belong to the package called NUMERIC under the WB_PREDEFINED_TRANS transformation module.

Examples 

Use the following commands to list the types of public transformation modules.

OMB+> OMBCC '/PUBLIC_PROJECT/'OMB+> OMBLIST TRANSFORMATION_MODULES

To view the types of predefined transformations, use the following command from the context of the WB_PREDEFINED_TRANS transformation module.

OMB+> OMBCC '/PUBLIC_PROJECT/WB_PREDEFINED_TRANS'OMB+> OMBLIST PACKAGES

To view the procedures under the Date category of the predefined transformations, first change context to the DATE package.

OMB+> OMBCC '/PUBLIC_PROJECT/WB_PREDEFINED_TRANS/PACKAGES/DATE'OMB+> OMBLIST PROCEDURES

Use the following command to use the Date transformation TRUNC in your mapping.

OMB+> OMBCREATE MAPPING 'MAP1'\
    > ADD TRANSFORMATION OPERATOR 'TRUNC_OPER' \
    > BOUND TO FUNCTION '/MY_PROJECT/WB_PREDEFINED_TRANS/DATE/TRUNC'

Public Custom Transformations

Public custom transformations are accessible across all projects in your repository. They belong to the transformation module WB_CUSTOM_TRANS under the project PUBLIC_PROJECT. Also, every project in your repository contains a transformation module called WB_CUSTOM_TRANS. This transformation module contains the public custom transformations.

Examples 

To list the types of custom transformations, you need to be in the context of the custom transformations module.

OMB+> OMBCC '/PUBLIC_PROJECT/WB_CUSTOM_TRANS'OMB+> OMBLIST FUNCTIONS

To view the custom public procedures, use the following command from the context of the WB_CUSTOM_TRANS module in the PUBLIC_PROJECT.

OMB+> OMBLIST PACKAGES

To use a public custom function in a mapping, navigate to the context of the WB_CUSTOM_TRANS transformation module under the project in which you are defining the mapping.

OMB+> OMBCC '/MY_PROJECT/MOD1'
OMB+> OMBCREATE MAPPING 'MAP1'\
    > ADD TRANSFORMATION OPERATOR 'FUNC_OPER' \
    > BOUND TO FUNCTION '/PUBLIC_PROJECT/WB_CUSTOM_TRANS/FUNC1

Custom Transformations that Belong to a Particular Project

You can create custom transformations whose scope is limited to the project in which they are defined. These custom transformations are defined in the context of a particular project and are accessible to all the modules within that project.

For example, the project MY_PROJECT contains two modules MOD1 and MOD2. In MOD1, you define a function called LOCAL_FUNC. This function is accessible from the context of both MOD1 and MOD2.

Custom transformations that belong to a particular project are part of the transformations in that project.

To create a custom transformation in the module MOD1, use the following syntax.

OMB+> OMBCREATE FUNCTION 'LOCAL_FUNC'\
    > ADD PARAMETER PARAM_1\
    > SET PROPERTIES (IN_OUT,DATATYPE) VALUES('in','varchar2')

Use the following command to reference the function LOCAL_FUNC in a mapping that you defined in module MOD2.

OMB+> OMBCREATE MAPPING 'MAP1'\
    > ADD TRANSFORMATION OPERATOR 'TRUNC_OPER' \
    > BOUND TO FUNCTION '/MY_PROJECT/MOD1/LOCAL_FUNC'