SQL & PL/SQL

Last week I posted a question (8398795 in desperation asking if any body had a good tutorial to load multiple blobs. One response back was google it, I deserved that Mustafa. I did a search and eventually found some useful information by googling the word 'DBMS_LOB' Thank You Billy for the hint. From this I was able to write an anonymous block that accomplished what I required, but then I was asked to put it into a package, I haven’t written a package in a while and must admit to being very rusty on this, but with the kind help of a number of you, Toon and Saubhik (2126112 I have been able to get this thing up and running.

By way of giving something back instead of asking questions all the time, I have included a script below that shows how I approached the bulk upload of records with a BLOB column using an external csv file.

Preparation
1. Place a number of documents (I used pdf's) into the directory specified by ULOAD_DIR.

2. Create a csv file and enter the following text. This can be done by copying the following records into a notepad file and then naming it EXTERNAL_DATA.csv.

sketch_a.pdf,application/pdf
sketch_b.pdf,application/pdf
sketch_c.pdf,application/pdf
sketch_d.pdf,application/pdf
sketch_e.pdf,application/pdf
sketch_f.pdf,application/pdf
sketch_g.pdf,application/pdf
sketch_h.pdf,application/pdf
sketch_i.pdf,application/pdf

3. Run the following script to create objects:

CONNECT SYSTEM/password@XE

CREATE DIRECTORY UPLOAD_DIR AS 'C:\temp';

GRANT READ ON DIRECTORY UPLOAD_DIR TO SCHEMA;
GRANT WRITE ON DIRECTORY UPLOAD_DIR TO SCHEMA;


CONNECT user/password@XE

-- CREATE TABLES

CREATE TABLE TARGET_TABLE
( FILENAME VARCHAR2(4000)
, MIME_TYPE VARCHAR2(4000)
, BLOB_CONTENT   BLOB
); 

CREATE TABLE EXTERNAL_TABLE
( FILENAME VARCHAR2(128)
, MIME_TYPE VARCHAR2(4000)
)
ORGANIZATION EXTERNAL ( 
  type oracle_loader 
  DEFAULT directory UPLOAD_DIR 
  access parameters ( 
    records delimited  by newline
    NOBADFILE
    NOLOGFILE
    fields  terminated by ','
    missing field values are null
  )
  location (UPLOAD_DIR:'EXTERNAL_DATA.csv')
)
reject limit unlimited;


-- CREATE SPECIFICATION

CREATE OR REPLACE
PACKAGE bulk_upload_pkg
AS
-- DECLARE TYPE
type get_fbt_bulk_upload_rec_type IS record
  ( filename external_table.filename%TYPE
  , mime_type external_table.mime_type%TYPE
    );
  
-- DECLARE CURSOR
  CURSOR c_get_fbt_bulk_upload RETURN get_fbt_bulk_upload_rec_type;
  
-- DECLARE PROCEDURE
  PROCEDURE bulk_upload_prc
    ( p_rec OUT get_fbt_bulk_upload_rec_type
    , p_blob OUT BLOB
    , p_srcfile OUT BFILE
      );

END bulk_upload_pkg;
/


-- CREATE BODY

CREATE OR REPLACE
PACKAGE BODY bulk_upload_pkg
AS
  CURSOR c_get_fbt_bulk_upload RETURN get_fbt_bulk_upload_rec_type IS
     SELECT filename
          , mime_type
       FROM external_table;
       
PROCEDURE bulk_upload_prc
  ( p_rec OUT get_fbt_bulk_upload_rec_type
  , p_blob OUT BLOB
  , p_srcfile OUT BFILE
    )
IS
BEGIN
  OPEN c_get_fbt_bulk_upload;
  LOOP
    FETCH c_get_fbt_bulk_upload INTO p_rec;
    EXIT
  WHEN c_get_fbt_bulk_upload%NOTFOUND;
   
    DBMS_LOB.CreateTemporary(p_blob, TRUE);
    DBMS_LOB.Open(p_blob, dbms_lob.Lob_ReadWrite);
    
    p_srcfile := Bfilename('UPLOAD_DIR', p_rec.filename);
    
    DBMS_LOB.FileOpen (p_srcfile, dbms_lob.File_ReadOnly);
    DBMS_LOB.LoadFromFile(p_blob, p_srcfile, DBMS_LOB.GetLength(p_srcfile));
    
    INSERT
       INTO fmbsite.TARGET_TABLE
      ( filename
      , mime_type
      , blob_content
      )
      VALUES
      ( p_rec.filename
      , p_rec.mime_type
      , EMPTY_BLOB()
      );
    
    UPDATE fmbsite.TARGET_TABLE SET blob_content = p_blob WHERE filename LIKE p_rec.filename;
    
       DBMS_LOB.FileClose(p_srcfile);
       
    COMMIT;
    
  END LOOP;
  
  IF c_get_fbt_bulk_upload%isopen THEN
    CLOSE c_get_fbt_bulk_upload;
  END IF;

END bulk_upload_prc;

END bulk_upload_pkg;
/

4. Run the following code:

DECLARE

  P_REC FMBSITE.BULK_UPLOAD_PKG.GET_FBT_BULK_UPLOAD_REC_TYPE;
  P_BLOB BLOB;
  P_SRCFILE BFILE;
  
BEGIN

  BULK_UPLOAD_PKG.BULK_UPLOAD_PRC
   ( P_REC => P_REC
   , P_BLOB => P_BLOB
   , P_SRCFILE => P_SRCFILE
    );

END;

5. Have a look at the table named TARGET_TABLE, it should contain the attributes from the CSV file and BLOB content from the UPLOAD_DIR

Cheers
Ben