25 Source and Target Operators

Setting Properties for Oracle Source and Target Operators

The Property Inspector displays the properties of the selected operator. It contains the following categories of parameters for source and target operators:

• Change Data Capture: This category is displayed only for tables and views. It contains the following properties: "Capture Consistency", "Change Data Capture Filter", "Enabled", and "Trigger Based Capture".

• Conditional Loading: You can set the following properties: "Target Filter for Update", "Target Filter for Delete", and "Match By Constraint".

• Data Chunking: This category is displayed for tables, views, and materialized views. It contains the following properties: Chunk Filter Condition, Chunking Enabled, and Parallel Chunk Filter Condition. For information about these properties, see "Chunking for Table Operators".

• Error Table: You can set the "Error Table Name", "Roll up Errors", and "Select Only Errors from this Operator" properties. This section of properties is displayed only for the following mapping operators: Table, View, Materialized View, External Table, and Dimension.

• General: Under the General node, you can set "Primary Source", "Target Load Order", and the Loading Type. Depending upon the type of target, you can set different values for the Loading Type as described in "Loading Types for Oracle Target Operators" and "Loading Types for Flat Files".

• Keys (read-only): You can view the "Key Name", "Key Type", and "Referenced Keys". If the operator functions as a source, then the key settings are used with the Join operator. If the operator functions as a target, then the key settings are used with the "Match By Constraint" parameter.

• File Properties: Under the file properties, you can view the "Bound Name".

• Temp Stage Table: This category is displayed for tables, views, materialized views, and external tables.It contains the following properties: Extra DDL Clauses, Is Temp Staging Table, and Temp Stage Table ID. These properties are described in "Creating Temporary Tables While Performing ETL".

Setting Attribute Properties

For each attribute in a source and target operator, parameters are categorized into the following types:

• General: Under the General properties, you can view the "Bound Name" property, the Business name, and Physical name.

• Chunking_column: This category is displayed only for attributes in Table, View, and Materialized View operators. It contains one property "Chunking Number Column".

• Code Template Metadata Tags: This category contains the following properties: "SCD", "UD1", "UD2", "UD3", "UD4", "UD5", and "UPD".

• Data Type Information: The data type properties are applicable to all operators. They include "Data Type", "Precision", "Scale", "Length", and "Fractional Seconds Precision".

• Loading Properties: The operators for tables, dimensions, cubes, views, and materialized views have a Loading Properties category. This category contains the following settings: "Load Column When Inserting Row", "Load Column When Updating Row", "Match Column When Updating Row", "Update: Operation", and "Match Column When Deleting Row".

Certain operators contain properties that are specific to that particular operator. These properties are listed under the Operator Specific Properties node and are described in the sections that discuss that operator.

Constant Operator

The Constant operator enables you to define constant values. You can place constants anywhere in any PL/SQL or ABAP mapping.

The Constant operator produces a single output group that contains one or more constant attributes. Oracle Warehouse Builder initializes constants at the beginning of the execution of the mapping.

For example, use a Constant operator to load the value of the current system date into a Table operator. In the Expression Builder, select the public transformation SYSDATE from the list of predefined transformations.

For more information about public transformations, see Chapter 4, "Overview of Transforming Data".

To define a Constant operator in a PL/SQL or ABAP mapping:

1. Drop a Constant operator onto the Mapping Editor canvas.

2. Right-click the Constant operator and select Open.

   The Constant Editor dialog box is displayed.

3. On the Output tab, create an output attribute by clicking the blank cell in the Attribute column and entering the name of the output attribute.

   The default data type assigned is NUMERIC. You can modify the data type and any other parameters associated with it such as length, precision, and so on.

4. Enter the expression associated with the output attribute.

   Use the Expression field for an output attribute to enter the expression. Or, click the Ellipsis button to the right of the Expression field to use the Expression Builder dialog box to define an expression.

   The length, precision, and scale properties assigned to the output attribute must match the values returned by the expressions defined in the mapping. For VARCHAR, CHAR, or VARCHAR2 data types, enclose constant string literals within single quotation marks, such as, 'my_string'.

