Interview Questions - SQL*Loader

SQL*LOADER:

Qns: What is SQL*LOADER

Ans: SQL*LOADER utility is used to transfer data from Flat file to Temporary tables.

For example we Used SQL*Loader mainly in Interface design to load data from flat file to Temporary table.

Qns: What is syntax of Control file?

Ans:

load data

INFILE <Path of File>

Append/Repalce/Insert into TABLE <Name of Table>

fields terminated by ','

optionally enclosed by " "

TRAILING NULLCOLS

(

COLUMN1,

COLUMN2,

.

.

.

COLUMN….N

)

Qns : How will you give position in fixed Files.

Ans: Column Name POSITION(1:2)

Like this.

load data

INFILE <Path of File>

Append/Replace/Insert into TABLE <Name of Table>

(

COLUMN1 POSITION(1:2),

COLUMN2 POSITION (3:6),

.

.

.

COLUMN….N

)

Qns :You have 100 records in flat file.if 99 records goes in temp table and One Record fails. Whats will happen to that Record file

Ans: That Record Goes into Bad File generated by SQL*Loader.

Qns: What is difference between Badfile, Logfile and Discard files.

Ans:

• Bad file: Record with error goes into Bad file.

• Log file: History of all records like number of records are loaded, number of record fails validations.

• Discard File: Records which fails in When Clause.

Qns: How will you Give Constant values to SQL*LOADER.

Ans : Column Name CONSTANT ‘AUD’

For Example:

load data

INFILE <Path of File>

Append/Repalce/Insert into TABLE <Name of Table>

fields terminated by ','

optionally enclosed by " "

TRAILING NULLCOLS

(

COLUMN1 CONSTNAT AUD’,

COLUMN2,

.

.

.

COLUMN….N

)

Qns : Whats is syntax of executing SQL*LOADER.

Ans : Sqlldr userid = <username/password@databasename> Control=<Path of Control File> Data= <Name of Data file>

Qns : SQL*Loader is auto commit or Not?

Ans: Yes Sql*loader is auto commit . because it end is give message Commit Point reached.

Qns : How will you register SQL*LOADER AS a Concurrent Programs.

Ans :

• Define executable by giving Execution Method as SQL*Loader.

• Define Concurrent Programs

• Attached Concurrent Program to the Request Group of appropriate Responsibility.

Qns: Where you placed Control file on server.

Ans: In Appropriate top bin Folder. For example if Control File is of Oracle Payable module then Control file should be placed in $AP_TOP/bin Folder.

Qns : In Which Mode You Placed Control Files on server.

Ans: ASCII mode

Qns: Whats the Common Error you faced during loading data from flat file to Temporary tables.

Ans: Error like.

• For insert Option your table should be empty.

• Started with “ but not ended with “

Qns : Can you Load data into Multiple tables with the help of SQL*Loaders.

Ans : Yes by giving condition in when clause.

Related Articles to Read