Integration Platform Technologies: Siebel Enterprise Application Integration > External Business Components > Process of Configuring External Business Components >
Creating the External Table Definition
You use Siebel Tools and the External Table Schema Import Wizard to import your external table definition into the Siebel Repository. You can create the table definition in one of two ways:
For more information about using Siebel Tools, see Using Siebel Tools. This task is a step in Process of Configuring External Business Components. Creating the External Table Definition from a DDL File
You can use the External Table Schema Import Wizard to create the table definition from a data definition language (DDL) file. It is possible to import an external view definition rather than a table definition. When a view rather than a table definition is imported, it is necessary to amend the Type property of the created Table definition to reflect External View. NOTE: You can import a database view definition as well as a table definition here. While no difference exists in the resulting Siebel Table object, if it references an external database view, only read access from the Siebel Application is supported.
To create the external table definition from a DDL file
- In Siebel Tools, check out and lock the appropriate project.
- From the File menu, choose New Object to display the New Object Wizards dialog box.
- Click the General tab, then double-click External Table Schema Import.
- In the External Table Schema Import Wizard, specify the following values, then click Next:
- Choose the project with which the new Table object definition will be associated.
- Specify DDL/Analytics as the schema source type.
- Choose the three-digit group code for table name generation. For example, if you choose AXA, then the format of the table names generated will be:
EX_AXA_00000001
- In the Import External Schema - DDL dialog box, specify the following, then click Next:
- Specify the database where the external table resides. The value specified must correspond to the database used by the Siebel schema, for example, Oracle Server Enterprise Edition.
- Provide the full path for the location of the SQL/DDL file that contains the external table definition.
- Confirm the entries, then click Finish to import the DDL file.
A Table object definition is added to the Siebel Repository, corresponding to the external table.
- Repeat Step 2 through Step 6 for every external table definition you want to import.
Creating the External Table Definition from an ODBC Data Source
You can use the External Table Schema Import Wizard to create the table definition from an Open Database Connectivity (ODBC) data source. To create the external table definition from an ODBC data source
- In Siebel Tools, check out and lock the appropriate project.
- From the File menu, choose New Object to display the New Object Wizards dialog box.
- Click the General tab, then double-click External Table Schema Import.
- In the External Table Schema Import Wizard, specify the following values, then click Next:
- Choose the project with which the new Table object definition will be associated.
- Specify ODBC as the schema source type.
- Choose the three-digit group code for table name generation. For example, if you choose AXA, then the format of the table names generated will be:
EX_AXA_00000001
- In the next dialog box, click Select Data Source.
The Select Data Source dialog box appears.
- Click the Machine Data Source tab, select the appropriate data source name, and then click OK.
- In the Connect to Database Type dialog box, on the Login tab enter the database user ID and password, then click OK.
- Enter the table owner for the data source, then click Next.
- Select the tables to import, then click Next.
- Confirm the entries, then click Finish.
A Table object definition is added to the Siebel Repository for each external table selected.
About Data Type Mappings for Importing Table Definitions
When importing table definitions, certain data type mappings are supported for use with the Siebel application. Table 49 contains the data type mappings you can use when importing table definitions.
Table 49. Supported Data Type Mappings by Product
|
|
MS SQL Server Data Types |
int |
Numeric with scale of 0 |
bigint |
Numeric with scale of 0 |
smallint |
Numeric with scale of 0 |
tinyint |
Numeric with scale of 0 |
float |
Numeric |
real |
Numeric |
decimal |
Numeric |
money |
Numeric |
smallmoney |
Numeric |
bit |
Numeric with a value of 0 or 1 |
char |
Character |
nchar |
Character |
varchar |
Varchar |
nvarchar |
Varchar |
text |
Long |
ntext |
Long |
datetime |
Date Time |
smalldatetime |
Date Time |
DB2 Universal Database Data Types |
UINT |
Numeric with scale of 0 |
BIGUINT |
Numeric with scale of 0 |
SMALLUINT |
Numeric with scale of 0 |
FLOAT |
Numeric |
REAL |
Numeric |
DECIMAL |
Numeric |
NUMERIC |
Numeric |
CHAR |
Character |
VARGRAPHIC |
Varchar |
LONG VARGRAPHIC |
Long |
CLOB |
CLOB |
DATE |
Datetime |
TIME |
Datetime |
TIMESTAMP |
Datetime |
Oracle Data Types |
Number |
Numeric |
TIMESTAMP WITH TIME ZONE |
Numeric |
TIMESTAMP WITH LOCAL TIME ZONE |
Numeric |
Char |
Character |
Nchar |
Character |
varchar2 |
Varchar |
nvarchar2 |
Varchar |
Long |
Long |
CLOB |
CLOB |
date |
Datetime |
Oracle Business Intelligence (BI) Server Data Types |
Integer |
Numeric with scale of 0 |
Smallint |
Numeric with scale of 0 |
Tinyint |
Numeric with scale of 0 |
Float |
Numeric |
Double |
Numeric |
Bit |
Character (1) |
Boolean |
Character (1) |
Char |
Character |
Varchar |
Varchar |
Longvarchar |
Long |
Datetime |
Datetime |
Date |
Datetime |
Time |
Datetime |
Table 50 contains the data types that are not supported for importing table definitions.
Table 50. Unsupported Data Type Mappings by Product
|
|
MS SQL Server |
timestamp |
varbinary |
binary |
image |
cursor |
uniqueidentifier |
DB2 Universal Database |
DBCLOB |
BLOB |
Oracle |
TIMESTAMP |
NCLOB |
BLOB |
BFILE |
ROWID |
UROWID |
RAW |
LONG RAW |
INTERVAL YEAR TO MONTH |
INTERVAL DAY TO SECOND |
Oracle BI Server |
Timestamp |
Varbinary |
Longvarbinary |
Binary |
Object |
Unknown |
About the New Imported Table Definition
After the table definition is imported using the External Table Schema Import Wizard, the external table and the external column names are generated. The external table name is stored in the Table object's Alias property. This external table name contains the following:
- An
EX prefix (for external table)
- A three-digit batch code specified in the External Table Schema Import Wizard
- An automatically generated seven-digit number
An example of the Table name is EX_ABC_0000001 . The external column name is stored in the Column child object's Alias property. An X is added as the prefix and a four-digit number is added as the suffix for the external column name, for example, X_ABC_0000001_0001 . The Table object's Type property is set to External or External View (if a view was imported). This column denotes that the table resides outside of the Siebel database.
|