SQL Loader Mostly asked Interview questions

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. 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.



2. How does one use the SQL*Loader utility?



One can load data into an Oracle database by using the sqlldr (sqlload on some platforms) utility. Invoke the utility without arguments to get a list of available parameters.

Look at the following example:

sqlldr scott/tiger control=loader.ctl



This sample control file (loader.ctl) will load an external data file containing delimited data:

load data

infile 'c:\data\mydata.csv'

into table emp ( empno, empname, sal, deptno )

fields terminated by "," optionally enclosed by '"'



The mydata.csv file may look like this:

10001,"Scott Tiger", 1000, 40

10002,"Frank Naude", 500, 20



Another Sample control file with in-line data formatted as fix length records. The trick is to specify "*" as the name of the data file, and use BEGINDATA to start the data section in the control file.

load data

infile *

replace

into table departments ( dept position (02:05) char(4), deptname position (08:27) char(20) )

begindata

COSC COMPUTER SCIENCE

ENGL ENGLISH LITERATURE

MATH MATHEMATICS

POLY POLITICAL SCIENCE



3. 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;

/



You might also want to investigate third party tools like TOAD or ManageIT Fast Unloader from CA to help you unload data from Oracle.