Question:  I want to store a PDF file inside Oracle.  I want a PL/SQL procedure to insert the PDF into my Oracle table inside a BLOB data type.  What are my options for inserting a PDF into Oracle?

Answer:  As the world's most flexible database, you have three options for storing a PDF.  Also see my notes on storing a Word document into an Oracle table.

• Use Apex - Storing a PDF into an Oracle table is easy if you use APEX.  This uses the wwv_flow_files Apex utility to make it simple to store PDF's and other images into Oracle table columns.
  
  
• Store PDF as BFILE - You can also leave the PDF's in a flat file, and use  the BFILE utility to map the PDF into Oracle using the dbms_lob.loadfromfile utility.  The advantage is that you don't have to actually load the PDF into an Oracle tablespace, but the disadvantage is that the PDF is outside of Oracle, and it cannot be managed (backed-up, kept consistent) is if the PDF resided inside a table.
  
  
• Store the PDF inside the table - The third option is to use a PL/SQL procedure to store the PDF directly into an Oracle table, and you use the same dbms_lob.loadfromfile utility

Procedure to load a PDF as a BFILE:

CREATE OR REPLACE PROCEDURE load_lob AS
  id         NUMBER;
  image1     BLOB;
  locator    BFILE;
  bfile_len  NUMBER;
  bf_desc    VARCHAR2(30);
  bf_name    VARCHAR2(30);
  bf_dir     VARCHAR2(30);
  bf_typ     VARCHAR2(4);
  ctr integer;
  CURSOR get_id IS
    SELECT bfile_id,bfile_desc,bfile_type FROM graphics_table;
BEGIN
  OPEN get_id;
LOOP
  FETCH get_id INTO id, bf_desc, bf_typ;
  EXIT WHEN get_id%notfound;
  dbms_output.put_line('ID: '||to_char(id));
  SELECT bfile_loc INTO locator FROM graphics_table WHERE bfile_id=id;
  dbms_lob.filegetname(
locator,bf_dir,bf_name);
  dbms_output.put_line('Dir: '||bf_dir);
  dbms_lob.fileopen(locator,dbms_lob.file_readonly);
  bfile_len:=dbms_lob.getlength(locator);
  dbms_output.put_line('ID: '||to_char(id)||' length: '||to_char(bfile_len));
  SELECT temp_blob INTO image1 FROM temp_blob;
  bfile_len:=dbms_lob.getlength(locator);
  dbms_lob.loadfromfile(image1,locator,bfile_len,1,1);
  INSERT INTO internal_graphics VALUES (id,bf_desc,image1,bf_typ);
  dbms_output.put_line(bf_desc||' Length: '||TO_CHAR(bfile_len)||
  ' Name: '||bf_name||' Dir: '||bf_dir||' '||bf_typ);
  dbms_lob.fileclose(locator);
END LOOP;
END;
/

 

Procedure to load a PDF into a BLOB column of a table:

CREATE OR REPLACE PROCEDURE load_lob AS
  id         NUMBER;
  image1     BLOB;
  locator    BFILE;
  bfile_len  NUMBER;
  bf_desc    VARCHAR2(30);
  bf_name    VARCHAR2(30);
  bf_dir     VARCHAR2(30);
  bf_typ     VARCHAR2(4);
  ctr integer;
  CURSOR get_id IS
    SELECT bfile_id,bfile_desc,bfile_type FROM graphics_table;
BEGIN
  OPEN get_id;
LOOP
  FETCH get_id INTO id, bf_desc, bf_typ;
  EXIT WHEN get_id%notfound;
  dbms_output.put_line('ID: '||to_char(id));
  SELECT bfile_loc INTO locator FROM graphics_table WHERE bfile_id=id;
  dbms_lob.filegetname(
locator,bf_dir,bf_name);
  dbms_output.put_line('Dir: '||bf_dir);
  dbms_lob.fileopen(locator,dbms_lob.file_readonly);
  bfile_len:=dbms_lob.getlength(locator);
  dbms_output.put_line('ID: '||to_char(id)||' length: '||to_char(bfile_len));
  SELECT temp_blob INTO image1 FROM temp_blob;
  bfile_len:=dbms_lob.getlength(locator);
  dbms_lob.loadfromfile(image1,locator,bfile_len,1,1);
  INSERT INTO internal_graphics VALUES (id,bf_desc,image1,bf_typ);
  dbms_output.put_line(bf_desc||' Length: '||TO_CHAR(bfile_len)||
  ' Name: '||bf_name||' Dir: '||bf_dir||' '||bf_typ);
  dbms_lob.fileclose(locator);
END LOOP;
END;
/

 

---