Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unanswered: SQL Loader - Only Loads First 2000 Rows

    The following control file loads the first 2000 rows of 32000 without any reported error. Any ideas?

    OPTIONS(DIRECT=FALSE)
    LOAD DATA
    TRUNCATE
    INTO TABLE cwfile
    FIELDS TERMINATED BY X"09"
    ( ORG_ID,
    SOURCEAPP,
    ID,
    ORGNAME,
    STREET,
    TOWN,
    COUNTY,
    POSTCODE,
    DNBCRTYCODE,
    Sequence_Number,
    Match_Code,
    BEMFABIND,
    DUNS_Number2,
    MATCHGRADE,
    CONFCODE,
    CONFCODEPERCENT,
    Transaction_Date,
    Transaction_Sequence_Number,
    Transaction_Type,
    Record_Type1,
    SiteMatchKeyChangeInd,
    Nixie_Change_Indicator,
    Tixie_Change_Indicator,
    DeList_Change_Indicator,
    OB_Change_Indicator,
    NonClassifiedSICChangeInd,
    UnfavorableBankruptcyChangeInd,
    Linkage_Change_Indicator,
    Nixie_Indicator,
    Tixie_Indicator,
    DeList_Indicator,
    OB_Indicator,
    Non_Classified_SIC_Indicator,
    UnfavorableBankruptcyInd,
    Source_Is_Site_Match,
    Source_Is_Data_Append,
    DHCHNG,
    SBCHNG,
    SALESCHNG,
    PHONECHNG,
    LEGALSTUSCHNG,
    YRSTARTCHNG,
    EMPGRPCHNG,
    ADDRGRPCHNG,
    GEOCHNG,
    CONAMECGNG,
    SETCHNG,
    SICCHNG,
    MISCCHNG,
    CEOCHNG,
    SPECTRUM,
    OTHER,
    FILLER1,
    Site_ID,
    DUNS_Number,
    Business_Name,
    Tradestyle,
    Second_Tradestyle,
    Physical_Street_Address,
    Second_Address_Line,
    Physical_City,
    Physical_State_Abbreviation,
    Physical_Zip,
    Country_Name,
    State_Name,
    County_Name,
    FILLER2,
    Latitude,
    Longitude,
    Mail_Address,
    Second_Mail_Address_Line,
    Mail_City,
    Mail_State,
    Mail_Zip,
    Carrier_Route_Code,
    Continent_Code,
    Nation_Code,
    State_Code,
    City_Code,
    County_Code,
    SMSA_Code,
    EconomicAreaofInflCode,
    FIPS_Country_Code,
    FIPS_Subdivision_1_Code,
    FIPS_Subdivision_2_Code,
    FIPS_MSA_Code,
    LATITUDE1,
    LONGITUDE1,
    Geocode_Accuracy,
    Telephone,
    Country_Telephone_Access_Code,
    Cable_Telex,
    Fax_number,
    CEO_Full_Name,
    CEO_First_Name,
    CEO_Middle_Initial,
    CEO_Last_Name,
    CEO_Suffix,
    CEO_Prefix,
    CEO_Title,
    CEO_MRC_Code,
    Gender_Code,
    Sales_Volume,
    Sales_Volume_Code,
    Annual_Sales_Local_Currency,
    AnnualSalesLocalCurrCode,
    Currency_Code,
    Employees_Total,
    Employees_Total_Code,
    Employees_Here,
    Employees_Here_Code,
    Year_Started,
    Status_Indicator,
    Subsidiary_Indicator,
    Manufacturing_Indicator,
    Population_Code,
    Small_Business_Indicator,
    Minority_Owned_Indicator,
    PublicPrivate_Indicator,
    Division_Indicator,
    Site_Status,
    Legal_Status,
    ImportExport_Code,
    OwnsRents,
    Square_Footage,
    Global_Ultimate_DUNS_number,
    Global_Ultimate_Name,
    Global_Ultimate_Indicator,
    GlobalUltimateFIPSCountryCode,
    Global_Ultimate_Country_Code,
    GlobalUltimateStateAbbr,
    Domestic_Ult_DUNS_Number,
    DomesticUltBusinessName,
    DomesticUltFIPSCountryCode,
    Domestic_Ult_Country_Code,
    Domestic_Ult_State_Abbr,
    Parent_DUNS_number,
    Headquarters_DUNS_number,
    ParentHQ_Name,
    ParentHQ_FIPS_Country_Code,
    ParentHQ_Country_Code,
    ParentHQ_State,
    Hierarchy_Code,
    Dias_Code,
    Number_of_Family_Members,
    Family_Update_Date,
    Major_Industry_Category,
    Line_of_Business,
    SIC1,
    SIC2,
    SIC3,
    SIC4,
    SIC5,
    SIC6,
    National_Identification_Number,
    Primary_Local_Activity_Code,
    Percent_Growth_Sales_3yr,
    Percent_Growth_Employees_3yr,
    Trend_Year_Sales_3yr,
    Trend_Year_Employees_3yr,
    Percent_Growth_Sales_5yr,
    Percent_Growth_Employees_5yr,
    Trend_Year_Sales_5yr,
    Trend_Year_Employees_5yr,
    Base_Year_Sales,
    Base_Year_Employees,
    Bank_DUNS_Number,
    Bank_Name,
    Bank_Address,
    Bank_City,
    Bank_State,
    Bank_ZIP,
    Accounting_Firm_Name,
    Busmeta1,
    Busmeta2,
    Busmeta3,
    Busmeta4,
    Tradmeta1,
    Tradmeta2,
    Tradmeta3,
    Tradmeta4,
    First_Executive_First_Name,
    First_Executive_Middle_Initial,
    First_Executive_Last_Name,
    First_Executive_Suffix,
    First_Executive_Prefix,
    First_Executive_Title,
    First_Executive_MRC,
    Second_Exec_First_Name,
    Second_Exec_Middle_Initial,
    Second_Exec_Last_Name,
    Second_Exec_Suffix,
    Second_Exec_Prefix,
    Second_Exec_Title,
    Second_Exec_MRC,
    Third_Exec_First_Name,
    Third_Exec_Middle_Initial,
    Third_Exec_Last_Name,
    Third_Exec_Suffix,
    Third_Exec_Prefix,
    Third_Exec_Title,
    Third_Exec_MRC,
    FIPS_City_code2,
    National_Type_Code,
    Marketability,
    FILLER3,
    Trade_Style_1,
    Trade_Style_2,
    Trade_Style_3,
    Trade_Style_4,
    Trade_Style_5,
    CWID,
    CWOrgID,
    CWSourceApp)

  2. #2
    Join Date
    Aug 2003
    Posts
    41
    Try adding a BADFILE and DISCARDFILE clause in the control file and see whether anything gets logged in.

    -Sunil

  3. #3
    Join Date
    Sep 2003
    Posts
    3
    My command line parameters are as follows:

    SQLLDR CONTROL=E:\Scripts\recshtdata.ctl, LOG=E:\Scripts\recshtdata.log, BAD=E:\Scripts\recshtdata.bad, DATA=E:\Scripts\recshtdata.dat USERID=dbowner/coredata@idb, ERRORS=999, LOAD=2000, DISCARD=E:\Scripts\recshtdata.dis,DISCARDMAX=5

    There is no bad output! The log file is as follows:

    SQL*Loader: Release 9.2.0.1.0 - Production on Thu Sep 11 11:32:12 2003

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    Control File: E:\Scripts\recshtdata.ctl
    Data File: E:\Scripts\recshtdata.dat
    Bad File: E:\Scripts\recshtdata.bad
    Discard File: E:\Scripts\recshtdata.dis
    (Allow 5 discards)

    Number to load: 2000
    Number to skip: 0
    Errors allowed: 999
    Bind array: 64 rows, maximum of 256000 bytes
    Continuation: none specified
    Path used: Conventional

    Table CWFILE, loaded from every logical record.
    Insert option in effect for this table: TRUNCATE

    ......

    Space allocated for bind array: 220848 bytes(4 rows)
    Read buffer bytes: 1048576

    Total logical records skipped: 0
    Total logical records read: 2000
    Total logical records rejected: 0
    Total logical records discarded: 0

    Run began on Thu Sep 11 11:32:12 2003
    Run ended on Thu Sep 11 11:32:16 2003

    Elapsed time was: 00:00:03.37
    CPU time was: 00:00:00.79

  4. #4
    Join Date
    Aug 2003
    Posts
    41
    Hello!

    By specifying LOAD=2000 in your commandline, you are asking SQL Loader to load only 2000 logical rows. That is exactly what it is doing. If you want all the records to be loaded, remove the LOAD parameter from your command line.

    -Sunil

  5. #5
    Join Date
    Jul 2003
    Location
    Near Paris France
    Posts
    60
    Try adding the parameter

    LOAD=32000 !!!

    It is not needed but....

  6. #6
    Join Date
    Sep 2003
    Posts
    3
    Cheers. Best read what I have written in future:-).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •