Oracle Discoverer
EUL Gateway Interface Specification v1.1.3
Table of Contents
1. Overview
2. Information loaded via a Gateway
2.1 Schemas
2.2 Objects
2.3 Columns
2.4 Filters
2.5 Unique Keys
2.6 Unique Key Columns
2.7 Foreign Keys
2.8 Foreign Key Columns
2.9 Object Foreign Key Usages
2.10 Hierarchies
2.11 Hierarchy Nodes
2.12 Hierarchy Node Columns
2.13 Hierarchy Segments
2.14 Business Areas
2.15 Business Area User Privileges
3. Installing a Gateway
3.1 Prompt for the gateway name and description
3.2 Determine the database and schema for the gateway
3.3 Determine the source for the external metadata
3.4 Determine the EUL in which to register the gateway
3.5 Create the interface objects if required
3.6 Make the interface objects accessible
3.7 Register the gateway with the EUL
4. Registering a Gateway
4.1 EUL4_GATEWAYS
4.2 Gateway Attribute Descriptions
5. Gateway Schema Layout
6. Gateway Interface Objects
6.1 EUL_GW_SCHEMAS
6.2 EUL_GW_OBJS
6.3 EUL_GW_COLS
6.4 EUL_GW_FILTERS
6.5 EUL_GW_UKS
6.6 EUL_GW_UK_COLS
6.7 EUL_GW_FKS
6.8 EUL_GW_FK_COLS
6.9 EUL_GW_OBJ_FK_USGS
6.10 EUL_GW_HIERS
6.11 EUL_GW_HIER_NDS
6.12 EUL_GW_HIER_N_COLS
6.13 EUL_GW_HIER_SEGS
6.14 EUL_GW_BAS
6.15 EUL_GW_BA_OBJREFS
6.16 EUL_GW_BA_UPRIVS
The EUL Gateway is a generic and open way of transferring metadata into the EUL. It is used to transfer metadata from a number of sources:
Data Warehouse / ETT tools
Legacy DSS systems
Custom applications
The basic mechanism relies on each gateway consisting of a number of pre-defined database objects (referred to as interface objects). Each interface object has a known name, set of columns and conforms to a known set of constraints. A gateway needs to be registered by inserting a row into an EUL metadata table.
This document specifies the structure and constraints for the interface objects and outlines the procedure for installing and registering a gateway.
Once the interface objects for a gateway have been created and registered with an EUL, the Discoverer Administration Edition loads the metadata into the EUL from the external source, either through its Load Wizard or through a command-line interface which is planned for the 3.1 release.
The main metadata elements accessed via the gateway are as follows:
A schema contains a group of objects. It may be on a remote database, accessible through a qualifier (database link). Schemas are also known as users or accounts.
An object describes a result set of data and may be simple or complex. An object belongs to one and only one schema and contains columns, filters, unique keys and foreign keys. An object must be one of the following types: simple, complex, summary.
A simple object directly describes a database object (table or view).
A complex object describes a result set which combines and/or modifies rows from one or more other objects (either simple or complex). If one were to compare the gateway's metadata against the database's data dictionary, then a simple object would be analogous to a database table and a complex object would be analogous to a database view.
If a complex object references more than one other object then there must be a valid join path between the referenced objects and an object foreign key usage (see ) must be defined for each join thus used.
A summary object describes an externally managed, pre-aggregated result set. Each of its columns maps to an column in a non-summary object. The column mapping is either a simple direct map or, for measures, it may involve an aggregation function (e.g. SUM). Summary objects should not be referenced by other objects (complex or summary).
The loading of summary objects is not supported in Discoverer 3.1.
In context of the EUL Gateway's metadata, a column describes both simple references to database columns and complex expressions which may involve a combination of literals, functions and column references (these types of columns are commonly known as derived items). Each column belongs to one and only one object. The metadata for a column includes attributes for describing its physical content as well as attributes for describing how the column is to be displayed and formatted in Discoverer.
Both simple and complex objects may contain derived items, though a derived item in a simple object can only reference other columns in the same object. Derived items in complex objects may reference columns in different objects as long as there is a valid join path between them.
Filters are analogous to non-join WHERE clauses in SQL and are used for restricting the row set of an object. A filter belongs to an object and each object may make use of several filters.
Filters may be mandatory, or optional. Mandatory filters are always applied to their owning object. Optional filters are made available in the Discoverer Analyzer Edition for inclusion in queries which involve their owning object.
An optional filter may refer to any column within the owning object. In addition to this, a mandatory filter belonging to a complex object may refer to any column in any of the referenced objects of the owning object.
This describes a unique key constraint on a database object. Each unique key belongs to one and only one object. One of the unique keys of an object may be designated as the primary key. Discoverer currently uses unique keys only as part of defining foreign key constraints.
Each of these describes a column within a unique key (and its position within the unique key).
This describes a foreign key constraint on a database object (used for joining objects together in Discoverer). Each foreign key belongs to one and only one object and normally refers to a unique key on the remote (master) object. The gateway specification also allows a foreign key to be defined by a join predicate expression (without the need to explicitly refer to a unique key on the remote object and without the need to explicitly define the foreign key columns).
The metadata may describe constraints (both foreign and unique keys) which do not necessarily physically exist on the database object. This allows Discoverer to describe, for example, joins between database views.
Each of these describes a column within a foreign key (and its position within the foreign key). If a composite foreign key explicitly references a remote unique key then its columns are matched to the remote unique key columns using their position within the key.
Object foreign key usages are required for complex objects and summary objects that reference more than one other object. Each object foreign key usage states that a particular complex/summary object makes use of a particular foreign key. Typically, the complex/summary object will refer to columns in the objects either side of the referenced foreign key.
Simple objects must not have object foreign key usages.
A hierarchy is a predefined path of analysis through one or more related objects. Discoverer uses hierarchy definitions for easy-to-access drill options in the Analyzer Edition. When a user wishes to perform a drill, Discoverer examines the items in the current query to see whether they exist in hierarchy nodes and if so, presents related hierarchy nodes as drill options. A hierarchy is defined by a number of hierarchy nodes and hierarchy segments. A hierarchy's segments define the relationships between its nodes.
The loading of hierarchies is not supported in Discoverer 3.1.
A hierarchy node is often referred to as a level and defines a source or target for a drill. Each node belongs to one and only one hierarchy. A hierarchy node contains references to one or more columns (via Hierarchy Node Columns). All the columns within one node must belong to the same object.
Each of these describes a reference to a column within a hierarchy node.
A hierarchy segment defines a relationship between two hierarchy nodes (and, therefore, allows Discoverer to drill between those two hierarchy nodes). Each hierarchy segment belongs to one and only one hierarchy.
If the two hierarchy nodes referenced by the hierarchy segment are in different objects, then a foreign key must be specified for navigating between the objects. If the two hierarchy nodes are contained within the same object then a foreign key should not be specified.
A business area is a named group of objects and is used for controlling access privileges to those objects. A business area may reference one or more objects and each object may be referenced by one or more business areas. It is not necessary for an object to be in a business area.
Business areas are also known as projects or application systems.
The loading of business areas is not supported in Discoverer 3.1. All the loaded objects are added to the same business area.
A business area user privilege describes the fact that a certain user has access to a certain business area for read-only (query) and possibly administrative purposes.
The loading of business area user privileges is not supported in Discoverer 3.1.
It is the responsibility of the gateway supplier to provide an appropriate installation process for their interface objects. The typical steps in the installation process should be as follows:
The user should be asked to provide a name and description for the new gateway.
The installation procedure should determine in which schema and database the gateway's interface objects will reside.
There may be more than one potential metadata source accessible from the schema and database where the interface objects are to be created. For example, both the test and production data warehouse repositories may reside in the same database.
In this case, the installation procedure will need to build the gateway interface objects to reference one of the alternative source repositories.
Similarly, there may be more than one EUL within the same database. The installation procedure will need to know with which EUL the gateway needs to be registered. It should be possible to register the same gateway with more than one EUL.
If the interface objects do not already exist or if the source of metadata for an existing gateway is being changed then the interface objects will need to be created, dropping any existing objects if they already exist.
Note that there may be no need to drop or create interface objects in the case that an existing gateway is being registered in a different EUL.
The gateway's interface objects need to be made accessible to those database users who wish to use the Discoverer Administration Edition in order to load metadata through it. The installation procedure may wish to provide user interaction for specifying users who wish to use the gateway and then explicitly grant select access to the users for each of the interface objects.
This is an optional step as access control can always be performed manually. Discoverer 3.1 will be shipped with functionality for supporting this (either within the product or through SQL scripts).
Finally, the gateway will need to be registered with the target EUL. The installation process should avoid re-registering a gateway within the same EUL (it may be the case that the gateway is already registered if the user is changing its source of metadata).
A gateway is registered with an EUL by inserting a row into the table EUL4_GATEWAYS. The format of the table is as follows:
GW_ID number(22) not null
GW_TYPE varchar2(10) not null
GW_GATEWAY_NAME varchar2(100) not null
GW_PRODUCT_NAME varchar2(100) not null
GW_DESCRIPTION varchar2(240) null
EGW_VERSION varchar2(30) null
EGW_DATABASE_LINK varchar2(64) null
EGW_SCHEMA varchar2(64) null
EGW_SQL_PARADIGM varchar2(10) null
GW_ELEMENT_STATE number(10) not null
GW_CREATED_BY varchar2(64) not null
GW_CREATED_DATE date not null
GW_UPDATED_BY varchar2(64) null
GW_UPDATED_DATE date null
NOTM number(10) null
GW_ID must be unique and should be populated by obtaining the next value from the sequence EUL_ID_SEQ (which resides in the target EUL schema).
GW_TYPE must be set to EGW.
NOTM is used by the EUL to track the state of a row in the metadata. It should always have a null value when a new row is inserted and should be incremented by one if a row is updated.
GW_ELEMENT_STATE should always be set to 0 (zero).
GW_CREATED_BY should be set to the current user and GW_CREATED_DATE should be set to the current system date.
Apart from GW_ID, there are no other uniqueness constraints for the contents of the table. Gateways will be presented to users of the Discoverer Administration Edition using the gateway name, product name and description and, so, it is the responsibility of the installation procedures to ensure the contents of this table remain sensible and recognizable with respect to those attributes.
The attributes of a gateway are as follows:
This is the name of the gateway as set by the administrator at install time (e.g. 'OADW Personnel Data Warehouse Repository'). The gateway name will not be required to be unique.
The name of the external product to which the gateway provides an interface (e.g. 'OADW').
The version string for the EUL Gateway specification. This will effectively tell the EUL the expected structure of the gateway's interface objects.
For gateways conforming to this specification, the value should always be '1.1' (the first two parts of the version number of this document).
The database link through which to access the gateway's interface objects. If the gateway interface objects are on the same database instance as the target EUL then this attribute should be null.
The name of the database schema which owns the gateway's interface objects.
The paradigm to be used for the parsing of the SQL fragments held in various columns of the gateway table. This may be one of:
OBJECT
TABLE
The default is 'OBJECT'.
If it is set to 'TABLE', then all SQL fragments should refer to tables by the name specified in the O_TABLE_NAME column of EUL_GW_OBJS (see ). If it is set to 'OBJECT' then the name specified in the 'O_OBJ_NAME' column should be used. In the case where these two object attributes are the same, the paradigm is obviously irrelevant. One case where the attributes would have to be different is where it is required that two simple objects which both refer to the same underlying table be loaded into the EUL. If these two objects are then joined to make a complex object, then the only way that the columns of each is distinguished is to set the paradigm to 'OBJECT'.
The schema layout for the gateway interface objects is shown in Figure 1.
This section specifies the database objects that each gateway must implement. The objects must exactly match the given structure and should conform to the specified constraints. If the constraints are not met then loading from the gateway will not be guaranteed to work.
Each interface object may be implemented as either a table or a view.
The interface objects are:
EUL_GW_SCHEMAS
EUL_GW_OBJS
EUL_GW_COLS
EUL_GW_FILTERS
EUL_GW_UKS
EUL_GW_UK_COLS
EUL_GW_FKS
EUL_GW_FK_COLS
EUL_GW_OBJ_FK_USGS
EUL_GW_HIERS
EUL_GW_HIER_NDS
EUL_GW_HIER_N_COLS
EUL_GW_HIER_SEGS
EUL_GW_BAS
EUL_GW_BA_OBJREFS
EUL_GW_BA_UPRIVS
Each row in this table defines a user schema which can either contain object, be the subject of a privilege or both.
S_SCHEM_NAME varchar2(64) not null
S_QUAL_NAME varchar2(64) null
S_SCHEM_NAME
The name of the user schema.
S_QUAL_NAME is optionally used to specify that the schema resides on a different database instance and is accessible through the given qualifier (database link).
Each row in this table represents an object.
O_S_SCHEM_NAME varchar2(64) not null
O_OBJ_NAME varchar2(64) not null
O_OBJ_TYPE varchar2(13) not null
O_HIDDEN varchar2(1) not null
O_TABLE_NAME varchar2(64) null
O_DISP_NAME varchar2(100) null
O_DESCRIPTION varchar2(240) null
O_SUMM_RFRSH_DATE date null
O_SUMM_VALIDITY varchar2(1) null
O_S_SCHEM_NAME
O_OBJ_NAME
O_S_SCHEM_NAME
The user schema name and external object name of the object.
The value of O_OBJ_TYPE must be one of:
TABLE
VIEW
COMPLEX
SUMMARY
The TABLE and VIEW values are used for simple objects. The value COMPLEX means this is a complex object and the value SUMMARY means this is a summary object.
The value of O_HIDDEN specifies whether the object is visible to the user and must be one of:
Y
N
For objects of type 'TABLE' the name of the database table upon which this object is based.
The name of the object to be displayed to the user.
The full description of the object.
This is only used for summary objects and contains the date the summary was last refreshed.
The value of O_SUMM_VALIDITY must be one of:
Y
N
This column is only used for summary objects and indicates whether the externally managed summary is in a valid state.
Each row in this table represents a column of an object.
C_O_SCHEM_NAME varchar2(64) not null
C_O_OBJ_NAME varchar2(64) not null
C_COL_NAME varchar2(64) not null
C_SQL_DERIVATION varchar2(240) null
C_DATATYPE varchar2(70) not null
C_NULL_INDICATOR varchar2(10) not null
C_HIDDEN varchar2(1) not null
C_LENGTH number(22) null
C_DECIMAL_PLACES number(22) null
C_DISP_NAME varchar2(100) null
C_HEADING varchar2(240) null
C_DESCRIPTION varchar2(240) null
C_DISP_SEQ number(22) null
C_DISP_LENGTH number(22) null
C_CASE_DISPLAY varchar2(10) null
C_CASE_STORAGE varchar2(10) null
C_ALIGNMENT varchar2(10) null
C_FORMAT_MASK varchar2(100) null
C_C_LOV_SCHEM_NAME varchar2(64) null
C_C_LOV_OBJ_NAME varchar2(64) null
C_C_LOV_COL_NAME varchar2(64) null
C_DEF_ROLLUP_FUNC varchar2(70) null
C_DISP_NULL_VALUE varchar2(240) null
C_ORD_CLAUSE_POS number(22) null
C_ORD_DIRECTION varchar2(10) null
C_PLACEMENT varchar2(10) null
C_CONTENT_TYPE varchar2(100) null
C_O_SCHEM_NAME
C_O_OBJ_NAME
C_COL_NAME
C_O_SCHEM_NAME
C_O_OBJ_NAME
C_C_LOV_SCHEM_NAME
C_C_LOV_OBJ_NAME
C_C_LOV_COL_NAME
The user schema name and object name that owns the column.
C_COL_NAME uniquely identifies the column within the object. It serves two purposes. First, it uniquely names the column in context of the owning object. Second, for non-derived columns of simple objects it can map to the name of the column in the underlying table or view which is being described.
C_SQL_DERIVATION is used for derived items of simple objects or columns of complex and summary objects. It contains an expression (in SQL) which specifies how the column is derived. It is used for specifying both simple references to other objects' columns and more complex expressions combining literals, functions and a number of database columns.
Column references must be qualified with schema names and either object names or table names where there is any ambiguity. Whether to use object names or tables names depends on the SQL paradigm in use for the gateway as specified in the EGW_SQL_PARADIGM column of the EUL_GATEWAY table (see ).
A column belonging to a simple object is simple or derived. If it is derived then it has a SQL expression which can only refer to other columns in the same simple object. If it is simple then the column name is given here or in C_COL_NAME. It is not necessary to qualify referenced columns for expressions in simple objects.
Columns belonging to complex objects have expressions in C_SQL_DERIVATION which can refer to any combination of columns as long as there is a join relationship between them (see Section 2 for a discussion on complex objects and object foreign key usages). Column references within expressions for complex objects must be fully qualified with the owning schema and object or table name where there is ambiguity. For complex objects, C_COL_NAME is used to uniquely identify the column within the object.
For columns belonging to summary objects, C_COL_NAME contains the name of the summary object column which contains the pre-aggregated data and C_SQL_DERIVATION contains the expression which is being pre-aggregated. The expression is either a reference to a mapped (dimension) column or an aggregate function applied to a mapped (measure) column. In either case, column references must be fully qualified.
Examples of what C_SQL_DERIVATION may contain:
Simple reference to a database column in a simple object:
empno
Derived item involving multiple columns in a simple object:
sal*12 + nvl(comm, 0)
Derived item involving multiple columns in a complex object:
scott.emp.ename || ' ' || scott.emp.dname
Summary object column which is a simple reference to a column in another object:
scott.emp.empno
Summary object column which is a reference to a column with an aggregate function:
sum(scott.emp.sal)
These last examples refer to a complex object based on an object called Emp1 based on the table EMP joined to an object called Dept1 based on the table DEPT and deal with the case of the ambiguous column DEPTNO:
Simple reference to column with EGW_SQL_PARADIGM set to 'TABLE':
scott.emp.deptno
Simple reference to column with EGW_SQL_PARADIGM set to 'OBJECT':
scott.emp1.deptno
The value of C_DATATYPE must be one of:
BINARY INTEGER
CHAR
DATE
DECIMAL
DOUBLE PRECISION
FLOAT
GRAPHIC
IMAGE
INTEGER
LONG
LONG RAW
LONG VARCHAR
LONG VARGRAPHIC
NUMBER
RAW
REAL
ROWID
SMALLINT
SOUND
TEXT
TIME
TIMESTAMP
VARCHAR
VARCHAR2
VARGRAPHIC
VIDEO
The value of C_NULL_INDICATOR indicates whether or not the column is null and must be one of:
NULL
NOT NULL
The value of C_HIDDEN indicates whether or not the column is visible to the user and must be one of:
Y
N
C_LENGTH is the maximum length of the data that the column can contain. This field should be completed for datatypes other than DATE. In the case of character datatypes, this property is the length in bytes of the column. For single byte character sets, this corresponds to the length in characters. In the case of numeric datatypes, this property specifies the number of digits in the number (not including the decimal point if it exists).
C_DECIMAL_PLACES is the number of decimal places allowed for the column. This is only applicable to numeric datatype columns.
The name of the column to be displayed to the user.
The heading for the column.
The full description of the column.
The value of C_DISP_SEQ must be a positive integer starting from 1. It indicates a default ordering of the columns in a query.
The value of C_DISP_LENGTH must be a positive integer starting from 1. It gives the maximum display width of the column in a query.
The value of C_CASE_DISPLAY may be one of:
LOWER
UPPER
CAPITALIZE
UNCHANGED
The value of C_CASE_STORAGE may be one of:
LOWER
UPPER
MIXED
The value of C_ALIGNMENT may be one of:
DEFAULT
LEFT
CENTER
RIGHT
The format mask to be used for the display of the column.
These columns give the (optional) schema name, object name and column name that identify another column within the gateway that is to provide a list of values for the current column. This is not supported in Discoverer 3.1.
The value of C_DEF_ROLLUP_FUNC gives the default aggregation function for the column to be used in query. It may be one of:
COUNT
SUM
AVG
MAX
MIN
VARIANCE
STDDEV
COUNT_DISTINCT
SUM_DISTINCT
AVG_DISTINCT
MAX_DISTINCT
MIN_DISTINCT
VARIANCE_DISTINCT
STDDEV_DISTINCT
Discoverer will use the database server to convert any null values for the item into the value specified by C_DISP_NULL_VALUE. This means no null values will be returned as data into the sheet. This is equivalent to the "Replace NULL with" item property accessible in the Administration Edition.
The value of C_ORD_CLAUSE_POS may be a positive integer starting from 1.
The value of C_ORD_DIRECTION must be one of:
ASC
DESC
The value of C_PLACEMENT indicates the default position in which the column will appear in a query and may be one of:
MEASURE
AXIS
XAXIS
YAXIS
ZAXIS
The value of C_CONTENT_TYPE must be one of:
FILE
(This list may be expanded in future, but, currently there is only one value.)
A content type of 'FILE' enables Discoverer Hyperdrill Plug In for this column. It signifies that the column contains the names of files with which Discoverer can launch external applications.
Each row in this table represents a filter that may be applied to a single object.
F_O_SCHEM_NAME varchar2(64) not null
F_O_OBJ_NAME varchar2(64) not null
F_DISP_NAME varchar2(100) not null
F_MANDATORY_FLAG varchar2(1) not null
F_DESCRIPTION varchar2(240) null
F_SQL varchar2(240) not null
F_O_SCHEM_NAME
F_O_OBJ_NAME
F_DISP_NAME
F_O_SCHEM_NAME
F_O_OBJ_NAME
The user schema name and object name to which the filter applies.
The name of the filter to be displayed to the user.
The value of F_MANDATORY_FLAG must be one of:
Y
N
The full description of the filter.
The SQL expression that defines the filter. It is never necessary to qualify the column names used by optional filters, but the columns of mandatory filters must be qualified if there is ambiguity. If qualification is used, then table names or object names should be used according to the setting of EGW_SQL_PARADIGM in the EUL_GATEWAY table (see ).
Each row in this table represents a unique key of a single object.
UK_O_SCHEM_NAME varchar2(64) not null
UK_O_OBJ_NAME varchar2(64) not null
UK_CONSTR_NAME varchar2(64) not null
UK_DISP_NAME varchar2(100) null
UK_PRIM_KEY_IND varchar2(1) not null
UK_DESCRIPTION varchar2(240) null
UK_O_SCHEM_NAME
UK_O_OBJ_NAME
UK_CONSTR_NAME
UK_O_SCHEM_NAME
UK_O_OBJ_NAME
The user schema name and object name to which the key applies.
The constraint name which uniquely identifies the key within its owning object.
The name of the key to be displayed to the user.
The value of UK_PRIM_KEY_IND indicates whether the key is a primary or candidate key and must be one of:
Y
N
The full description of the key to be displayed to the user.
Each row in this table represents a column that forms part of a unique key of an object.
UKC_UK_SCHEM_NAME varchar2(64) not null
UKC_UK_OBJ_NAME varchar2(64) not null
UKC_UK_CONSTR_NAME varchar2(64) not null
UKC_C_COL_NAME varchar2(64) not null
UKC_SEQUENCE number(22) not null
UKC_UK_SCHEM_NAME
UKC_UK_OBJ_NAME
UKC_UK_CONSTR_NAME
UKC_C_COL_NAME
UKC_UK_SCHEM_NAME
UKC_UK_OBJ_NAME
UKC_UK_CONSTR_NAME
UKC_SEQUENCE
UKC_UK_SCHEM_NAME
UKC_UK_OBJ_NAME
UKC_UK_CONSTR_NAME
UKC_UK_SCHEM_NAME
UKC_UK_OBJ_NAME
UKC_C_COL_NAME
The user schema name, object name and constraint name which owns the key column.
The column name for this key column
Within a unique key, the value of UKC_SEQUENCE must start from 1 and must be incremented by exactly one for each subsequent column in the key. Any set of foreign key columns that match this unique key will be numbered in a similar manner. When a join is constructed, the columns from the unique key and foreign key are paired in this sequence.
Each row in this table represents a foreign key of a single object that references a single unique key of another single object.
FK_O_SCHEM_NAME varchar2(64) not null
FK_O_OBJ_NAME varchar2(64) not null
FK_CONSTR_NAME varchar2(64) not null
FK_ONE_TO_ONE varchar2(1) not null
FK_OJOIN_MASTER varchar2(1) not null
FK_OJOIN_DETAIL varchar2(1) not null
FK_DISP_NAME varchar2(100) null
FK_DESCRIPTION varchar2(240) null
FK_REM_SCHEM_NAME varchar2(64) null
FK_REM_OBJ_NAME varchar2(64) null
FK_REM_UK_NAME varchar2(64) null
FK_SQL_PREDICATE varchar2(240) null
FK_O_SCHEM_NAME
FK_O_OBJ_NAME
FK_CONSTR_NAME
FK_O_SCHEM_NAME
FK_O_OBJ_NAME
FK_REM_SCHEM_NAME
FK_REM_OBJ_NAME
FK_REM_UK_NAME
This foreign key is used if the explicit unique key reference method of registering joins is used. In this case the FK_SQL_PREDICATE column must be null.
FK_REM_SCHEM_NAME
FK_REM_OBJ_NAME
This foreign key is used if the SQL predicate method of registering joins is used. In this case the FK_REM_UK_NAME column must be null and the FK_SQL_PREDICATE not null.
The user schema name and object name which owns the key.
The constraint name which uniquely identifies the key within its owning object.
The value of FK_ONE_TO_ONE indicates whether the resulting join is a "one to one" join and must be one of:
Y
N
The value of FK_OJOIN_MASTER indicates whether the resulting join is an outer join on the master side ( after the column of the master object) and must be one of:
Y
N
The value of FK_OJOIN_MASTER indicates whether the resulting join is an outer join on the detail side ( after the column of the detail object) and must be one of:
Y
N
The name of the key to be displayed to the user.
The full description of the key to be displayed to the user.
These user schema name and object name of the object that contains the unique key to which the foreign key is joined
There are two alternative ways of specifying the foreign key join predicates. Although either method is used to specify equi-joins, the SQL Predicate method must be used for specifying non-equi joins.
Use the foreign key to EUL_GW_UKS to reference a unique key on the master object. The foreign key must have foreign key columns specified and these are matched to unique key columns using the relative position within the key as defined by the sequence number.
Using this method, the column FK_SQL_PREDICATE should have a null value and the foreign key to EUL_GW_UKS must be specified. The FK_REM_UK_NAME column then gives the constraint name of the unique key.
Use FK_SQL_PREDICATE to specify the join condition(s) in SQL. The where keyword should not be included and the or operator should not be used. Composite foreign keys should use the and operator between join conditions. Column references must be qualified if there is column name ambiguity, otherwise, qualification is optional. If qualification is used, then table names or object names should be used according to the setting of EGW_SQL_PARADIGM in the EUL_GATEWAY table (see ). The foreign key does not require foreign key columns to be defined in EUL_GW_FK_COLS and should not reference a unique key, though it must reference the remote object.
For example, the foreign key between EMP and DEPT would be specified using the SQL predicate:
emp.deptno = dept.deptno
An example of a composite foreign key would be:
detail.fk_col1 = master.pk_col1 and detail.fk_col2 = master.pk_col2
Using this method, the column FK_REM_UK_NAME must have a null value, but the columns FK_REM_SCHEM_NAME and FK_REM_OBJ_NAME must have values which reference the remote object for the foreign key.
An outer join on the master and an outer join on the detail should not both be specified
Each row of this table represents a column that forms part of a foreign key of an object.
FKC_FK_SCHEM_NAME varchar2(64) not null
FKC_FK_OBJ_NAME varchar2(64) not null
FKC_FK_CONSTR_NAME varchar2(64) not null
FKC_C_COL_NAME varchar2(64) not null
FKC_SEQUENCE number(22) not null
FKC_FK_SCHEM_NAME
FKC_FK_OBJ_NAME
FKC_FK_CONSTR_NAME
FKC_C_COL_NAME
FKC_FK_ SCHEM_NAME
FKC_FK_OBJ_NAME
FKC_FK_CONSTR_NAME
FKC_SEQUENCE
FKC_FK_ SCHEM_NAME
FKC_FK_OBJ_NAME
FKC_FK_CONSTR_NAME
FKC_FK_ SCHEM_NAME
FKC_FK_OBJ_NAME
FKC_C_COL_NAME
The user schema name, object name and constraint name of the foreign key which owns the key column.
The name of the key column.
Within a foreign key, the value of FKC_SEQUENCE must start from 1 and must be incremented by exactly one for each subsequent column in the key. The sequence numbers match those in the columns of the unique key to which the owning foreign key is joined
The rows of this table are used when specifying a complex object. A complex object references a number of foreign keys of other simple or complex objects. The objects whose foreign keys are referenced are the ones which comprise the complex object. For instance, if a complex object is made that contains objects EMP and DEPT, then one foreign key will be referenced - the foreign key on EMP which refers to the unique key on DEPT.
A complex folder can use many foreign keys, and each foreign key is used by many complex objects. This many to may relationship is resolved by this table.
OFU_O_SCHEM_NAME varchar2(64) not null
OFU_O_OBJ_NAME varchar2(64) not null
OFU_FK_SCHEM_NAME varchar2(64) not null
OFU_FK_OBJ_NAME varchar2(64) not null
OFU_FK_CONSTR_NAME varchar2(64) not null
OFU_O_SCHEM_NAME
OFU_O_OBJ_NAME
OFU_FK_SCHEM_NAME
OFU_FK_OBJ_NAME
OFU_FK_CONSTR_NAME
OFU_O_SCHEM_NAME
OFU_O_OBJ_NAME
OFU_FK_ SCHEM_NAME
OFU_FK_OBJ_NAME
OFU_FK_CONSTR_NAME
The schema name and object name of the complex object which uses the specified foreign key.
The schema name, object name and constraint name of the foreign key which is used by the complex object.
Each row of this table represents a hierarchy.
HI_HIER_NAME varchar2(64) not null
HI_DESCRIPTION varchar2(240) null
HI_HIER_NAME
The name of the hierarchy to be displayed to the user.
The full description of the hierarchy to be displayed to the user.
Each row of this table represents a node that belongs to a single hierarchy.
HN_HI_HIER_NAME varchar2(64) not null
HN_NODE_NAME varchar2(64) not null
HN_DESCRIPTION varchar2(240) null
HN_HI_HIER_NAME
HN_NODE_NAME
HN_HI_HIER_NAME
The name of the hierarchy that owns this node.
The name of the hierarchy node.
The full description of the node.
Each row of this table represents a column that belongs to a single hierarchy node.
HNC_HN_HIER_NAME varchar2(64) not null
HNC_HN_NODE_NAME varchar2(64) not null
HNC_C_SCHEM_NAME varchar2(64) not null
HNC_C_OBJ_NAME varchar2(64) not null
HNC_C_COL_NAME varchar2(64) not null
HNC_HN_HIER_NAME
HNC_HN_NODE_NAME
HNC_C_ SCHEM_NAME
HNC_C_OBJ_NAME
HNC_C_COL_NAME
HNC_HN_HIER_NAME
HNC_HN_NODE_NAME
HNC_C_ SCHEM_NAME
HNC_C_OBJ_NAME
HNC_C_COL_NAME
The hierarchy name and hierarchy node name to which this column belongs.
The user schema name, object name and column name that is owned by the hierarchy node.
All columns referenced by a single hierarchy node must be fully contained within one object.
Each row in this table represents a path between two nodes of the same hierarchy. The path is directed, in that it has a parent node and a child node.
HS_HI_HIER_NAME varchar2(64) not null
HS_HN_PAR_N_NAME varchar2(64) not null
HS_HN_CHILD_N_NAME varchar2(64) not null
HS_FK_SCHEM_NAME varchar2(64) null
HS_FK_OBJ_NAME varchar2(64) null
HS_FK_CONSTR_NAME varchar2(64) null
HS_HI_HIER_NAME
HS_HN_PAR_N_NAME
HS_HN_CHILD_N_NAME
HS_HI_HIER_NAME
HS_HN_PAR_N_NAME
HS_HI_HIER_NAME
HS_HN_CHILD_N_NAME
HS_FK_ SCHEM_NAME
HS_FK_OBJ_NAME
HS_FK_CONSTR_NAME
The name of the hierarchy that owns the segment.
The name of the parent node of the segment.
The name of the child node of the segment.
The user schema name, object name and constraint name of the foreign key that is traversed by the hierarchy segment..
Each row in this table represents a business area which can contain objects.
BA_NAME varchar2(64) not null
BA_DESCRIPTION varchar2(240) null
BA_NAME
The name of the business area.
The full description of the business area.
The rows in this table are used when assigning objects to business areas. Each business area can contain many objects and each object may be owned by many business areas. This many to may relationship is resolved by this table.
BOR_BA_NAME varchar2(64) not null
BOR_O_SCHEM_NAME varchar2(64) not null
BOR_O_OBJ_NAME varchar2(64) not null
BOR_BA_NAME
BOR_O_SCHEM_NAME
BOR_O_OBJ_NAME
BOR_BA_NAME
BOR_O_ SCHEM_NAME
BOR_O_OBJ_NAME
The name of the business area that owns the objects.
The user schema name and object name of the objects owned by the business area.
Each row in this table represents an access privilege over a single business area granted to a single user.
BUP_BA_NAME varchar2(64) not null
BUP_S_SCHEM_NAME varchar2(64) not null
BUP_ADMIN_PRIV varchar2(1) not null
BUP_BA_NAME
BUP_S_ SCHEM_NAME
BUP_BA_NAME
BUP_S_ SCHEM_NAME
The name of the business area for which permission is to be granted.
The name of the user schema to which permission is to be granted.
The value of BUP_ADMIN_PRIV defines whether the specified user is allowed admin privilege as well as simple access privilege and must be one of:
Y
N
|
