alter table "OEHR_EMPLOYEES" add
("PHOTO" BLOB NULL,
 "FILENAME" VARCHAR2(255) NULL,
 "MIMETYPE" VARCHAR2(255) NULL,
 "LAST_UPDATE_DATE" DATE NULL)
/   

set serverout on

CREATE OR REPLACE DIRECTORY photo_loc AS 'd:\temp';

create or replace
PROCEDURE update_lob 
  (p_file IN VARCHAR2, p_dir IN VARCHAR2)
IS
  i_id    NUMBER;         
  v_b     BLOB;
BEGIN
  DBMS_OUTPUT.ENABLE;
  DBMS_OUTPUT.PUT_LINE('Begin updating row...');
   update oehr_employees 
      set photo = EMPTY_BLOB(),
      filename='dogsmall.gif',
      mimetype='image/gif'
      where employee_id = 101
      RETURNING photo into v_b;
    loadLOBFromBFILE_proc(v_b,p_file, p_dir);
    DBMS_OUTPUT.PUT_LINE('Rows updated.');
  COMMIT;
END update_lob;
/

create or replace
PROCEDURE update_lob2 
  (p_file IN VARCHAR2, p_dir IN VARCHAR2)
IS
  i_id    NUMBER;         
  v_b     BLOB;
BEGIN
  DBMS_OUTPUT.ENABLE;
  DBMS_OUTPUT.PUT_LINE('Begin updating row...');
   update oehr_employees 
      set photo = EMPTY_BLOB(),
      filename='flowersmall.gif',
      mimetype='image/gif'
      where employee_id = 103
      RETURNING photo into v_b;
    loadLOBFromBFILE_proc(v_b,p_file, p_dir);
    DBMS_OUTPUT.PUT_LINE('Rows updated.');
  COMMIT;
END update_lob2;
/


CREATE OR REPLACE PROCEDURE loadLOBFromBFILE_proc 
  (p_dest_loc IN OUT BLOB, p_file_name IN VARCHAR2, 
   p_file_dir IN VARCHAR2)
IS
  v_src_loc  BFILE := BFILENAME(p_file_dir, p_file_name);
  v_amount   INTEGER := 4000;
BEGIN
  DBMS_LOB.OPEN(v_src_loc, DBMS_LOB.LOB_READONLY);
  v_amount := DBMS_LOB.GETLENGTH(v_src_loc);
  DBMS_LOB.LOADFROMFILE(p_dest_loc, v_src_loc, v_amount);
  DBMS_LOB.CLOSE(v_src_loc);
END loadLOBFromBFILE_proc;
/

execute update_lob('dogsmall.gif', 'PHOTO_LOC')
execute update_lob2('flowersmall.gif', 'PHOTO_LOC')
