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
 

SQL*Loader – Loading Data from Data Files

By Exforsys | on July 25, 2005 |
Oracle 10g

In this tutorial you will learn about SQL*Loader – Input Data and Datafiles, Fixed Record Format, Variable Record Format and Stream Record Format.

SQL*Loader is useful when you need to load the files in batch mode. SQL* Loader supports three different type of data files. You will need to specify the “INFILE” parameter with the file format and additional parameters required.

Fixed Record Format: This format is useful when you have a data file with fixed layout.

Variable Record Format: This format is used when you have different record lengths in the data file. You will need to specify the record length in the beginning of the each record. This format provided greater flexibility to have the data loaded compared to fixed record formatted files.

Stream Record Format: This format is used when the records are not in the fixed or specified size. Each record can be any length and records will be identified by using the record terminator.

You can specify terminator_string either in character or hexadecimal format. Char is enclosed in single or double quotes and hexadecimal should be used when nonprintable characters like new line feed or tab characters.

There are few types of hex characters which can be used. Please note that these may change based on the operating system you are using. In Unix/Linux based systems, the default to the line feed character will be n and Windows uses either n or rn as the default record terminator. Just to avoid any issues with various character sets, you may want to check NLS_LANG parameters for your session. Check to make sure that your record terminator is not part of the data record.

There are various options available, please use “sqlldr” for the parameters and usage.

Now let us take a look at the loading some sample data to a table using command prompt and OEM interface.

}
  • Log into OEM. (Note: If you have followed default installation with starter database, you will have a link in Oracle menu to “Database Control – “database name”. )
  • Login and select Data Movement,


  • Select Load Data from user file option. (If you have a control file already then you can use or select "Automatically Generate Control File" option).

In this tutorials we are going to select the first option, then use the generated control file to load the data using command prompt.

Oracle needs access to the host, enter the server login and password, if you prefer you can check “Save as Preferred Credentials” otherwise leave it unchecked.

Step 1 – Load Data: Data Files


Here is the sample file we are going to use.
  1. FirstName1,LastName1,Address1,City1,Country1
  2. FirstName2,LastName2,Address2,City2,Country2
  3. FirstName3,LastName3,Address3,City3,Country3
  4. FirstName4,LastName4,Address4,City4,Country4

Step 2 – Load Data: Table and File Format

Enter Database name, Table Name, if you need to create select “ Create new table” option or just enter the table name which is already there.

  1. CREATE TABLE customer
  2. (First_Name CHAR(50),
  3. Last_Name CHAR(50),
  4. Address CHAR(50),
  5. City CHAR(50),
  6. Country CHAR(25));

Step 3 – Character Delimiters

Here you can change the settings. In our care Field delimiter is comma and optional filed enclosure is double quotes.


Verify the setting and click next to continue with Step 4

Step 4 – Load Data: Load Method

There are various methods you can use to load the data and it depends on the need and how much data you are loading . We will be discussing these methods in details later. For simplicity we are going to use Conventional path method.

Step 5 – Load Data: Options

If you would like any records to written to the rejected file, select Bad file option and enter the path for the file to be generated. Keep in mind all of these paths related to the server not your local PC.

Step 6 – Load Data: Schedule

If you would like to schedule the job to run later date, you can use this step else click next

Step 7 – Review 


Verify the setting, submit job

Click on the Job link to see the status

Control file created

  1. LOAD DATA
  2. INFILE 'D:APPEXFORSYSORADATAEXFORSYSexample1.dat' "STR 'rn'"
  3. APPEND
  4. INTO TABLE customer
  5. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  6. (
  7. FIRST_NAME CHAR,
  8. LAST_NAME CHAR,
  9. ADDRESS CHAR,
  10. CITY CHAR,
  11. COUNTRY CHAR
  12. )

If you see the following error during the job submission , Here are few things you will need to verify

Make sure service is running


You will need to run the following command

Logon as SYSMAN and run

  1. EXECUTE MGMT_USER.MAKE_EM_USER(‘username’);

username is the username that you are using to load the data.

After you complete the above, return back and continue the same step again where you have received the error


{mospagebreak title= SQL*Loader – Loading data from Command Prompt}

Login to SQL Plus to remove the data we have loaded from OEM.

Launch Command Prompt

  1. sqlldr username/password@dbname control=commandload.ctl


Here is the copy of the control file used in the example.
  1. LOAD DATA
  2. INFILE 'E:oraclesqlloadercommandload.dat' "STR 'rn'"
  3. APPEND
  4. INTO TABLE customer
  5. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  6. (
  7.   FIRST_NAME CHAR,
  8.   LAST_NAME CHAR,
  9.   ADDRESS CHAR,
  10.   CITY CHAR,
  11.   COUNTRY CHAR
  12. )

Here is the logfile generated from the above demo.
  1. SQL*Loader: Release 11.2.0.1.0 - Production ON Sun Mar 6 13:19:31 2011
  2.  
  3. Copyright (c) 1982, 2009, Oracle AND/OR its affiliates.  ALL rights reserved.
  4.  
  5. Control File:   commandload.ctl
  6. DATA File:      E:oraclesqlloadercommandload.dat
  7.   File processing OPTION string: "STR 'rn'"
  8.   Bad File:     commandload.bad
  9.   Discard File:  NONE specified
  10.  
  11.  (Allow ALL discards)
  12.  
  13. NUMBER TO LOAD: ALL
  14. NUMBER TO skip: 0
  15. Errors allowed: 50
  16. Bind array:     64 ROWS, maximum OF 256000 bytes
  17. Continuation:    NONE specified
  18. Path used:      Conventional
  19.  
  20. TABLE CUSTOMER, loaded FROM every logical record.
  21. INSERT OPTION IN effect FOR this TABLE: APPEND
  22.  
  23.    COLUMN Name                  POSITION   Len  Term Encl Datatype
  24. ------------------------------ ---------- ----- ---- ---- ---------------------
  25. FIRST_NAME                          FIRST     *   ,  O(") CHARACTER            
  26. LAST_NAME                            NEXT     *   ,  O(") CHARACTER            
  27. ADDRESS                              NEXT     *   ,  O(") CHARACTER            
  28. CITY                                 NEXT     *   ,  O(") CHARACTER            
  29. COUNTRY                              NEXT     *   ,  O(") CHARACTER            
  30.  
  31.  
  32. Table CUSTOMER:
  33.   4 Rows successfully loaded.
  34.   0 Rows not loaded due to data errors.
  35.   0 Rows not loaded because all WHEN clauses were failed.
  36.   0 Rows not loaded because all fields were null.
  37.  
  38.  
  39. Space allocated for bind array:                  82560 bytes(64 rows)
  40. Read   buffer bytes: 1048576
  41.  
  42. Total logical records skipped:          0
  43. Total logical records read:             4
  44. Total logical records rejected:         0
  45. Total logical records discarded:        0
  46.  
  47. Run began on Sun Mar 06 13:19:31 2011
  48. Run ended on Sun Mar 06 13:19:31 2011
  49.  
  50. Elapsed time was:     00:00:00.05
  51. CPU time was:         00:00:00.03 )
« « Registering New Forms in Oracle Apps 11i
What’s New in the .NET Framework 2.0 ? » »

Author Description

Avatar

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

Free Training

RSSSubscribe 394 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
  • Oracle SQL*Loader – Working with Discarded and Rejected Records

    March 30, 2011 - 0 Comment
  • Oracle SQL*Loader – Working with Discarded and Rejected Records

    March 30, 2011 - 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

  • Oracle SQL*Loader – Working with Discarded and Rejected Records
  • 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