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 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.
Connector Parts
Connector parts are the fields you configure to connect with a data source or target.
The settings that are available depend on the connector you select.
For a list of all parts for source connectors, see
Specifying Connector, Parts, and Properties.
For a list of all parts for target connectors, see
Specifying Connector, Parts, and Properties.
Property Options
You can set the following source (S) and target (T) properties.
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
Supported 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)