ttTableSchemaFromOraQueryGet

This built-in procedure evaluates a SELECT query on a table in an Oracle database and generates a CREATE TABLE SQL statement that you can choose to run. The TimesTen CREATE TABLE statement matches the result set column names and types.

This procedure does not create the TimesTen table, it only returns a statement that identifies the table schema.

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 GuideOracle TimesTen In-Memory Database Operations Guide.

Required Privilege

This procedure requires no privileges. The session user must have all 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

ttTableSchemaFromOraQueryGet(['tblOwner'], 'tblName', 'selectSQL')

Parameters

ttTableSchemaFromOraQueryGet has the parameters:

Parameter Type Description

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`	Table name for the `CREATE TABLE` statement. 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. Any expressions in the `SELECT` list should be provided with a column alias; otherwise, an implementation dependent column name is assumed and the expression is not evaluated.

tblOwner

TT_CHAR (30)

TimesTen table owner (optional). If not provided, the connection ID is used.

tblName

TT_CHAR (30) NOT NULL

Table name for the CREATE TABLE statement.

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.

Any expressions in the SELECT list should be provided with a column alias; otherwise, an implementation dependent column name is assumed and the expression is not evaluated.

Result Set

ttTableSchemaFromOraQueryGet returns the result set:

Column	Type	Description
`createSQL`	`TT_VARCHAR (409600) NOT NULL`	A `CREATE TABLE` statement that matches the result set of the `SELECT` query on an Oracle database.

Examples

This example, returns the CREATE TABLE statement to create the TimesTen HR.EMPLOYEES table with all columns found in the Oracle database HR.EMPLOYEES table.

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.

Notes

The query on the Oracle database cannot have any parameter bindings.
TimesTen returns an error if the query cannot be described on the Oracle database, for example, if there is a syntax error.
If an output column type does not have a matching type in TimesTen, TimesTen outputs a warning and the following line for the column definition: >>>>column_name column_type /* reason */
If the query on the Oracle database outputs types not supported by TimesTen, you can add a CAST clause in the SELECT list to explicitly change the output to a TimesTen supported type. Column aliases can be specified for expressions in the SELECT list.
If the query on the Oracle database has LOB output, it is mapped to a VAR type.