Oracle

This page shows how to set up and use the Oracle destination. The Oracle destination loads data to an Oracle database.

Requirements

As of Xtract Universal version 4.2.34.0 the Oracle data provider is included in to the setup of Xtract Universal. There are no additional installations needed to use Oracle database destination.

Create a new Oracle Destination

Follow the steps below to add a new Oracle destination to Xtract Universal:

1. In the main window of the Designer, navigate to Server > Manage Destinations. The window “Manage Destinations” opens.
   
2. Click [Add] to create a new destination. The window "Destination Details" opens.
   
3. Enter a Name for the destination.
4. Select the destination type Oracle from the drop-down menu. A list of connection details opens.
5. Fill out the destination details to connect to the destination.
6. Click [OK] to confirm your input.

The destination can now be assigned to extractions.

Destination Details

The destination details define the connection to the destination. The Oracle destination supports 3 types of connections:

The input fields vary depending on the selected authentication method.

Default

Host

Enter the name of the Oracle server.

Port

Enter the Oracle server connection port (Default: 1521).

SID / Service name

Enter the unique name (SID) or the alias (service name) of the Oracle database.

Username

Enter the user name.

Password

Enter the password.

Test Connection

Check the database connection.

Wallet

TNS Name

Enter the TNS name of the connection as it is stored in the tnsnames.ora file in your wallet.
For more information, see Oracle Documentation: Download Client Credentials (Wallets).

Username

Enter the user name.

Password

Enter the password.

Wallet location

Enter the path to your wallet, e.g., C:\Oracle\Wallet.
Note that the wallet location must be accessible for the user that runs the Xtract Universal service.

Test Connection

Check the database connection.

Connect Descriptor

Connect Descriptor

Enter a connect descriptor (connection string), see Oracle Documentation: View TNS Names and Connection Strings for an Autonomous Database Instance. A Connect Descriptor uses the following format:

(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = [oracle host name])(PORT = [port number]))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = [oracle service name])))

Username

Enter the user name.

Password

Enter the password.

Test Connection

Check the database connection.

Assign the Oracle Destination to an Extraction

Extractions write data to their assigned destination. Follow the steps below to assign a destination to an extraction:

1. In the main window of the Designer, select an extraction.
2. Click [ Destination]. The window “Destination Settings” opens.
   
3. In the “Destination Settings” window, select a destination from the dropdown list.
   
4. Optional: edit the destination settings.
5. Click [OK] to confirm your input.

When running the extraction, the extracted SAP data is now written to the destination.

Destination Settings

The destination settings only affect the extraction that the destination is assigned to. To open the destination settings, select an extraction in the main window of the Designer and click [ Destination]. The window "Destination Settings" opens.

File Name

Determines the name of the target table. The following options are available:

Append timestamp

Add the timestamp in the UTC format (_YYYY_MM_DD_hh_mm_ss_fff) to the file name of the extraction

Use Script Expressions as Dynamic File Names

Script expressions can be used to generate a dynamic file name. This allows generating file names that are composed of an extraction's properties, e.g. extraction name, SAP source object. This scenario supports script expressions based on .NET and the following XU-specific custom script expressions:

#{Source.Name}#

#{Extraction.ExtractionName}#

#{Extraction.FullyQualifiedExtractionName}#

#{Extraction.Type}#

#{Extraction.SapObjectName}#

#{Extraction.Timestamp}#

#{Extraction.SapObjectName.TrimStart("/".ToCharArray())}#

#{Extraction.SapObjectName.Replace('/', '_')}#

#{Extraction.Fields["FISCPER"].RangeSelections[0].LowerValue}#

#{Extraction.Fields["FISCPER"].RangeSelections[0].UpperValue}#

#{Extraction.Context}#

#{Extraction.Fields["[NameSelectionFiels]"].Selections[0].Value}#

#{Odp.UpdateMode}#

#{TableExtraction.WhereClause}#

Column Name Style

Defines the style of the column name. Following options are available:

Convert dates

Converts the character-type SAP date (YYYYMMDD, e.g., 19900101) to a special date format (YYYY-MM-DD, e.g., 1990-01-01). Target data uses a real date data-type and not the string data-type to store dates.

Year 0

Converts the SAP date 00000000 to the entered value.

Year 9999

Converts the SAP date 9999XXXX to the entered value.

Invalid values

If an SAP date cannot be converted to a valid date format, the invalid date is converted to the entered value. NULL is supported as a value. When converting the SAP date the two special cases 00000000 and 9999XXXX are checked first.

Preparation

Defines the action on the target database before the data is inserted into the target table.

To only create the table in the first step and not insert any data, you have two options:

1. Copy the SQL statement and execute it directly on the target data database.
2. Select the None option for Row Processing and execute the extraction.

Once the table is created, it is up to you to change the table definition, e.g., by creating corresponding key fields and indexes or additional fields.

Row Processing

Defines how the data is inserted into the target table.

Finalization

Defines the action on the target database after the data has been successfully inserted into the target table.

Debugging

By activating the checkbox Disable bulk operations, the default bulk insert is deactivated when writing to a database.

This option enables detailed error analysis, if certain data rows cannot be persisted on the database. Possible causes for the incorrect behavior are incorrect values with regard to the stored data type.

Debugging needs to be deactivated again, after the successful error analysis, otherwise the performance of the database write processes remains low.

Transaction style

One Transaction

Preparation, Row Processing and Finalization are all performed in a single transaction.

• Advantage: clean rollback of all changes.
  
• Disadvantage: possibly extensive locking during the entire extraction period.

Three Transactions

Preparation, Row Processing and Finalization are each executed in a separate transaction.

• Advantage: clean rollback of the individual sections, possibly shorter locking phases than with One Transaction (e.g. with DDL in Preparation, the entire DB is only locked during preparation and not for the entire extraction duration).
  
• Disadvantage: no rollback of previous step possible (error in Row Processing only rolls back changes from Row Processing, but not Preparation).

RowProcessingOnly

Only Row Processing is executed in a transaction. Preparation and Finalization without an explicit transaction (implicit commits).

• Advantage: DDL in 'Preparation and Finalization* for DBMS that do not allow DDL in explicit transactions (e.g. AzureDWH)
  
• Disadvantage: no rollback of Preparation/Finalization.

No Transaction

No explicit transactions.

• Advantage: no transaction management required by DBMS (locking, DB transaction log, etc.). This means no locking and possible performance advantages.
  
• Disadvantage: no rollback

Merge Data

The following example depicts the update of the existing data records in a database by running an extraction to merge data. In this case, merging means changing a value of a field or inserting a new data row or updating an existing record in SAP.

With a merge command, the updated value is written to the destination database table. The merge command ensures delta processing: new records are inserted into the database and / or existing records are updated.

Prerequisites

You need a table with existing SAP data, in which new data can be merged.
Ideally, the table with existing data is created in the initial load with the corresponding Preparation option and filled with data with the Row Processing option Insert.

After the table is created, open SAP and change a field value in the SAP table that is used for the data merge.

Merge Command

The merge process is performed using a staging table and takes place in three steps:

1. A temporary table is created.
2. The data is inserted in the temporary table.
3. The temporary table is merged with the target table and then the temporary table is deleted.

Follow the steps below to set up the merge process in Xtract Universal:

1. In the main window of the Xtract Universal Designer, select an extraction from the list of extractions.
2. Click [ Destination]. The window "Destination Settings" opens.
3. Make sure to assign Oracle destination to the extraction.
4. Apply the following destination settings:
   
5. Click [OK] and run the extraction.

More information about the updated fields can be found in the SQL statement.
It is possible to edit the SQL statement if necessary, e.g., to exclude certain columns from the update.
Fields that do not appear in the SQL statement are not affected by changes.

Custom SQL Statements

The Oracle destination supports the use of custom SQl statements in the staging steps of the database. Follow the steps below to create custom SQL statements in the Oracle destination:

1. In the main window of the Xtract Universal Designer, select an extraction from the list of extractions..
2. Click [ Destination], the window "Destination Settings" opens.
3. Make sure to assign the Oracle destination to the extraction.

4. Select the option Custom SQL from the drop-down list in one of the following sections:

   • Preparation
   • Row Processing
   • Finalization

   

5. Click [Edit SQL] . The window "Edit SQL" opens.

6. Enter your custom SQL statement and click [OK] to confirm your input.

Use Templates

Existing SQL commands can be used as templates.
You can write your user-defined SQL expressions and adapt the loading of the data to your needs. You can additionally execute stored procedures that exist in the database. To do so, use the SQL templates provided in the following staging steps:

• Preparation, e.g., in Drop & Create or Create if Not Exists
• Row Processing, e.g., in Insert or Merge
• Finalization

Follow the steps below to generate a Custom SQL command from a template:

1. In one of the staging steps, select the Custom SQL option from the drop-down list .
2. Click [Edit SQL] . The dialogue "Edit SQL" opens.
   
3. Navigate to the drop-down menu and select an existing command .
4. Click [Generate Statement]. A new statement is generated.
   
5. Click [Copy] to copy the statement to the clipboard.
6. Click [OK] to confirm your input.

Check out the Microsoft SQL Server example for details on predefined expressions.

Use Script Expressions

You can use script expressions for Custom SQL commands. The following Xtract Universal specific custom script expressions are supported:

#{Extraction.ExtractionName}#

#{Extraction.FullyQualifiedExtractionName}#

#{Extraction.TableName }#

#{Extraction.RowsCount }#

#{Extraction.RunState}#

#{(int)Extraction.RunState}#

#{Extraction.Timestamp}#

For more information, see Script Expressions.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

#{
   iif
   (
      ExistsTable("MAKT"),
      "TRUNCATE TABLE \"MAKT\";",
      "
         CREATE TABLE \"MAKT\"(
            \"MATNR\" VARCHAR(18),
            \"SPRAS\" VARCHAR(2),
            \"MAKTX\" VARCHAR(40));
      "
   )
}#

Create a Status Overview

The table "ExtractionStatistics" provides an overview and status of the executed Xtract Universal extractions. To create the "ExtractionStatistics" table, create an SQL table according to the following example:

1
2
3
4
5
6
7

CREATE TABLE [dbo].[ExtractionStatistics](
    [TableName] [nchar](50) NULL,
    [RowsCount] [int] NULL,
    [Timestamp] [nchar](50) NULL,
    [RunState] [nchar](50) NULL
) ON [PRIMARY]
GO

The ExtractionStatistics table is filled in the Finalization process step, using the following SQL statement:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

INSERT INTO [ExtractionStatistics]
(
     [TableName], 
     [RowsCount], 
     [Timestamp],
     [RunState]
)
VALUES
(
     '#{Extraction.TableName}#', 
     '#{Extraction.RowsCount}#', 
     '#{Extraction.Timestamp}#',
     '#{Extraction.RunState}#'
);