sql loader interview questions

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
Append/Repalce/Insert into 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
Append/Replace/Insert into 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
Append/Repalce/Insert into 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 = Control= Data=

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.

Can one load variable and fix length data records?

Yes, look at the following control file examples. In the first we will load delimited data (variable length):

LOAD DATA
INFILE *
INTO TABLE load_delimited_data
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS (data1, data2)
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"

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

Can one skip header records load while loading?

Use the "SKIP n" keyword, where n = number of logical rows to skip. Look at this example:
LOAD DATA
INFILE *
INTO TABLE load_positional_data
SKIP 5(data1 POSITION(1:5), data2 POSITION(6:15))
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB

Can one modify data as it loads into the database?

Data can be modified as it loads into the Oracle Database. Note that this only applies for the conventional load path and not for direct path loads.
LOAD DATA
INFILE *
INTO TABLE modified_data( rec_no "my_db_sequence.nextval", region CONSTANT '31', time_loaded "to_char(SYSDATE, 'HH24:MI')", data1 POSITION(1:5) ":data1/100", data2 POSITION(6:15) "upper(:data2)", data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')" )
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112

LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","(addr, city, state, zipcode, mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)", mailing_city "decode(:mailing_city, null, :city, :mailing_city)", mailing_state)

Can one load data into multiple tables at once?

Look at the following control file:
LOAD DATA
INFILE *
REPLACE
INTO TABLE emp WHEN empno != ' ' ( empno POSITION(1:4) INTEGER EXTERNAL, ename POSITION(6:15) CHAR, deptno POSITION(17:18) CHAR, mgr POSITION(20:23) INTEGER EXTERNAL )
INTO TABLE proj WHEN projno != ' ' ( projno POSITION(25:27) INTEGER EXTERNAL, empno POSITION(1:4) INTEGER EXTERNAL )

Can one selectively load only the records that one need?

Look at this example, (01) is the first character, (30:37) are characters 30 to 37:
LOAD DATA
INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_tableWHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'(region CONSTANT '31', service_key POSITION(01:11) INTEGER EXTERNAL, call_b_no POSITION(12:29) CHAR )

Can one skip certain columns while loading data?

One cannot use POSTION(x:y) with delimited data. Luckily, from Oracle 8i one can specify
FILLER columns. FILLER columns are used to skip columns/fields in the load file, ignoring fields that one does not want. Look at this example:

LOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ',' ( field1, field2 FILLER, field3 )

How does one load multi-line records?

One can create one logical record from multiple physical records using one of the following two clauses:

CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.

CONTINUEIF - use if a condition indicates that multiple records should be treated as one.
Eg. by having a '#' character in column 1

How can get SQL*Loader to COMMIT only at the end of the load file?

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

Can one improve the performance of SQL*Loader?

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. Refer to chapter 8 on Oracle server Utilities manual.

Turn off database logging by specifying the UNRECOVERABLE option. This option can only be used with direct data loads.

Run multiple load jobs concurrently.

What is the difference between the conventional and direct path loader?

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 Utilities Users Guide.
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.