4Using Oracle Data Loader On Demand

Using Oracle Data Loader On Demand

This chapter provides information about the tasks supported by Oracle Data Loader On Demand. It includes the following topics:

• Insert, Update, and Upsert Parameters

• Resuming or Halting Operations in Oracle Data Loader On Demand

• Displaying Information About the Job Status

• Status Messages from Oracle Data Loader On Demand

• Guidelines for Using Oracle Data Loader On Demand

Insert, Update, and Upsert Parameters

To insert, update, or upsert records in Oracle CRM On Demand, you must prepare your data file and a corresponding map file. For information on preparing a data file, see Data File for Oracle Data Loader On Demand. For information on creating a map file, see Map File for Oracle Data Loader On Demand.

You can also create a properties file. For information on the format of a properties file, see Properties File for Oracle Data Loader On Demand.

The following table lists the Insert, Update, and Upsert parameters that you set in the properties file. Note that all parameters are in lower case. The values used with the parameters can be case sensitive (such as path names). If an optional parameter (such as -csvdelimiter) is not specified, its default value is used.

Table Insert, Update, and Upsert Parameters in the Properties File

Parameter	Required or Optional	Description
-datafilepath value	Required	The full path (including the file name) of the data file that you want to import.
-mapfilepath value	Required	The full path (including the file name) of the map file.
-recordtype value	Required	Record type of the data that you want to import. For a list of supported record types, see Supported Record Types for Oracle Data Loader On Demand.
-duplicatecheckoption value	Optional	Duplicate check option. Use this parameter only for Update and Upsert operations. Use this parameter to specify which field Oracle Data Loader On Demand uses to check records to determine whether the record is a duplicate. The value must be one of the following: externalid. This is the default value. rowid If you specify this parameter for an Insert operation, it is ignored. No duplicate checking occurs, and it does not cause an error or warning.
-datetimeformat value	Optional	Date and time format. The date and time formats for this selection are listed in the table in the topic:Use the Supported Date and Time Formats.
-csvdelimiter value	Optional	CSV delimiter used in the data file. The value must be one of the following: , (comma). This is the default value. This delimiter applies to the data file only; it is not used for the map file or properties file. ; (semicolon)
-importloglevel value	Optional	Specify the error level of the log file. The value must be one of the following: all. Lists all error messages. errorswarnings. Lists error and warning messages only. errors. Lists error messages only. This is the default value. This setting is for Insert, Update, and Upsert operations on the server. For troubleshooting tasks, you can increase the log level to all, which records all detailed error messages. However, the all error level slows down an operation.
-waitforcompletion	Optional	When specified, Oracle Data Loader On Demand displays a status message if the processing of the request finishes with a status of Completed. When specified, if you press CTRL+C , then Oracle Data Loader On Demand prompts you to continue processing or cancel.
- disableemailnotification	Optional	Specifies whether an email notification is sent after a database import. If this parameter is not in the parameter list, then the email notification is sent after the database import. If this parameter is in the parameter list, then the email notification is not sent.
- senduserlogfile	Optional	Specifies whether Oracle Data Loader On Demand sends the user log file after a database import. If disableemailnotification is in the parameter list, then the value of senduserlogfile is ignored. Note: If the user log file size exceeds 20 MB, then Oracle Data Loader On Demand does not email the file, but does note this fact in the user log file. Refer to the userlogfiledir parameter to specify where the user log file is stored.
- userlogfiledir value	Optional	Specifies the directory in which to save the user log file. If you do not use -userlogfiledir to specify a directory and the -waitforcompletion parameter is present in the properties file, then the user log file is stored in the same directory as the Oracle_DataLoader_OnDemand.jar file. If neither the -userlogfiledir parameter or the -waitforcompletion parameter are specified, then the user log file is not saved.

Resuming or Halting Operations in Oracle Data Loader On Demand

This topic describes how to halt or resume operations in Oracle Data Loader On Demand.

You can halt Oracle Data Loader On Demand by pressing CTRL+C. This halts the process of submission of the remaining records in the data file. All records that have been submitted continue to be processed by the server. To resume processing, create a properties file using the parameters specified in the following table. To stop requests on the server, log in to Oracle CRM On Demand, navigate to the Import Queue and cancel the request.

To halt operations in Oracle Data Loader On Demand

• Press CTRL+C.

This halts the process of submission of the remaining records in the data file. All records that have been submitted continue to be processed by the server.

The following procedure describes how to obtain the request ID of a previously interrupted request.

To obtain a request ID

1. Navigate to the folder where the log files were stored.

   The log files are saved at the location specified with the –clientlogfiledir parameter.

2. Open the last log file and navigate to the bottom of the file.

   A message displays the request ID similar to the following:

   To resubmit  ... please re-execute ... using the RESUME option while providing the 
   following Request ID
   

The following procedure describes how to resume operations in Oracle Data Loader On Demand.

To resume operations in Oracle Data Loader On Demand

• Create a properties file using the required or optional parameters.

  For more information on the required or optional parameters, see the following table.

The following table lists the Resume parameters for Oracle Data Loader On Demand.

Table Resume Parameters for Oracle Data Loader On Demand

Parameter	Required or Optional	Description
-resumerequest value	Required	Request ID of the import request that you want to resume.
-waitforcompletion	Optional	When specified, you get a prompt if: The processing of the request is complete with a status of Completed. You press CTRL+C.

Displaying Information About the Job Status

You can display the details about the progress of your request in Oracle CRM On Demand. The Import queue displays information, such as the number of records processed, the status of your request, and other information.

To display information about the job status

1. Log in to Oracle CRM On Demand.

2. Navigate to the import queue: Admin, Import Export tools, and then Import Queue.

3. To display further details about a request, drill down on a request by clicking the record type link to navigate to the detail page for the request.

   When your request is complete, your map file and log file appear as attachments under the Request Detail page.

Status Messages from Oracle Data Loader On Demand

The following table lists the status messages in the import queue that Oracle Data Loader On Demand returns after processing a data file.

Table Status Messages in the Import Queue

Status	Description
Cancelled	The request was cancelled before or during processing.
Cancelling	The request is being cancelled by the user.
Completed	The request completed without any errors during the Insert, Update, or Upsert operation.
Completed with Errors	The request completed, but some records failed to be inserted or updated.
Error	The request was not processed due to one or more errors. Records might or might not have been processed.
In Progress	The request is being processed by Oracle CRM On Demand.
Queued	The request has been submitted, but it has not been processed yet.
Re-queued	The request has been submitted again by Oracle CRM On Demand Customer Care.

Guidelines for Using Oracle Data Loader On Demand

Follow these guidelines when using Oracle Data Loader On Demand. Adhering to these guidelines helps you to avoid errors.

Use the Required Field Mappings

Every record type has a list of fields that is required. If you omit a required field, Oracle Data Loader On Demand returns an error. The following table lists the preconfigured required fields for each record type. You can also mark other fields as required by changing their properties under the Field Management section of the Admin screen.

Table Required Fields for Record Types

Record Type	Required Field
Account	Account Name, Owner
Account Address	Account, Address, Address Type
Account Book	Account, Book Name
Account Competitor	Account Id or Account Name or Account External Unique Id, Competitor ID or Competitor name or Competitor External Id
Account Contact	If the External Id is chosen as the option to check duplicates, then Account External Id and Contact External Id are required fields. If one or both of these are missing, Oracle Data Loader On Demand returns an error. If the Row Id is chosen as the option to check duplicates, then Account Id and Contact Id are required fields.
Account Partner	Account Id or Account Name or Account External Unique Id, Partner ID or Partner name or Partner: External System Id
Account Team	Account Access, User
Accreditation	Name, Status, Owner
Accreditation Request	Accreditation, Partner
Address	Address Name
Allocation	Allocation Type, Start Date, Owner, Sample
Application	Name, First Name, Last Name, Submission Status
Appointment	Subject, Start Time, End Time, Owner
Appointment Contact	Appointment External ID, Contact External ID or Contact Full Name
Appointment User	Appointment External ID, User External ID or User Sign In ID
Asset	Product Name
Blocked Product	Product, Contact
Book	Book Name
Book User	Book, Users
Broker Profile	Broker Profile Name, Broker Profile Year, Partner
Business Plan	Plan Name, Period, Type, Status, Owner
Campaign	Campaign Name, Source Code, Owner
Campaign Recipient	None
Certification	Name, Status, Owner
Certification Request	Candidate, Certification
Claim	Claim #, Report Date, Loss Date and Time, Policy
Contact	First Name, Last Name, Owner
Contact Address	Contact, Address, Address Type
Contact Book	Book Name
Contact State License	Contact, License Number
Contact Team	Contact Access, User
Course	Name, Status, Owner
Course Enrollment	Candidate, Course
Coverage	Coverage Name, Policy
Custom Object 1	Name, Owner
Custom Object 2	Name, Owner
Custom Object 3	Name, Owner
Custom Object 4-15	Name, Owner
Damage	Claim, Damage Name
Deal Registration	Name, Type, Principal Partner, Submission Status, Owner
Dealer	Name, Owner
Event	Name, Start Date, End Date, Event Objective, Owner
Exam	Name, Status, Owner
Exam Registration	Candidate, Exam, Exam Date
Financial Account	Financial Account Number, Type, Financial Account
Financial Account Holder	Financial Account Holder Name, Financial Account, Role
Financial Account Holding	Financial Account, Financial Product, Financial Account Holdings Name
Financial Plan	Type, Financial Plan Name, Status
Financial Product	Financial Product Name
Financial Transaction	Financial Account, Financial Product, Transaction Type, Transaction ID
HCP Contact Allocation	Type, Start Date, Contact Name, Product Name
Insurance Property	Policy, Type
Inventory Audit Report	Completed Date, Inventory Period, Owner, Reason, Status, Type
Inventory Period	Start Date, Owner, Active, Reconciled
Invitee	Event, Contact Name
Involved Party	Claim, Contact, Involved Party Name, Role
Lead	First Name, Last Name, Owner Full name
MDF Request	Id, Request Name, Principal Partner Account, Fund, Submission Status, Due Date, Owner
Message Plan	Name, Owner, Type, Product, Status, Disclosure Mandatory, Enable Followup, Lock Sequence
Messaging Plan Item	Sequence Number, Disclosure Message, Type, Parent Message Plan
Messaging Plan Item Relations	Type, Parent MP Item
Modification Tracking	Object Id, Event Name, Object Name, Modification Number
Note	Not applicable
Objective	Objective Name, Type, Status, Period, Owner
Opportunity	Name of Opportunity, Account, Sales Stage, Close Date, Owner
Opportunity Contact Role	Opportunity Name, Contact Name, Buying Role, Created Date, First Name, Last Name
Opportunity Product Revenue	Product, Opportunity
Opportunity Team	Opportunity External ID or Opportunity Name, Opportunity Access, User
Order	Order Id
Order Item	Order item Number, Quantity, Product, Order, Status
Partner	Partner Name, Owner
Partner Program	Partner Program Name, Status
Plan Account	Account, Business Plan
Plan Contact	Contact, Business Plan
Plan Opportunity	Business Plan, Opportunity
Policy	Policy #, Policy Type
Policy Holder	Policy, Policy Holder Name, Role
Portfolio	Account Number, Owner
Price List	Price List Name, Effective From, Type, Status
Price List Line Item	Price List, Product
Product Category	Category Name
Product	Product Name
Product Indication	Indication Name, Product
SP Request	Id, Request Name, Principal Partner Account, Submission Status, Owner
Sample Disclaimer	Status
Sample Inventory	Inventory Period, Opening Balance, Sample
Sample Lot	Lot #, Sample, Expiration Date
Sample Request	Contact, Shipping Address, Owner
Sample Request Item	Quantity, Product, Order, Order Item Number, Status
Sample Transaction	Date, Type
Service Request	None
Social Profile	Author, Community, Active, Contact
Solution	Title
Special Pricing Product	Item Number, Product
Task	Subject, Due Date, Priority, Owner
Task Contact	Task External ID, Contact External ID or Contact Full Name
Task User	Task External ID, User External ID or User Sign In ID
Transaction Item	Line Number, Transaction #, Product, Quantity
User	First Name, Last Name, Role, Work Phone #, Email, Status, Alias, User Id, User Sign In Id
Vehicle	VIN, Vehicle Currency, Product Name
Vehicle Contact	Vehicle External ID or VIN, Contact External ID or Contact Name, Product Name

