Table of Contents

Data Dictionary Translation Views

Data dictionary translations are views on non-Oracle data dictionary tables. They help Heterogeneous Services translate references to Oracle data dictionary tables into queries that can retrieve the equivalent information from the non-Oracle data dictionary.

For example, the following SELECT statement retrieves information about tables from a SQL Server data dictionary table through the DB link: 

SELECT * FROM USER_TABLES@MyDatabaseLinkOfMyODBCSource;

Generic Connectivity Data Dictionary Mapping

The table below list Oracle data dictionary view names and the equivalent ODBC or OLE DB APIs used. It shows a list of all Oracle data dictionary view names supported by Generic Connectivity.

View ODBC API OLE DB API
ALL_CATALOG SQLTables DBSCHEMA_CATALOGS
ALL_COL_COMMENTS SQLColumns DBSCHEMA_COLUMNS
ALL_CONS_COLUMNS SQLPrimaryKeys, SQLForeignKeys DBSCHEMA_PRIMARY_KEYS, DBSCHEMA_FOREIGN_KEYS
ALL_CONSTRAINTS SQLPrimaryKeys, SQLForeignKeys DBSCHEMA_PRIMARY_KEYS, DBSCHEMA_FOREIGN_KEYS
ALL_IND_COLUMNS SQLStatistics DBSCHEMA_STATISTICS
ALL_INDEXES SQLStatistics DBSCHEMA_STATISTICS
ALL_OBJECTS SQLTables, SQLProcedures, SQLStatistics DBSCHEMA_TABLES, DBSCHEMA_PROCEDURES, DBSCHEMA_STATISTICS
ALL_TAB_COLUMNS SQLColumns DBSCHEMA_COLUMNS
ALL_TAB_COMMENTS SQLTables DBSCHEMA_TABLES
ALL_TABLES SQLStatistics DBSCHEMA_STATISTICS
ALL_USERS SQLTables DBSCHEMA_TABLES
ALL_VIEWS SQLTables DBSCHEMA_TABLES
DICTIONARY SQLTables DBSCHEMA_TABLES
USER_CATALOG SQLTables DBSCHEMA_TABLES
USER_COL_COMMENTS SQLColumns DBSCHEMA_COLUMNS
USER_CONS_COLUMNS SQLPrimaryKeys, SQLForeignKeys DBSCHEMA_PRIMARY_KEYS, DBSCHEMA_FOREIGN_KEYS
USER_CONSTRAINTS SQLPrimaryKeys, SQLForeignKeys DBSCHEMA_PRIMARY_KEYS, DBSCHEMA_FOREIGN_KEYS
USER_IND_COLUMNS SQLStatistics DBSCHEMA_STATISTICS
USER_INDEXES SQLStatistics DBSCHEMA_STATISTICS
USER_OBJECTS SQLTables, SQLProcedures, SQLStatistics DBSCHEMA_TABLES, DBSCHEMA_PROCEDURES, DBSCHEMA_STATISTICS
USER_TAB_COLUMNS SQLColumns DBSCHEMA_COLUMNS
USER_TAB_COMMENTS SQLTables DBSCHEMA_TABLES
USER_TABLES SQLStatistics DBSCHEMA_STATISTICS
USER_USERS SQLTables DBSCHEMA_TABLES
USER_VIEWS SQLTables DBSCHEMA_TABLES

Support

Data dictionary table contains no information?

Mimicked instead of translated

Data dictionary tables can be mimicked instead of translated. If a data dictionary translation is not possible because the non-Oracle data source does not have the required information in its data dictionary, then Heterogeneous Services causes it to appear as if the data dictionary table is available, but the table contains no information.

To retrieve information about which Oracle data dictionary views or tables are translated or mimicked for the non-Oracle system, connect as user SYS and issue the following query on the HS_CLASS_DD view: 

SELECT DD_TABLE_NAME, TRANSLATION_TYPE
FROM   HS_CLASS_DD
WHERE  FDS_CLASS_NAME LIKE 'SYBASE%';

DD_TABLE_NAME                  T
-----------------------------  -
ALL_ARGUMENTS                  M
ALL_CATALOG                    T
ALL_CLUSTERS                   T
ALL_CLUSTER_HASH_EXPRESSIONS   M
ALL_COLL_TYPES                 M
ALL_COL_COMMENTS               T
ALL_COL_PRIVS                  M
ALL_COL_PRIVS_MADE             M
ALL_COL_PRIVS_RECD             M
...

Legal values for translation type are:

  • 'T' or 't' for 'translate',
  • 'M' or 'm' for 'mimic'.

Registration ?

To store information about an agent in the data dictionary, Oracle must performed a registration action. Agents do not have to be registered. If an agent is not registered, Oracle stores information about the agent in memory instead of in the data dictionary.

Error information

Heterogeneous Services agent uploads data dictionary content to an ORACLE server on the first connection from the server to the agent.

If an error occurs in the data dictionary translation, the information on the exact translation causing an error is written:

  • to a trace (*.TRC) file for the ORACLE instance
  • and to the ORACLE instance's alert log.

Documentation / Reference