ttLoadFromOracle

No character set conversion is performed when loading data from an Oracle database into a TimesTen table. The TimesTen database and the Oracle database must use the same character set.

The procedure requires the connection attribute UID, the connection attribute OraclePWD and the connection attribute OracleNetServiceName to be specified. You must commit after calling this procedure.

For more details and usage information, see Loading Data From an Oracle Database Into a TimesTen Table Without Cache in the Oracle TimesTen In-Memory Database Operations Guide.

Required Privilege

This procedure requires INSERT privileges to the table to be loaded. The session must have all the required privileges to run the query on the Oracle database.

Usage in TimesTen Scaleout and TimesTen Classic

This procedure is supported in TimesTen Classic.

TimesTen Scaleout applications can call this built-in procedure.

In TimesTen Scaleout, this procedure runs locally on the element from which it is called.

Related Views

This procedure has no related views.

Syntax

ttLoadFromOracle(['tblOwner'], 'tblName', 'Query' [,numThreads], 'Options')

Parameters

ttLoadFromOracle has these parameters:

Parameter	Type	Description
`tblOwner`	`TT_CHAR (30)`	TimesTen table owner (optional). If not provided, the connection ID is used.
`tblName`	`TT_CHAR (30) NOT NULL`	Name of the table to be loaded with data from the Oracle database. You can use the built-in procedure `ttTableSchemaFromOraQueryGet` to get a schema with which to build the table, if one does not already exist. The specified TimesTen table cannot be a system table, a synonym, a view, a materialized view or a detail table of a materialized view, a global temporary table or a cache group table.
`SelectSQL`	`TT_VARCHAR (409600) NOT NULL`	A `SELECT` query on an Oracle database to derive the table column definition. The query on an Oracle database cannot have any parameter bindings. Provide any expressions in the `SELECT` list with a column alias. Otherwise, an implementation dependent column name is assumed and the expression is not evaluated.
`numThreads`	`TT_INTEGER`	Number of threads for parallel load (optional). If `NULL`, defaults to 4. Provides parallel loading for tables. Specifies the number of loading threads to run concurrently. One thread performs the bulk fetch from the Oracle database and the other threads perform the inserts into TimesTen. Each thread uses its own connection or transaction. The minimum value for NumThreads is 2. The maximum value is 10. If you specify a value greater than 10, TimesTen assigns the value 10. You can also use the readers option to specify the total number of threads from the `numThreads` parameter to use for bulk fetching from the Oracle database.
`Options`	`TT_VARCHAR (1000)`	Option string, specified as `key=value` pairs. For the supported values, see Options String. Defaults to `NULL`. See the table below for more information.

Options String

The options are specified as key = value pairs and the pairs are separated by semi-colons.

Option parameter	Value	Description
`localOnly`	`Y` or `N`	This option only loads rows from a specific instance. Load a specific instance in the grid and use this option. When you use this option, `ttLoadFromOracle` selects all rows from the table, but ignores any rows that are not hashed to the specific instance. This option is only supported in TimesTen Scaleout. The default value is `N`.
`ErrorThreshold`	`value` > `0`	This option sets the error threshold for which the built-in procedure returns an error messages. The default value is `1`. When the `ttLoadFromOracle` built-in procedure encounters an error, the built-in procedure stops and returns an error message.
`IgnoreDuplicates`	`Y` or `N`	This option makes the `ttLoadFromOracle` built-in procedure ignore uniqueness constraint violations, which results in duplicates being ignored. You can only use this option if the TimesTen table has a uniqueness constraint on it. The default value is `N`.
`ResumeFromSCN`	`scn_of_last_load`	This option resumes the load operation from the specific SCN. When the `resumeFromSCN` option is enabled, it automatically ignores duplicates. The default value is the latest SCN.
`DirectLoad`	`Y` or `N`	This option enables a bulk insert mode which has performance benefits. This option is only supported in TimesTen IMDB. The default value is `N`.
`readers`	`numThreads` > `value` > `0`	This option specifies the total number of threads from the `numThreads` parameter to use for bulk fetching from the Oracle database. For example, if you specify a numThreads parameter of `8` and a readers option of `3`, 3 threads bulk fetch data from the Oracle database and 5 threads load data into the TimesTen database.

Result Set

ttLoadFromOracle returns the result set:

Column	Type	Description
`numRows`	`TT_BIGINT NOT NULL`	Number of rows loaded.
`numErrors`	`TT_INTEGER NOT NULL`	Number of rows with errors.
`errCode`	`TT_INTEGER`	TimesTen error code, one of: `0` - Load completed successfully without errors `-1` - Load completed successfully with errors `-2` - Load terminated early with errors `-3` - Load terminated early with a fatal error, for example, an out-of-space error, a loss of connection or an invalidation.
`errMsg`	`VARCHAR2 (4000)`	Error message, containing: Start and end time of load Statement `SCN` used to query the data Number of rows with errors Number of rows loaded

Examples

The following example selects loads the TimesTen table about employees from the Oracle database HR.EMPLOYEES table and loads it into the TimesTen HR.EMPLOYEES table. In this example an error is returned. In this example, the column STATE is a TT_TINYINT.

Command> CALL ttLoadFromOracle ('HR','EMPLOYEES',
'SELECT * FROM HR.EMPLOYEES');
< 99, 0, 0, 'Started=2014-08-01 13:48:21; Ended=2014-08-01 13:48:23;
 Statement=ttLoadFromOracle('HR', 'SELECT * FROM HR.EMPLOYEES'); SCN=1234567;
 Errors=1; Rows Loaded=99' >
<  NULL, NULL, 2614,'Value outside of range supported by integral type. Column
 STATE=-1' >

Notes

TimesTen does not empty the table before the load. The target table does not require a primary key. TimesTen returns an error if the query output cannot be converted to rows in the target table due to a mismatch of column types or number of columns. Loading data into TimesTen LOB columns is not supported. If the query on the Oracle database has LOB output, it is mapped to a VAR type.
The load process does not check that the column data types and sizes in the TimesTen table match the data types and sizes of the result set. Instead, the insert is attempted and if the column data types cannot be mapped or the Oracle Database data from the SQL query exceeds the TimesTen column size, TimesTen returns an error. LOB columns are truncated to 4 MB.
When a table is altered to add columns, secondary partitions are added. Loading a table with multiple partitions is not supported by ttLoadFromOracle.