1. Generate Surrogate Key In Oracle Software
2. Generate Surrogate Key In Oracle Software
3. Surrogate Key In Oracle
4. What Is A Surrogate Key

Apr 20, 2006  How to Generate Sequences and Surrogate Keys in Generic SQL Published Apr 20, 2006. For this reason, database designers sometimes introduce a surrogate key, which uniquely identifies every row in the table and is “more minimal” than the inherently unique aspect of the data. The usual choice is a monotonically increasing integer, which. Jan 07, 2020  And then add primary key constraint. ALTER TABLE autoincrementtb ADD ( CONSTRAINT autoincrementtbpk PRIMARY KEY (id) ); Now we will create a sequence to generate unique auto incremented values. A sequence is a data object that can be used by multiple users to generate auto increment values(no duplicate values will be generated). Create new columns for the target table, at the very least surrogate primary key; something to identify the originating system is very useful too. Populate those when you load the data. You may want to enforce (source system key, source system name ) as a unique key, in which case you will need to add that originating system identifier column. Jun 09, 2005  Good morning Tracy, It's good practice to use a surrogate key in any dimension, and it certainly doesn't hamper linking to any fact table. What you DO need in any case is some way of recognising a distinct record ready to be loaded into a dimension, in your case the concatenated 3 columns you mentioned. Surrogate Key In Database Design. Last updated on February 12, 2013 by topcat. Surrogate keys can be generated in a variety of ways, and most databases offer ways to generate surrogate keys. For example, Oracle uses SEQUENCE, MySQL.

This chapter describes the basic characteristics of the CZ schema, the schema settings and how they are used, and provides some schema maintenance tips.

This chapter covers the following topics:

Overview

This chapter describes the basic characteristics of the CZ schema, the schema settings and how they are used, and provides some schema maintenance tips:

Characteristics of the Oracle CZ Schema

For a description of the CZ schema, see Oracle CZ Schema.

Online Tables and Integration Tables

The CZ schema contains online and integration tables. The online and integration tables are organized into subschemas for storing the data of configuration models and saved configurations.

The online tables contain the data that is used by Oracle Configurator Developer and the runtime Oracle Configurator. Every online table that receives imported data has a corresponding import table. For example, CZ_ITEM_TYPES is populated with data from the CZ_IMP_ITEM_TYPE table during the import process. See CZ Subschemas for more information about the CZ subschemas.

The integration tables consist of import and control tables. See Import Tables for information about the import tables and Control Tables for information about control tables. See Populating the CZ Schema for information about using the integration tables.

CZ Subschemas

Both the online and integration tables of the CZ schema are organized into subschemas:

• ADMN - Administrative

• CNFG - Saved Configurations

• ITEM - Item Master

• LCE - Logic for Configuration (Generate Logic)

• PB - Publication

• PROJ - Project Structure

• RP - Repository

• RULE - Rule

• TXT - Text

• TYP - Data Typing

• UI - User Interface

• XFR - Transfer specifications and control

Additionally, there are some key table views:

• CZ_CONFIG_DETAILS_V stores selected BOM Model node records.

• CZ_CONFIG_ITEMS_V stores all selected node records for both BOM Models and Oracle Developer Models

See CZ Subschemas for a listing of tables in each subschema. For table details, see the CZ eTRM on MetaLink, Oracle’s technical support Web site.

Public Synonyms

The CZ schema does not use public synonyms.

Schema Customization

Customizing the data model of the CZ schema is not recommended, because such customizations may not be preserved during an upgrade or migration.

Various user expansion fields in the CZ schema, such as USERNUMn and USERSTRn in the CZ_PS_NODES table, are available for custom use. The data in the user expansion fields is preserved during a schema upgrade or migration. For more information, see the CZ eTRM on MetaLink, Oracle’s technical support Web site.

Import Tables

Every import table corresponds to an online table both structurally and relationally. Each import table contains the same fields as the corresponding online table, as well as additional fields to manage the import and correlate the data with the existing data in the online table.

Import tables consist of:

Because import tables are meant to capture as much data as possible, all fields are nullable and there are no integrity constraints such as primary-key definitions, unique indexes, or foreign-key references. The import tables allow batch population of the CZ schema’s online tables.

Each import table’s name is similar to its online counterpart. Import tables have CZ_IMP prefix instead of just CZ_. For example, the imported data in CZ_IMP_PROPERTY populates CZ_PROPERTIES, and CZ_IMP_ITEM_TYPE populates CZ_ITEM_TYPES.

The import tables temporarily store extracted or legacy data that concurrent programs access when creating, updating, or deleting records in the CZ schema. The CZ_IMP tables are populated by running the Populate or Refresh Configuration Models concurrent programs. For more information see Populate and Refresh Configuration Models Concurrent Programs.

For more information about:

• How data moves from sources outside the CZ schema through the import tables to the online tables, see Populating the CZ Schema

• Dependencies among import tables and import table codes, see Dependencies Among Import Tables.

Import Control Fields

Import control fields contain data that is used to manage the import process for each record. Import control data is not transferred to the online tables and is not used to resolve key values or anything else. Import Control Fields describes the import control fields.

The following table shows the Import Control Fields including field name, data type, and description.

Online Data Fields

