6 Loading Data from an Oracle Database into a TimesTen Table Without Cache

You can load the results of a SQL query from a back-end Oracle database into a single table on TimesTen without creating a cache group and cache table to contain the results. TimesTen provides tools that run a user-provided SELECT statement on the Oracle database and load the result set into a table on TimesTen.

The following are the major steps that are performed to accomplish this task:

  1. Create a table with the correct columns and data types on TimesTen.

  2. Provide a SELECT statement that runs on the Oracle database to generate the desired result set.

  3. Load the result set into the table on TimesTen.

TimesTen provides two methods to accomplish these tasks:

  • The ttIsql utility provides the createandloadfromoraquery command that, once provided the TimesTen table name and the SELECT statement, automatically creates the TimesTen table, runs the SELECT statement on the Oracle database, and loads the result set into the TimesTen table. This command is described fully in Use ttIsql to Create a Table and Load SQL Query Results.

  • The ttTableSchemaFromOraQueryGet built-in procedure evaluates the user-provided SELECT statement to generate a CREATE TABLE statement that can be run to create a table on TimesTen, which would be appropriate to receive the result set from the SELECT statement. The ttLoadFromOracle built-in procedure runs the SELECT statement on the Oracle database and loads the result set into the TimesTen table. These built-in procedures are described in Use TimesTen Built-In Procedures to Recommend a Table and Load SQL Query Results.

Both methods require the following:

  • Both the TimesTen and Oracle databases involved must be configured with the same national database character set.

  • When you connect to the TimesTen database, the connection must contain the same connection attributes that are required when using cache groups, as follows:

    • The user name, which must be the same on both the TimesTen and Oracle databases

      Note:

      The correct privileges must be granted to these users on each database for the SQL statements that run on their behalf.

    • The correct passwords for each user as appropriate in the PWD and OraclePWD connection attributes

    • The OracleNetServiceName connection attributes that identifies the Oracle database instance

  • For either method, the user provides the following:

    • The table name on the TimesTen database where the results of the SQL query is loaded. If the owner of the table is not provided, the table is created with the current user as the owner. The table name is not required to be the same name as the table name on the Oracle database against which the SQL statement is run. This table does not require a primary key. If the table already exists, a warning is issued and the retrieved rows are appended to the table.

    • Optionally, use the numThreads parameter on ttIsql createandloadfromoraquery or ttLoadFromOracle to specify the number of parallel threads that you would like to use in parallel when loading the table with the result set. This defaults to four.

    • The SQL SELECT statement that runs against the Oracle database to obtain the required rows. The tables specified within this SELECT statement must be fully qualified, unless the tables are within the schema of the current Oracle Database user. The query cannot have any parameter bindings.

      The SELECT list should contain either simple column references or column aliases. For example, any expressions in the SELECT list should be provided with a column alias. You can also use the column alias to avoid duplication of column names in the result table. For example, instead of using SELECT C1+1 FROM T1, use SELECT C1 + 1 C2 FROM T1, which would create a column named C2.

TimesTen evaluates the SELECT statement and uses the column names, data types, and nullability information to create the table on TimesTen into which the result set is loaded. The column names and data types (either the same or mapped) are taken from the tables on the Oracle database involved in the SELECT statement. However, other Oracle Database table definition information (such as DEFAULT values, primary key, foreign key relationships, and so on) are not used when creating the CREATE TABLE statement for the TimesTen table.

Note:

If the evaluation returns any unsupported data types or if the query cannot run on the Oracle database, such as from a syntax error, a warning is logged and a comment is displayed for the unsupported column in the output. However, if the data type is not supported by TimesTen, you can cast the data type directly in the SELECT list to a TimesTen supported data 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.

The load is automatically committed every 256 rows. If an error is encountered during the load, it terminates the load, but does not roll back any committed transactions. Any errors returned from the Oracle database are reported in the same manner as when using cache groups.

Because you can use the createandloadfromoraquery command and the ttLoadFromOracle built-in procedure to load into an existing TimesTen table, the following restrictions apply:

  • You cannot load into system tables, dictionary tables, temporary tables, detail tables of views, materialized view tables, tables, or tables already in a cache group. In addition, you cannot use a synonym for the table name.

  • If you load the result set into an existing table that is the referencing table (child table) of a foreign key constraint, the constraint is not validated. As a result, rows that are missing a parent row may be loaded. Instead, you should verify all foreign keys after the table is loaded. However, the ttLoadFromOracle built-in procedure enables you to ignore constraint violations by setting the options parameter to IgnoreDuplicates=Y.

The following sections provide more details on each individual method: