Oracle Transparent Gateway Microsoft SQL Server Administrator's Guide Release 8.1.6 for Windows NT Part Number A82868-01 |
|
The Oracle Transparent Gateway for Microsoft SQL Server translates a query that refers to an Oracle database server data dictionary table into a query that retrieves the data from a Microsoft SQL Server data dictionary. You perform queries on data dictionary tables over the database link in the same way you query data dictionary tables in the Oracle database server. The gateway data dictionary is similar to the Oracle database server data dictionary in appearance and use.
This appendix contains the following sections:
Microsoft SQL Server data dictionary information is stored in the Microsoft SQL Server database as Microsoft SQL Server system tables. All Microsoft SQL Server system tables have names prefaced with "dbo". The Microsoft SQL Server system tables define the structure of a database. When you change data definitions, Microsoft SQL Server reads and modifies the Microsoft SQL Server system tables to add information about the user tables.
Accessing a gateway data dictionary table or view is identical to accessing a data dictionary in an Oracle database. You issue a SQL SELECT statement specifying a database link. The Oracle database server data dictionary view and column names are used to access the gateway data dictionary. Synonyms of supported views are also acceptable. For example, the following statement queries the data dictionary table ALL_TABLES to retrieve all table names in the Microsoft SQL Server database:
SQL> SELECT * FROM "ALL_TABLES"@MSQL;
When a data dictionary access query is issued, the gateway:
Queries issued directly to individual Microsoft SQL Server system tables are allowed but they return different results because the Microsoft SQL Server system table column names differ from those of the data dictionary view. Also, certain columns in a Microsoft SQL Server system table cannot be used in data dictionary processing.
The gateway supports the following views and tables:
No other Oracle database server data dictionary tables or views are supported. If you use a view not on the list, you receive the Oracle database server error code for no more rows available.
Queries through the gateway of the supported data dictionary tables and views beginning with the characters ALL_ might return rows from the Microsoft SQL Server database when you do not have access privileges for those Microsoft SQL Server objects. When querying an Oracle database with the Oracle data dictionary, rows are returned only for those objects you are permitted to access.
The tables in this section list Oracle data dictionary view names and the equivalent Microsoft SQL Server system table names. A plus sign (+) indicates that a join operation is involved.
There is a minor difference between the gateway data dictionary and a typical Oracle database server data dictionary. The Oracle database server columns that are missing in a Microsoft SQL Server data dictionary table are filled with zeros, spaces, null values, not-applicable values (N.A.), or default values, depending on the column type.
The gateway data dictionary tables and views provide the following information:
They are described here with information retrieved by an Oracle Enterprise Manager DESCRIBE command. The values in the Null? column might differ from the Oracle database server data dictionary tables and views. Any default value is shown to the right of an item, but this is not information returned by DESCRIBE.
Name | Null? | Type | Value |
---|---|---|---|
OWNER |
NOT NULL |
VARCHAR2(255) |
|
TABLE_NAME |
NOT NULL |
VARCHAR2(255) |
|
TABLE_TYPE |
|
VARCHAR2(5) |
"TABLE" or "VIEW" |
Name | Null? | Type | Value |
---|---|---|---|
OWNER |
NOT NULL |
VARCHAR2(255) |
|
TABLE_NAME |
NOT NULL |
VARCHAR2(255) |
|
COLUMN_NAME |
NOT NULL |
VARCHAR2(255) |
|
COMMENTS |
NOT NULL |
CHAR(1) |
|
Name | Null? | Type | Value |
---|---|---|---|
OWNER |
|
VARCHAR2(30) |
|
CONSTRAINT_NAME |
|
VARCHAR2(30) |
|
TABLE_NAME |
|
VARCHAR2(30) |
|
COLUMN_NAME |
|
VARCHAR2(4000) |
|
POSITION |
|
NUMBER |
|
Name | Null? | Type | Value |
---|---|---|---|
OWNER |
NOT NULL |
VARCHAR2(255) |
|
TABLE_NAME |
NOT NULL |
VARCHAR2(255) |
|
TABLE_TYPE |
|
VARCHAR2(5) |
"TABLE" or "VIEW" |
COMMENTS |
|
VARCHAR2(1) |
NULL |
Name | Null? | Type | Value |
---|---|---|---|
USERNAME |
NOT NULL |
VARCHAR2(255) |
|
USER_ID |
NOT NULL |
NUMBER(5) |
|
CREATED |
|
VARCHAR2(0) |
NULL |
Name | Null? | Type | Value |
---|---|---|---|
OWNER |
NOT NULL |
VARCHAR2(255) |
|
TABLE_NAME |
NOT NULL |
VARCHAR2(255) |
|
TABLE_TYPE |
|
VARCHAR2(5) |
"TABLE" or "VIEW" |
Name | Null? | Type | Value |
---|---|---|---|
OWNER |
NOT NULL |
VARCHAR2(255) |
|
TABLE_NAME |
NOT NULL |
VARCHAR2(255) |
|
COLUMN_NAME |
NOT NULL |
VARCHAR2(255) |
|
COMMENTS |
NOT NUL |
CHAR(1) |
NULL |
Name | Null? | Type | Value |
---|---|---|---|
OWNER |
NOT NULL |
VARCHAR2(255) |
|
TABLE_NAME |
NOT NULL |
VARCHAR2(255) |
|
TABLE_TYPE |
|
VARCHAR2(5) |
"TABLE" or "VIEW" |
COMMENTS |
|
VARCHAR2(1) |
NULL |
Name | Null? | Type | Value |
---|---|---|---|
TABLE_NAME |
NOT NULL |
VARCHAR2(255) |
|
COLUMN_NAME |
NOT NULL |
VARCHAR2(255) |
|
COMMENTS |
NOT NULL |
CHAR2(1) |
NULL |
Name | Null? | Type | Value |
---|---|---|---|
TABLE_NAME |
NOT NULL |
VARCHAR2(255) |
|
COMMENTS |
NOT NULL |
CHAR(1) |
|
Name | Null? | Type | Value |
---|---|---|---|
DUMMY |
NOT NULL |
VARCHAR2(1) |
"X" |
Name | Null? | Type | Value |
---|---|---|---|
TABLE_NAME |
NOT NULL |
VARCHAR2(255) |
|
TABLE_TYPE |
|
VARCHAR2(5) |
"TABLE" or "VIEW" |
Name | Null? | Type | Value |
---|---|---|---|
TABLE_NAME |
NOT NULL |
VARCHAR2(255) |
|
COLUMN_NAME |
NOT NULL |
VARCHAR2(255) |
|
COMMENTS |
|
VARCHAR2(1) |
NULL |
Name | Null? | Type | Value |
---|---|---|---|
OWNER |
|
VARCHAR2(30) |
|
CONSTRAINT_NAME |
|
VARCHAR2(30) |
|
TABLE_NAME |
|
VARCHAR2(30) |
|
COLUMN_NAME |
|
VARCHAR2(4000) |
|
POSITION |
|
NUMBER |
|
Name | Null? | Type | Value |
---|---|---|---|
TABLE_NAME |
NOT NULL |
VARCHAR2(255) |
|
TABLE_TYPE |
|
VARCHAR2(5) |
"TABLE" or "VIEW" |
COMMENTS |
|
VARCHAR2(1) |
NULL |
|
Copyright © 2001 Oracle Corporation. All Rights Reserved. |
|