The import tables’ data fields exactly match the fields in the corresponding online table and are used to hold the data to be put into the online table.

Surrogate Key Fields

Surrogate key fields in the import tables hold the customer-provided extrinsic identifications for data to be imported. These include both foreign surrogate keys and surrogate primary keys.

Foreign Surrogate Key – A foreign surrogate key is a reference to a different table made through that table’s surrogate primary key rather than through the online table’s integer key value. A foreign surrogate key consists of one or more fields that resolve references from one import table to another. These keys are named FSK_table_refno_fldnum, where table is the name of the referenced table, refno is the number of the table-to-table reference, and fldnum is the position of the referenced surrogate-key field in the referenced import table. Note that refno is required to keep unique names for tables with multiple references to the same table, and generally, the fldnum is 1.

Surrogate Primary Key – As a rule, imported tables contain a single field named ORIG_SYS_REF, which is used to hold the external value that uniquely identifies each record. In some cases, however, the online CZ table has a primary key consisting entirely of references to other tables. In this case, the surrogate primary key actually consists of the foreign surrogate keys that correspond to the native foreign keys in the online table.

Dependencies Among Import Tables

Dependencies among import tables must be heeded especially when custom importing single tables. Dependencies Among CZ Schema Import Tables, 'Foreign Surrogate Key' lists the column in the import table whose value is dependent on the table listed in 'Depends on'. For example, the FSK_ITEMTYPE_1_1 column in CZ_IMP_ITEM_MASTER gets its value from CZ_IMP_ITEM_TYPE.NAME and helps in key resolution. FSK_ITEMTYPE_1_1 (default) is populated depending on the PK_USEEXPANSION indicator (0, 1, or 2) in CZ_XFR_TABLES. See Populating Import Tables for the order in which the CZ_IMP tables are populated.

Note: Oracle recommends that limited usage of FSK_***_EXT columns as these columns will eventually be desupported.

A strong dependency means a value is required to successfully import that record. If Default is YES, then there is a default value in that column and import succeeds even if the dependency is strong and no value is imported. The following Dependencies Among CZ Schema Import Tables lists the dependencies.

The following table shows the dependencies between the import tables.

Control Tables

The control tables provide the mechanism for controlling what data is imported or refreshed when populating the CZ schema import tables with data from outside sources. The control table names are prefixed with CZ_XFR.

When running Oracle Configurator Populate and Refresh Configuration Models Concurrent Programs, records in the CZ_XFR tables determine which import tables are enabled for import, what data is imported, and how the data is imported.

The following tables control the import process at the table and field level:

• CZ_XFR_FIELDS

• CZ_XFR_PROJECT_BILLS

• CZ_XFR_TABLES

The following tables contain import information:

• CZ_XFR_RUN_INFOS

• CZ_XFR_RUN_RESULTS

• CZ_XFR_STATUS_CODES

CZ_XFR_TABLES identifies the mapping of the import table to the online table, as well as the rules for importing data into the CZ schema.

CZ_XFR_FIELDS identifies the transfer rules for the fields that are transferred during the Populate or Refresh Configuration Models concurrent programs. Every field is updated during import or refresh, but the update can be retracted by using the NOUPDATE flag in the CZ_XFR_FIELDS table. If a field that is transferred does not have an entry in the CZ_XFR_FIELDS table, then that field is updated.

For example, setting the NOUPDATE flag to 1 in the CZ_XFR_FIELDS table for CZ_ITEM_MASTERS.DESC_TEXT, inhibits the updating of the Item Master description in CZ_ITEM_MASTERS.DESC_TEXT when a Model is refreshed. Setting a value in the CZ_XFR_FIELDS Table shows how to set the field in the CZ_XFR_FIELDS table so that changes made to the BOM Model’s Item description do not appear in Oracle Configurator Developer.

Setting a value in the CZ_XFR_FIELDS Table

CZ_DB_SETTINGS Table

The CZ_DB_SETTINGS table provides parameters that affect certain applications and CZ schema processes.

Only one CZ_DB_SETTINGS table exists in a CZ schema.

Accessing the CZ_DB_SETTINGS Table

A user’s responsibility determines whether they can view or edit the CZ_DB_SETTINGS table. A user must have the Oracle Configurator Administrator responsibility to edit the CZ_DB_SETTINGS table through concurrent programs. For more information, see View Configurator Parameters and Modify Configurator Parameters.

Organization of the CZ_DB_SETTINGS Table

The parameters in the CZ_DB_SETTINGS table are mapped to a particular section of the CZ schema. The particular section is identified in the SECTION_NAME field and contains relevant database parameters. The sections are:

• IMPORT - Controls how BOM Model data is imported into the CZ schema

• LogicGen - Governs how the Model’s logic is generated

• ORAAPPS_INTEGRATE - Controls how Oracle Configurator integrates with other Oracle Applications

• SCHEMA - Sets general parameters that control the CZ schema

• UISERVER - Governs the behavior of the runtime Oracle Configurator user interface

Each parameter contains the following fields:

• DATA_TYPE specifies the parameter’s datatype. All CZ_DB_SETTINGS values are stored as VARCHAR2(255) in the VALUE field. If the DATA_TYPE is an integer, then the Configurator converts the data in the VALUE field to an integer before using it. For example, the Batchsize default value is stored as string 10000, but Configurator interprets string 10000 as integer 10000.

• SETTING_ID identifies the parameter.

• VALUE is the parameter’s data. This value may be set during an installation or upgrade of the database instance. The Oracle Configurator Administrator can modify a value by running the Modify Configurator Parameters concurrent program.

CZ_DB_SETTINGS Parameters

Some of the CZ_DB_SETTINGS parameter values are predefined during an installation or upgrade of Oracle Configurator. The Oracle Configurator Administrator can modify the values of these parameters by running the Modify Configurator Parameters concurrent program. For information on running concurrent programs, see Running Configurator Concurrent Programs. For specific information on modifying the parameters in the CZ_DB_SETTINGS table, see Settings in CZ_DB_SETTINGS Table that lists the parameters in the CZ_DB_SETTINGS table that can be modified.

AltBatchValidateURL

AltBatchValidateURL allows the batch validation process to bypass the URL that is normally used for batch validation. This might be necessary if your database cannot communicate with your Web server.

If Oracle Configurator uses Secure Sockets Layer (SSL), then you can enable batch validation by creating an additional non-SSL-enabled (HTTP) servlet port and specifying its URL as the value of AltBatchValidateURL. For additional SSL information, see MetaLink, Oracle’s technical support Web site.

If your configurator servlet is set up to use HTTPS, then you can set AltBatchValidateURL to be a servlet using HTTP, and avoid some SSL encryption and handshaking overhead. Since the communication is between the Application database and the Application middle tier, communication does not cross the internet and thus HTTPS may not be necessary. This configuration does require extra setup and is not required.

You can also enable batch validation by using your existing SSL-enabled port for Oracle Configurator and setting up the Oracle Wallet for use by Oracle Configurator, as described on OracleMetaLink. In this case, you do not set any value for AltBatchValidateURL.

If you use a firewall, have your database setup in a DMZ, or have some other network configuration where the database cannot communicate with the web server, then you should set up an internal web server. After setting up an internal web server, you must then set the AltBatchValidateURL setting in the CZ_DB_SETTINGS table to be the URL for the configurator servlet on your internal web server.

For more information regarding DMZ setup, see MetaLink, Oracle’s technical support Web site.

To insert the AltBatchValidateURL into the CZ_DB_SETTINGS table, use the SQL INSERT statement shown in Adding AltBatchValidateURL to CZ_DB_SETTINGS.

Adding AltBatchValidateURL to CZ_DB_SETTINGS

BadItemPropertyValue

BadItemPropertyValue indicates the action that is taken when an Item’s PROPERTY _VALUE in the CZ_IMP_ITEM_PROPERTY_VALUES table does not match the DATA_TYPE in the CZ_PROPERTIES online table. The default value (F) forces the record to be updated to include the PROPERTY_VALUE so that it is imported into the CZ_ITEM_PROPERTY_VALUES online table. Valid Values for the BadItemPropertyValue Setting lists the valid values for BadItemPropertyValue setting and the disposition:

The following table lists the valid values for the BadItemProperty value setting, along with a description of the value.

BatchSize

BatchSize indicates the number of records that are modified before committing a transaction in batch operations. The BatchSize setting is also used during a purge operation.

Ordinarily a database stored procedure runs as a single transaction that is considered pending until the calling operation commits the transaction. The pending changes are lined up in a rollback segment. If the calling operation is cancelled, then the transaction is rolled back. If the calling operation encounters an error, then the pending changes in the rollback segment are discarded. However, some batch operations, such as import, can involve many more records than the database can handle as a single transaction. If the transaction is too big, then the database fails an operation with a rollback-segment error. To avoid a rollback_segment error, import and other batch-like operations count up the modified records in the database and when the count matches the BatchSize value, the operation commits the transaction and resets the counter. Every record is not committed individually because it is considerably more economical to commit many updates at once.

BOM_REVISION

BOM_REVISION indicates the BOM revision in the Oracle Applications database from which data is being imported into the CZ schema. This setting is checked to ensure that the correct date format is used in the call to the BOM Model explosion procedure.

The value of BOM_REVISION is the Oracle Applications revision number used to determine which explosion date format to use. . Valid values are 5.0.628 for Release 10.7, 11.0.28 for Release 11.0, and 11.5.0 for both Release 11i and Release 12. If the value is null (default), then 11.5.0 is used. The call to the BOM Model explosion procedure checks up to the second decimal point of this value.

If the value is 11.5.n or Release 12, then the date format YYYY-MM-DD is used. Otherwise, DD/MON/RR is used for Release 10.7 or 11.0.

CommitSize

CommitSize indicates the number of import records in each database transaction between commits. CommitSize has the same purpose as BatchSize. for more information, see BatchSize. CommitSize is used during import.

DISPLAY_INSTANCE_NAME

DISPLAY_INSTANCE_NAME determines whether an Instance Name column appears in the Oracle Configurator Summary page. Oracle Configurator checks this setting only if multiple instances of one or more components exist in the configuration.

If DISPLAY_INSTANCE_NAME is set to TRUE and at least one component in the configuration has multiple instances, then the Instance Name column appears and displays the name of each instance.

If DISPLAY_INSTANCE_NAME is set to FALSE or there are no components with multiple instances in the configuration, then the Instance Name column does not appear. If set to False but there are multiple instances in the configuration, then instance names appear in the Description column (instead of each Item’s description).

FREEZE_REVISION

FREEZE_REVISION indicates the revision number at the freeze stage. This parameter is used to capture the revision levels for the implementation of database package bodies and views. For example, if a table is tuned to improve performance, but the fields and the data returned are the same, then there is no need to change the MAJOR_VERSION or MINOR_VERSION but the FREEZE_REVISION value reflects the reworked view. This setting is read-only and populated when applying a patch.

GenerateGatedCombo

GenerateGatedCombo determines how a FALSE logic state is propagated in Explicit Compatibility, Property-based Compatibility and Design Chart Rules. See the Oracle Configurator Developer User’s Guide for additional information about Gated Combinations.

GenerateUpdatedOnly

GenerateUpdatedOnly set to YES, causes logic generation to skip all referenced Models whose logic is up-to-date. GenerateUpdatedOnly set to NO causes the logic of all referenced Models to be generated even if their logic is up-to-date.

GenStatisticsBOM

GenStatisticsBOM set to YES forces the optimizer to update the internal statistics on the BOM_EXPLOSIONS table before running queries in the CZ schema. Generating statistics allows the optimizer to choose a better execution plan based on the current data structure in a table.

GenStatisticsCZ

GenStatisticsCZ set to YES forces the optimizer to update the internal statistics on the entire CZ schema before running queries in the CZ schema. Generating statistics allows the optimizer to choose a better execution plan based on the current data structure in a table.

MAJOR_VERSION

MAJOR_VERSION indicates the major version label for the CZ schema. This setting is read-only and is populated when upgrading the schema.

MaximumErrors

MaximumErrors indicates the limit of errors allowed before an import run is terminated. If you have a large amount of data to import, or you are not concerned with the process stopping once a certain number of errors is reached, then set this parameter to an extremely large number.

MemoryBulkSize

MemoryBulkSize regulates the memory usage of import. The smaller the setting, the less memory is required for import. This number is used during import for the cz_ps_nodes extraction procedure for specifying the number of records that are processed in the same pass. If the value entered is less than the total number of records to be imported, then the specified number of records is loaded and processed, and then the next group of records is loaded and processed. If there is no value entered, then the MemoryBulkSize is set to 10000000.

MINOR_VERSION

MINOR_VERSION indicates the minor version label for the CZ schema. This value is read-only and is populated when applying a patch. The MINOR_VERSION does not change during a particular family pack release.

MULTISESSION

MULTISESSION indicates the way in which a new import session interacts with other import sessions.

• A positive value indicates the number of seconds to wait while another import session is running. The current state is checked every second. After the number of seconds has elapsed, control goes to the waiting import session if no other session is active, or an exception is raised if another import session is still running.

• A value of 0 means do not wait if another import session is running, and immediately raise an exception if a session is already running.

• A negative value means ignore other import sessions and run this import session immediately without raising an exception. Setting this parameter to a negative number is equivalent to disabling it. If a session is currently running and a new import session begins, then the first session is not aborted and there is the risk of data corruption.

When MULTISESSION is missing from the CZ_DB_SETTINGS table, it is equivalent to the default 0.

If an import session is terminated, then the CZ_XFR_RUN_INFOS table may end up in an inconsistent state with the value of COMPLETED something other than 1. Smartmovie 4.15 license key generator download.

OracleSequenceIncr

OracleSequenceIncr indicates the number of primary-key values allocated by each use of a sequence. The default setting means that keys are assigned in increments of 20. Both runtime Oracle Configurator and Configurator Developer ask for a sequence value once, and then manage the sequence value minus 1 in memory. When the block is used up, runtime Oracle Configurator and Configurator Developer again call for a sequence value. Keeping the default value at 20 saves round trips to the database.

Warning: Changing the default OracleSequenceIncr setting of 20 is likely to have adverse effects. The value of OracleSequenceIncr should not be modified.

PsNodeName

PsNodeName indicates the source field to be loaded into the NAME field in the CZ_PS_NODES table. The source field is either the RefPartNbr or the DESCRIPTION field in the MTL_SYSTEM_ITEMS table. RefPartNbr is the default so that the name loaded into the Model structure in Oracle Configurator Developer matches the name in CZ_ITEM_MASTERS.

PublicationLocalBOMSynch

PublicationLocalBOMSynch controls whether the BOM Synchronization process runs automatically when a publication is created for a Model that was imported from a remote server, on the same instance in which Configurator Developer is running. If BOM Synchronization does not run in this scenario, it is possible for the publication lookup process to fail if the host application is also running on the local instance that launched Oracle Configurator.

To automatically run BOM Synchronization in the scenario described above, set the CZ_DB_SETTINGS parameter PublicationLocalBOMSynch to YES by running the Modify Configurator Parameters concurrent program. Enter the following parameters when submitting this program:

• Section Name: Publication

• Setting ID: PublicationLocalBOMSynch

• Value: YES

• Type: 4

PublicationLogging

PublicationLogging indicates whether a trace of the publication process is logged in the CZ_DB_LOGS table. The trace is helpful for debugging purposes and can be viewed in the log file. For more information about viewing log files, see Viewing Log Files.

PublishingCopyRules

PublishingCopyRules indicates whether or not configuration rules are copied during publishing. If PublishingCopyRules is set to NO, then only Configurator Extension rules are copied during publishing. The publishing process is faster when PublishingCopyRules is set to NO.

Oct 06, 2018  First thing that you need to do on your macOS machine is to create a directory that will store your SSH keys. Then you will generate a public and private key for your account, launch the Terminal and punch in some commands: Create a.ssh Directory. Change to the home directory. Cd / Create a SSH directory name.ssh and move into it. Mkdir.ssh; cd.ssh. Sep 26, 2019  To generate SSH keys in macOS, follow these steps: Enter the following command in the Terminal window. Press the ENTER key to accept the default location. Type in a passphrase. You can also hit the ENTER key to accept the default (no passphrase).

If the PublishingCopyRules is set to YES, then all rules are copied and both the source and published Models have the same rules.

Note: Setting 'PublishingCopyRules' to 'NO' only affects you if changes are made to logic generation that are incompatible with previous versions of Oracle Configurator. If the rules for a published Model are not copied, then you cannot generate logic for the published Models. Using the NO setting requires republishing all published Models.

PurgeDeleteConfigBatchsize

When you run the concurrent program Purge Configurator Tables, you can control its commit behavior by setting this parameter, which specifies how often the purge program issues a commit, in terms of a number of configurations. For example, a value of 200 specifies a commit after deleting a batch of 200 configurations.

RefPartNbr

RefPartNbr identifies the source fields that are loaded from the MTL_SYSTEM_ITEMS table into CZ_ITEM_MASTERS.REF_PART_NBR. This is a segment from the System Item key flexfield definition.

RefPartNbr determines what name is displayed for each imported Model structure node. The default value ’CONCATENATED_SEGMENTS’ enables the BOM Model import process to construct BOM Model node names using multi-segment part numbers.

When RefPartNbr is set to ’SEGMENT1’, only MTL_SYSTEM_ITEMS.SEGMENT1 is the source of the node names in the imported Model structure. If you want to use only the first segment of a part number as the node name, the Oracle Configurator Administrator must manually set RefPartNbr to ’SEGMENT1’ by running the Modify Configurator Parameters concurrent program.

Any value for RefPartNbr other than ’CONCATENATED_SEGMENTS’ or ’SEGMENT1’ causes the import process to retrieve the value of the DESCRIPTION column from MTL_SYSTEM_ITEMS and displays the Item description as the node name in Configurator Developer.

Warning: Examine MTL_SYSTEM_ITEMS_VL. CONCATENATED_SEGMENTS to verify that the field is correctly populated. If the field is incorrectly populated, then the entry in Oracle Inventory may be wrong. If the entry is correct, check CZ_IMP_ITEM_MASTER.REF_PART_NBR to see that the value is the same as that in MTL_SYSTEM_ITEMS_VL. CONCATENATED_SEGMENTS.

Concatenated segments, including separators, must not exceed 1000 characters, which is the limit of the CZ_PS_NODES.NAME field. Any description longer than 1000 characters is truncated. The default separator is a dot (.). Other valid separators are , -, or a custom value. See the Oracle Inventory User’s Guide for more information about setting up part numbers.

You can enter multi-segment Items in the From Item and To Item input fields when you run either the Populate or Refresh Configuration Models concurrent program. You must include any separators that exist in the Item’s part number when you enter multi-segment Item names.

Warning: When updating an existing Model in Configurator Developer to use multi- segment part numbers, you must either reimport or refresh the BOM Model. Confirm that the BOM Model is getting re-exploded during import. The CZ_DB_SETTINGS.RUN_BILL_EXPLODER should be Yes.

ResolvePropertyDataType

ResolvePropertyDataType controls whether Item Catalog Descriptive Elements are imported into Configurator Developer as Item Properties with a data type of Text or Decimal Number. If the value for this setting is NO, all imported Item Properties have a data type of Text in Configurator Developer.

If the value of this setting is YES, then all Descriptive Elements whose value is a number are imported as Item Properties and have a data type of Decimal Number. All Descriptive Elements whose value is text (for example, Weight) have a data type of Text.

If ResolvePropertyDataType is null, then all Descriptive Elements are imported into Configurator Developer as Item Properties with a data type of Text.

ResolvePropertyDataType Setting table illustrates how ResolvePropertyDataType affects how Descriptive Elements values are imported into Oracle Configurator Developer.

Item Property is a protected field in the CZ schema (the NOUPDATE flag is set during import). Once you import a BOM Model, you cannot change an Item Property’s data type simply by modifying the ResolvePropertyDataType setting and then refreshing the BOM Model.

RestoredConfigDefaultModelLookupDate

RestoredConfigDefaultModelLookupDate setting controls which publication Oracle Configurator uses on an order when called from Order Management. If this setting is config_creation_date, then Oracle Configurator uses the order line creation date. If this setting is null, then Oracle Configurator uses sysdate.

For more information, see DEFAULT_RESTORED_CFG_DATES.

Revision Date and User

Revision Date and User is read-only and documents the date and time at which the CZ schema was last upgraded, and the username of the user who performed the task.

RUN_BILL_EXPLODER

RUN_BILL_EXPLODER is a YES/NO flag (default=YES) that indicates whether the Oracle Applications Bills of Material exploder should be run on each bill that is marked for import in the CZ_XFR_PROJECT_BILLS table in the CZ schema at the time of import. See Populating the CZ Schema for more information on exploding a BOM Model.

The Oracle Configurator Populate or Refresh Configuration Models concurrent programs load bills and Items based on top bills listed in the CZ_XFR_PROJECT_BILLS table in the CZ schema. Before extracting, if the RUN_BILL_EXPLODER setting is set to YES, then the procedure calls the BOM Model exploder to refresh data in BOM_EXPLOSIONS for each record in the CZ_XFR_PROJECT_BILLS table. If RUN_BILL_EXPLODER is set to NO, then the concurrent program transfers the BOM Models that are flagged for import in the CZ_XFR_PROJECT_BILLS table without running the BOM Model exploder first.

Note: The Populate or Refresh Configuration Models concurrent programs do not explode BOM Models when importing from a remote server. See Exploding BOM Models in Oracle Applications for details.

CZ_INTL_TEXTS contains the text string from the DESCRIPTION field in the BOM_EXPLOSIONS table for each imported BOM Model structure node.

The Oracle Configurator SQL*Plus scripts and concurrent programs target all or a subset of BOM Models exploded in the BOM_EXPLOSIONS table in the Oracle Applications database. Selected BOM Model Items come from the BOM_BILL_OF_MATERIAL and the BOM_INVENTORY_COMPONENTS tables.

Note: Importing a BOM Model from a remote instance may fail if RUN_BILL_EXPLODER is set to YES in the local instance. (In this case, an error message similar to the following appears: 'ORA-03113: end-of-file on communication channel.') If this occurs, set RUN_BILL_EXPLODER flag to No and then re-submit the import concurrent program. The new setting should enable the process to complete successfully.

Generate Surrogate Key In Oracle Software

SuppressSuccessMessage

The SuppressSuccessMessage setting affects runtime Oracle Configurator behavior by suppressing messages that would normally be shown. The setting determines whether a message is displayed after fixing a validation error.

If SuppressSuccessMessage is set to NO, then after fixing a validation error a runtime success message is displayed. If SuppressSuccessMessage is set to YES, then after fixing a validation error a runtime success message is not displayed.

To insert SuppressSucessMessage into CZ_DB_SETTINGS, use the SQL*Plus INSERT statement shown in Adding SuppressSuccessMessage to CZ_DB_SETTINGS.

Adding SuppressSuccessMessage to CZ_DB_SETTINGS

TimeImport

TimeImport enables the collection of timing information during import.

UI_NODE_NAME_CONCAT_CHARS

UI_NODE_NAME_CONCAT_CHARS sets the concatenation character that is used when generating UI captions using both the node name and description. The default concatenation character separating each text string is a comma surrounded by two spaces. (For example: 'AT62431 , Sentinal Custom Laptop'). The Oracle Configurator Administrator can change the concatenation character that separates each string by running the Modify Configurator Parameters concurrent program.

UseLocalTableInExtractionViews

UseLocalTableInExtractionViews is a YES/NO flag. If UseLocalTableInExtractionViews is set to YES, then definitions of some import extraction views include the DUAL table in the join. The UseLocalTableInExtractionViews setting is ignored if the import source server is local.

Note: If you are importing or refreshing from a remote database instance and the database instance is version 8i, then UseLocalTableInExtractionViews must be set to YES. This is because of an RDBMS bug. If this setting is not YES, then the following error appears in the cz_db_logs table after running the Populate and Refresh Configuration Models Concurrent Programs :'ORA-01025: UPI parameter out of range'

UtlHttpTransferTimeout

UtlHttpTransferTimeout allows modification of the timeout length that is used inside the call to the UTL_HTTP.REQUEST procedure during batch validation. The value is the number of seconds. Once the call completes, the timeout is set back to its original value.

To insert UtlHttpTransferTimeout into the CZ_DB_SETTINGS, use the SQL*Plus INSERT statement shown in Adding UtlHttpTransferTimeout to CZ_DB_SETTINGS.

Adding UtlHttpTransferTimeout to CZ_DB_SETTINGS

Note: This functionality is available only in Oracle 9i and later.

A surrogate key (or synthetic key, entity identifier, system-generated key, database sequence number, factless key, technical key, or arbitrary unique identifier^{[citation needed]}) in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data, unlike a natural (or business) key which is derived from application data.^[1]

Definition[edit]

There are at least two definitions of a surrogate:

Surrogate (1) – Hall, Owlett and Todd (1976)

A surrogate represents an entity in the outside world. The surrogate is internally generated by the system but is nevertheless visible to the user or application.^[2]

Surrogate (2) – Wieringa and De Jonge (1991)

A surrogate represents an object in the database itself. The surrogate is internally generated by the system and is invisible to the user or application.

The Surrogate (1) definition relates to a data model rather than a storage model and is used throughout this article. See Date (1998).

An important distinction between a surrogate and a primary key depends on whether the database is a current database or a temporal database. Since a current database stores only currently valid data, there is a one-to-one correspondence between a surrogate in the modeled world and the primary key of the database. In this case the surrogate may be used as a primary key, resulting in the term surrogate key. In a temporal database, however, there is a many-to-one relationship between primary keys and the surrogate. Since there may be several objects in the database corresponding to a single surrogate, we cannot use the surrogate as a primary key; another attribute is required, in addition to the surrogate, to uniquely identify each object.