5. Click OK to close the Constant Editor dialog box.

Construct Object Operator

The Construct Object operator enables you to create SQL object data types (object types and collection types), PL/SQL object types, and cursors in a mapping by using the individual attributes that they comprise.

For example, you can use a Construct Object operator to create a SQL object type that is used to load data into a table that contains a column whose data type is an object type. You can also use this operator to create the payload that loads data into an advanced queue. This operator also enables you to construct a SYS.REFCURSOR object.

The Construct Object operator has one input group and one output group. The input group represents the individual attributes that comprise the object type. The output of the Construct Object operator is an object type that is created using the individual attributes. In a mapping, the data type of the output attribute of the Construct Object operator should match the target attribute to which it is being mapped.

Figure 25-1 displays a mapping that uses a Construct Object operator. The source table CUST_SRC uses separate attributes to store each component of the customer address. But the target table CUSTOMERS uses an object type to store the customer address. To load data from the CUST_SRC table into the CUSTOMERS table, the customer address should be an object type whose signature matches that of the customer address in CUSTOMERS. The Construct Object operator takes the individual attributes from CUST_SRC, that store the customer address as input, and constructs an object type. The Construct Object operator is bound to the user-defined data type CUST_ADDR stored in the workspace.

Figure 25-1 Construct Object Operator in a Mapping

Description of "Figure 25-1 Construct Object Operator in a Mapping"

To define a Construct Object operator in a mapping:

1. Drag and drop a Construct Object operator onto the Mapping Editor canvas.

2. Use the Add Construct Object dialog box to create or select an object. For more information about these options, see "Using the Add Operator Dialog Box to Add Operators".

3. Map the individual source attributes that are used to construct the object to the input group of the Construct Object operator.

4. Map the output attribute of the Construct Object operator to the target attribute. The data type of the target attribute should be an object type.

   The signatures of the output attribute of the Construct Object operator and the target attribute should be the same.

Cube Operator

Use the Cube operator to source data from or load data into cubes.

The Cube operator contains a group with the same name as the cube. This group contains an attribute for each of the cube measures. It also contains the attributes for the surrogate identifier and business identifier of each dimension level that the cube references. Additionally, the Cube operator displays one group for each dimension that the cube references.

If you specify an Orphan Management Policy and create an error table for a cube, when you add this cube to a mapping, the Cube operator contains a group called ERROR_<cube_name>. This is an output group that contains attributes that are displayed in the Cube operator details, but not in the Cube operator on the mapping canvas. To create data flows using these attributes, display these attributes on the canvas by selecting the ERROR_<cube_name> group on the canvas and from the Graph menu, selecting Select Display Set, and then All.

You can bind a Cube operator to a cube defined in any Oracle module in the current project. You can also synchronize the Cube operator and update it with changes made to the cube to which it is bound. To synchronize a Cube operator, right-click the Cube operator on the Mapping Editor canvas and select Synchronize.

Data Generator Operator

Use a Data Generator operator to introduce a sequence, record number, or system date into a mapping. You can use a single Data Generator operator to map multiple of these functions.

For mappings with flat file sources and targets, the Data Generator operator connects the mapping to SQL*Loader to generate the data stored in the database record.

The following functions are available:

• RECNUM

• SYSDATE1

• SEQUENCE

Oracle Warehouse Builder can generate data by specifying only sequences, record numbers, system dates, and constants as field specifications. SQL*Loader inserts as many records as are specified by the LOAD keyword.

The Data Generator operator has one output group with predefined attributes corresponding to Record Number, System Date, and a typical Sequence. Use the Data Generator operator to obtain record number, system date, or a sequence. For all other functions, use a Constant operator or Expression operator.