Null Values

Every record in the data file must have valid values for the required fields as listed in the previous table. Required fields cannot contain null values, nor can null values be loaded into any required field. Non-required fields can contain null values. In the case of updating records, null values in the import file can replace data in the database. For example, if an existing record has the value of 555-1234 for a contact’s fax number, and if the data file has a null for that contact’s fax number, then after the update the database contains a null for that contact’s fax number.

Use the Supported Date and Time Formats

Make sure your date and time formats conform to the formats in the following table.

Table Supported Date and Time Formats

Option	Geographic Location	Date and Time Format
can	Canada	DD/MM/YYYY hh:mm:ss PM
eur	Europe	DD.MM.YYYY 24:mm:ss
other	All other geographic locations not specifically covered by other options.	YYYY-MM-DD 24:mm:ss
uk	United Kingdom	DD/MM/YYYY 24:mm:ss
usa	United States of America This is the default option.	MM/DD/YYYY hh:mm:ss PM

Use the Supported File Formats

The data files and map files must be formatted correctly:

• Data file. The data file must be a comma or semicolon-delimited text file. The default character set encoding is UTF-8. If you use a different character set encoding, then specify it using the -characterset property.

• Map file. The map file must use the same character set encoding as the data file and must be a comma-delimited text file, with the following header:

  "Oracle CRM On Demand Field","Import File Field"
  

Make Sure Data Files Are Correct

The following mistakes can make a data file unreadable:

• Blank column header. The first line of the data file must list the field names to process.

• Mismatch between data file header and the map file. For each field listed in the data file header, there must be an entry in the map file.

• Character set encoding. The data file and the map file must use the same character set encoding. The default character set encoding is UTF-8. If you use a different character set encoding, then specify it using the -characterset property.

• Missing field in a record. The first line of the data file must list the field names (the file must have a header), which are also referenced by the map file. Make sure that every record in the data file has the same number of fields.

• Empty data file. Make sure there is data in the data file.

• Invalid delimiter value. The only supported delimiters are the comma (,) and the semicolon (;). Make sure the properties file specifies which delimiter you use.

• Required Oracle CRM On Demand fields must contain a value. Make sure required fields are specified in the data file. If required fields are blank, the fields are not mapped by Oracle Data Loader during the import process and if the required fields are not mapped, the import fails.

• Associated records must exist. Make sure any associated records specified in the data file exist in Oracle CRM On Demand prior to importing.

• Picklist values must be an exact match. Make sure picklist values are an exact match. Values are case sensitive. Leading or trailing blank characters are not allowed. Blank characters are not allowed at the end of the file.

• Cascading picklist values must be correct. Make sure cascading picklist values are correct.

• Oracle CRM On Demand fields must be mapped only once. Make sure you have not mapped an Oracle CRM On Demand field more than once.