Oracle Database
This page shows how to connect to an Oracle database and write SAP data to it. The setup in Xtract Universal.iQ consists of 3 main steps:
Connect to an Oracle Database
To create a connection to an Oracle database:
- Open the Connections menu.
- Click [Add Connection].
- Enter a name in the Connection Name field, e.g., the name of your server.
- Select Microsoft SQL Server from the Connection Type dropdown. The corresponding settings open.
- Enter the server address of the Oracle database in the system settings, e.g.,
myServer.exampleor192.168.1.1. - Enter user credentials in the authentication settings.
- Enter the name (SID) or alias of an existing database in the database settings.
- Click [Test Connection] to validate the connection parameters. A status message opens in the bottom right corner of the window.
- If the connection is successful, click [Save].
The connection is now listed in the Connections menu and can be assigned to services.
Next, assign the destination to a service.
Assign Oracle Database to a Service
To assign the Oracle Database destination to a service:
- Open the Services menu.
- Select one or more services.
- Click [ Edit].
- Select an existing Oracle Database connection from the Destination dropdown.
- Click [Save].
When running the service, the data is written to the Oracle database.
Next, check if the service-specific destination settings match your needs.
Connection Settings
Connection settings define how Xtract Universal.iQ connects to an Oracle database. To open the connection settings:
- Open the Connections menu.
- Select a connection of type Oracle Database.
- Click [ Edit].
The connection settings of the Oracle Database connection open and can be modified.
System
The System section contains settings that define which Oracle server to connect to.
Host
Enter the hostname of the Oracle database server.
Port
Enter the Oracle server connection port. The default port is 1521.
Authentication
The Authentication section contains settings that define how to connect to a Oracle Database.
Username
Enter the username of an Oracle account that has access rights to the desired database.
Password
Enter the password of the Oracle account.
Database
The Database section contains settings that define which Oracle Database database to connect to.
SID / Service name
Enter the unique name (SID) or the alias (service name) of the Oracle database.
Wallet location
Connect to an Oracle Database using a connection from a wallet. Use this option when mTLS (Mutual TLS) authentication is required. To use a wallet, add the wallet to the container using the input/ folder and enter the exact path to the wallet.
Test Connection
Check the database connection.
Destination Settings
Destination settings define how and where a service saves its output data, such as the target folder and filename. To open the destination settings:
- Open the Services menu.
- Select a service that uses the Oracle Database connection.
- Click [ Edit]. The service settings open.
- Open the tab Destination Settings.
The destination settings for Oracle Database are open and can be modified.
File Name
Determines the name of the target table. The following options are available:
| Option | Description |
|---|---|
| Same as service name | Adopt the name of the service. |
| Custom | Define a name of your choice in the Custom File Name input field. |
Append timestamp
Adds a timestamp in the format _YYYY_MM_DD to the filename of the service.
Column Name Format
Defines the format of the column name. The following options are available:
| Option | Description |
|---|---|
| Code | The SAP technical column name is used as column name in the destination e.g., MAKTX. |
| PrefixedCode | The SAP technical column name is prefixed by SAP object name and the tilde character e.g., MAKT~MAKTX |
| CodeAndText | The SAP technical column name and the SAP description separated by an underscore are used as column name in the destination e.g., MAKTX_Material Description (Short Text). |
| TextAndCode | The SAP description and the SAP technical column name description separated by an underscore are used as column name in the destination e.g., Material Description (Short Text)_MAKTX. |
| Text | The SAP description is used as column name in the destination e.g., Material Description (Short Text). |
Processing
The Processing section defines how data is written to the database using preparation, row processing and finalization steps.
Preparation
The preparation step defines the action on the target database before the data is inserted into the target table.
| Option | Description |
|---|---|
| Drop & Create | Remove table if available and create new table (default). |
| Truncate Or Create | Empty table if available, otherwise create. |
| Create | Create table if not available. |
| Prepare Merge | Prepares the merge process and creates e.g. a temporary staging table, see Merge Data. |
| None | No action. |
| Custom SQL | Here you can define your own script. |
Row Processing
The row processing step defines how the data is inserted into the target table.
| Option | Description |
|---|---|
| Insert | Insert records (default). |
| Fill merge staging table | Insert records into the staging table. |
| None | No action. |
| Custom SQL | Define your own script. |
Finalization
The finalization step defines the action on the target database after the data has been successfully inserted into the target table.
| Option | Description |
|---|---|
| Finalize Merge | Closes the merge process and deletes the temporary staging table, for example. |
| None | No action (default). |
| Custom SQL | Define your own script. |
Transaction Style
The transaction style defines how the data is transferred to the target database.
| Option | Description |
|---|---|
| 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. Recommendation: Only use One Transaction in combination with DML commands, e.g., "truncate table" and "insert". Using DDL commands commits the active transaction, causing rollback issues for the steps after the DDL command. Example: if a table is created in the preparation step, the opened "OneTransaction" is committed and a rollback in the next steps is not performed correctly. |
| 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 a previous step possible (an 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 run without an explicit transaction (implicit commits). Advantage: DDL in Preparation and Finalization for DBMS platforms that do not allow DDL in explicit transactions, e.g., AzureDWH. Disadvantage: No rollback of Preparation or Finalization. |
| No Transaction | No explicit transactions. Advantage: No transaction management required by the DBMS (locking, transaction logs, etc.). This means no locking and possible performance advantages. Disadvantage: No rollback. |
Enable Debug Mode
Warning
Performance decrease!
The performance decreases when bulk insert is disabled. Disable the bulk insert only when necessary, e.g., upon request of the support team.
Enabling the checkbox Enable debug mode (slower, more detailed error messages), deactivates the default bulk insert 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 regarding the stored data type.
Disable the checkbox after successful error analysis, otherwise the performance of the database write processes remains low.
Note
Bulk operations are not supported when using Custom SQL statements, e.g., in Row Processing. Bulk operations lead to performance decrease. To increase performance when using Custom SQL statements, it is recommended to perform the custom processing in the Finalization step.
Date Conversion
Convert SAP Dates
Converts the SAP date format (YYYYMMDD) to a regular date format (YYYY-MM-DD), e.g., 19900101 becomes 1990-01-01. Target data uses a real date data-type and not the string data-type to store dates.
Replace SAP Dates 0000XXXX with
Converts the SAP date 00000000 to the entered value.
Replace SAP Dates 9999XXXX with
Converts the SAP date 9999XXXX to the entered value.
Replace Invalid SAP Dates with
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.