Top 17 Sql Loader Interview Questions You Must Prepare 27.Jul.2024

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:

LOAD DATA

CHARACTERSET WE8EBCDIC500

INFILE data.ebc "fix 86 buffers 1024"

BADFILE data.bad'

DISCARDFILE data.dsc'

REPLACE

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

You can save the data in text file with proper separators from Microsoft Excel.

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

You can use the SKIP parameter to skip number of records. In addition, you can use SKIP = 1 to skip the header record.

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

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.

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 (

       image_id   NUMBER(5),

       file_name  VARCHAR2(30),

       image_data BLOB);

Control File:

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

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

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

SQL Loader is a tool to lead data from file to a database table.

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.

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.

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.

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

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

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.

Yes.