Use to define a specific technology. In the Oracle Data Integrator terminology, this is any type of technology accessible by JDBC, ODBC, JMS, JNDI, JCA, or any operating system.
Oracle Data Integrator allows scripts to be generated and executed in the languages compatible with the technologies defined in its repository. Any type of technology can be declared and defined provided that Oracle Data Integrator can access it through appropriate middleware (JDBC, ODBC, JMS) or through an operating system command.
Example of technologies: Oracle, Sybase, Sybase IQ, DB2, Files, and so forth.
The Technology Editor has the following tabs:
Properties | Description |
---|---|
Name | Name of the technology, as it appears in the different lists in the user interface. |
Code | Technology code, which allows the technology to be referenced among the different repositories.
Caution: Avoid changing this code because this could make some Oracle Data Integrator objects inconsistent if they reference a technology from another repository. |
Technology type | Classification of the technology. The possible values are:
|
Logical/Physical | Indicates if the technology is physical or logical.
Three cases are possible:
|
This section details the technology's data handling capacities.
Properties | Description |
---|---|
Select | The technology's capacity to construct a data array resulting from a query. |
Where | The technology's capacity to filter this array according to conditions. |
Join Support | The technology's capacity to join several data arrays (two-dimensional data sets) into one only. This capacity can take several forms:
|
Partitioning Support | The technology's capacity to support partitioning.
|
Support SELECT lookup | The technology's capacity to support lookup as a SELECT statement in a SELECT clause. |
Support Derived Table | The technology's capacity to support a SELECT statement in a FROM clause. |
Support Set Operator | The technology's capacity to support set-based operators.
Comma-separated Operator List, lists the operators supported by the given technology. For example, for Oracle:
|
Support Window Functions | The technology's capacity to support window functions in a SELECT clause, such as MAX(...) OVER(PARITITION BY ... ORDER BY ...) . |
Constant Source Support | A boolean field. If set to True , the technology allows a query syntax to return a constant set of column values, without an actual table being included in the From clause. For example, Oracle technology has this field set to True , because Oracle supports a syntax SELECT 1,2,3 FROM DUAL , which returns a one row select result with constant column values, even though no source table is specified in the FROM clause. |
Constant Source Table Name | Specify a dummy table name for technologies that support constance source and use a dummy table name to specify a constant query. For example, Oracle has a dummy table name, DUAL , which is used for constant queries with no real source table in the FROM clause. Other technologies may support constant query with some other syntax that does not have a dummy table name, for example, by omitting the FROM clause altogether. In such a case, the Constant Source Support field will be True , but this field will have a value of null . |
This section shows how to locate and name the data containers for this technology.
Properties | Description |
---|---|
File | Shows if this technology is based on the use of files. |
Using "Data server" | Shows that the technology uses a data server whose name can be used to name objects. If the check box is selected, the term used to describe the data servers for the technology must be specified.
For example, in Oracle technology, the data server is accessed by an Instance/DBLink. |
Using "Catalog" | Shows that the technology uses a catalog to name its datastores. If the check box is selected, the term used to describe the catalog concept in the technology must be specified.
For example, Microsoft SQL Server uses a catalog named Database, IBM AS/400 uses a catalog named Library, Oracle does not use any catalog. |
Using "Schema" | Shows that the technology uses a schema to name its datastores. If the box is selected, the term used to describe the schema concept in the technology must be specified.
For example, Microsoft SQL Server uses a schema named Owner, Oracle uses a schema named Schema/User, Microsoft Access does not use schema. |
Character Encoding | Shows the character encoding to be used while reading and writing data. |
Local object mask | Defines how an object is named when you are connected to the data server on which the object is based.
For example, for Oracle, you should indicate In Oracle Data Integrator, the tags available for this mask are:
Caution: These tags are case-sensitive. |
Remote object mask | Defines how an object is named when you are connected to a different data server from the one on which the object is based.
For example, for Oracle, you should indicate The tags available for this mask are the same as for local objects, to which must be added |
Partition Mask | Shows how to name a partition in a SELECT statement. This field in enabled only when partitions are selected.
In Oracle Data Integrator, the tags available for this mask are:
For example: |
Sub-Partition Mask | Sub-Partition Mask shows how to name a sub-partition in a SELECT statement. This field in enabled only when Sub-Partitions is selected in Partitioning Support.
In Oracle Data Integrator, the tags available for this mask are the same as for the Partition mask. For example: |
Supports Native Sequences | Technology's capacity to support native sequences. If this option is selected, you need to provide at least a value for the Local Sequence Mask. |
Local Sequence Mask | Shows the SQL code to use when accessing a native sequence that is on the current (local) data server.
For example: |
Remote Sequence Mask | Shows the SQL code to use when accessing a native sequence that is on the remote data server.
For example: |
Local Sequence Current Value Mask | Shows the SQL code to use when accessing a native sequence current value that is on the current (local) data server.
For example: |
Remote Sequence Current Value Mask | Shows the SQL code to use when accessing a native sequence current value that is on the remote data server.
For example: |
The SQL properties are reserved for technologies with data or join filtering capacities (using the WHERE clause).
This section details the syntax used for filter clauses. It is only displayed for technologies with a data array filtering capacity.
Properties | Description |
---|---|
Order by | Shows the type of syntax used in SQL for the technology, following an ORDER BY clause. The possible values are:
|
Group by | Shows the type of syntax used in SQL for the technology, following a GROUP BY clause. The possible values are the same as for an ORDER BY clause. |
Having | Shows the type of syntax used in SQL for the technology, following a HAVING clause. The possible values are the same as for an ORDER BY clause. |
This section details the syntax used for sequenced joins (SQL ISO). It is only displayed for technologies with a join sequencing capacity.
Properties | Description |
---|---|
Clause Location | Shows whether the join clause is located on the "FROM" level or the "WHERE" level in the query syntax. |
Brackets Supported in the ON clauses | Shows whether the technology accepts sub-sets delimited by parentheses (this is the case with SQL ISO syntax). |
INNER | Shows that the technology supports inner joins. The key word specifying an inner join must be given, for example, "INNER JOIN". |
CROSS | Shows that the technology supports Cartesian product. The key word specifying a product set must be given, for example, "CROSS JOIN" or ",". |
LEFT OUTER | Shows that the technology supports left outer joins. The key word specifying a left outer join must be given, for example, "LEFT OUTER JOIN". |
RIGHT OUTER | Shows that the technology supports right outer joins. The key word specifying a right outer join must be given, for example, "RIGHT OUTER JOIN". |
FULL OUTER | Shows that the technology supports full outer joins. The key word specifying a full outer join must be given, for example, "FULL OUTER JOIN". |
NATURAL | Show that the technology supports natural joins. The key word specifying a natural join must be given, for example, "NATURAL JOIN". |
The parameters of non-sequenced joins allow outer join clauses to be generated in the WHERE clause.
Properties | Description |
---|---|
Outer Keyword | Keyword or sign imposed by the syntax as a reminder that an element is considered external. For example, for Oracle the Outer Keyword is: (+). |
Outer location | Shows if the OUTER Keyword is located outside (OUTER side) or inside (INNER side) the join syntax. |
Clause Location | Shows if the OUTER Keyword should be placed before or after the name of the object contributing to the join. |
Queries used by Oracle Data Integrator to perform specific tasks. These queries are technology-specific.
The SQL query for PKs, AKs, FKs, and Indexes is defined by the following parameters:
CATALOG (if supported by technology)
SCHEMA (if supported by technology)
TABLE
The SQL query for native sequences is defined with CATALOG and SCHEMA.
Properties | Description |
---|---|
Current Date | Query that can be sent at anytime to a data server of this technology to return the current date and time of the server. This query is used internally to recover connections. |
Reverse-Engineer PKs | Query used by Common Format Designer to compare the data model with the metadata from the data server. This query retrieves primary key definitions. Note that this query is not used for standard or customized reverse-engineering. This query should return a record set (one row per primary key column) that contains the following VARCHAR fields:
The record set should be ordered by PK_NAME and by the position of COLUMN_NAME in the primary key. |
Reverse-Engineer AKs | Query is used by Common Format Designer to compare the data model with the metadata from the data server. This query is not used for standard or customized reverse-engineering. This query retrieves alternate key definitions. This query should return a record set (one row per alternate key column) that contains the following VARCHAR fields:
The record set should be ordered by AK_NAME and by the position of COLUMN_NAME in the alternate key. |
Reverse-Engineer FKs | Query is used by Common Format Designer to compare the data model with the metadata from the data server. This query is not used for standard or customized reverse-engineering. This query retrieves foreign key definitions. This query should return a record set (one row per foreign key column) containing the following VARCHAR fields:
The record set should be ordered by FK_NAME and by the position of COLUMN_NAME in the foreign key. |
Reverse-Engineer Indexes | Query is used by Common Format Designer to compare the data model with the metadata from the data server. This query is not used for standard or customized reverse-engineering. This query reverse-engineers indexes. This query should return a record set (one row per index column) containing the following VARCHAR fields:
The record set should be ordered by INDEX_NAME and by the position of COLUMN_NAME in the index. |
Reverse-Engineer Check Constraints | Query is used by Common Format Designer to compare the data model with the metadata from the data server. This query is not used for standard or customized reverse-engineering. This query reverse-engineers check constraints. This query should return a record set (one row per check constraint) containing the following VARCHAR fields:
|
Reverse-Engineer Native Sequences | Query used to retrieve definitions for the native sequences. This query should return a record set (one row per native sequence) that contains the following VARCHAR field:
The record set should be ordered by SEQUENCE_NAME. |
Sequence Next Value In Non-binded Mode | Query used only for native sequences using #_nextval . The result of this SQL query is used in #_nextval . |
Sequence Current Value In Non-binded Mode | Query used only for native sequences current value using #_currval . The result of this SQL query is used in #_currval. |
This section includes additional parameters for the technology.
Properties | Description |
---|---|
Column-Alias Separator | Element designed to separate a column from its alias in a SQL SELECT clause.
In the ISO standard, this separator is "AS", however, it is not supported by all databases and may therefore be left incomplete. |
Table-Alias Separator | Element designed to separate a table from its alias in a SQL FROM clause. This element may be left incomplete. |
Date Function | Function allowing the date and the time to be returned. For example, getdate() for Microsoft SQL Server or sysdate under Oracle |
String Datatype Mask | The syntax used to describe Chain-type data in DDL orders (table creation). The Oracle Data Integrator tags %L (data length) and %P (data precision) can be used in the syntax description. |
Date Datatype Mask | Syntax used to describe Date-type data in DDL orders (table creation). |
Numerical Datatype Mask | The syntax used to describe Numerical-type data in DDL orders (table creation). The Oracle Data Integrator tags %L (data length) and %P (data precision) can be used in the syntax description. |
Longvarbinary Datatype Mask | Syntax used to describe longvarbinary-type data in DDL orders. To use like Numerical Datatype Mask. |
DDL Null key word | Word used to describe a column that can contain empty values (column known as NULLABLE). |
Maximum column name length | Maximum length of a column name in number of characters. All column names generated by Oracle Data Integrator are truncated to this size. |
Maximum table name length | Maximum length of a datastore (table) name in number of characters. All table names generated by Oracle Data Integrator are truncated to this size. |
This section includes the prefixes that are used for table names. You can define a prefix for the following tables:
Error Table
Temporary Index
Integration Table
Loading Table
Journal Table
Journal View
Journal Trigger
This tab describes the languages implemented by the technology and allows overwriting any of the properties of the language.
Note concerning User Functions:
To make a user function available for a specific technology:
In the Language column, select the language of the technology.
Select Default.
Make sure that you have selected the corresponding technology from the Technology type list on the Definition tab. The Oracle Data Integrator API does not work with user functions.
Properties | Description |
---|---|
Language | Language that is totally or partially implemented by the technology, selected from the list of defined languages. |
Compatible | Shows that the language for the technology inherits all the language elements marked as standard. For more information, see the Language Editor topic. |
Default | Shows that this is the default language for this technology. |
Object Delimiter | Character used to delimit object names. Replaces the standard delimiter given in the language.
Caution: If the two delimiters (before and after the object name) are different, then you should use both of them. If not, one delimiter is enough. Use a single double-quote " to generate code like this: create table MySchema."My_Table" ( ... Use two square brackets [] to generate code like this: create table MySchema.[My_Table] ( ... |
Word Separator | Characters that can be used to separate language words (objects, key words, literals). The characters declared in this list cause the object (table/schema/column) to be enclosed in the characters declared for the 'Object Delimiter'. For example, if the table name contains a # character, some databases do not accept that table name to be used, except where enclosed in double quotes: MySchema.MyTable#1 becomes MySchema.'MyTable#1' if the ' (single quote) is declared as the object delimiter and # is declared as a word separator. |
Literal Delimiter | Character used to delimit the literal names (values). Replaces the standard delimiter given in the language.
Caution: If the two delimiters (before and after the literal) are different, then you should use both of them. If not, one delimiter is enough. |
Objects case-sensitive | Object names in this technology are case-sensitive.
For example, objects are not case-sensitive in the SQL Language by default. Therefore, code like this is generated to preserve names using upper and lowercase letters: create table Snps_Temp.dbo.My_Table ( My_Column1 VARCHAR(20) NULL, MY_COLUMN2 VARCHAR(20) NULL ) If Objects case-sensitive is selected, then code like the following is generated: create table Snps_Temp.dbo."My_Table" ( "My_Column1" VARCHAR(20) NULL, MY_COLUMN2 VARCHAR(20) NULL ) |
Words case-sensitive | Keywords of this language are case-sensitive. |
Oracle Fusion Middleware Developing Integration Projects with Oracle Data Integrator, Oracle Fusion Middleware Developing Knowledge Modules with Oracle Data Integrator, and Oracle Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator