What Oracle Object Is Used To Manage Auto-generated Primary Key

  

AUTO INCREMENT Field

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

The Identity column is new to Oracle 12c, and this article explains what it is for and how to use it. Have you ever needed to generate a unique value for a. How to Use the Identity Columns in Oracle for Auto Generated Primary Keys - Spiceworks. This primary key column is known as an identity or auto increment column. When a new row is inserted into the auto-increment column, an auto-generated sequential integer is used for the insert. For example, if the value of the first row is 1, then.

Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

Dec 05, 2011  In Oracle, you can create an auto increment field using ‘sequence’ database object that can be assigned as primary keys. Using Oracle ‘sequence’ object, you can generate new values for a column. An Oracle sequence is an object like a table or a stored procedure. Examples with walkthrough explanations are provided. Create a sequence. A primary key constraint define a column or series of columns that uniquely identify a given row in a table. Defining a primary key on a table is optional and you can only define a single primary key on a table. A primary key constraint can consist of one or many columns (up to 32).

Syntax for MySQL

The following SQL statement defines the 'Personid' column to be an auto-increment primary key field in the 'Persons' table:

CREATE TABLE Persons (
Personid int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (Personid)
);

MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.

By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.

To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:

To insert a new record into the 'Persons' table, we will NOT have to specify a value for the 'Personid' column (a unique value will be added automatically):

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen');

The SQL statement above would insert a new record into the 'Persons' table. The 'Personid' column would be assigned a unique value. The 'FirstName' column would be set to 'Lars' and the 'LastName' column would be set to 'Monsen'.

Syntax for SQL Server

The following SQL statement defines the 'Personid' column to be an auto-increment primary key field in the 'Persons' table:

CREATE TABLE Persons (
Personid int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);

The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature.

In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record.

Tip: To specify that the 'Personid' column should start at value 10 and increment by 5, change it to IDENTITY(10,5).

To insert a new record into the 'Persons' table, we will NOT have to specify a value for the 'Personid' column (a unique value will be added automatically):

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen');

The SQL statement above would insert a new record into the 'Persons' table. The 'Personid' column would be assigned a unique value. The 'FirstName' column would be set to 'Lars' and the 'LastName' column would be set to 'Monsen'.

Syntax for Access

The following SQL statement defines the 'Personid' column to be an auto-increment primary key field in the 'Persons' table:

CREATE TABLE Persons (
Personid AUTOINCREMENT PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);

The MS Access uses the AUTOINCREMENT keyword to perform an auto-increment feature.

By default, the starting value for AUTOINCREMENT is 1, and it will increment by 1 for each new record.

Tip: To specify that the 'Personid' column should start at value 10 and increment by 5, change the autoincrement to AUTOINCREMENT(10,5).

To insert a new record into the 'Persons' table, we will NOT have to specify a value for the 'Personid' column (a unique value will be added automatically):

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen');

What Oracle Object Is Used To Manage Auto-generated Primary Key Mean

The SQL statement above would insert a new record into the 'Persons' table. The 'Personid' column would be assigned a unique value. The 'FirstName' column would be set to 'Lars' and the 'LastName' column would be set to 'Monsen'.

Syntax for Oracle

In Oracle the code is a little bit more tricky.

You will have to create an auto-increment field with the sequence object (this object generates a number sequence).

Use the following CREATE SEQUENCE syntax:

CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;

The code above creates a sequence object called seq_person, that starts with 1 and will increment by 1. It will also cache up to 10 values for performance. The cache option specifies how many sequence values will be stored in memory for faster access.

To insert a new record into the 'Persons' table, we will have to use the nextval function (this function retrieves the next value from seq_person sequence):

