General / Documentos / Soporte Técnico / Servicios / Tilsor
 
 
 


SQL*Loader: Loading Binary Files Into Oracle Using SQL*Loader

Creation Date: 21-JAN-2000
Last Revision Date: 03-AUG-2000


Loading Binary files into Oracle using SQL*Loader
=================================================

This article will give you examples of loading binary files such as MS Word
documents, images, videos, etc. into Oracle BLOB and/or LONG RAW columns
using the SQL*Loader utility.

In Oracle8 you should load data into the BLOB (Binary Large Object) datatype.
Oracle8 BLOB columns hold up to 4Gb and are easily modifyable using the
DBMS_LOB package. The preferred means of loading binary files into Oracle8
is using the DBMS_LOB.LOADFROMFILE packaged procedure rather than relying
on SQL*Loader. See [NOTE:73787.1]

As a result, this document will focus exclusively on loading data using
SQL*Loader to load binary files into LONG RAW data type columns.

Note: Loading binary data can also be done via Pro*C. An example
of how to do this can be found in [NOTE:96959.1]


Which files can you load?
-------------------------

You can load any type of file. This includes MS-Word documents,
images files (e.g. JPG, GIF, BMP, TIF, PIC, etc.), sound files
(eg. MP3, WAV, AU, etc.), executables and any other file format.

Note: This particular article focuses on non-textual, binary files such as
those described above. However, the same technics would apply to large text
files, although these would preferrable be loaded into CLOB and/or LONG
datatype columns.

The methods described below all limit the SQL*Loader control files to a single
record (file image) per control file. As a rule, this is preferable when dealing
with loading of large individual OS file based data.


Which column datatype should you use for binary files?
------------------------------------------------------

In Oracle7, you must use either the RAW or LONG RAW data type for binary data.
These are the only column data types on which Oracle does not attempt to
perform any NLS character set encoding scheme conversion. They are simply
stored "as is". The differences between the RAW and LONG RAW are as follows:

Data type Maximum size Indexable
--------- ------------ ---------
RAW 255 bytes Yes
LONG RAW 2 Giga bytes No

In other words; you’d use the RAW as your unique key and index it, and the LONG
RAW for the actual data. Now, to have a key as RAW (binary) you must know the
exact layout of your binary file so you know where (and if) there is a value
that you can use as unique key. In other words; you must know exactly where in
the header to find this value. For 3rd party products, you are sometimes able
to get a "technical reference guide" which will give you this information.


SQL*Loader control file processing option for loading binary files
------------------------------------------------------------------


When you define your datafile via the INFILE (or INDDN) keyword, you can also
specify OS specific file processing option(s). The syntax is:

INFILE <filename> <options>

The most common file processing options are as follows:

UNIX: [ "FIX n" | "BLK n" | "STR n" ]
VMS: [ "FIXED=n" | "STREAM=n" | "VAR=n" ]
Windows NT: [ "FIX n" | "VAR n" ]

When loading binary data, you have to use the processing option FIX/FIXED.
FIX/FIXED tells SQL*Loader that we are loading fixed-length records. Even
though you may sometimes be able to successfully load binary files using the
STR/STREAM option, your success will be OS dependent (e.g. whether your OS
uses floating point or native integer numbers in machine bytecode
representations). Therefore, always use FIX/FIXED when loading binary data.

In the examples below, the FIX option has been used. If you are on another
platform, simply replace it with your OS specific keyword, and if not included
in the list above, please refer to your Oracle OS documentation.

SQL*Loader has a limitation on physical (fixed) record length which is 64Kb
(to be exact, it’s actually 65535 bytes!). This basically means that if your
record (for binary files this is the physical file size) is greater than 64Kb
you must load the file as multiple logical records into one physical record
using CONCATENATE and/or PIECED keywords.


SQL*Loader control file
-----------------------

Step 1)
Set the value (length) of your fields to be loaded. The datatype for
binary data in SQL*Loader must be either RAW or GRAPHIC to ensure that Oracle
does not perform any conversion of it. In general, RAW is the datatype to use.
Please refer to the Oracle7 Server Utilities for a full description of the
possible different datatypes to use when loading binary data.

Step 2)
Calculate the value for FIX. It holds the value (in bytes) of 1
record. SQL*Loader will read in up to this number of bytes (regardless of any
possible NL/CR) and threat this as 1 record. In other words; if you have more
records in the file, they all must be of the same length. If your record
length is greater than 64Kb you should read the warning below when loading
files greater than 64Kb.

Step 3)
Calculate the value for BINDSIZE. The default BINDSIZE of 64Kb if
often enough, but you may have to set it manually if loading big files/having
big records. The easiest way to set it, is to perform step 1 and 2 above,
and then set the BINDSIZE to a low value (eg. 1 byte) and attempt the load.
This should return with an error indicating the exact size needed.

For example:

SQL*Loader-600: Specified max. bind size 1 bytes must be 28689 bytes
to hold 1 row.

As per the error, we need to set out BINDSIZE to a value of 28689 bytes.


Loading in "conventional" or "direct" path?
-------------------------------------------

Using direct path is undoubtly a faster way of loading data than the
conventional path. However, when loading binary data it is prone to ORA-600
errors depending on which port you are using, how large the binary
file is, how big your record has been defined as, etc. Therefore, we recommend
using the conventional path load for these type of loads.

Should you ignore this recommendation and decide to load in direct path, you
will have to specify an additional keyword in your controlfile

READBUFFERS n

since the default of 4 buffers is generally not enough. If you set it too low,
the following error will be returned:

ORA-02374: No more slots for read buffer queue.

Bug:390719 - prior to V7.3.3, if you set READBUFERS higher than 63, you will
receive the following error:

ORA-00600: internal error code, arguments: [16060],...


Sample TABLE for the demo loads below
-------------------------------------

We’ll be loading into the sample table BINARY_FILES which is defined as:

CREATE TABLE BINARY_FILES
(
KEY VARCHAR2(40),
BINARY_FILE LONG RAW
);

Since I’m generating my own "KEY", I’ve elected to use a VARCHAR2. If you know
the layout of your binary file, you could store the "KEY" in a RAW as well
(which can be indexed), or use a NUMBER and generate a sequence via SQL*Loader.


Control file for loading a binary file, record/file size < 64Kb
---------------------------------------------------------------

When the file is less or equal to 64Kb (max. length for one physical record
using SQL*Loader), the control file below applies. You can load files of less
than 64Kb in length as 1 physical record without needing to concatenate 64Kb
"blocks" of the file. The field length for BINARY_FILE is the exact
length, and has the same value as FIX. This is because the file only holds 1
record. For example:

Filetype: MS-Word document
Filesize: 28672 bytes (28 Kb)
Control file: OPTIONS (BINDSIZE=28689)
LOAD DATA
INFILE BIN_LOAD.DOC "FIX 28672"
APPEND
INTO TABLE BINARY_FILES
(KEY CONSTANT "MSWord_document",
BINARY_FILE RAW(28672))


Control file for loading a binary file record/file size >= 64Kb
---------------------------------------------------------------

When the files/records are greater than 64Kb, you hit a SQL*Loader limitation
on the max size of physical records. The following error will be returned:

LDR-510: Physical record in data file (%s) is longer than the maximum(64KB)

See <Note.48487.1> for additional information.

You can still load the files/records, but you must concatenate "chunks" (blocks)
of 64Kb (or an approx. multiple of file/record) so that SQL*Loader can cope with
it. This is accomplished by setting the FIX close to (or equal to) a multiple of
the entire file/record size. That is: if the entire file holds 1 record and
FIX = 481078 bytes (see example below) you would calculate the exact
CONCATENATE setting using:

CONCATENATE = CEIL ( recordsize_in_bytes / FIX )

When using the max FIX value, you’d get the following:

( 481078 / 65535 = 7.34 ) resulting in CONCATENATE = 8

Or you can simplify and select a number close to a multiple since SQL*Loader
does recognize when EOF has been reached!

CONCATENATE = ( recordsize_in_bytes / 50000 )

For which you’d get the following:

( 481078 / 50000 = 9.62 ) resulting in CONCATENATE = 10


WARNING: If your file includes more than 1 record (ie. multiple files) and each
record is greater than 64Kb (ie. concatenation is required) your FIX value must
be an exact common divisor for all the files listed in your control file.
Obviously, this is then only possible when your record size is an even number,
or in the extrememly rare case where an existing common divisor exists!
Therefore, it is easier if 1 control file refers to 1 record only!


WARNING: If you have files/records greater than 64Kb and you use the CONCATENATE
option, the field length MUST be big enough to hold the entire file/record. Even
though you specify a FIX value of e.g. 10000 and a field length of only 10,
SQL*Loader will *not* return an error - but only 10 bytes will be inserted and
your stored binary file will be incomplete!


Filetype: Bitmap file
Filesize: 481078 bytes (470 Kb)
Control file: OPTIONS (BINDSIZE=500016)
LOAD DATA
INFILE PAINTB.BMP "FIX 50000"
APPEND
CONCATENATE 10
INTO TABLE BINARY_FILES
(KEY CONSTANT "BMP_01",
BINARY_FILE RAW(481078))


Filetype: Audio Video file (30 seconds)
Filesize: 3713276 bytes (3.5 Mb)
Control file: OPTIONS (BINDSIZE=3713297)
LOAD DATA
INFILE WELCOME3.AVI "FIX 65535"
APPEND
CONCATENATE 57
INTO TABLE BINARY_FILES
(KEY CONSTANT "AVI_01",
BINARY_FILE RAW(3713276))


How do you dump the images to files again?
------------------------------------------

You could use a Pro*C program like in [NOTE:96959.1] for example. All the tests
below has been read via SQL*Loader and dumped again via the Pro*C program and
were successfully re-loaded in the appropriate application. Other options
include OCI, JDBC, ODBC, Oracle Forms, and Oracle Web Server among others.

When moving binary files from platform to platform, you have to make sure you
transfer your files in binary mode. Be aware that certain binary files are
machine dependent and it may not be possible to take the "raw" file and move
it from UNIX to VMS for example. The executable file system entries on VMS
contain additional information that UNIX executable file system entries do not.

Export/import is probably the easiest way to move the binary files from one
database to another database. When export reads a RAW or LONG RAW, it is read
"as is" and import reads "as is". When using export/import, you have to set
your BUFFER parameter according to the LONG sizes that you may be
exporting/importing.


RELATED DOCUMENTS
-----------------

To find more information, please refer to your documentation (Oracle7 Server
Utilities Guide, Server and Tools administrators guide, Server for <OS>
reference guide, Getting Started with <OS> and README files) or visit the
Oracle Metalink site (http://metalink.oracle.com).