Explore topic-wise InterviewSolutions in .

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

Yes.

Yes.

SQL Loader is a tool to lead data from FILE to a DATABASE table.

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

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

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

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

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

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

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.