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;
Articles Related
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.