Figure 25-2 shows a mapping that uses the Data Generator operator to obtain the current system date. The data from a flat file CUSTOMERS_TXT is loaded in to a staging table CUST_STAGE. The staging table contains an additional attribute for the date on which the data was loaded. The SYSDATE1 attribute of the Data Generator operator is mapped to the DATE_LOADED attribute of the staging table CUST_STAGE.

Figure 25-2 Data Generator in a Mapping

Description of "Figure 25-2 Data Generator in a Mapping"

To define a Data Generator in a SQL*Loader mapping:

1. Drop a Data Generator operator onto the Mapping Editor canvas.

2. Select the SEQUENCE attribute from the Data Generator operator and map it to the target column.

   Oracle Warehouse Builder displays the properties of this attribute in the Property Inspector.

3. In the Expression field, click the Ellipsis button to open the Expression Builder and define an expression.

4. (Optional) Repeat steps 2 and 3 for the RECNUM attribute.

Dimension Operator

Use the Dimension operator to source data from or load data into dimensions and Slowly Changing Dimensions.

The Dimension operator contains one group for each level in the dimension. The groups use the same name as the dimension levels. The level attributes of each level are listed under the group that represents the level.

You cannot map a data flow to the surrogate identifier attribute or the parent surrogate identifier reference attribute of any dimension level. Oracle Warehouse Builder automatically populates these columns when it loads a dimension.

You can bind and synchronize a Dimension operator with a dimension stored in the workspace. To avoid errors in the generated code, ensure that the workspace dimension is deployed successfully before you deploy the mapping that contains the Dimension operator. To synchronize a Dimension operator with the workspace dimension, right-click the dimension on the Mapping Editor canvas and select Synchronize.

If you specify an Orphan Management Policy and create an error table for a dimension, when you add this dimension to a mapping, the Dimension operator contains a group called ERROR_<dimension_name>. This is an output group that contains attributes that are displayed in the dimension operator details, but not in the Dimension operator on the mapping canvas. To create data flows using these attributes, display these attributes on the canvas by selecting the ERROR_<dimension_name> group on the canvas and from the Graph menu, selecting Select Display Set, and then All.

To use a Dimension operator in a mapping:

1. Drag and drop a Dimension operator onto the Mapping Editor canvas.

   Oracle Warehouse Builder displays the Add Dimension dialog box.

2. Use the Add Dimension dialog box to select a dimension.

   Alternatively, you can combine Steps 1 and 2 into one single step. In the Mapping Editor, navigate to the Projects Navigator. Select the dimension and drag and drop it onto the Mapping Editor canvas.

3. Map the attributes from the Dimension operator to the target, or map attributes from the source to the Dimension operator.

Expand Object Operator

The Expand Object operator enables you to expand an object type and obtain the individual attributes that comprise the object type.

You can bind and synchronize an Expand Object operator with a workspace object type. To avoid generation errors in the mapping, ensure that you deploy the workspace object type before you deploy the mapping.

The Expand Object operator has one input group and one output group. The input group represents the object type to expand to obtain its individual attributes. When you bind an Expand Object operator to a workspace object, the output group of the operator contains the individual attributes that comprise the object type.

To successfully deploy a mapping that contains an Expand Object operator, ensure that the following conditions are satisfied.

• The schema that contains the source tables must be on the same instance as the warehouse schema.

• The warehouse schema is granted the SELECT privilege on the source tables.

• The warehouse schema is granted the EXECUTE privilege on all the object types and nested tables used in the Expand Object operator.

Figure 25-3 displays a mapping that uses an Expand Object operator. The source table CUSTOMERS contains a column CUSTOMER_ADDRESS of data type ADDR_TYPE, a SQL object type. But the target table CUST contains four different columns, of Oracle built-in data types, that store each component of the customer address. To obtain the individual attributes of the column CUSTOMER_ADDRESS, create an Expand Object operator that is bound to the object type ADDR_TYPE. You then map the CUSTOMER_ADDRESS column to the input group of an Expand Object operator. The output group of the Expand Object operator contains the individual attributes of the column CUSTOMER_ADDRESS. Map these output attributes to the target operator.

