User Guide : Map Connectors : Source and Target Map Connectors : Oracle 10g, 11g, and 12c
 
Share this page                  
Oracle 10g, 11g, and 12c
Oracle is a relational database management system. The Oracle 10g, 11g, and 12c connectors enable the integration platform to perform create, read, update, and delete operations on Oracle 10g, 11g, and 12c data tables.
Note:  You must have the Oracle 10g, 11g, or 12c standard 64-bit client installed on your system and you must have access to Oracle. If Oracle is installed on your local PC, the drive and directory path of your Oracle application must be included in your path. The path statement is automatically modified when your Oracle client software is installed. The Oracle Database Manager must be running.
Oracle 11g and 12c connectors support Oracle wallet to manage public key security credentials on Oracle clients. If you have configured the Oracle client to use Oracle wallet, then you can provide only the Database Alias name and connect to the source or target within a map (User Name and Password is not required.) For information about Oracle wallet and how to create it in the client, see Using Oracle Wallet Manager section in the Oracle Database Advanced Security Administrator's Guide available at the following location:
https://docs.oracle.com/cd/B28359_01/network.111/b28530/asowalet.htm#i1006322
Other Oracle Connectors
You can connect to Oracle interfaces in the following ways:
Oracle Multimode: Perform multiple operations (such as table drops, table inserts) directly on your target database. See Oracle 10g, 11g, and 12c Multimode.
Oracle Direct Path: Deliver incremental loads of detail data to data warehouses and data marts. See Oracle Direct Path 10g, 11g, and 12c.
Oracle SQL Loader: Produce flat loader files that can be uploaded into an Oracle database using the Oracle SQL Loader utility. See Oracle SQL Loader.
Oracle Siebel CRM On Demand 12 and 14: Access data through the OnDemand Web Services interface with this multimode connector. See Oracle Siebel CRM On Demand 12 and 14.
This connector sets field width in bytes. What actually varies is the number of characters that fit into a given field. For example, if a field width is 10 bytes and the encoding is Shift-JIS, the field can hold a minimum of 5 characters and a maximum of 10. This means that if you try to write 8 Japanese characters, data is truncated (the Kanji characters all take 2 bytes).
Global Transactions Supported
Oracle 10g, 11g, and 12c connectors support global transactions. When used with shared server mode, you can reference database links in Oracle SQL queries on source tables. For more information, see Oracle Database Global Transaction Support.
Unicode Support
The Oracle 10g, 11g, and 12c connectors include Unicode support for wide characters. To enable double-byte support, change the Regional and Language Options on your system to a double-byte language, such as Japanese.
Connectivity Pointers
The Oracle standard client must be installed to connect to Oracle 10g, 11g, and 12c databases.
If your database has many tables, selecting the table from a list may take more time than typing the UserID.Tablename value in the Source Table/View box. If you enter the table name without the user ID and then connect to the database, you can reselect the table in the source data browser to view it.
If possible, create query statements using the source database application, so that syntax and validity can be checked by the native application. You can then copy the query statement and paste it in the Query Statement box.
Connector-Specific Notes
The BINARY_FLOAT and BINARY_DOUBLE data types are not supported on Linux systems.
Property Options
You can set the following source (S) and target (T) properties.
Property
ST
Version
Use
AllowDistributedTransactions
ST
10g, 11g, 12c
If set to true (default), distributed transaction processing is allowed.
AppendHint
T
10g, 11g, 12c
Applies only to insert operations. The default value is false. If this property is set to true, the hint is used in the Insert statement.
ArraySize
T
10g, 11g, 12c
Number of records fetched or inserted with each cursor operation. Default is zero. Maximum allowed is 100.
Code Page
ST
10g,11g
The code page translation table tells the integration platform which encoding to use for reading and writing data. The default is ANSI, which is the standard in the US. For the supported list, see CodePage.
CommitFrequency
T
10g, 11g, 12c
Controls how often data is committed to the database. Default is zero, meaning that data is committed at the end of the transformation, allowing rollback on error. This is the slowest setting. When doing large transformations, this is not practical as it may produce too many transaction log entries. Setting the CommitFrequency to some nonzero value tells the connector to do a database commit after inserting or updating the specified number of records. This keeps the transaction log from getting too large but limits the restartability of the transformation.
ConstraintDDL
T
10g, 11g, 12c
Pass-through mechanism that allows you to specify any additional data definition language (DDL) statements that need to be executed when a new table is created. DDL is used to define keys, indexes, constraints and integrity rules for SQL databases. There is no default.
Directory Alias
T
10g, 11g, 12c
Allows you to specify the name of the Directory Alias. No default exists for this property.
Encoding
ST
11g, 12c
Determines how to store characters in Oracle 11g and 12c source and target files. Default is OEM.
For choices, see Encoding Reference.
ParallelHint
ST
10g, 11g, 12c
Use this property to include Parallel Hints in statements. Supported ParallelHint values are listed below
0 (default) – No hint is included.
1 – The hint is included in statements as: "parallel(table, DEFAULT)". This sets the degree of parallelism to the settings of the initialization parameters.
> 1(greater than 1) – The value is used as a degree of parallelism, with the hint included in statements as: "parallel(table, PROPVAL)".
QueryRewrite
S
12c
Allows connection to enable query rewriting and to set integrity levels. Property behavior and integrity levels are listed below:
None - No altered session is issued and query rewriting is disabled.
Enforced - The session is altered to allow query rewriting. A materialized view is used, provided that it guarantees the same result as using the detail tables. QueryRewrite can use no stale materialized views or trust relationships. This is the default value.
Trusted - The session is altered to allow query rewriting. A materialized is used, provided that it is not stale. However, QueryRewrite may use trusted relationships, such as those declared by dimension objects or constraints that have not been validated.
Stale Tolerated - The session is altered to allow query rewriting. The materialized view is used even when data in detail tables has changed. Note that this property must be set before connecting since the alter sessions occur during connection.
Synonyms
ST
10g, 11g, 12c
If set to true, allows you to see synonyms. The alias names appear in the table list along with the tables. Default is false.
SystemTables
ST
10g, 11g, 12c
If set to true, this property allows you to see all tables created by the DBA in the database. The system table names appear in the table list. Default is false.
UseArrays
T
10g, 11g, 12c
To use arrays, select true. The default is false (no arrays). UseArrays allows this connector to send batches of records (multiple rows/columns contained in one block as a blob array) to an Oracle Target at one time. This reduces some of the function call and network overhead that would occur if each record had to be sent to Oracle one at a time. Performance improvements of about four times occur with array inserts.
Limitation: Array inserts make error handling difficult. If one of the records has invalid data, such as nulls or a duplicate key, you get one error for the entire batch of records. Because of this, using reject files is not suggested when using this property.
Views
ST
10g, 11g, 12c
If set to true (default), allows you to see views. View names appear in the table list along with table names.
WhereStmt
S
10g, 11g, 12c
Provides a pass-through mechanism where advanced users can construct the Where clause of the query themselves. It can be used as an alternative to writing a lengthy query statement. This might be used to get the Oracle server to filter the data based on a condition before it is sent to the integration platform. Omit WHERE when you enter the clause. No default exists for this property.
CodePage
This translation table determines which encoding to use for reading and writing data. The default is ANSI, the standard in the US. The following code pages are available:
ANSI (default)
OEM
0037 US (EBCDIC)
0273 Germany (EBCDIC)
0277 Norway (EBCDIC)
0278 Sweden (EBCDIC)
0280 Italy (EBCDIC)
0284 Spain (EBCDIC)
0285 UK (EBCDIC)
0297 France (EBCDIC)
0437 MSDOS United States
0500 Belgium (EBCDIC)
0850 MSDOS Multilingual (Latin 1)
0860 MSDOS Portuguese
0861 MSDOS Icelandic
0863 MSDOS Canadian French
0865 MSDOS Nordic
1051 Roman-8
Length
These are field lengths in your data. If you need to change field lengths, reset them in the schema.
Caution!  A field length shorter than the default may truncate data.
Data Types
The following data types are supported.
Name
Syntax
Notes
VARCHAR2
(size[BYTE|CHAR])
Variable-length character string having maximum length of size bytes for characters. Maximum size is 4000 bytes or characters and minimum is 1 byte or 1 character. You must specify size for VARCHAR2.
BYTE indicates the column has byte length semantics; CHAR indicates the column has character semantics.
NVARCHAR2
(size)
Variable-length character string having maximum length of size characters. Maximum size is determined by the national character set definition. You must specify size for NVARCHAR2.
NUMBER
(p,s)
Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.
LONG
N/A
Character data of variable length up to 64000 bytes.
DATE
N/A
Valid date range from January 1, 4712 BC to December 31, 9999 AD.
BINARY_FLOAT
N/A
32-bit floating point number. This data type requires 5 bytes, including the length byte.
BINARY_DOUBLE
N/A
64-bit floating point number. This data type requires 9 bytes, including the length byte.
TIMESTAMP
(fractional_seconds_precision)
Year, month and day values of date, as well as hour, minute and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values of fractional_seconds_precision are 0 to 9. The default is 6.
TIMESTAMP with TIMEZONE
(fractional_seconds_precision)
All values of TIMESTAMP as well as TIME ZONE displacement value, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6.
TIMESTAMP with LOCAL TIME ZONE
(fractional_seconds_precision)
All values of TIMESTAMP with TIME ZONE, with the following exceptions:
Data is normalized to the database time zone when it is stored in the database.
When the data is retrieved, users see the data in the session time zone.
INTERVAL YEAR TO MONTH
(year_precision)
Stores a period of time in years and months, where year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default is 2.
INTERVAL DAY TO SECOND
(day_precision) (fractional_seconds_precision)
Stores a period of time in days, hours, minutes and seconds, where
day_precision is the maximum number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2.
fractional_seconds_precision is the number of digits in the fractional part of the SECOND field. Accepted values are 0 to 9. The default is 6.
RAW
(size)
Raw binary data length of size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.
LONG RAW
N/A
Raw binary data of variable length up to 2 GB.
ROWID
N/A
Base 64 string representing the unique address of a row in its table. This data type is primarily for values returned by the ROWID pseudocolumn.
UROWID
[(size)]
Base 64 string representing the logical address of a row of an index-organized table. The optional size is the size of a column of type UROWID. The maximum size and default is 4000 bytes.
CHAR
(size [BYTE|CHAR])
Fixed-length character data of length of size bytes. Maximum size is 2000 bytes or characters. Default and minimum size is 1 byte. BYTE and CHAR have the same semantics as VARCHAR2.
NCHAR
(size)
Fixed-length character data of length of size characters. Maximum size is determined by the national character set definition, with an upper limit of 2000 bytes. Default and minimum size is 1 character.
CLOB
N/A
A character large object containing single- or multibyte characters. Both fixed and variable-width character sets are supported, both using the database character set. Maximum size is (4 GB - 1 byte) * (database block size).
NCLOB
N/A
A character large object containing Unicode characters. Both fixed-width and variable width character sets are supported, both using the database national character set. Maximum size is (4 GB - 1 byte) * (database block size). Stores national character set data.
BLOB
N/A
A binary large object. Maximum size is (4 GB - 1 byte) * (database block size).
BFILE
N/A
Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 GB.