2 Replies Latest reply: Dec 11, 2012 3:51 AM by 977838 RSS

    Sql Loader Error

    977838
      Hi

      I am executing owb mapping which extracts the file and loads into table also have few filter conditions.

      whenever there are discards, mapping is giving warnings. Can we skip them as Job should get success.

      Thanks
        • 1. Re: Sql Loader Error
          David Allan-Oracle
          Hi

          There is a configuration property - Errors Allowed, that has a default value of 50, you can change this (this gets generated as the SQLLoader ERRORS= values in the OPTIONS statement for the SQLLoader control file. You can also set the configuration value for Suppress Errors/Feedback to true, its default value is false, this sets the SILENT property in the SQLLoader control file.

          Cheers
          David
          • 2. Re: Sql Loader Error
            977838
            Thanks for your reply.

            I tried the same, but get it is not getting success as it is discarding all rows because failing in Filter condition.

            My intention is, Mapping should get success even it fails in Filter condition. i don't mind whether it writes into target.

            Below is the log:


            DATAGENERATOR
            Description : Runtime User : REP Started : 2012-12-11 09:48:53.0
            Status
            Log

            SQL*Loader: Release 10.2.0.1.0 - Production on Tue Dec 11 09:48:54 2012

            Copyright (c) 1982, 2005, Oracle. All rights reserved.

            Control File: C:\MSBI\DATAGENERATOR.ctl
            Character Set WE8MSWIN1252 specified for all input.

            Data File: C:\MSBI\DEPARTMENTS.TXT
            File processing option string: "STR X'0A'"
            Bad File: C:\MSBI\BADFILE.TXT
            Discard File: C:\MSBI\DISCARD.TXT
            (Allow 21 discards)

            Number to load: ALL
            Number to skip: 0
            Errors allowed: 50
            Bind array: 200 rows, maximum of 50000 bytes
            Continuation: none specified
            Path used: Conventional
            Silent options: FEEDBACK and ERRORS
            Table "GENERATOR_DATA", loaded when "DNAME" = 0X5245534541524348(character 'RESEARCH')
            Insert option in effect for this table: REPLACE
            TRAILING NULLCOLS option in effect

            Column Name Position Len Term Encl Datatype
            ------------------------------ ---------- ----- ---- ---- ---------------------
            "RECNUM" RECNUM
            "SYSDATE1" SYSDATE
            "SEQUENCE" SEQUENCE (COUNT, 1)
            "SAMPFIELD1" FIRST * , O(X) CHARACTER
            SQL string for column : "1"
            "SAMPFIELD2" NEXT * , O(X) CHARACTER
            SQL string for column : "1"
            "DNAME" 1 20 , O(X) CHARACTER

            value used for ROWS parameter changed from 200 to 85
            Record 1: Discarded - failed all WHEN clauses.
            Record 2: Discarded - failed all WHEN clauses.
            Record 3: Discarded - failed all WHEN clauses.
            Record 4: Discarded - failed all WHEN clauses.
            Record 5: Discarded - failed all WHEN clauses.
            Record 6: Discarded - failed all WHEN clauses.
            Record 7: Discarded - failed all WHEN clauses.
            Record 8: Discarded - failed all WHEN clauses.
            Record 9: Discarded - failed all WHEN clauses.
            Record 10: Discarded - failed all WHEN clauses.
            Record 11: Discarded - failed all WHEN clauses.
            Record 12: Discarded - failed all WHEN clauses.
            Record 13: Discarded - failed all WHEN clauses.

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


            Space allocated for bind array: 49640 bytes(85 rows)
            Read buffer bytes: 65536

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

            Run began on Tue Dec 11 09:48:54 2012
            Run ended on Tue Dec 11 09:48:54 2012

            Elapsed time was: 00:00:00.13
            CPU time was: 00:00:00.08

            SQL*Loader: Release 10.2.0.1.0 - Production on Tue Dec 11 09:48:54 2012

            Copyright (c) 1982, 2005, Oracle. All rights reserved.



            Job Summary
            Updated : 2012-12-11 09:48:54.0 Job Final Status : Completed with warnings Job Processed Count : 1 Job Error Count : 0 Job Warning Count : 0

            Thanks