Figure 25-3 Expand Operator in a Mapping

Description of "Figure 25-3 Expand Operator in a Mapping"

To define an Expand Object operator in a mapping:

1. Drag and drop an Expand Object operator onto the Mapping Editor canvas.

2. Use the Add Expand Object dialog box to create or select an object. For more information about these options, see "Using the Add Operator Dialog Box to Add Operators".

3. Map the source attribute that is expanded to the input group of the Expand Object operator.

   The signature of the input object type should be same as that of the Expand Object operator.

4. Map the output attributes of the Expand Object operator to the target attributes.

External Table Operator

The External Table operator enables you to source data stored in external tables in the workspace. You can then load the external table data into another workspace object or perform transformations on the data. For example, you can source data stored in an external table, transform the data using mapping operators, and then load the data into a dimension or a cube.

Figure 25-4 displays a mapping that uses the External Table operator. The External Table operator EXPENSE_CATEGORY_EXT is bound to the external table of the same name in the workspace. The data stored in this external table is used to load the dimension EXPENSE_CATEGORIES.

Figure 25-4 External Table Operator in a Mapping

Description of "Figure 25-4 External Table Operator in a Mapping"

To create a mapping that contains an External Table operator:

1. Drag and drop an External Table operator onto the Mapping Editor canvas.

   Oracle Warehouse Builder displays the Add External Table dialog box.

2. Use the Add External Table dialog box to create or select an external table. For more information about these options, see "Using the Add Operator Dialog Box to Add Operators".

3. Map the attributes from the output group of the External Table operator to the target operator or the intermediate transformation operator.

Mapping Input Parameter Operator

You can introduce information external to Oracle Warehouse Builder as input into a mapping using a Mapping Input Parameter.

For example, you can use a Mapping Input Parameter operator to pass SYSDATE to a mapping that loads data to a staging area. Use the same Mapping Input Parameter operator to pass the timestamp to another mapping that loads the data to a target.

When you generate a mapping, a PL/SQL package is created. Mapping input parameters become part of the signature of the main procedure in the package.

The Mapping Input Parameter operator has a cardinality of one. It creates a single row set that can be combined with another row set as input to the next operator.

Each Mapping Input Parameter operator becomes an output attribute in the Mapping Input Parameter operator. These output attributes can then be used by connecting them to other operators within the Mapping Editor.

When you define the Mapping Input Parameter operator, you specify a data type and an optional default value.

To define a Mapping Input Parameter operator in a mapping:

1. Drag and drop a Mapping Input Parameter operator onto the Mapping Editor canvas.

2. Right-click the Mapping Input Parameter operator and select Open Details.

   The Mapping Input Parameter Editor is displayed.

3. Select the Output Attributes link on the left, to display the Output Attributes tab.

4. To add an output attribute, click a blank field in the Attribute column and provide a name for the output attribute. Also specify details such as data type, length, precision, scale, and seconds description for the attribute, as applicable.

   You can rename the attributes and define the data type and other attribute properties.

5. Click OK to close the Mapping Input Parameter Editor.

6. Connect the output attribute of the Mapping Input Parameter operator to an attribute in the target operator.

   Figure 25-5 displays the mapping that uses a Mapping Input Parameter operator.

   Figure 25-5 Mapping Editor Showing A Mapping Input Parameter

   
   Description of "Figure 25-5 Mapping Editor Showing A Mapping Input Parameter"
   
   

Mapping Output Parameter Operator

Use a single Mapping Output Parameter operator to send values out of a PL/SQL mapping to applications external to Oracle Warehouse Builder.

A Mapping Output Parameter operator is not valid for a SQL*Loader mapping. When you generate a mapping, a PL/SQL package is created. Mapping Output Parameters become part of the signature of the main procedure in the package.

The Mapping Output Parameter operator has only one input group and no output groups. You can have only one Mapping Output Parameter operator in a mapping. Only attributes that are not associated with a row set can be mapped into a Mapping Output Parameter operator. For example, constant, input parameter, output from a premapping process, or output from a post process can all contain attributes that are not associated with a row set.

To define a Mapping Output Parameter operator in a mapping:

1. Drag and drop a Mapping Output Parameter operator onto the Mapping Editor canvas.

2. Right-click the Mapping Output Parameter operator and select Open Details.

   The Mapping Output Parameter Editor is displayed.

3. Click the Input Attributes tab link on the left to display the Input Attributes page.

4. To add an input attribute, click a blank field in the Attribute column and provide a name for the input attribute. Also specify details such as data type, length, precision, scale, and seconds description for the attribute, as applicable.

   You can rename the attributes and define the data type and other attribute properties.

5. Click OK to close the Mapping Output Parameter editor.

6. Connect the input attribute of the Mapping Output Parameter operator to an attribute in the target operator.

   Figure 25-6 displays an example of a Mapping Output Parameter operator used in a mapping.

   Figure 25-6 Mapping Editor Showing An Output Parameter Operator

   
   Description of "Figure 25-6 Mapping Editor Showing An Output Parameter Operator"
   
   

Materialized View Operator

The Materialized View operator enables you to source data from or load data into a materialized view stored in the workspace.

For example, you can use the data stored in a materialized view to load a cube. The Materialized View operator has one I/O group called INOUTGRP1. You cannot add additional groups to this operator, but you can add attributes to the existing I/O group.

You can bind and synchronize a Materialized View operator to a workspace materialized view. The workspace materialized view must be deployed before the mapping that contains the Materialized View operator is generated to avoid errors in the generated code package.

Figure 25-7 displays a mapping that uses a Materialized View operator. The data from the two source tables PRODUCTS and ALL_SALES is joined using a Joiner operator. This data is then aggregated using an Aggregator operator. The aggregated data is used to load the materialized view SALES_MV.

Figure 25-7 Mapping that Contains a Materialized View Operator

Description of "Figure 25-7 Mapping that Contains a Materialized View Operator"

To create a mapping that contains a Materialized View operator:

1. Drag and drop a Materialized View operator onto the Mapping Editor canvas.

   Oracle Warehouse Builder displays the Add Materialized View dialog box.

2. Use the Add Materialized View dialog box to create or select a materialized view. For more information about these options, see "Using the Add Operator Dialog Box to Add Operators".

3. Map the attributes of the Materialized View operator.

   If you are using the Materialized View operator as a target, connect the source attributes to the Materialized View operator attributes. If you are using the materialized view as a source, then connect the Materialized View operator attributes to the target.

Queue Operator

A Queue operator enables you to use advanced queues as sources or targets in mappings.

Some of the most critical tasks in creating and maintaining a data warehouse include refreshing existing data, and adding new data from the operational databases. Use the Queue operator to capture changes made to source objects and send those changes to a staging database or directly to a data warehouse or operational data store.

Sequence Operator

A Sequence operator generates sequential numbers that increment for each row.

For example, you can use the Sequence operator to create surrogate keys while loading data into a dimension table. You can connect a Sequence to a target operator input or to the inputs of other types of operators. You can combine the sequence outputs with outputs from other operators.

Because sequence numbers are generated independently of tables, the same sequence can be used for multiple tables. Sequence numbers may not be consecutive, because the same sequence can be used by multiple sessions.

This operator contains an output group containing the following output attributes:

• CURRVAL: Generates from the current value

• NEXTVAL: Generates a row set of consecutively incremented numbers beginning with the next value

You can bind and synchronize Sequences to a workspace sequence in one of the modules. The workspace sequence must be generated and deployed before the mapping containing the Sequence is deployed to avoid errors in the generated code package. See "Using the Add Operator Dialog Box to Add Operators" for more information.

Generate mappings with sequences using row-based mode. Sequences may be incremented even if rows are not selected. If you want a sequence to start from the last number, then do not run your SQL package in set-based or in set-based with failover operating modes. See "Runtime Parameters" for more information about configuring mode settings.

Figure 25-8 shows a mapping that uses a Sequence operator to automatically generate the primary key of a table. The NEXTVAL attribute of the Sequence operator is mapped to an input attribute of the target table UNIFIED_PRODUCTS. The other input attributes from the source table, ORACLE_PRODUCTS, are mapped directly to the target.

Figure 25-8 Sequence Operator in a Mapping

Description of "Figure 25-8 Sequence Operator in a Mapping"

To define a Sequence operator in a mapping:

1. Drag and drop the Sequence operator onto the Mapping Editor canvas.

   Oracle Warehouse Builder displays the Add Sequence dialog box.

2. Use the Add Sequence dialog box to create or select a sequence. For more information about these options, see "Using the Add Operator Dialog Box to Add Operators".

3. Connect the required output attribute from the Sequence operator to a target attribute.

Table Operator

The Table operator enables you to source data from and load data into tables stored in the workspace.

You can bind and synchronize a Table operator to a workspace table. To avoid errors in the generated code package, the workspace table must be deployed before the mapping that contains the Table operator is generated.

Figure 25-8 displays a mapping that uses Table operators as both source and target. Figure 25-2 displays a mapping that uses the Table operator as a target.

To define a Table operator in a mapping:

1. Drag and drop a Table operator onto the Mapping Editor canvas.

   Oracle Warehouse Builder displays the Add Table dialog box.

2. Use the Add Table dialog box to create or select a table. For more information about these options, see "Using the Add Operator Dialog Box to Add Operators".

3. Map the attributes of the Table operator.

   If you are using the table as a target, connect the source attributes to the Table operator attributes. If you are using the table as a source, then connect the Table operator attributes to the target.

Varray Iterator Operator

When you have an object of type nested table or Varray, you can use the Varray Iterator operator to iterate through the values in the table type.

This operator accepts a table type attribute as the source, and generates a value that is of the base element type defined in the nested table or Varray type. If the operator is bound, then reconciliation operations are performed on the operator. Reconciliation operations are not supported for unbound Varray Iterator operators.

You can create the Varray Iterator operator as either a bound operator or an unbound operator. You cannot synchronize or validate an unbound Varray Iterator operator. It has only one input group and one output group. You can have an input group with attributes of other data types. However, there must be at least one table type attribute.

The attributes of the output group are a copy of the attributes of the input group. The only difference is that instead of the table type to which the operator is bound (which is one of the input group attributes), the output group has an attribute that equals the base element type of the input group. The input group is editable. The output group is not editable.

Figure 25-9 displays a mapping that contains a Varray Iterator operator.

Figure 25-9 Varray Iterator Operator in a Mapping

Description of "Figure 25-9 Varray Iterator Operator in a Mapping"

To define a Varray Iterator operator in a mapping:

1. Drag and drop a Varray Iterator operator onto the Mapping Editor canvas.

   Oracle Warehouse Builder displays the Add Varray Iterator dialog box.

2. From the Add Varray Iterator dialog box, select either an unbound operator or a bound operator.

   • If you select the unbound operator, then a Varray Iterator operator with no attributes is created. You must create these attributes manually.

   • If you select the bound operator, then you must select one of the available nested table or Varray types shown in the tree. The output attribute equals the base element.

3. Click OK.

4. Map the attributes of the Varray Iterator operator.

For an unbound operator, right-click the unbound Varray Iterator operator on the Mapping Editor canvas and then select Open Details. This opens the Varray Iterator editor dialog box. You can add attributes to the input group by using the Add button. You can only change the data type of the attributes in the output group.

