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. |
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. |