Property | S/T | Version | Use |
|---|---|---|---|
AllowDistributedTransactions | S/T | 10g, 11g, 12c, 19c | If set to true (default), distributed transaction processing is allowed. |
AppendHint | T | 10g, 11g, 12c, 19c, 21c | 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, 19c, 21c | Number of records fetched or inserted with each cursor operation. Default is zero. Maximum allowed is 65535. |
Code Page | S/T | 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, 19c, 21c | 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, 21c | 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. |
DirectoryAlias | T | 10g, 11g, 12c, 19c, 21c | Allows you to specify the name of the Directory Alias. No default exists for this property. |
Encoding | S/T | 11g, 12c, 19c, 21c | Determines how to store characters in Oracle 11g, 12c, and 19c source and target files. Default is OEM. |
ParallelHint | S/T | 10g, 11g, 12c, 19c, 21c | 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, 19c, 21c | 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 and the integrity level is set to "enforced". It means that a materialized view will be used provided it guarantees to give the same result as using the detail tables. Using this parameter means the query rewrite will not use stale materialized views or trust relationships. • Trusted - The session is altered to allow query rewriting and the integrity level is set to "trusted". It means that a materialized view will be used provided it is not stale. However query rewrite 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 and the integrity level is set to "stale_tolerated". It means that the materialized view will be used even when data in the detail tables has changed. Note: This property must be set before connecting since the alter sessions occur during connection. |
Synonyms | S/T | 10g, 11g, 12c, 19c, 21c | 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 | S/T | 10g, 11g, 12c, 19c, 21c | 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, 19c, 21c | 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 | S/T | 10g, 11g, 12c, 19c, 21c | 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. |
WhereStmt | S | 10g, 11g, 12c, 19c, 21c | Provides a pass-through mechanism for SQL connectors where advanced users can construct the Where clause of the SQL query themselves. It can be used as an alternative to writing a lengthy query statement. You may use this to instruct the SQL database server to filter the data based on a particular condition before sending it to the integration platform. There is no default value for this property. Note: This property is not applicable when the source connection is a query statement or file. This property enables data filtering when you select a table. |
Name | Syntax | Notes |
|---|---|---|
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. |
BINARY_DOUBLE | N/A | 64-bit floating point number. This data type requires 9 bytes, including the length byte. |
BINARY_FLOAT | N/A | 32-bit floating point number. This data type requires 5 bytes, including the length byte. |
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. |
BLOB | N/A | A binary large object. Maximum size is (4 GB - 1 byte) * (database block size). |
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. |
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). |
DATE / DATETIME | N/A | Valid date range from January 1, 4712 BC to December 31, 9999 AD. Note: DATE data type is referred as DATETIME in Oracle connectors. |
DOUBLE | N/A | The DOUBLE PRECISION data type is a floating-point number with binary precision 126. |
FLOAT | FLOAT [(p)] | A subtype of the NUMBER data type having precision p. A FLOAT value is represented internally as NUMBER. The precision p can range from 1 to 126 binary digits. A FLOAT value requires from 1 to 22 bytes. |
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. |
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. |
LONG | N/A | Character data of variable length up to 64000 bytes. |
LONG RAW | N/A | Raw binary data of variable length up to 2 GB. |
LONG VARCHAR | (size) | The LONG VARCHAR type allows storage of character strings with a maximum length of 32,700 characters. It is identical to VARCHAR, except that you cannot specify a maximum length when creating columns of this type. |
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. |
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. |
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. |
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. |
RAW | (size) | Raw binary data length of size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value. |
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. |
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 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. |
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. |
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. |
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. |