Oracle: SQL Loader example: Load binary data (long raw)

Creating the table

First, we need to have a destination table:

create table tq84_sql_loader_long_raw (
   filename varchar2(20),
   author   varchar2(16),
   url      varchar2(80),
   image    long raw
);

Github repository Oracle-Patterns, path: /SQLLoader/binary-data/create_table_long_raw.sql

Control files

Apparently, in order to load a long raw with SQL*Loader, each file needs its own control file. (If this is not the case, I didn't find out how it would be possible otherwise).

When loading binary data that is greater then 64 KB, we have to find out the number of pieces that are smaller than 64 KB that, when concatenated, will result in the file that we're trying to load.

The first file, Binary-code.jpg, is 197,303 bytes large. This is equal to 4*49258 bytes. Therefore, we use the specificers concatenate 4 and "fix 49258" in the control file. We also have to set the file size for the column image (raw(197032)).

Because the other columns are filled with data that pertains to the image loaded, we specify them as constant.

The resulting control file is:

options(bindsize=197032, rows=1)
load data
infile  'Binary-code.jpg'    "fix 49258"
concatenate 4             --  4 * 49258 = 197032
preserve blanks
into table tq84_sql_loader_long_raw
append
(
  filename constant 'Binary-code.jpg' ,
  author   constant 'Christiaan Colen',
  url      constant 'https://ccsearch.creativecommons.org/photos/f63dd034-4de4-49c8-a500-270aedec50c5',
  image    raw(197032)
)

Github repository Oracle-Patterns, path: /SQLLoader/binary-data/load_long_raw_Binary-code.ctl

Similarly, for Eschenbergturm.jpg whose size is 179,395 (= 5 * 35789) bytes, we get the following control file:

options(bindsize=197032, rows=1)
load data
  infile  'Eschenbergturm.jpg'    "fix 35879"
  concatenate 5                --  5 * 35789 = 179395
  preserve blanks
  into table tq84_sql_loader_long_raw
append 
(
  filename constant 'Eschenbergturm.jpg' ,
  author   constant 'Nouhailler',
  url      constant 'https://ccsearch.creativecommons.org/photos/bb7d21fe-40de-46cb-a6e1-02df76cfa15a',
  image    raw(179395)
)

Github repository Oracle-Patterns, path: /SQLLoader/binary-data/load_long_raw_Eschenbergturm.ctl

Finally, the third file we load is Oracle-Buildings.jpg (size = 67,083 = 19 * 14057 bytes) and the corresponding control file is:

options(bindsize=267083, rows=1)
load data
  infile  'Oracle-Buildings.jpg'    "fix 14057"
concatenate 19                  --  19 * 14057 = 267083
preserve blanks
into table tq84_sql_loader_long_raw
append
(
  filename constant 'Oracle-Buildings.jpg' ,
  author   constant 'Otto Geldermann',
  url      constant 'https://ccsearch.creativecommons.org/photos/53cbf2b7-0bc2-427a-abc3-cbf0413d9abb',
  image    raw(267083)
)

Github repository Oracle-Patterns, path: /SQLLoader/binary-data/load_long_raw_Oracle-Buildings.ctl

Loading the data

Now, with these control files, we can load the data into the prepared table:

sqlldr control=load_long_raw_Binary-code.ctl      userid=rene/rene@ORA18
sqlldr control=load_long_raw_Eschenbergturm.ctl   userid=rene/rene@ORA18
sqlldr control=load_long_raw_Oracle-Buildings.ctl userid=rene/rene@ORA18

Oracle: SQL Loader example: Load binary data (long raw)

Creating the table

Control files

Loading the data

See also