Question 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.
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.
Question 2. How Can One 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.
Question 3. 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. 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.
Question 4. 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 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.
Question 5. How Does One Use Sql*loader To Load Images, Sound Clips And Documents?
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 (
INTO TABLE image_table
FIELDS TERMINATED BY ‘,’
image_data LOBFILE (file_name) TERMINATED BY EOF
Question 6. How Does One Load Ebcdic Data?
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:
INFILE data.ebc “fix 86 buffers 1024”
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
Question 7. What Is The Sql*loader?
SQL Loader is a tool to lead data from file to a database table.
Question 8. What Is The Difference Between The Conventional And Direct Path Loads?
- 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.
Question 9. What Is The Difference Between The Sql*loader And Import Utilities?
- 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.
Question 10. Can You Load Data Into Multiple Tables At Once?
Question 11. How You Improve The Performance Of Sql*loader?
- 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.
Question 12. How Can You Load Microsoft Excel Data Into Oracle?
You can save the data in text file with proper separators from Microsoft Excel.
Question 13. Can You Skip Header Records While Loading?
You can use the SKIP parameter to skip number of records. In addition, you can use SKIP = 1 to skip the header record.
Question 14. How Can You Load Multi Line Records?
- 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.
Question 15. How Can You Get Sql*loader To Commit Only At The End Of The Load File?
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.
Question 16. Can You Selectively Load Only Those Records That You Need?
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.
Question 17. How Does Sql*loader Handles Newline Characters In A Record?
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.
PL/SQL Interview Questions
Adv Java Tutorial
Adv Java Interview Questions
SQL Database Tutorial
SQL Database Interview Questions
SQL DBA Interview Questions
PL/SQL and Informatica Interview Questions
PL/SQL Interview Questions
Oracle apps Interview Questions
Oracle AOL (Application Object Library) Interview Questions