scott@ORA92> SELECT banner FROM v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE	9.2.0.1.0	Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

scott@ORA92> CREATE TABLE taskmemo
  2    (task_memo    BLOB,
  3  	proj_id      NUMBER,
  4  	task_id      NUMBER,
  5  	memo_type_id NUMBER)
  6  /

Table created.

scott@ORA92> CREATE OR REPLACE PROCEDURE insert_or_update_task_memo
  2    (p_task_memo_raw IN RAW,
  3  	p_proj_id	IN taskmemo.proj_id%TYPE,
  4  	p_task_id	IN taskmemo.task_id%TYPE,
  5  	p_memo_type_id	IN taskmemo.memo_type_id%TYPE)
  6  IS
  7    v_task_memo_blob    BLOB;
  8  BEGIN
  9    SELECT task_memo
 10    INTO   v_task_memo_blob
 11    FROM   taskmemo
 12    WHERE  proj_id = p_proj_id
 13    AND    task_id = p_task_id
 14    AND    memo_type_id = p_memo_type_id;
 15    --
 16    DBMS_LOB.WRITEAPPEND
 17  	 (v_task_memo_blob, UTL_RAW.LENGTH (p_task_memo_raw), p_task_memo_raw);
 18  EXCEPTION
 19    WHEN NO_DATA_FOUND THEN
 20  	 INSERT INTO taskmemo (task_memo, proj_id, task_id, memo_type_id)
 21  	 VALUES (EMPTY_BLOB(), p_proj_id, p_task_id, p_memo_type_id)
 22  	 RETURNING task_memo INTO v_task_memo_blob;
 23  	 DBMS_LOB.WRITEAPPEND
 24  	   (v_task_memo_blob, UTL_RAW.LENGTH (p_task_memo_raw), p_task_memo_raw);
 25  END;
 26  /

Procedure created.

scott@ORA92> SHOW ERRORS
No errors.

scott@ORA92> CREATE OR REPLACE PROCEDURE read_task_memo
  2    (p_proj_id      IN taskmemo.proj_id%TYPE,
  3  	p_task_id      IN taskmemo.task_id%TYPE,
  4  	p_memo_type_id IN taskmemo.memo_type_id%TYPE)
  5  IS
  6    v_task_memo_blob   BLOB;
  7  BEGIN
  8    SELECT task_memo
  9    INTO   v_task_memo_blob
 10    FROM   taskmemo
 11    WHERE  proj_id = p_proj_id
 12    AND    task_id = p_task_id
 13    AND    memo_type_id = p_memo_type_id;
 14    --
 15    DBMS_OUTPUT.PUT_LINE
 16  	 (UTL_RAW.CAST_TO_VARCHAR2 (DBMS_LOB.SUBSTR (v_task_memo_blob, 200, 1)));
 17  END;
 18  /

Procedure created.

scott@ORA92> SHOW ERRORS
No errors.

scott@ORA92> EXEC insert_or_update_task_memo (UTL_RAW.CAST_TO_RAW ('TESTING INSERT'), 482, 33596, 30)

PL/SQL procedure successfully completed.

scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> EXEC read_task_memo (482, 33596, 30)
TESTING INSERT

PL/SQL procedure successfully completed.

scott@ORA92> EXEC insert_or_update_task_memo (UTL_RAW.CAST_TO_RAW (' AND TESTING UPDATE'), 482, 33596, 30)

PL/SQL procedure successfully completed.

scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> EXEC read_task_memo (482, 33596, 30)
TESTING INSERT AND TESTING UPDATE

PL/SQL procedure successfully completed.

CREATE OR REPLACE PROCEDURE insert_or_update_task_memo
  (p_task_memo_raw IN RAW,
   p_proj_id       IN taskmemo.proj_id%TYPE,
   p_task_id       IN taskmemo.task_id%TYPE,
   p_memo_type_id  IN taskmemo.memo_type_id%TYPE)
IS
  v_task_memo_blob    BLOB;
BEGIN
  SELECT task_memo 
  INTO   v_task_memo_blob
  FROM   taskmemo
  WHERE  proj_id = p_proj_id
  AND    task_id = p_task_id
  AND    memo_type_id = p_memo_type_id
  <b>FOR    UPDATE</b>;
  --  
  DBMS_LOB.WRITEAPPEND
    (v_task_memo_blob, UTL_RAW.LENGTH (p_task_memo_raw), p_task_memo_raw);
  <b>COMMIT;</b>
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    INSERT INTO taskmemo (task_memo, proj_id, task_id, memo_type_id)
    VALUES (EMPTY_BLOB(), p_proj_id, p_task_id, p_memo_type_id)
    RETURNING task_memo INTO v_task_memo_blob;
    DBMS_LOB.WRITEAPPEND 
      (v_task_memo_blob, UTL_RAW.LENGTH (p_task_memo_raw), p_task_memo_raw);
  <b>COMMIT;</b>