INSERT INTO Persons (Personid,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen');

The SQL statement above would insert a new record into the 'Persons' table. The 'Personid' column would be assigned the next number from the seq_person sequence. The 'FirstName' column would be set to 'Lars' and the 'LastName' column would be set to 'Monsen'.


This chapter contains the following:

About Import Management Documentation

This topic provides an overview of the types of help available for importing data into Oracle Applications Cloud using Import Management.

You can get the information about import from the following:

  • Oracle Engagement Cloud Documentation on the Cloud Help Center (https://docs.oracle.com/en/cloud/saas/index.html).

    • Explore the latest critical reference content supporting Oracle Engagement Cloud.

    • Download PDF versions of guides, find links to reference materials and white papers, and search for specific topics.

    • Browse import data topics on the Administer tab that provide key import information.

  • Oracle Engagement Cloud Help

    • Click the Help icon in Oracle Engagement Cloud, and browse general topics on import or search on individual import objects for examples, attributes, and other information.

    • Access an online version of Oracle Applications Help here: (https://fusionhelp.oracle.com/helpPortal/faces/AtkHelpPortalMain?_adf.ctrl-state=w9peupsov_5)

  • Oracle Engagement Cloud Understanding Import and Export Management guide available on the Oracle Engagement Cloud Help Center (http://www.oracle.com/pls/topic/lookup?ctx=cloud&id=FAIEM)

    To understand what attributes are available for import for each object, start by searching help using the keywords Import Your <Object Name> Data. For example, to get help on importing accounts, enter Import Your Account Data.

Data Import Options

You can import your data from an external application into Oracle Applications Cloud in several different ways, including using public web services. Which import method you use depends on the type of data you're importing, the volume of data, and technical requirements. If you're integrating one cloud service with other cloud services, then you must use additional import methods as described in the appropriate guides.

When you're importing data for a particular object, you must make sure that any prerequisite objects already exist in the application. For example, if you're importing contacts for an account, then the account must already exist in the application. If one import job depends on the contents of another import job, then complete the prerequisite job before starting the dependent job. For example, if you're importing both accounts and opportunities, then import accounts before importing opportunities.

The following table lists the import methods and references to further information.

Import MethodDescriptionWhen to UseHow to AccessFor More Information

/salt-generate-keys-for-minion.html. Quick Import Excel Macros

You can use the import macros to speed up and simplify the import of up to 5000 records at a time for some objects. They validate your data entries, provide list of values, and automatically populate constant values.

The macros create data files that are automatically imported using File Import.

You can import the following objects using import macros:

  • Sales Users

  • Products and product groups

  • Accounts

  • Contacts

  • Account Hierarchy

  • Leads

  • Opportunities

These are best to import data in your initial deployment.

The macros are targeted to the simple proof of concept sales automation use case covered in the Importing Sales Users chapter of the Getting Started with Your Sales Implementation guide. For example, the import macros assume that you're importing account, contact, and lead records for one country at a time.

The macros generate log files with information about the File Import job, and these log files can also serve as a learning tool for more complex import.

You can download the Excel macros and any required mapping files from the Getting Started with Your Implementation: Quick Import Macros (Document ID 2229503.1) article on My Oracle Support.

The chapters in the Getting Started with Your Sales Implementation guide provide detailed instructions and video tutorials for using the macros.

For instructions on how to add your own fields to the macros, see. How to Configure Quick Import Macro for importing Employee Resources (Doc ID 2364229.1) article on My Oracle Support.

Import Management

Import Management improves definition, error handling, and performance for importing flat files (.CSV). For example, Import Management has drag and drop mapping capabilities and validates the first ten records in your data file before you import to ensure the data meets the import constraints of the various attributes. You can use Import Management to import data files with up to 50,000 records each.

Import Management is available for importing data for the following application objects:

  • Account

  • Action

  • Action Plan

  • Activity

  • Address

  • Asset

  • Attachment

  • Business Plan

  • Campaign

  • Classification

  • Consumer

  • Contact

  • Deal Registration

  • Household

  • Inbound Message

  • Interaction

  • Message

  • Opportunity

  • Partner

  • Partner Program

  • Program Enrollment

  • Relationship

  • Resource

  • Sales Lead

  • Sales Objective

  • Sales Order Header

  • Self-Service Role

  • Service Request

  • Subscription

  • SvcResourceCapacities

  • Template

  • Any additional objects you create

Use Import Management for importing outside the scope of the Getting Started with Your Implementation guide (this guide).

Oracle plans to discontinue File Import support with the 20C Update.

Click Import Management in the Navigator.

See the Understanding Import and Export Management guide for instructions on using the import.

File Import

File Import supports the import of data files with up to 100,000 records each for the broadest range of objects, including custom objects.

Use File Import only for any objects not yet covered by Import Management.

Oracle plans to discontinue File Import support with the 20C Update.

You can access File Import tasks by navigating to the following in the Setup and Maintenance work area:

  • Offering: Sales

  • Functional Area: Data Import and Export

You can learn how to import in the Understanding File-Based Data Import and Export guide.

The File-Based Data Import for Oracle Engagement Cloud guide provides information about the import attributes, including valid values and validations.

External Data Loader Client

Command-line tool that's used to import high-volume flat source data files into Oracle Engagement Cloud. This tool splits a large data file into multiple smaller files to adhere to Engagement Cloud's import volume limits, and enables the tracking of import status.

The client supports the same objects as Import Management.

Use this import method for importing very large data files for the objects supported by Import Management.

You can download the client from Oracle Support Document 2325249.1 (External Data Loader Client) on My Oracle Support.

Instructions for using the client are available in the document and in the client itself.

For REST API documentation, see the REST API for Oracle Engagement Cloud guide.

Web Services

Web services are available for external client applications to start and monitor import jobs. You can manage Import Management jobs using REST web services, and File Import jobs using SOAP web services.

Use web services to manage import jobs to import directly from an external application.

Public APIs are available for both the Import Management/REST services and the File Import/SOAP services.

The following REST APIs are available for import:

  • Import Activities

  • Import Activity Maps

  • Import Export Objects Metadata

The SOAP service is called the File Import Activity Service.

For REST API documentation, see the REST API for Oracle Engagement Cloud guide.

For SOAP API documentation, see the SOAP Web Services for Oracle Engagement Cloud guide.

High-Volume Import

The high-volume import mode is designed to import millions of records at once. This mode doesn't trigger any custom logic configured through the application composer. You can import the following objects using this mode:

  • Account

  • Asset

  • Contact

  • Hierarchy

  • Hierarchy Member

Use this import mode for importing very large number of records for the objects supported.

You can access High-Volume Import either using REST services or using External Data Loader Client.

When importing using REST, set the 'High-volume' attribute to enable this mode of import.

When importing using the External Data Loader Client, set the command-line parameter '-high-volume' to enable this mode of import.

For REST API documentation, see the REST API for Oracle Engagement Cloud guide.

The chapter Importing Data Using External Data Loader Client in the guide File-Based Data Import for Oracle Engagement Cloud provides information about using the External Data Loader Client.

The following figure provides an architectural overview of the different import methods:

  • Both File Import (callout 1) and the Quick Import (callout 2) use the same SOA architecture to import data into Oracle Engagement Cloud. When you import data using the quick import Excel macros, you're creating an import activity in File Import using the SOAP web services. The macro import creates the same import activity and uses the same mapping as you do when you initiate the import from the application. You can monitor each import in the macro or in the application itself.

  • Both Import Management (callout 3) and the External Data Loader Client (callout 4) use the same scheduled processes for import. When you import very large files using the client, the REST APIs create multiple processes to respect the Import Management file size limit.

  • To import from an external application, use the SOAP and REST web services directly (callouts 5 and 6).

Related Topics

Overview of Import Process

This topic provides an overview of the components used to import data.

Import Process Flow

The following figure explains the various stages in the import process:

  1. Evaluate your import data.

  2. Identify the relevant import objects.

  3. Map your data to the import objects.

  4. Create an import activity.

  5. Verify the import results.

Import Objects and Import Queue

You can find details about the attributes such as data type, length, and user key information for each object on the Import Objects tab. You can download the template for each import object by clicking the Download icon. You can create a mapping either while creating an import or separately using the Import Objects tab. On the Import Object Details page, click the Display Name link for the object to navigate to the Manage Mapping page. On this page, you can create a new import mapping, upload a map, or manage an existing mapping.

The Import Queue tab lets you view the imports that are in various statuses, such as active, completed, or unsuccessful. You can further drill down into an import activity by clicking the import name link to view the status details.

Application Composer and Custom Extensions

If you create additional attributes on an object in Application Composer, then these extensions are available for import and export once you generate the import and export artifacts in Application Composer.

File-Size Limits for Import

You can import a maximum of 50,000 records per data file. This limit is applicable for files imported using the User Interface and the REST service. The size of the data file can't exceed 250 MB.

You can submit multiple jobs at one time. However, the application processes only 10 jobs at a time. All other jobs are queued and executed as and when previous jobs complete.

Note: If you're using the importActivities REST service to submit jobs (also known as 'import activities'), then each job can contain up to 20 CSV files in the request payload. This means that 10 jobs, each with 20 CSV files of 50,000 records, can instruct the application to process up to one million records at a time.

To import much higher volumes of data, you must use the External Data Loader Client (EDLC). EDLC accepts files with more than 50,000 records. It breaks these larger files into smaller pieces of 50,000 each and imports them.

Here are the file and record limits for Import Management:

Import MethodologyFiles per JobMaximum Records Processed

User Interface

1 file per job (50k records)

500k records

REST Service and EDLC

20 files per job (50k records per file)

1 million records

How You Use Alternate Keys to Import Records

Alternate keys help you uniquely identify object records, so that you can create, update, delete, and manage relationships. This helps you import and build relationships to other objects, without the need to know the system-generated primary keys of each record. Use of known alternate keys also eliminates the need to requery and remap data.

Here are some examples of Alternate Keys:

  • Public Unique Identifier (PUID) - supports the ability to define an auto-generated sequence number.

  • Original System/Original System Reference (OS/OSR) - consists of two fields:

    • Original System - refers to the source system from which the record was sourced. Administrator must configure this field.

    • Original System Reference - refers to the unique identifier for the record in the source system.

  • Email Address - the email address value of a record can be used on some objects (such as Employee Resource) to identify a record.

  • Record Name - the name of the record can be used on some objects (such as Custom Objects) to identify a record. However it must be set to enforce uniqueness otherwise it can't be used as a key.

The keys resolve in the following order of precedence:

  1. Primary Key (PK)

  2. Public Unique Identifier (PUID)

  3. OS/OSR (if applicable)

  4. Any other alternate keys (PUID must be the first alternate key in the order of precedence if more than one alternate key is supported)

Here are the use cases supported in Import Management, high-volume import mode, and External Data Loader Client:

What You're Trying to do
How to do it

Update a value on a record

  • Refer to the record using object-specific Primary Key(s) or using object specific PUID(s). For example, LeadId(PK) and ResourceId(PK) or LeadNumber(PUID) and PartyNumber (PUID)

  • Refer to the record only by one of its defined AK values

  • Refer to the record only by one of its defined OS/OSR values

Import a child record and associate it with a parent record

  • Use one of the parent PK/PUID values. For example, LeadNumber(PUID),ResourceId (PK) or LeadId(PK),PartyNumber(PUID)

  • Use one of the parent AK values

  • Use the parent OS/OSR values

Scenarios for Creating, Updating, and Deleting Records

Scenario A shows the different ways of creating, updating, and deleting records using different combinations of keys with account object as an example. The following image and tables highlight the use cases for this scenario:

Scenario A for alternate key import

ColumnMeaning

PartyId

Primary key. Value comes from system-generated document sequence number. Never null.

PartyNumber

PUID. When record is created value is either passed in or system-generated. Never null.

SourceSystem

Source system name. Can be null. (Only a limited number of objects support OS/OSR construct)

SourceSystemReferenceValue

Source system reference. Can be null. (Only a limited number of objects support OS/OSR construct)

CEOName

This is a non-key data attribute on the object. Can be null.

Use Case
Source file contentsDescriptionPKPUIDOSOSRNon-key Data

Create a record, pass in PUID on primary object

PartyNumber: CA4139

CEOName: John Smyth

A record created

98769 (system-generated)

CA4139

N/A

N/A

John Smyth

Create a record, without passing PUID value on primary object

CEOName: John Smyth

A record created

98770 (system-generated)

CA4140 (system-generated)

N/A

N/A

John Smyth

Create a record, pass in OS/OSR on primary object

SourceSystem: Siebel CRM

SourceSystemReferenceValue: 3-0007

CEOName: John Smyth

A record created

98771 (system- generated)

CF380A (system- generated)

Siebel CRM

3-0007

John Smyth

Update data attribute on existing record, using PK to identify the record

PartyId : 98769

CEOName: Soloman

Record updated

98769

N/A

N/A

N/A

Soloman

Update data attribute on existing record, using OS/OSR to identify the record

SourceSystem: Siebel CRM

SourceSystemReferenceValue: 3-0007

CEOName: Soloman

Record updated

98771

N/A

Siebel CRM

3-0007

Soloman

Delete existing record, using PK to identify the record

PartyId : 98769

Record deleted

98769

N/A

N/A

N/A

N/A

Delete existing record, using PUID to identify the record

PartyNumber : CA4139

Record deleted

98770

CA4139

N/A

N/A

N/A

Delete existing record, using OS/OSR to identify the record

SourceSystem: Siebel CRM

SourceSystemReferenceValue: 3-0007

Record deleted

98771

N/A

Siebel CRM

3-0007

N/A

What Oracle Object Is Used To Manage Auto-generated Primary Keys

Scenario B shows the different ways of creating child records, updating and deleting relationship to the parent record. Here account is the parent and address is the child object. The following image and tables highlight the use cases for this scenario:

What oracle object is used to manage auto-generated primary key of life

Scenario B for alternate key import

What Oracle Object Is Used To Manage Auto-generated Primary Key 2017

ColumnMeaning

PartyId

Primary key. Value comes from system-generated document sequence number. Never null.

PartyNumber

PUID. When record is created value is either passed in or system-generated. Never null.

SourceSystem

Source system name. Can be null.

SourceSystemReferenceValue

Source system reference identifier Can be null.

CEOName

This is a non-key data attribute on the object. Can be null.

PartyId

Foreign key to the parent record. This value contains the PK of the record in the parent object.

AddressNumber

Foreign key to the parent record. This value contains the PUID of the record in the parent object.

Use Case
Source file contentsDescriptionPKPUIDOSOSRNon-key DataForeign KeyForeign Key (enriched transient value)

Create a child record, pass in custom PUID and parent object PK value

PartyNumber: CA2700

CEOName: Rhode

PartyId: 98770

A child record is created, relationship to parent is established through PK

5001 (system- generated)

CA2700

N/A

N/A

Rhode

98770

CF383

Create a child record, pass in parent object PUID value

CEOName: Rhode

AddressNumber: CF383

A child record is created, relationship to parent is established through PUID

5002 (system- generated)

system- generated

N/A

N/A

Rhode

98769

CF383

Update the relationship to point to a different parent, using the PUID of the child as the key

PartyNumber: CA2700

AddressNumber: CF383

Existing child record updated relationship to a new parent established through PUID

5001

CA2700

N/A

N/A

Rhode

98772

CF383

Delete the relationship to the parent

PartyId: 5002

ERROR - child record should not exist stand alone. Must have relationship to a parent. (Address object is an exception to this.)

N/A

N/A

N/A

N/A

N/A

N/A

N/A