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, 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;

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.