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