DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: SQLLOADER PROBLEM

  1. #1
    Join Date
    Jan 2001
    Posts
    20
    I am Having a problem loading a datafile using SQLLOADER.
    I really don't know what the problem is. The Control File? The Datafile? Can Someone help me out? Here is the control file, datafile and error message I am getting.


    Datafile:
    1234567890002HSBCI Frank's CO
    1334567890002HSBCI JJ's CO
    1434567890002HSBCI JOHNS's CO
    1534567890002HSBCI MARKS's CO

    Table layout:
    SQL> desc cust
    Name Null? Type
    ------------------------------- -------- ----
    ACCT_NO NOT NULL CHAR(10)
    BANK_NO NOT NULL CHAR(3)
    FEED NOT NULL VARCHAR2(16)
    PROFIT_CENTER NOT NULL VARCHAR2(6)
    ACCT_OFFICER CHAR(3)
    NAME VARCHAR2(40)
    ADDR VARCHAR2(160)
    ICB_FLAG CHAR(1)
    PRINT_SUPPRESS_FLAG CHAR(1)
    COMA_STATEMENT_FLAG CHAR(2)
    STOP_HOLD_FLAG VARCHAR2(2)
    ACCT_STATUS CHAR(2)
    ACCT_TYPE CHAR(2)
    SEND_STMNT_TO_PARENT CHAR(1)
    LAST_TXN_DATE DATE
    CREATE_TIME NOT NULL DATE
    DDA_INSERT_TIME DATE
    PENDING CHAR(1)
    PARENT_ACCT_NO CHAR(10)
    PARENT_BANK_NO CHAR(3)
    PASSWORD VARCHAR2(8)


    Control file:(loadcust.txt)

    LOAD DATA
    INFILE 'sqltest.dat'
    INTO TABLE CUST
    (ACCT_NO POSITION(1:10) CHAR,
    BANK_NO POSITION(11:13) CHAR,
    FEED POSITION(14:18) CHAR,
    ADDR POSITION(28:37) CHAR,
    ICB_FLAG CONSTANT "0",
    PRINT_SUPPRESS_FLAG CONSTANT "0",
    CREATE_TIME SYSDATE,
    DDA_INSERT_TIME SYSDATE,
    PENDING CONSTANT "V")



    ERROR MESSAGE:
    SQL*Loader-524: partial record found at end of datafile (loadcust.txt)

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    How many rows were loaded? If the number of rows loaded is the same number of rows in the data file, you have an extra blank line at the end of your data file.
    Jeff Hunter

  3. #3
    Join Date
    Jan 2001
    Posts
    20
    No rows were loaded. That is just a few lines of the datafile. Do you have any other suggestions? Does my control file look O.K.? As you see I did not include all columns in the table could this be the problem?

  4. #4
    Join Date
    Jan 2001
    Posts
    20
    Oh and name of the file that I'm getting the error on is on the control file.

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I got a couple of results.
    1. The first time I tried it, I got your error message. I had blank lines at the end of my control file.

    2. After I cleaned up my control file and re-loaded, I got the following output in my log file:

    -----Begin Log----
    SQL*Loader: Release 8.1.5.1.0 - Production on Tue Jan 23 09:20:35 2001

    (c) Copyright 1999 Oracle Corporation. All rights reserved.

    Control File: loadcust.ctl
    Data File: sqltest.dat
    Bad File: sqltest.bad
    Discard File: none specified

    (Allow all discards)

    Number to load: ALL
    Number to skip: 0
    Errors allowed: 50
    Bind array: 64 rows, maximum of 65536 bytes
    Continuation: none specified
    Path used: Conventional

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

    Column Name Position Len Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    ACCT_NO 1:10 10 CHARACTER
    BANK_NO 11:13 3 CHARACTER
    FEED 14:18 5 CHARACTER
    ADDR 28:37 10 CHARACTER
    ICB_FLAG CONSTANT
    Value is '0'
    PRINT_SUPPRESS_FLAG CONSTANT
    Value is '0'
    CREATE_TIME SYSDATE
    DDA_INSERT_TIME SYSDATE
    PENDING CONSTANT
    Value is 'V'

    Record 1: Rejected - Error on table CUST.
    ORA-01400: cannot insert NULL into ("JEFF"."CUST"."PROFIT_CENTER")

    Record 2: Rejected - Error on table CUST.
    ORA-01400: cannot insert NULL into ("JEFF"."CUST"."PROFIT_CENTER")

    Record 3: Rejected - Error on table CUST.
    ORA-01400: cannot insert NULL into ("JEFF"."CUST"."PROFIT_CENTER")

    SQL*Loader-524: partial record found at end of datafile (sqltest.dat)

    ------End Log------

    Basically, this is telling you that three records were rejected because they had no PROFIT_CENTER defined. Since PROFIT_CENTER is tagged as NOT NULL and you have not specified a default PROFIT_CENTER, sqlldr rejects the record.

    Oh, and the last line means I got a blank line in the data file.
    Jeff Hunter

  6. #6
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    Put a blank line at the end in the Control File; I have faced this problem in the past and blank line helped solve it.

    - Rajeev

Posting Permissions

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


Click Here to Expand Forum to Full Width