SQL * LOADER Interview Question and Answers

1.What is SQL*Loader and what is it used for?

            SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database

    -- This is a sample control file

    LOAD DATA

    INFILE 'sample.dat'

    BADFILE 'sample.bad'

    DISCARD FILE 'sample.dsc'

    APPEND

    INTO TABLE emp

    WHEN (57) = '.'

   TRAILING NULLCOLS

2. Is there a SQL*Unloader to download data to a flat file?

            Oracle does not supply any data unload utilities. However, you can use SQL*Plus to select and format your data and then spool it to a file:

set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on

spool oradata.txt

select col1 || ',' || col2 || ',' || col3 from tab1 where  col2 = 'XYZ';

spool off

Alternatively use the UTL_FILE PL/SQL package:

rem Remember to update initSID.ora, utl_file_dir='c:\oradata' parameter

declare

            fp utl_file.file_type;

begin

            fp := utl_file.fopen('c:\oradata','tab1.txt','w');

           utl_file.putf(fp, '%s, %s\n', 'TextField', 55);

           utl_file.fclose(fp);

end;

/

3. BAD File in Sql * loader?

BAD File contains the records which are rejected either by SQL Loader or by the database because of the bad formatting or data type mismatch.

4. DISCARD File in Sql * loader?

DISCARD file is to store the records that are neither inserted into table nor rejected as bad. This is an optional parameter with SQL Loader and by default name would be .dsc.

depends on  filtration.

5. What is TRAILING NULLCOLS?

The TRAILING NULLCOLS clause causes SQL*Loader to treat any fields that are missing at the end of a record as null. 

If the last field does not contain data, during the loading (inserting data), this clause (TRAILING NULLCOLS) says to load a 'null value’.

6.Loading positional (fixed length) data

If you need to load positional data (fixed length), look at the following control file example:

LOAD DATA

  INFILE *

  INTO TABLE load_positional_data

  (  data1 POSITION(1:5),

     data2 POSITION(6:15)

  )

BEGINDATA

11111AAAAAAAAAA

22222BBBBBBBBBB

7. Load Images, Sound Clips and Documents

CREATE TABLE image_table (

       image_id   NUMBER(5),

       file_name  VARCHAR2(30),

       image_data BLOB);

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

8. How to stop after 1000 records.

Load=1000 is used.