ttTableSchemaFromOraQueryGet
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 |
---|---|---|
|
|
TimesTen table owner (optional). If not provided, the connection ID is used. |
|
|
Table name for the 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. |
|
|
A Any expressions in the |
Result Set
ttTableSchemaFromOraQueryGet
returns the result set:
Column | Type | Description |
---|---|---|
|
|
A |
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 theSELECT
list to explicitly change the output to a TimesTen supported type. Column aliases can be specified for expressions in theSELECT
list. -
If the query on the Oracle database has LOB output, it is mapped to a
VAR
type.