END;
/

scott@ORA92> CREATE OR REPLACE PROCEDURE insert_or_update_task_memo
  2    (p_task_memo	IN VARCHAR2,
  3  	p_proj_id	IN taskmemo.proj_id%TYPE,
  4  	p_task_id	IN taskmemo.task_id%TYPE,
  5  	p_memo_type_id	IN taskmemo.memo_type_id%TYPE)
  6  IS
  7    v_task_memo_raw	   RAW(32767) := UTL_RAW.CAST_TO_RAW (p_task_memo);
  8    v_task_memo_blob    BLOB;
  9  BEGIN
 10    SELECT task_memo
 11    INTO   v_task_memo_blob
 12    FROM   taskmemo
 13    WHERE  proj_id = p_proj_id
 14    AND    task_id = p_task_id
 15    AND    memo_type_id = p_memo_type_id
 16    FOR    UPDATE;
 17    --
 18    DBMS_LOB.WRITEAPPEND
 19  	 (v_task_memo_blob, UTL_RAW.LENGTH (v_task_memo_raw), v_task_memo_raw);
 20    COMMIT;
 21  EXCEPTION
 22    WHEN NO_DATA_FOUND THEN
 23  	 INSERT INTO taskmemo (task_memo, proj_id, task_id, memo_type_id)
 24  	 VALUES (EMPTY_BLOB(), p_proj_id, p_task_id, p_memo_type_id)
 25  	 RETURNING task_memo INTO v_task_memo_blob;
 26  	 DBMS_LOB.WRITEAPPEND
 27  	   (v_task_memo_blob, UTL_RAW.LENGTH (v_task_memo_raw), v_task_memo_raw);
 28    COMMIT;
 29  END;
 30  /

Procedure created.

scott@ORA92> SHOW ERRORS
No errors.

scott@ORA92> CREATE OR REPLACE PROCEDURE read_task_memo
  2    (p_proj_id      IN taskmemo.proj_id%TYPE,
  3  	p_task_id      IN taskmemo.task_id%TYPE,
  4  	p_memo_type_id IN taskmemo.memo_type_id%TYPE)
  5  IS
  6    v_task_memo_blob   BLOB;
  7  BEGIN
  8    SELECT task_memo
  9    INTO   v_task_memo_blob
 10    FROM   taskmemo
 11    WHERE  proj_id = p_proj_id
 12    AND    task_id = p_task_id
 13    AND    memo_type_id = p_memo_type_id;
 14    --
 15    DBMS_OUTPUT.ENABLE (1000000);
 16    DBMS_OUTPUT.PUT_LINE ('length of task memo: ' || DBMS_LOB.GETLENGTH (v_task_memo_blob));
 17    FOR i IN 0 .. FLOOR (DBMS_LOB.GETLENGTH (v_task_memo_blob) / 250) LOOP
 18  	 DBMS_OUTPUT.PUT_LINE
 19  	   (UTL_RAW.CAST_TO_VARCHAR2 (DBMS_LOB.SUBSTR (v_task_memo_blob, 250, i * 250 + 1)));
 20    END LOOP;
 21  END;
 22  /

Procedure created.

scott@ORA92> SHOW ERRORS
No errors.

scott@ORA92> EXEC insert_or_update_task_memo ('TESTING INSERT', 482, 33596, 30)

PL/SQL procedure successfully completed.

scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> EXEC read_task_memo (482, 33596, 30)
length of task memo: 14
TESTING INSERT

PL/SQL procedure successfully completed.

scott@ORA92> BEGIN
  2    FOR i IN 1 .. 2000 LOOP
  3  	 insert_or_update_task_memo (' AND TESTING UPDATE ' || i, 482, 33596, 30);
  4    END LOOP;
  5  END;
  6  /

PL/SQL procedure successfully completed.

scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> EXEC read_task_memo (482, 33596, 30)
length of task memo: 46907
TESTING INSERT AND TESTING UPDATE 1 AND TESTING UPDATE 2 AND TESTING UPDATE 3 AND TESTING UPDATE 4
AND TESTING UPDATE 5 AND TESTING UPDATE 6 AND TESTING UPDATE 7 AND TESTING UPDATE 8 AND TESTING
UPDATE 9 AND TESTING UPDATE 10 AND TESTING UPDATE 11 
... truncated to save space
UPDATE 1996 AND TESTING UPDATE 1997 AND TESTING
UPDATE 1998 AND TESTING UPDATE 1999 AND TESTING UPDATE 2000

PL/SQL procedure successfully completed.

CREATE OR REPLACE PROCEDURE SC_LOAD_BLOB(p_id IN NUMBER, p_filename IN VARCHAR2) AS
  l_blob  blob;
  l_bfile bfile;
 begin
  insert into SC_LOB_DATA values (p_id, empty_blob())
  returning attBLOB into l_blob;
  l_bfile := bfilename('RIDOT_ATTACHMENTS_DIR', p_filename);
  dbms_lob.FILEOPEN(l_bfile);
  dbms_lob.loadfromfile(l_blob, l_bfile, dbms_lob.GETLENGTH(l_bfile));
  dbms_lob.FILECLOSE(l_bfile);
 end;
/

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
PL/SQL Release 9.2.0.3.0 - Production
CORE    9.2.0.3.0       Production
TNS for 32-bit Windows: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production

scott@ORA92> CREATE TABLE sc_lob_data
  2    (doc_id	      NUMBER,
  3  	attBLOB       BLOB,
  4  	CONSTRAINT    sc_lob_data_doc_id_pk
  5  		      PRIMARY KEY (doc_id))
  6  /

Table created.

scott@ORA92> CREATE OR REPLACE DIRECTORY MY_DIR AS 'c:\oracle'
  2  /

Directory created.

scott@ORA92> CREATE OR REPLACE PROCEDURE sc_load_BLOB
  2    (p_id	   IN NUMBER,
  3  	p_filename IN VARCHAR2)
  4  AS
  5    l_BLOB	      BLOB;
  6    l_bfile	      BFILE;
  7  BEGIN
  8    INSERT INTO sc_lob_data VALUES (p_id, EMPTY_BLOB())
  9    RETURNING attBLOB into l_BLOB;
 10    l_bfile := BFILENAME ('MY_DIR', p_filename);
 11    DBMS_OUTPUT.PUT_LINE (DBMS_LOB.FILEEXISTS (l_bfile));
 12    DBMS_LOB.FILEOPEN (l_bfile);
 13    DBMS_LOB.LOADFROMFILE (l_BLOB, l_bfile, DBMS_LOB.GETLENGTH (l_bfile));
 14    DBMS_LOB.FILECLOSE (l_bfile);
 15  END sc_load_BLOB;
 16  /

Procedure created.

scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> EXECUTE sc_load_BLOB (1, 'clip.gif')
1
BEGIN sc_load_BLOB (1, 'clip.gif'); END;

*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
ORA-06512: at "SYS.DBMS_LOB", line 504
ORA-06512: at "SCOTT.SC_LOAD_BLOB", line 12
ORA-06512: at line 1

CREATE OR REPLACE PROCEDURE read_attBLOB
  (p_id       IN NUMBER)
IS
  l_BLOB         BLOB;
BEGIN
  SELECT attBLOB 
  INTO   l_BLOB
  FROM   sc_lob_data
  WHERE  doc_id = p_id;
  --  
  DBMS_OUTPUT.ENABLE (1000000);
  DBMS_OUTPUT.PUT_LINE ('length of attBLOB: ' || DBMS_LOB.GETLENGTH (l_BLOB));
  FOR i IN 0 .. FLOOR (DBMS_LOB.GETLENGTH (l_BLOB) / 250) LOOP
    DBMS_OUTPUT.PUT_LINE 
      (UTL_RAW.CAST_TO_VARCHAR2 (DBMS_LOB.SUBSTR (l_BLOB, 250, i * 250 + 1)));
  END LOOP;
END;
/
SHOW ERRORS
SET SERVEROUTPUT ON
EXECUTE read_attBLOB (1)

procedure download_document(pid in number) is
lloblocator event_document.document_file%type;
cursor lcur is
select document_file
  from event_document
 where id = pid;
begin
  open lcur;
  fetch lcur into lloblocator;
  close lcur;
  --
  wpg_docload.download_file(lloblocator); -- if its blob it can be 
directly downloaded
  --
end;