


CREATE
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;
/

