User Guide : Map Connectors : Source and Target Map Connectors : Oracle 10g, 11g, 12c and 19c Direct Path
 
Share this page             
Oracle 10g, 11g, 12c and 19c Direct Path
Oracle's Direct Path interface is a way for data warehouses and data marts to perform incremental loads of detail data. The Oracle Direct Path 10g, 11g, 12c, and 19c connectors enable the integration platform to write to Oracle tables.
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, 12c and 19c Multimode.
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 CRM On Demand: Access data through the OnDemand Web Services interface with this connector. See Oracle CRM On Demand.
Note:  You must have the Oracle 10g, 11g, 12c, or 19c 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.
The Oracle Direct Path connector accesses Oracle 10g, 11g, 12c, and 19c databases. The advantage of Oracle Direct Path API is that it is faster than the conventional ways of loading data into an Oracle database. Oracle's SQL loader utility uses the same API included in Oracle. Most of the Direct Path API speed comes from circumventing work done with conventional methods. Four instances make inserts into an Oracle table slow:
Transaction logging
Transaction logs are used to undo changes if something goes wrong. In the case of bulk loads, this is not usually an issue, since bulk loads are generally done to new or empty tables.
Indexing
Indexing speeds up queries to the data in a table, but slows down the process of inserting data. For each record that is inserted, the DBMS adds a new entry in each index in the table. It is often faster to load the data without indexing and to rebuild the indexes as a separate step.
Integrity checking
This process checks for duplicate keys, Null values and other integrity rule violations. These DBMS data validations take time.
Transaction isolation
In these transactions, the DBMS makes changes only visible to users once the work is committed.
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).
Connector-Specific Notes
Mass load improves performance. This connector is faster than the standard Oracle connector, however, it works best in mass load. This means that data is committed at the end of the transformations and not for Append mode. Use Replace mode for the fastest run times; if you want to use Append, use one of the other Oracle connectors instead. See Oracle 10g, 11g, 12c, and 19c.
Restrictions on Using Direct Path Loads. In addition to the general load conditions described in Oracle’s documentation, the following conditions must be satisfied to use the direct path load method:
Tables are not clustered.
Tables to be loaded do not have any active transactions pending.
To check for this condition, use the Enterprise Manager command MONITOR TABLE to find the object ID for the tables you want to load. Then use the command MONITOR LOCK to see if there are any locks on the table.
No SQL strings are in the control file.
The following features are not available with Direct Path load:
loading object columns
loading LOBs
loading VARRAYs
loading nested tables
specifying OIDs for object tables with system-generated OIDs
specifying SIDs
loading REF columns
loading BFILE columns
physical records (set by the command-line option READSIZE) larger than 64k
Unicode Support for Oracle 10g, 11g, 12c, and 19c - These 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.
Property Options
You can set the following source (S) and target (T) properties.
Property
ST
Version
Use
CodePage
T
10g
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, 19c
Allows you to control how often data is committed to the database when the AutoCommit property is set to False.
The default value is zero that is, the data is committed at the end of the transformation, allowing rollback on error. This is the slowest setting. When performing large transformations, this is not practical as it may produce too many transaction log entries.
Specifying a nonzero value indicates that data is committed to the database after inserting or updating specified number of records.
ConstraintDDL
T
10g, 11g, 12c, 19c
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, 19c
Allows you to specify the name of the Directory Alias. No default exists for this property.
DPParallel
T
10g, 11g, 12c, 19c
Sets the direct path parallel attribute to allow parallel loading. The default is false. If you set this property to true, parallel loading is allowed, but it may also slow performance.
Encoding
T
11g, 12c, 19c
Determines how to store characters in Oracle 11g, 12c, and 19c target files. Default is OEM.
Synonyms
T
10g, 11g, 12c, 19c
If set to true, allows you to see synonyms. The synonym names appear in the table list along with the tables. Default is false.
SystemTables
T
10g, 11g, 12c, 19c
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.
Note:  This property is applicable only if the user is logged onto the database as the database administrator. Only the DBA has access to system tables.
Views
T
10g, 11g, 12c, 19c
If set to True, this property allows you to see the view names in the table list along with the table names. Default is True.
Note:  This property supports only Append and DeleteAndAppend output modes and does not support the Replace output mode.
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
Data Types
These data types are available in Oracle Direct Path:
Char
Date
DateTime
LongRaw
LongVarChar
Number
Raw
RowID
VarChar2
Data Types Supported for Direct Path Loading
The following external data types are valid for scalar columns in a direct path load operation:
SQLT_CHR
SQLT_DAT
SQLT_INT
SQLT_UIN
SQLT_FLT
SQLT_BIN
SQLT_NUM
SQLT_PDN
The following external object data types are supported:
SQLT_NTY – column objects (FINAL and NOT FINAL) and SQL string columns
SQLT_REF – REF columns (FINAL and NOT FINAL)
The following table types are supported:
Nested tables
Object tables (FINAL and NOT FINAL)