Sql Loader Interview Questions & Answers

4 avg. rating (80% score) - 2 votes

Sql Loader Interview Questions & Answers

The SQL Loader is designed as a data loading utility that moves the data from other/external location to relational database. SQL*Loader would support various load formats, selective loading, and multi-table loads. Several companies use it for taking advantage of cost effective, linear storage processing. One can check the availability of the job across cities including Mumbai, Chennai, Delhi, Bangalore, Pune and Hyderabad. SQL Loader role consists of handling multiple data formats, table loads and configuration of Oracle database. Wisdomjobs has interview questions which are exclusively designed for job seekers to assist them in clearing job interviews. SQL Loader interview questions and answers are useful for administrators to attend job interviews and get selected for SQL Loader job position.

Sql Loader Interview Questions

Sql Loader Interview Questions
    1. Question 1. What Is Sql*loader And What Is It Used For?

      Answer :

      SQL Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.

      SQL Loader (sqlldr) is the utility to use for high performance data loads. The data can be loaded from any text file and inserted into the database.

    2. Question 2. How Can One Get Sql*loader To Commit Only At The End Of The Load File?

      Answer :

      One cannot, but by setting the ROWS= parameter to a large value, committing can be reduced. Make sure you have big rollback segments ready when you use a high value for ROWS.

    3. Question 3. Can One Improve The Performance Of Sql*loader?

      Answer :

      • A very simple but easily overlooked hint is not to have any indexes and/or constraints (primary key) on your load tables during the load process. This will significantly slow down load times even with ROWS= set to a high value.
      • Add the following option in the command line: DIRECT=TRUE. This will effectively bypass most of the RDBMS processing. However, there are cases when you can't use direct load. For details, refer to the FAQ about the differences between the conventional and direct path loader below.
      • Turn off database logging by specifying the UNRECOVERABLE option. This option can only be used with direct data loads.
      • Run multiple load jobs concurrently.

    4. Question 4. What Is The Difference Between The Conventional And Direct Path Loader?

      Answer :

      • The conventional path loader essentially loads the data by using standard INSERT statements. The direct path loader (DIRECT=TRUE) bypasses much of the logic involved with that, and loads directly into the Oracle data files. More information about the restrictions of direct path loading can be obtained from the Oracle Server Utilities Guide.
      • Some of the restrictions with direct path loads are:
      • Loaded data will not be replicated
      • Cannot always use SQL strings for column processing in the control file.

    5. Question 5. How Does One Use Sql*loader To Load Images, Sound Clips And Documents?

      Answer :

      SQL*Loader can load data from a "primary data file", SDF (Secondary Data file - for loading nested tables and VARRAYs) or LOBFILE. The LOBFILE method provides an easy way to load documents, photos, images and audio clips into BLOB and CLOB columns. Look at this example:

      Given the following table:

      CREATE TABLE image_table (

             image_id   NUMBER(5),

             file_name  VARCHAR2(30),

             image_data BLOB);

      Control File:

      LOAD DATA

      INFILE *

      INTO TABLE image_table

      REPLACE

      FIELDS TERMINATED BY ','

      (

       image_id   INTEGER(5),

       file_name  CHAR(30),

       image_data LOBFILE (file_name) TERMINATED BY EOF

      )

      BEGINDATA

      001,image1.gif

      002,image2.jpg

      003,image3.jpg

    6. Question 6. How Does One Load Ebcdic Data?

      Answer :

      Specify the character set WE8EBCDIC500 for the EBCDIC data.

      The following example shows the SQL*Loader controlfile to load a fixed length EBCDIC record into the Oracle Database:

      LOAD DATA

      CHARACTERSET WE8EBCDIC500

      INFILE data.ebc "fix 86 buffers 1024"

      BADFILE data.bad'

      DISCARDFILE data.dsc'

      REPLACE

      INTO TABLE temp_data

      (

       field1    POSITION (1:4)     INTEGER EXTERNAL,

       field2    POSITION (5:6)     INTEGER EXTERNAL,

       field3    POSITION (7:12)    INTEGER EXTERNAL,

       field4    POSITION (13:42)   CHAR,

       field5    POSITION (43:72)   CHAR,

       field6    POSITION (73:73)   INTEGER EXTERNAL,

       field7    POSITION (74:74)   INTEGER EXTERNAL,

       field8    POSITION (75:75)   INTEGER EXTERNAL,

       field9    POSITION (76:86)   INTEGER EXTERNAL

    7. Question 7. What Is The Sql*loader?

      Answer :

      SQL Loader is a tool to lead data from file to a database table.

    8. Question 8. What Is The Difference Between The Conventional And Direct Path Loads?

      Answer :

      • The direct path load loads data directly into datafiles while conventional path load uses standard insert statements.
      • There are a few restrictions with direct path loads. The data loaded using direct path does not replicate.

    9. Question 9. What Is The Difference Between The Sql*loader And Import Utilities?

      Answer :

      • Import and SQL*Loader both can be used to load data in a database.
      • However, import works in combination with export and can read files generated by export only.

    10. Question 10. Can You Load Data Into Multiple Tables At Once?

      Answer :

      Yes.

    11. Question 11. How You Improve The Performance Of Sql*loader?

      Answer :

      • You can use direct path load to improve the performance.
      • Indexes and constraints make inserts slow. Removing indexes and constraints improve performance of inserts; and therefore, of SQL*Loader.

    12. Question 12. How Can You Load Microsoft Excel Data Into Oracle?

      Answer :

      You can save the data in text file with proper separators from Microsoft Excel.

    13. Question 13. Can You Skip Header Records While Loading?

      Answer :

      You can use the SKIP parameter to skip number of records. In addition, you can use SKIP = 1 to skip the header record.

    14. Question 14. How Can You Load Multi Line Records?

      Answer :

      • You can use the CONCATENATE or CONTINUEIF function to join multiple physical records to form a single logical record.
      • However, CONTINUEIF is used if a condition indicates that multiple records should be treateed as one. For example, a # character in the first column.

    15. Question 15. How Can You Get Sql*loader To Commit Only At The End Of The Load File?

      Answer :

      You cannot ensure a commit only at the end of the load file but you can increase the value of ROWS parameter to ensure the commit after certain number of rows.

    16. Question 16. Can You Selectively Load Only Those Records That You Need?

      Answer :

      Yes, you can use the WHEN clause to specify the selection criteria. However, it does not allow you to use the OR clause; instead, you can only use the AND clause.

    17. Question 17. How Does Sql*loader Handles Newline Characters In A Record?

      Answer :

      SQL*Loader expects a record to be in a single line; therefore, whenever it encounters a newline character in a record, it treats the record as a new record and either throws an error bases on the constraints of a table or inserts erroreous records without throwing any error.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Database Tutorial