4Using Oracle Data Loader On Demand

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.