7.34 Technology Editor

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:

7.34.1 Definition

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:
  • Database or files: Any technology accessible through ODBC or JDBC. Flat and XML files are part of this category, because Data Integrator includes a JDBC access driver for flat files, and a JDBC driver for XML files.

  • Operating system: Any operating system on which Oracle Data Integrator can launch commands via a Java virtual machine.

  • Topics (JMS): Any MOM accessible through JMS and including topic management (publication and subscription).

  • Queue (JMS): Any MOM accessible through JMS.

  • Oracle Data Integrator API: Technology allowing calls to the Oracle Data Integrator tools.

  • Oracle Data Integrator Connector: Technology allowing calls to a Java API.

  • Bean Scripting Framework: Technology allowing calls to a script interpreter.

  • Web Service Container: Technology allowing the deployment of web services.

Logical/Physical Indicates if the technology is physical or logical.

Three cases are possible:

  • Logical and Physical Technology: If a technology is both logical and physical, it can support logical and physical schemas. Its logical schemas can be mapped in the contexts exclusively on physical schemas of this technology only.

  • Logical Technology: If a technology is exclusively logical, it can support logical schemas only, which can be mapped in the contexts on physical schemas of any technology. For example: If you define an exclusively logical technology GENERIC_ISO_SQL, its logical schemas can give access to physical schemas based on Oracle, DB2, and other data servers.

  • Physical Technology: If a technology is exclusively physical, it can support physical schemas only, and its schemas can be accessed only through the logical schemas of an exclusively logical technology.


Data Handling

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:
  • If no check box is selected, the technology does not support joins.

  • If only Ordered is selected, the technology support joins in the ANSI-SQL syntax. These joins are specified in the FROM clause, as in the example below.

    For example:

    FROM emp LEFT JOIN dept ON (emp.deptno = dept.deptno)

  • If only Not Ordered is selected, the technology supports joins in a non-ordered syntax. These joins are typically specified in the WHERE clauses with an technology-specific join operator, as in the following example for Oracle Database:

    WHERE emp.deptno (+) = dept.deptno

  • If both Ordered and Not Ordered are selected, the technology supports both ordered and non-ordered joins. Usually, technologies supporting both syntaxes do not allow mixing them within the same statement.

  • To enable the Mixed syntax allowed option, select both Ordered and Not Ordered. If Mixed syntax allowed is selected, the technology supports mixing ordered and non-ordered joins within the same statement.

Partitioning Support The technology's capacity to support partitioning.
  • No: The technology does not support partitioning.

  • Partitions: The technology supports partitions in SELECT statements.

  • SubPartitions: The technology supports partitions and subpartitions in SELECT statements.

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:

UNION, UNION ALL, MINUS, INTERSECT

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.

Naming Rules

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 %SCHEMA.%OBJECT to symbolize the syntax SCOTT.EMP giving access to the EMP table belonging to the user SCOTT.

In Oracle Data Integrator, the tags available for this mask are:

  • %CATALOG to symbolize the catalog name (property of the physical schema).

  • %SCHEMA to symbolize the schema name (property of the physical schema).

  • %OBJECT to symbolize the datastore name.

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 %SCHEMA.%OBJECT@DSERVER to symbolize the syntax SCOTT.EMP@NYORK allowing access to the EMP table of the user SCOTT of the instance NYORK (remote data server).

The tags available for this mask are the same as for local objects, to which must be added %DSERVER representing the name of the data server (property of the connection).

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:

  • %CATALOG to symbolize the catalog name (property of the physical schema).

  • %SCHEMA to symbolize the schema name (property of the physical schema).

  • %OBJECT to symbolize the datastore name.

  • %PARTITION to symbolize a partition name.

For example: %SCHEMA.%OBJECT PARTITION(%PARTITION)

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: %SCHEMA.%OBJECT SUBPARTITION(%PARTITION)

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: %SCHEMA.%OBJECT.nextval

Remote Sequence Mask Shows the SQL code to use when accessing a native sequence that is on the remote data server.

For example: %SCHEMA.%OBJECT.nextval@%DSERVER

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: %SCHEMA.%OBJECT.currval

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: %SCHEMA.%OBJECT.currval@%DSERVER


7.34.2 SQL

The SQL properties are reserved for technologies with data or join filtering capacities (using the WHERE clause).

Where

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:
  • Complex Expression: The clause is followed by a complex expression or the column name.

  • Column Number: The ORDER BY clause is followed by the column numbers.

  • Alias: The ORDER BY clause is followed by the column aliases.

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.

Ordered Joins (ISO)

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".

Not Ordered 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.

Specific Queries

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:
  • PK_NAME: Name of the primary key.

  • COLUMN_NAME: Name of the primary key column.

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:
  • AK_NAME: Name of the alternate key.

  • COLUMN_NAME: Name of the alternate key column.

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:
  • FK_NAME: Name of the foreign key.

  • COLUMN_NAME: Name of the foreign key column.

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:
  • INDEX_NAME: Name of the index.

  • COLUMN_NAME: Name of the index column.

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:
  • CHECK_NAME: Name of the check constraint.

  • CHECK_TXT: SQL statement of the constraint.

  • CHECK_STATUS: Status of the check constraint - '1' for enabled, '0' for disabled.

  • CHECK_DESC: Description of the check constraint.

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:
  • SEQUENCE_NAME: Name of the native sequence.

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.

7.34.3 Advanced

Parameters

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.

Default Table Prefixes

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

7.34.4 Language

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:

  1. In the Language column, select the language of the technology.

  2. Select Default.

  3. 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