1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Controlling order of data import through sql loader

Discussion in 'General' started by SomnathG, Jan 27, 2011.

  1. SomnathG

    SomnathG Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I ma using SQL Loader to import data form CSV file. After importing the data I am using STORED PROCEDURE to validate uploaded data. Now I have to display error message with the actual row number of imported CSV file to users. For that, I need to insert data into table in the exact order of CSV file. As per my understanding, SQL Loader should uploads data in the same order of CSV file, but in reality that is not happening. Can you guys please give me a solution for this problem? It's very urgent.


    Thanks In Advance.

    Somnath Guha
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Order is never guaranteed on a sql loader insert. It may occasionally happen that the data loaded remains in the same order as the lines in the CSV file but this is an exception and not the rule. Why is it necessary to return the row number from the source file except to make it easy on you? The data is not order-dependent so returning the record which fails validation should suffice as that record can be found by a text search of the file. You would like to insert the data in order but it is not a necessity. Your understanding of how Oracle inserts data needs to be adjusted; read here:

    http://oratips-ddf.blogspot.com/2006/10/location-location-location.html
     
  3. SomnathG

    SomnathG Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    I found some solution for this. Instead of using 'REPLACE' in the control file, if we use 'TRUNCATE', then order remains same. My previous code was :

    LOAD DATA
    INFILE 'C:/upload/UPDT_AGENT_BUCKET_DETAILS.csv'
    REPLACE
    INTO TABLE ENTITY_BUCKET_TEMP
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    (
    AGENTOFFICEID CHAR,
    ACCOUNTTYPE CHAR,
    PLAN_ORDER CHAR
    )


    My current control file is look like this:

    LOAD DATA
    INFILE 'C:/upload/UPDT_AGENT_BUCKET_DETAILS.csv'
    TRUNCATE
    INTO TABLE ENTITY_BUCKET_TEMP
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    (
    AGENTOFFICEID CHAR,
    ACCOUNTTYPE CHAR,
    PLAN_ORDER CHAR
    )


    I have tested with my modified code multiple times and it yields desired results for me.

    Thanks,
    Somnath
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is because you are essentially creating an empty table each time you load the data with the truncate option; that is the rare case when loader data CAN remain in the same order as it appears in the source file, however it is still not a guaranteed result.

    It is nice that this works for now.