Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

Oracle SQL*Loader – Working with Discarded and Rejected Records

By Exforsys | on March 30, 2011 |
Oracle 10g

In this tutorial you will learn how to use the options in SQL*Loader for generating Bad File, SQL*Loader Rejects, Oracle Database Rejects, Discard File, Log File and Logging Information.

Consider a scenario where you have a data file with city, states and zip codes. You are interested in loading data only for one particular state and reject any record which does not have a zip code. Such records which you are not interested can be in the discard file.

SQL*Loader rejects records with missing quotes and Oracle database rejects records when a piece of information is missing but is required as per the table definition or constraints, such records will be written to the bad file.

In order to skip the records using discard file option, you will need to enter the criteria in the control file which I will discuss as part of an example walkthrough.

SQL*Loader creates the log file at the specified location or working directory where you are running the process with details about the load along with execution steps, records counts and any error messages.

Working with Discard and Bad files using Command prompt

Let me get walk you through with a simple example to see how we can put these options together.

Create a table called zipcode to start with 

  1.  CREATE TABLE &quot;EXFORSYS&quot;.&quot;ZIPCODE&quot;<br />
  2. (&quot;ZIPCODE&quot; NUMBER(5,0) NOT NULL ENABLE,<br />
  3. &quot;CITY&quot; VARCHAR2(20 BYTE),<br />
  4. &quot;STATE&quot; VARCHAR2(2 BYTE),<br />
  5. CONSTRAINT &quot;ZIPCODE_PK&quot;<br />
  6. PRIMARY KEY (&quot;ZIPCODE&quot;));

Sample data – copy and save the the text file as zipcode.dat

  1. MA,1138,Springfield<br />
  2. MA,1139,Springfield<br />
  3. MA,1144,Springfield<br />
  4. MA,1151,Indian Orchard<br />
  5. MA,1152,Springfield<br />
  6. MA,1199,Springfield<br />
  7. NJ,7670,Tenafly<br />
  8. NJ,7675,Westwood<br />
  9. NJ,7676,Ho-Ho-Kus<br />
  10. NJ,7677,Woodcliff Lake<br />
  11. NJ,7688,Teaneck<br />
  12. NJ,7701,Red Bank<br />
  13. NJ,7702,Shrewsbury<br />
  14. NY,10174,NEW York<br />
  15. NY,10175,NEW York<br />
  16. NY,10176,NEW York<br />
  17. NY,10177,NEW York<br />
  18. NY,10178,NEW York<br />
  19. NY,10179,NEW York<br />
  20. NY,10184,NEW York<br />
  21. NY,,NEW York<br />
  22. ,10186,NEW York<br />
  23. ,10187,NEW York <br />
  24. ,10188,NEW York

Here is the control file we are going to use to load the data. Copy and save the file as zipcode.ctl

  1. LOAD DATA&nbsp; INFILE 'ZIPCODE.DAT'<br />
  2. BADFILE 'ZIPCODE.BAD'<br />
  3. DISCARDFILE 'ZIPCODE.DIS'<br />
  4. INSERT INTO TABLE ZIPCODE<br />
  5. WHEN STATE&nbsp; = 'NY'<br />
  6. FIELDS TERMINATED BY ','<br />
  7. (STATE, ZIPCODE,CITY)

INFILE ‘ZIPCODE.DAT’ – This is the input file name
BADFILE ‘ZIPCODE.BAD’ – This is the rejected or bad records file
DISCARDFILE ‘ZIPCODE.DIS’ – This is the discard file where the condition is not matching. We are looking to load only New York state data.
INSERT INTO TABLE ZIPCODE – There are few options you can use , INSERT/APPEND/REPLACE.
FIELDS TERMINATED BY ‘,’ – Fields are terminated by comma in the data file
(STATE, ZIPCODE,CITY) – Column names

To load from command prompt:
 
Change to the directory where you have saved zipcode.dat and zipcode.ctl files.
 
Run the following command.

  1.  SQLLDR USERNAME/PASSWORD@SERVICENAME CONTROL=CONTROLFILENAME

Here is the data from zipcode table. As per the condition “WHEN STATE = ‘NY’”, we are interested to load only the NY records.

Now, take a look for the ZIPCODE.LOG log file in working directory where you have run the process.
 
Table ZIPCODE:
7 Rows successfully loaded.
1 Row not loaded due to data errors.
16 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
 
Record 21: Rejected – Error on table ZIPCODE, column ZIPCODE.
 
NY,,New York – This is the record 21 in the sample data, ZIPCODE value is not there and it’s required as per our table definition “"ZIPCODE" NUMBER(5,0) NOT NULL”.
 
