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:

Create a table with the correct columns and data types on TimesTen.
Provide a SELECT statement that runs on the Oracle database to generate the desired result set.
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:

Use ttIsql to Create a Table and Load SQL Query Results

The ttIsql utility provides the createandloadfromoraquery command, which takes a table name, the number of parallel threads, and a SELECT statement that is to run on the Oracle database as input parameters.

From these parameters, TimesTen performs the following:

Evaluates the SQL query and creates an appropriate table, if not already created, with the provided table name where the columns are those named in the SQL query with the same (or mapped) data types as those in the Oracle Database tables from which the resulting data is retrieved.
Loads the results of the SQL query as it runs on the Oracle database into this table. The call returns a single number indicating the number of rows loaded. Any subsequent calls to this command append retrieved rows to the table.

Note:

See the createandloadfromoraquery command in ttIsql in the Oracle TimesTen In-Memory Database Reference.

The following ttIsql example connects providing the DSN, user name, password for the user on TimesTen, and the password for the same user name on the Oracle database. Then, it runs the createandloadfromoraquery command to evaluate the SELECT statement. The employees table is created on TimesTen with the same column names and data types as the columns and data types of the retrieved rows. Then, the table is populated with the result set from the Oracle database over two parallel threads.

% ttisql -connstr "DSN=database1;UID=hr;PWD=hr;OraclePWD=oracle"
Copyright (c) 1996, 2024 Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql
connect -connstr "DSN=database1;UID=hr;PWD=********;OraclePWD=********";
Connection successful: DSN=database1;UID=hr;
DataStore=/timesten/install/sample_db/DemoDataStore/database1;
DatabaseCharacterSet=AL32UTF8;
ConnectionCharacterSet=AL32UTF8;DRIVER=/timesten/install/lib/libtten.so;
PermSize=128;TempSize=64;OracleNetServiceName=inst1;

(Default setting AutoCommit=1)
Command> createandloadfromoraquery employees 2 SELECT * FROM hr.employees;
Mapping query to this table:
    CREATE TABLE "HR"."EMPLOYEES" (
    "EMPLOYEE_ID" number(6,0) NOT NULL,
    "FIRST_NAME" varchar2(20 byte),
    "LAST_NAME" varchar2(25 byte) NOT NULL,
    "EMAIL" varchar2(25 byte) NOT NULL,
    "PHONE_NUMBER" varchar2(20 byte),
    "HIRE_DATE" date NOT NULL,
    "JOB_ID" varchar2(10 byte) NOT NULL,
    "SALARY" number(8,2),
    "COMMISSION_PCT" number(2,2),
    "MANAGER_ID" number(6,0),
    "DEPARTMENT_ID" number(4,0)
     )
Table employees created
< 107, 0, 0, Started=2015-09-11 21:12:45 (GMT); Ended=2015-09-11 21:12:46 (GMT);
Load successfully completed; OracleSCN=779534; Rows Loaded=107; Errors=0;
Statement=ttLoadFromOracle(HR, EMPLOYEES, SELECT * FROM hr.employees, 2) >
1 row found.

Issue the DESCRIBE command to show the new table:

Note:

In this example, the table owner is not specified, so it defaults to the current user. In this example, the current user is hr.

Command> DESCRIBE employees;
 
Table HR.EMPLOYEES:
  Columns:
    EMPLOYEE_ID                     NUMBER (6) NOT NULL
    FIRST_NAME                      VARCHAR2 (20) INLINE
    LAST_NAME                       VARCHAR2 (25) INLINE NOT NULL
    EMAIL                           VARCHAR2 (25) INLINE NOT NULL
    PHONE_NUMBER                    VARCHAR2 (20) INLINE
    HIRE_DATE                       DATE NOT NULL
    JOB_ID                          VARCHAR2 (10) INLINE NOT NULL
    SALARY                          NUMBER (8,2)
    COMMISSION_PCT                  NUMBER (2,2)
    MANAGER_ID                      NUMBER (6)
    DEPARTMENT_ID                   NUMBER (4)
 
1 table found.
(primary key columns are indicated with *)
 
Command> SELECT * FROM employees;
< 114, Den, Raphaely, DRAPHEAL, 515.127.4561, 2002-12-07 00:00:00, PU_MAN, 
11000, <NULL>, 100, 30 >
< 115, Alexander, Khoo, AKHOO, 515.127.4562, 2003-05-18 00:00:00, PU_CLERK, 
3100, <NULL>, 114, 30 >
…
< 205, Shelley, Higgins, SHIGGINS, 515.123.8080, 2002-06-07 00:00:00, 
AC_MGR, 12008, <NULL>, 101, 110 >
< 206, William, Gietz, WGIETZ, 515.123.8181, 2002-06-07 00:00:00, 
AC_ACCOUNT, 8300, <NULL>, 205, 110 >
107 rows found.

The following example uses the createandloadfromoraquery command to create the emp table on TimesTen and populate it in parallel over four threads with data from the hr.employees table on the Oracle database, where employee_id is less than 200.

Command> createandloadfromoraquery emp 4 SELECT * FROM hr.employees 
WHERE employee_id < 200;
Mapping query to this table:
    CREATE TABLE "HR"."EMP" (
    "EMPLOYEE_ID" number(6,0) NOT NULL,
    "FIRST_NAME" varchar2(20 byte),
    "LAST_NAME" varchar2(25 byte) NOT NULL,
    "EMAIL" varchar2(25 byte) NOT NULL,
    "PHONE_NUMBER" varchar2(20 byte),
    "HIRE_DATE" date NOT NULL,
    "JOB_ID" varchar2(10 byte) NOT NULL,
    "SALARY" number(8,2),
    "COMMISSION_PCT" number(2,2),
    "MANAGER_ID" number(6,0),
    "DEPARTMENT_ID" number(4,0)
     )
 
Table emp created< 100, 0, 0, Started=2015-09-11 21:30:56 (GMT); Ended=2015-09-11 21:30:57 (GMT);
Load successfully completed; OracleSCN=780073; Rows Loaded=100; Errors=0;
Statement=ttLoadFromOracle(HR, EMP, SELECT * FROM hr.employees 
WHERE employee_id < 200, 4) >1 row found.

Then, the following createandloadfromoraquery retrieves all employees whose id is > 200 and the result set is appended to the existing table in TimesTen. A warning tells you that the table already exists and that 6 rows were added to it.

Command> createandloadfromoraquery emp 4 SELECT * FROM hr.employees 
WHERE employee_id > 200;
Warning 2207: Table HR.EMP already exists
< 6, 0, 0, Started=2015-09-11 21:34:31 (GMT); Ended=2015-09-11 21:34:31 (GMT);
Load successfully completed; OracleSCN=780176; Rows Loaded=6; Errors=0; 
Statement=ttLoadFromOracle(HR, EMP, SELECT * FROM hr.employees 
WHERE employee_id > 200, 4) >
1 row found.

A parallel load operation may take a long time to run and you may want to cancel the operation. See Cancel a Parallel Load Operation.

Use TimesTen Built-In Procedures to Recommend a Table and Load SQL Query Results

You can create the TimesTen table and load the result set from the Oracle database into the table using built-in procedures.

While the createandloadfromoraquery command automatically performs all of the tasks for creating the TimesTen table and loading the result set from the Oracle database into it, the following two built-in procedures separate the same functionality into the following two steps:

The ttTableSchemaFromOraQueryGet built-in procedure evaluates the SQL query and generates the CREATE TABLE SQL statement that you can choose to issue. In order to run this statement, the user should have all required privileges to run the query on the Oracle database. This enables you to view the table structure without processing. However, it does require you to issue the recommended CREATE TABLE statement yourself.
The ttLoadFromOracle built-in procedure runs the SQL query on the back-end Oracle database and then loads the result set into the TimesTen table. It requires the TimesTen table name where the results are loaded, the Oracle Database SQL SELECT statement to obtain the required rows, and the number of parallel threads that you would like to be used in parallel when loading the table with this result set. Optionally, you can specify error threshold options to avoid issues such as uniqueness violations and data conversion issues with the IgnoreDuplicates option.

Note:

See ttTableSchemaFromOraQueryGet and ttLoadFromOracle in the Oracle TimesTen In-Memory Database Reference.

The following example connects providing the DSN, user name, password for the user on TimesTen, the password for a user with the same name on the Oracle database, and the OracleNetServiceName for the Oracle database instance. Then, it calls the ttTableSchemaFromOraQueryGet built-in procedure to evaluate the SELECT statement and return a recommended CREATE TABLE statement for the employees table. Then, the CREATE TABLE statement runs. Finally, the example calls the ttLoadFromOracle built-in procedure to load the employees table with the result set from the Oracle database. The load is performed in parallel over four threads, which is the default.

Note:

If autocommit is set to off, then the user must either commit or roll back manually after loading the table.

% ttisql -connstr "DSN=database1;uid=hr;pwd=hr;OraclePWD=oracle"
Copyright (c) 1996, 2024 Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql
connect -connstr "DSN=database1;UID=hr;PWD=********;OraclePWD=********";
Connection successful: DSN=database1;UID=hr;
DataStore=/timesten/install/sample_db/DemoDataStore/database1;
DatabaseCharacterSet=AL32UTF8;
ConnectionCharacterSet=AL32UTF8;DRIVER=/timesten/install/lib/libtten.so;
PermSize=128;TempSize=64;OracleNetServiceName=inst1;

Command> call ttTableSchemaFromOraQueryGet('hr','employees', 
'SELECT * FROM hr.employees');
< CREATE TABLE "HR"."EMPLOYEES" (
"EMPLOYEE_ID" number(6,0) NOT NULL,
"FIRST_NAME" varchar2(20 byte),
"LAST_NAME" varchar2(25 byte) NOT NULL,
"EMAIL" varchar2(25 byte) NOT NULL,
"PHONE_NUMBER" varchar2(20 byte),
"HIRE_DATE" date NOT NULL,
"JOB_ID" varchar2(10 byte) NOT NULL,
"SALARY" number(8,2),
"COMMISSION_PCT" number(2,2),
"MANAGER_ID" number(6,0),
"DEPARTMENT_ID" number(4,0)
 ) >
1 row found.

Command> CREATE TABLE "HR"."EMPLOYEES" (
"EMPLOYEE_ID" number(6,0) NOT NULL,
"FIRST_NAME" varchar2(20 byte),
"LAST_NAME" varchar2(25 byte) NOT NULL,
"EMAIL" varchar2(25 byte) NOT NULL,
"PHONE_NUMBER" varchar2(20 byte),
"HIRE_DATE" date NOT NULL,
"JOB_ID" varchar2(10 byte) NOT NULL,
"SALARY" number(8,2),
"COMMISSION_PCT" number(2,2),
"MANAGER_ID" number(6,0),
"DEPARTMENT_ID" number(4,0)
 );

Command> call ttLoadFromOracle ('HR','EMPLOYEES','SELECT * FROM HR.EMPLOYEES');
< 107, 0, 0, Started=2015-09-11 21:52:51 (GMT); Ended=2015-09-11 21:52:51 (GMT);
Load successfully completed; OracleSCN=780552; Rows Loaded=107; Errors=0;
Statement=ttLoadFromOracle(HR, EMPLOYEES, SELECT * FROM HR.EMPLOYEES, 4) >
1 row found.

Command> SELECT * FROM hr.employees;
< 100, Steven, King, SKING, 515.123.4567, 2003-06-17 00:00:00, AD_PRES, 24000,
<NULL>, <NULL>, 90 >
< 101, Neena, Kochhar, NKOCHHAR, 515.123.4568, 2005-09-21 00:00:00, AD_VP, 17000, 
<NULL>, 100, 90 >
...
< 205, Shelley, Higgins, SHIGGINS, 515.123.8080, 2002-06-07 00:00:00, AC_MGR, 
12008, <NULL>, 101, 110 >
< 206, William, Gietz, WGIETZ, 515.123.8181, 2002-06-07 00:00:00, AC_ACCOUNT, 
8300, <NULL>, 205, 110 >
107 rows found.

The following example creates a table on the Oracle database, where employee_id is a column with a PRIMARY KEY constraints and email is a column with a UNIQUE constraint.

CREATE TABLE employees
(employee_id NUMBER(6) PRIMARY KEY, 
first_name VARCHAR2(20), 
last_name VARCHAR2(25) NOT NULL, 
email VARCHAR2(25) NOT NULL UNIQUE, 
phone_number VARCHAR2(20), 
hire_date DATE NOT NULL, 
job_id VARCHAR2(10) NOT NULL, 
salary NUMBER(8,2), 
commission_pct NUMBER(2,2), 
manager_id NUMBER(6), 
department_id NUMBER(4));

Then, the following ttTableSchemaFromOraQueryGet built-in procedure evaluates the SQL query and generates a CREATE TABLE SQL statement. Note that in the suggested CREATE TABLE SQL statement the PRIMARY KEY and UNIQUE constraints are not carried over from the Oracle database. Nullability constraints are carried over from the Oracle database. This also applies to the createandloadfromoraquery command.

Command> call ttTableSchemaFromOraQueryGet ('hr', 'employees', 
'SELECT * FROM hr.employees');
< CREATE TABLE "HR"."EMPLOYEES" ( 
"EMPLOYEE_ID" number(6,0) NOT NULL,
"FIRST_NAME" varchar2(20 byte),
"LAST_NAME" varchar2(25 byte) NOT NULL,
"EMAIL" varchar2(25 byte) NOT NULL,
"PHONE_NUMBER" varchar2(20 byte),
"HIRE_DATE" date NOT NULL,
"JOB_ID" varchar2(10 byte) NOT NULL,
"SALARY" number(8,2),
"COMMISSION_PCT" number(2,2),
"MANAGER_ID" number(6,0),
"DEPARTMENT_ID" number(4,0)
 ) >
1 row found.

A parallel load operation may take a long time to run and you may want to cancel the operation. See Cancel a Parallel Load Operation.

Cancel a Parallel Load Operation

You can cancel and cleanly stop all threads that are performing a parallel load operation with either the SQLCancel(hstmt) ODBC function (this applies for ODBC 2.5 and ODBC 3.5) or by pressing Ctrl-C in the ttIsql utility.

See TimesTen ODBC Support in the Oracle TimesTen In-Memory Database C Developer's Guide and Canceling ODBC Functions in this book.

A parallel load operation periodically commits, so any successful operations are not rolled back. When you issue the cancel command, TimesTen performs the cancel operation:

Before insert threads are spawned.
After an insert batch commit (every 256 rows)
After the main thread completes a fetch from the Oracle database.

To retry a cancelled parallel load operation, delete previously inserted rows from the TimesTen database to avoid duplicate rows.