Although Hall et al. (1976) say nothing about this, others^[specify] have argued that a surrogate should have the following characteristics:

• the value is unique system-wide, hence never reused
• the value is system generated
• the value is not manipulable by the user or application
• the value contains no semantic meaning
• the value is not visible to the user or application
• the value is not composed of several values from different domains.

Surrogates in practice[edit]

In a current database, the surrogate key can be the primary key, generated by the database management system and not derived from any application data in the database. The only significance of the surrogate key is to act as the primary key. It is also possible that the surrogate key exists in addition to the database-generated UUID (for example, an HR number for each employee other than the UUID of each employee).

A surrogate key is frequently a sequential number (e.g. a Sybase or SQL Server 'identity column', a PostgreSQL or Informixserial, an Oracle or SQL ServerSEQUENCE or a column defined with AUTO_INCREMENT in MySQL). Some databases provide UUID/GUID as a possible data type for surrogate keys (e.g. PostgreSQL UUID or SQL Server UNIQUEIDENTIFIER).

Having the key independent of all other columns insulates the database relationships from changes in data values or database design (making the database more agile) and guarantees uniqueness.

In a temporal database, it is necessary to distinguish between the surrogate key and the business key. Every row would have both a business key and a surrogate key. The surrogate key identifies one unique row in the database, the business key identifies one unique entity of the modeled world. One table row represents a slice of time holding all the entity's attributes for a defined timespan. Those slices depict the whole lifespan of one business entity. For example, a table EmployeeContracts may hold temporal information to keep track of contracted working hours. The business key for one contract will be identical (non-unique) in both rows however the surrogate key for each row is unique.

Some database designers use surrogate keys systematically regardless of the suitability of other candidate keys, while others will use a key already present in the data, if there is one.

Some of the alternate names ('system-generated key') describe the way of generating new surrogate values rather than the nature of the surrogate concept.

Approaches to generating surrogates include:

• Universally Unique Identifiers (UUIDs)
• Globally Unique Identifiers (GUIDs)
• Object Identifiers (OIDs)
• Sybase or SQL Server identity column IDENTITY OR IDENTITY(n,n)
• OracleSEQUENCE, or GENERATED AS IDENTITY (starting from version 12.1)^[3]
• SQL ServerSEQUENCE (starting from SQL Server 2012)^[4]
• PostgreSQL or IBM Informix serial
• MySQLAUTO_INCREMENT
• SQLiteAUTOINCREMENT
• AutoNumber data type in Microsoft Access
• AS IDENTITY GENERATED BY DEFAULT in IBM DB2
• Identity column (implemented in DDL) in Teradata
• Table Sequence when the sequence is calculated by a procedure and a sequence table with fields: id, sequenceName, sequenceValue and incrementValue

Advantages[edit]

Immutability[edit]

Surrogate keys do not change while the row exists. This has the following advantages:

• Applications cannot lose their reference to a row in the database (since the identifier never changes).
• The primary or natural key data can always be modified, even with databases that do not support cascading updates across related foreign keys.

Requirement changes[edit]

Attributes that uniquely identify an entity might change, which might invalidate the suitability of natural keys. Consider the following example:

An employee's network user name is chosen as a natural key. Upon merging with another company, new employees must be inserted. Some of the new network user names create conflicts because their user names were generated independently (when the companies were separate).

In these cases, generally a new attribute must be added to the natural key (for example, an original_company column).With a surrogate key, only the table that defines the surrogate key must be changed. With natural keys, all tables (and possibly other, related software) that use the natural key will have to change.

Some problem domains do not clearly identify a suitable natural key. Surrogate keys avoid choosing a natural key that might be incorrect.

Performance[edit]

Surrogate keys tend to be a compact data type, such as a four-byte integer. This allows the database to query the single key column faster than it could multiple columns. Furthermore, a non-redundant distribution of keys causes the resulting b-tree index to be completely balanced. Surrogate keys are also less expensive to join (fewer columns to compare) than compound keys.

Compatibility[edit]

While using several database application development systems, drivers, and object-relational mapping systems, such as Ruby on Rails or Hibernate, it is much easier to use an integer or GUID surrogate keys for every table instead of natural keys in order to support database-system-agnostic operations and object-to-row mapping.

Uniformity[edit]

When every table has a uniform surrogate key, some tasks can be easily automated by writing the code in a table-independent way.

Validation[edit]

It is possible to design key-values that follow a well-known pattern or structure which can be automatically verified. For instance, the keys that are intended to be used in some column of some table might be designed to 'look differently from' those that are intended to be used in another column or table, thereby simplifying the detection of application errors in which the keys have been misplaced. However, this characteristic of the surrogate keys should never be used to drive any of the logic of the applications themselves, as this would violate the principles of Database normalization.

Disadvantages[edit]

Disassociation[edit]

The values of generated surrogate keys have no relationship to the real-world meaning of the data held in a row. When inspecting a row holding a foreign key reference to another table using a surrogate key, the meaning of the surrogate key's row cannot be discerned from the key itself. Every foreign key must be joined to see the related data item. If appropriate database constraints have not been set, or data imported from a legacy system where referential integrity was not employed, it is possible to have a foreign-key value that does not correspond to a primary-key value and is therefore invalid. (In this regard, C.J. Date regards the meaninglessness of surrogate keys as an advantage. ^[5])

To discover such errors, one must perform a query that uses a left outer join between the table with the foreign key and the table with the primary key, showing both key fields in addition to any fields required to distinguish the record; all invalid foreign-key values will have the primary-key column as NULL. The need to perform such a check is so common that Microsoft Access actually provides a 'Find Unmatched Query' wizard that generates the appropriate SQL after walking the user through a dialog. (It is, however, not too difficult to compose such queries manually.) 'Find Unmatched' queries are typically employed as part of a data cleansing process when inheriting legacy data.

Surrogate keys are unnatural for data that is exported and shared. A particular difficulty is that tables from two otherwise identical schemas (for example, a test schema and a development schema) can hold records that are equivalent in a business sense, but have different keys. This can be mitigated by NOT exporting surrogate keys, except as transient data (most obviously, in executing applications that have a 'live' connection to the database).

When surrogate keys supplant natural keys, then domain specific referential integrity will be compromised. For example, in a customer master table, the same customer may have multiple records under separate customer IDs, even though the natural key (a combination of customer name, date of birth, and E-mail address) would be unique. To prevent compromise, the natural key of the table must NOT be supplanted: it must be preserved as a unique constraint, which is implemented as a unique index on the combination of natural-key fields.

Query optimization[edit]

Relational databases assume a unique index is applied to a table's primary key. The unique index serves two purposes: (i) to enforce entity integrity, since primary key data must be unique across rows and (ii) to quickly search for rows when queried. Since surrogate keys replace a table's identifying attributes—the natural key—and since the identifying attributes are likely to be those queried, then the query optimizer is forced to perform a full table scan when fulfilling likely queries. The remedy to the full table scan is to apply indexes on the identifying attributes, or sets of them. Where such sets are themselves a candidate key, the index can be a unique index.

These additional indexes, however, will take up disk space and slow down inserts and deletes.

Normalization[edit]

Surrogate keys can result in duplicate values in any natural keys. To prevent duplication, one must preserve the role of the natural keys as unique constraints when defining the table using either SQL's CREATE TABLE statement or ALTER TABLE ..ADD CONSTRAINT statement, if the constraints are added as an afterthought.

Business process modeling[edit]

Because surrogate keys are unnatural, flaws can appear when modeling the business requirements. Business requirements, relying on the natural key, then need to be translated to the surrogate key. A strategy is to draw a clear distinction between the logical model (in which surrogate keys do not appear) and the physical implementation of that model, to ensure that the logical model is correct and reasonably well normalised, and to ensure that the physical model is a correct implementation of the logical model.

Inadvertent disclosure[edit]

Proprietary information can be leaked if sequential key generators are used. By subtracting a previously generated sequential key from a recently generated sequential key, one could learn the number of rows inserted during that time period. This could expose, for example, the number of transactions or new accounts per period. There are a few ways to overcome this problem:

Generate Surrogate Key In Oracle Software

• Increase the sequential number by a random amount.
• Generate a random key such as a UUID

Inadvertent assumptions[edit]

Sequentially generated surrogate keys can imply that events with a higher key value occurred after events with a lower value. This is not necessarily true, because such values do not guarantee time sequence as it is possible for inserts to fail and leave gaps which may be filled at a later time. If chronology is important then date and time must be separately recorded.

See also[edit]

References[edit]

Citations[edit]

Surrogate Key In Oracle

1. ^'What is a Surrogate Key? - Definition from Techopedia'. Techopedia.com. Retrieved 2020-02-21.
2. ^P A V Hall, J Owlett, S J P Todd, 'Relations and Entities', Modelling in Data Base Management Systems (ed GM Nijssen),North Holland 1976.
3. ^http://docs.oracle.com/database/121/SQLRF/statements_7002.htm#SQLRF01402
4. ^https://msdn.microsoft.com/en-us/library/ff878091.aspx
5. ^ C.J. Date. The primacy of primary keys. From 'Relational Database Writings, 1991-1994. Addison-Wesley, Reading, MA.

Sources[edit]

• This article is based on material taken from the Free On-line Dictionary of Computing prior to 1 November 2008 and incorporated under the 'relicensing' terms of the GFDL, version 1.3 or later.

What Is A Surrogate Key

• Nijssen, G.M. (1976). Modelling in Data Base Management Systems. North-Holland Pub. Co. ISBN0-7204-0459-2.
• Engles, R.W.: (1972), A Tutorial on. CiteSeerX10.1.1.16.3195.Cite journal requires journal= (help)
• Date, C. J. (1998). 'Chapters 11 and 12'. Relational Database Writings 1994–1997. ISBN0201398141.
• Carter, Breck. 'Intelligent Versus Surrogate Keys'. Retrieved 2006-12-03.
• Richardson, Lee. 'Create Data Disaster: Avoid Unique Indexes – (Mistake 3 of 10)'. Archived from the original on 2008-01-30. Retrieved 2008-01-19.
• Berkus, Josh. 'Database Soup: Primary Keyvil, Part I'. Retrieved 2006-12-03.