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.
You can use the SKIP parameter to skip number of records. In addition, you can use SKIP = 1 to skip the header record.
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
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.
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.