Tutorial: Loading images or any blob type files from server to Oracle Database using PLSQL - Part 1

Recently I was working with External tables which is becoming very handy in all the data transfer activities that my work wants to me to perform. They are really cool alternatives to SQL*Loader Utility. I also came across a requirement to load images from your local / server machine to Oracle Database and this is where I wanted to take a look at external tables whether they are equipped to handle images / documents or any other MIME types for that case, the answer was YES and it made my coding world easy to understand and deploy.

There are many ways which we can load image / document type file formats into Oracle Database fields (BLOB Type):- Java methods, DBMS_LOB package or External Tables. This blog post will detail on the DBMS_LOB and External Table to handle loading images / documents stored on a local / server machine as a complete file.

External Table:

1) Create a file (file.txt) for the external table to read with columns ID, file_name, file_name

1,034517.jpg,034517.jpg
2,037608.jpg,037608.jpg
3,045023.jpg,045023.jpg
4,049412.jpg,049412.jpg
5,059125.jpg,059125.jpg
6,066945.jpg,066945.jpg

2) Create a database directory where the file will be stored on the machine

CREATE OR REPLACE DIRECTORY xxfile_dir AS '/usr/tmp/JK'  

3) Save the file 'file.txt' and the files that are referenced inside the file in the directory mentioned in step 2

4) Now connect to database and create an external table with the columns ID, file_name, BLOB column for your file

CREATE TABLE XXLOAD_FILE (
  ID                NUMBER(10),
  FILE_NAME         CHAR(15),
  BLOB_CONTENT      BLOB
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY XXFILE_DIR
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
    BADFILE XXFILE_DIR:'lob_tab_%a_%p.bad'
    LOGFILE XXFILE_DIR:'lob_tab_%a_%p.log'
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (
      ID                CHAR(10),
      FILE_NAME         CHAR(15),
      BLOB_FILENAME     CHAR(100)
    )
    COLUMN TRANSFORMS (BLOB_CONTENT FROM LOBFILE (BLOB_FILENAME) FROM (XXFILE_DIR) BLOB)
  )
  LOCATION ('file.txt')
)
PARALLEL 2
REJECT LIMIT UNLIMITED;

4) Now you are all done, either call the table using a select statement or in your procedures and load into your document / image tables. You can see that the field BLOB_FILENAME does not match the actual definition as this field is used to actually fetch the file and save it into BLOB_CONTENT field using the COLUMN TRANSFORMS function

select * from XXLOAD_FILE
or
create table XXFILE as
select ID, substr(FILE_NAME,1,instr(FILE_NAME,'.',1)-1) person_no, FILE_NAME, BLOB_CONTENT FILE from XXLOAD_FILE


As this blog post is getting really huge, I would be splitting the tutorial using DBMS_LOB in the next post.