View Operator

The View operator enables you to source data from or load data into a view stored in the workspace.

You can bind and synchronize a View operator to a workspace view. The workspace view must be deployed before the mapping that contains the View operator is generated to avoid errors in the generated code package.

To define a View operator in a mapping:

1. Drag and drop a View operator onto the Mapping Editor canvas.

   Oracle Warehouse Builder displays the Add View dialog box.

2. Use the Add View dialog box to create or select a view. For more information about these options, see "Using the Add Operator Dialog Box to Add Operators".

3. Map the attributes of the View operator.

   If you are using the view as a target, connect the source attributes to the View operator attributes. If you are using the view as a source, then connect the View operator attributes to the target.

Limitations of Using Non-Oracle or Remote Targets

The following limitations apply when you use a remote or Gateway target in a mapping:

• You cannot set the Loading Type property of the target operator to TRUNCATE/INSERT.

  This results in a validation error when you validate the mapping.

• For Gateway targets, setting the Loading Type property of the target operator to INSERT/UPDATE produces the same result as setting the loading type to INSERT.

• The RETURNING clause is not supported in a DML statement.

  The RETURNING clause enables you to obtain the ROWIDs of the rows that are loaded into the target using row-based mode. These ROWIDs are recorded by the run time auditing system. But in a remote or Gateway target, the RETURNING clause is not generated, and nulls are passed to the run time auditing system for the ROWID field.

• In set-based mode, you cannot load data from an Oracle database into a remote or Gateway target. All other modes, including set-based failover, are supported.

  When you set the Operating Mode property of the target operator to set-based, a run time error occurs.

• Row-based bulk processing is not supported.

Oracle Warehouse Builder Workarounds for Non-Oracle and Remote Targets

When you use a remote or Gateway target in a mapping, default workarounds are used for certain restricted activities. These workarounds are listed for your information only. You need not explicitly do anything to enable these workarounds.

The default workarounds used for a remote or a Gateway target are as follows:

• When you set the loading type of a target to INSERT/UPDATE or UPDATE/INSERT in Oracle9i database and to UPDATE in Oracle Database 11g, a MERGE statement is generated to implement this mapping in set-based mode. But a MERGE statement cannot be run against remote or Gateway targets. Thus, when you use a remote or Gateway target in a mapping, code is generated without a MERGE statement. The generated code equals that generated when the PL/SQL generation mode is set to Oracle8i.

• For set-based DML statements that reference a database sequence that loads into a remote or Gateway target, the GLOBAL_NAMES parameter must be set to TRUE. When code is generated for a mapping, this parameter is set to TRUE if the mapping contains a remote or Gateway target.

• For a multitable insert to a remote or Gateway target, an INSERT statement is generated per table instead of a multitable insert statement.

• While performing bulk inserts on a remote or non-Oracle Database, bulk processing code is not generated. Instead, code that processes one row at a time is generated. The Generate Bulk property of the operator is ignored.

Flat File Operator

Use a Flat File operator to extract data from or load data into flat file.

A Flat File operator can be used either as a source or target in a mapping. However, the two are mutually exclusive within the same mapping. There are differences in code generation languages for flat file sources and targets. Subsequently, mappings can contain a mix of flat files, relational objects, and transformations, but with the restrictions discussed later in this section.

You have the following options for Flat File operators:

• Using previously imported flat files

• Importing and binding new flat files into your mapping

• Defining new flat file sources or targets in mappings

To use a Flat File operator in a mapping:

1. Drag and drop a Flat File operator onto the Mapping Editor canvas.

2. Use the Add Flat File dialog box to create or select an object. For more information about these options, see "Using the Add Operator Dialog Box to Add Operators".

3. Map the attributes from the Flat File operator to the target, or map attributes from the source to the Flat File operator.

For examples of using flat files as sources and targets in a mapping, see Chapter 7, "Using SQL*Loader, SAP, and Code Template Mappings".