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!

Error on sql loader log file

Discussion in 'SQL PL/SQL' started by Aj_aaron2002@yahoo.co.uk, Aug 24, 2010.

  1. Aj_aaron2002@yahoo.co.uk

    Aj_aaron2002@yahoo.co.uk Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    Hello All,
    I have been trying to load data unto an empldr table. But when I invoke the loader, all is well as it brings back a message saying:

    Load Completed - Logical record count 14.

    But when i check the empldr table, there is no row selected - select * from empldrThis is the command am using to load the data : sqlldr userid=scott/tiger control=c:\users\ajayia\empldr1.ctl log=c:\users\ajayia\log.txt DIRECT=TRUE


    The Data file looks like this



    7369 SMITH CLERK 7902 17-DEC-80 800 20
    7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
    7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
    7566 JONES MANAGER 7839 02-APR-81 2975 20
    7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
    7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
    7782 CLARK MANAGER 7839 09-JUN-81 2450 10
    7788 SCOTT ANALYST 7566 19-APR-87 3000 20
    7839 KING PRESIDENT 17-NOV-81 5000 10
    7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
    7876 ADAMS CLERK 7788 23-MAY-87 1100 20
    7900 JAMES CLERK 7698 03-DEC-81 950 30
    7902 FORD ANALYST 7566 03-DEC-81 3000 20
    7934 MILLER CLERK 7782 23-JAN-82 1300 10



    When i checked the log.txt file i.e the log file

    I find that the hiredate column is giving error ORA-01847 : Rejected - Error on table EMPLDR, column HIREDATE.
    ORA-01847: day of month must be between 1 and last day of month.

    Please help am new to sql loader. Thanks in anticipation.
    Also I am using oracle 10g version 10.2.3.0

    This is what the log file looks like


    Control File: c:\users\ajayia\empldr1.ctl
    Data File: c:\users\ajayia\empldr.txt
    Bad File: c:\users\ajayia\empldr.bad
    Discard File: c:\users\ajayia\empldr.dsc
    (Allow all discards)

    Number to load: ALL
    Number to skip: 0
    Errors allowed: 50
    Continuation: none specified
    Path used: Direct

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

    Column Name Position Len Term Encl Datatype



    --------------------------------------------------------------------------------
    ----------
    --------------------------------------------------------------------------------
    ---- ----
    --------------------------------------------------------------------------------
    EMPNO 1:4 4 CHARACTER
    NULL if EMPNO = BLANKS
    ENAME 6:15 10 CHARACTER
    JOB 17:25 9 CHARACTER
    MGR 27:30 4 CHARACTER
    NULL if MGR = BLANKS
    HIREDATE 32:39 8 DATE DD-MON-YYYY
    NULL if HIREDATE = BLANKS
    SAL 41:48 8 CHARACTER
    NULL if SAL = BLANKS
    COMM 50:57 8 CHARACTER
    NULL if COMM = BLANKS
    DEPTNO 59:61 3 CHARACTER
    NULL if DEPTNO = BLANKS

    Record 1: Rejected - Error on table EMPLDR, column HIREDATE.
    ORA-01847: day of month must be between 1 and last day of month

    Record 2: Rejected - Error on table EMPLDR, column HIREDATE.
    ORA-01847: day of month must be between 1 and last day of month

    Record 3: Rejected - Error on table EMPLDR, column HIREDATE.
    ORA-01847: day of month must be between 1 and last day of month

    Record 4: Rejected - Error on table EMPLDR, column HIREDATE.
    ORA-01847: day of month must be between 1 and last day of month

    Record 5: Rejected - Error on table EMPLDR, column HIREDATE.
    ORA-01847: day of month must be between 1 and last day of month

    Record 6: Rejected - Error on table EMPLDR, column HIREDATE.
    ORA-01847: day of month must be between 1 and last day of month

    Record 7: Rejected - Error on table EMPLDR, column HIREDATE.
    ORA-01847: day of month must be between 1 and last day of month

    Record 8: Rejected - Error on table EMPLDR, column HIREDATE.
    ORA-01847: day of month must be between 1 and last day of month

    Record 9: Rejected - Error on table EMPLDR, column HIREDATE.
    ORA-01840: input value not long enough for date format

    Record 10: Rejected - Error on table EMPLDR, column HIREDATE.
    ORA-01847: day of month must be between 1 and last day of month

    Record 11: Rejected - Error on table EMPLDR, column HIREDATE.
    ORA-01847: day of month must be between 1 and last day of month

    Record 12: Rejected - Error on table EMPLDR, column HIREDATE.
    ORA-01847: day of month must be between 1 and last day of month

    Record 13: Rejected - Error on table EMPLDR, column HIREDATE.
    ORA-01847: day of month must be between 1 and last day of month

    Record 14: Rejected - Error on table EMPLDR, column HIREDATE.
    ORA-01847: day of month must be between 1 and last day of month

    Table EMPLDR:
    0 Rows successfully loaded.
    14 Rows not loaded due to data errors.
    0 Rows not loaded because all WHEN clauses were failed.
    0 Rows not loaded because all fields were null.

    Bind array size not used in direct path.
    Column array rows : 5000
    Stream buffer bytes: 256000
    Read buffer bytes: 1048576

    Total logical records skipped: 0
    Total logical records read: 14
    Total logical records rejected: 14
    Total logical records discarded: 0
    Total stream buffers loaded by SQL*Loader main thread: 2
    Total stream buffers loaded by SQL*Loader load thread: 0

    Run began on Tue Aug 24 16:07:35 2010
    Run ended on Tue Aug 24 16:07:42 2010

    Elapsed time was: 00:00:07.47
    CPU time was: 00:00:00.17
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your data is not formatted in fixed-length records; you cannot use positional notation in your SQL*Loader control file because position 43 in each record will not return the proper data. Your .dat file should look like this:

    Code (SQL):
    7369 SMITH      CLERK     7902 17-DEC-80 800               20
    7499 ALLEN      SALESMAN  7698 20-FEB-81 1600     300      30
    7521 WARD       SALESMAN  7698 22-FEB-81 1250     500      30
    7566 JONES      MANAGER   7839 02-APR-81 2975              20
    7654 MARTIN     SALESMAN  7698 28-SEP-81 1250     1400     30
    7698 BLAKE      MANAGER   7839 01-MAY-81 2850              30
    7782 CLARK      MANAGER   7839 09-JUN-81 2450              10
    7788 SCOTT      ANALYST   7566 19-APR-87 3000              20
    7839 KING       PRESIDENT      17-NOV-81 5000              10
    7844 TURNER     SALESMAN  7698 08-SEP-81 1500     0        30
    7876 ADAMS      CLERK     7788 23-MAY-87 1100              20
    7900 JAMES      CLERK     7698 03-DEC-81 950               30
    7902 FORD       ANALYST   7566 03-DEC-81 3000              20
    7934 MILLER     CLERK     7782 23-JAN-82 1300              10
     
     
    and your control file is wrong on the SAL starting position as it should be 42, not 41.

    Correcting your data and your control file I was able to successfully load your data into the EMP table:

    Code (SQL):
     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
    7369 SMITH CLERK 7902 17-DEC-08 800 20
    7499 ALLEN SALESMAN 7698 20-FEB-08 1600 300 30
    7521 WARD SALESMAN 7698 22-FEB-08 1250 500 30
    7566 JONES MANAGER 7839 02-APR-08 2975 20
    7654 MARTIN SALESMAN 7698 28-SEP-08 1250 1400 30
    7698 BLAKE MANAGER 7839 01-MAY-08 2850 30
    7782 CLARK MANAGER 7839 09-JUN-08 2450 10
    7788 SCOTT ANALYST 7566 19-APR-08 3000 20
    7839 KING PRESIDENT 17-NOV-08 5000 10
    7844 TURNER SALESMAN 7698 08-SEP-08 1500 0 30
    7876 ADAMS CLERK 7788 23-MAY-08 1100 20
    7900 JAMES CLERK 7698 03-DEC-08 950 30
    7902 FORD ANALYST 7566 03-DEC-08 3000 20
    7934 MILLER CLERK 7782 23-JAN-08 1300 10
     
     
     
  3. Aj_aaron2002@yahoo.co.uk

    Aj_aaron2002@yahoo.co.uk Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    Thanks alot for the response.
    Please can you send your control file to me. I have tried what you suggested but am still getting thesame error.

    My control file looks like this. Please see if i have done anything wrong.


    LOAD DATA

    INFILE 'c:\users\ajayia\empldr.txt'

    BADFILE 'c:\users\ajayia\empldr.bad'

    DISCARDFILE 'c:\users\ajayia\empldr.dsc'
    TRUNCATE
    PRESERVE BLANKS
    -- INSERT
    INTO TABLE empldr
    (empno POSITION(01:04) INTEGER EXTERNAL NULLIF empno=BLANKS,
    ename POSITION(06:15) CHAR,
    job POSITION(17:25) CHAR,
    mgr POSITION(27:30) INTEGER EXTERNAL NULLIF mgr=BLANKS,
    hiredate POSITION(31:39) DATE 'DD-MON-RR' NULLIF hiredate=BLANKS,
    sal POSITION(42:48) INTEGER EXTERNAL NULLIF sal=BLANKS,
    comm POSITION(50:57) INTEGER EXTERNAL NULLIF comm=BLANKS,
    deptno POSITION(59:61) INTEGER EXTERNAL NULLIF deptno=BLANKS)



    Thanks for your support.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That controlfile will not work if you have not reformatted your data file to match the fields with the positions. I provided a reformatted data file that you should be using to achieve success (unfortunately due to formatting in the forum you may need to adjust spacing to provide fixed-length records).

    Please see my previous post.