Rest of the records are not matching condition “WHEN STATE = ‘NY’“. In few cases, STATE value is not present.

  1. Record 1: Discarded - failed ALL WHEN clauses. <br />
  2. Record 2: Discarded - failed ALL WHEN clauses. <br />
  3. Record 3: Discarded - failed ALL WHEN clauses. <br />
  4. Record 4: Discarded - failed ALL WHEN clauses. <br />
  5. Record 5: Discarded - failed ALL WHEN clauses. <br />
  6. Record 6: Discarded - failed ALL WHEN clauses. <br />
  7. Record 7: Discarded - failed ALL WHEN clauses. <br />
  8. Record 8: Discarded - failed ALL WHEN clauses. <br />
  9. Record 9: Discarded - failed ALL WHEN clauses. <br />
  10. Record 10: Discarded - failed ALL WHEN clauses. <br />
  11. Record 11: Discarded - failed ALL WHEN clauses.<br />
  12. Record 12: Discarded - failed ALL WHEN clauses.<br />
  13. Record 13: Discarded - failed ALL WHEN clauses. <br />
  14. Record 22: Discarded - failed ALL WHEN clauses. <br />
  15. Record 23: Discarded - failed ALL WHEN clauses. <br />
  16. Record 24: Discarded - failed ALL WHEN clauses.

Let’s take a look at the discard and rejected files. As per the log file, there should be 16 discarded records and 1 bad record.

File contents of bad record file – ‘ZIPCODE.BAD’

  1.  NY,,NEW York

File contents of discard file – ‘ZIPCODE.DIS’

  1. MA,1138,Springfield <br />
  2. MA,1139,Springfield <br />
  3. MA,1144,Springfield <br />
  4. MA,1151,Indian Orchard <br />
  5. MA,1152,Springfield <br />
  6. MA,1199,Springfield <br />
  7. NJ,7670,Tenafly <br />
  8. NJ,7675,Westwood <br />
  9. NJ,7676,Ho-Ho-Kus <br />
  10. NJ,7677,Woodcliff Lake <br />
  11. NJ,7688,Teaneck <br />
  12. NJ,7701,Red Bank <br />
  13. NJ,7702,Shrewsbury <br />
  14. ,10186,NEW York <br />
  15. ,10187,NEW York <br />
  16. ,10188,NEW York

Here are the input and result files from the above run.

 

 

Working with Discard and Bad files using OEM

Login in to OEM, Select “Load Data from User Files” from the “Data Movement” menu.

Select “Use Existing Control File” and click on Continue

You will see a new window, select the control file. In our example, zipcode.ctl

Click next and select the data file you would like to use

We are going to use the “Conventional Path” load method.

The next screen will prompt for several options as shown below. For practice purpose, we are going to fill the options for bad, discard and log file parameters with where you would like those files to be created.

After filling up the Job Parameters, you can also set other Job Schedule options such as starting time and job repeat. We will be just submitting the job immediately.

 Here is the summary of the job along with the parameters and control file locations that we have entered.

Finally, let us verify the data now as shown in screen below

You can see the records (7 Rows are fetched) list in the Query Result section.

There are several other advanced options based on the amount of data and the format of the input files. Hope this article has provided you the basic knowledge to get started with SQL*Loader. Once you get the basic idea of how SQL*Loader works, I would recommend that you read the Oracle product documentation based on the version you will be using.

« « What is Ad Hoc Testing
Oracle 11g PL/SQL New Features » »

Author Description

Avatar

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Free Training

RSSSubscribe 0 Followers
  • Popular
  • Recent
  • Oracle 10g Installation Guide on Windows 7

    May 11, 2005 - 0 Comment
  • Working with Oracle Listener

    June 2, 2005 - 0 Comment
  • Oracle Data Pump Import

    July 22, 2005 - 0 Comment
  • Oracle Data Pump Export

    July 23, 2005 - 0 Comment
  • SQL*Loader – Loading Data from Data Files

    July 25, 2005 - 0 Comment
  • SQL*Loader – Loading Data from Data Files

    July 25, 2005 - 0 Comment
  • Oracle Data Pump Export

    July 23, 2005 - 0 Comment
  • Oracle Data Pump Import

    July 22, 2005 - 0 Comment
  • Working with Oracle Listener

    June 2, 2005 - 0 Comment
  • Oracle 10g Installation Guide on Windows 7

    May 11, 2005 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • SQL*Loader – Loading Data from Data Files
  • Oracle Data Pump Export
  • Oracle Data Pump Import
  • Working with Oracle Listener
  • Oracle 10g Installation Guide on Windows 7

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT