spool  1 
connect system/manager
drop directory documents;
CREATE OR REPLACE DIRECTORY documents AS 'C:\BLOB';
GRANT READ ON DIRECTORY documents TO PUBLIC;
connect scott/tiger

DROP TABLE my_docs;
DROP SEQUENCE my_docs_seq;
DROP PROCEDURE load_file_to_my_docs;

CREATE TABLE my_docs (
  id    NUMBER( 10 )     NOT NULL,
  name  VARCHAR2( 200 )  NOT NULL,
  doc   BLOB           NOT NULL
)
/

ALTER TABLE my_docs ADD (
  CONSTRAINT my_docs_pk PRIMARY KEY (id)
)
/

CREATE SEQUENCE my_docs_seq;


CREATE OR REPLACE PROCEDURE load_file_to_my_docs (p_file_name  IN  my_docs.name%TYPE) AS
  v_bfile      BFILE;
  v_blob       BLOB;
BEGIN
  INSERT INTO my_docs (id, name, doc)
  VALUES (my_docs_seq.NEXTVAL, p_file_name, empty_blob())
  RETURN doc INTO v_blob;

  v_bfile := BFILENAME('DOCUMENTS', p_file_name);
  Dbms_Lob.Fileopen(v_bfile, Dbms_Lob.File_Readonly);
  Dbms_Lob.Loadfromfile(v_blob, v_bfile, Dbms_Lob.Getlength(v_bfile));
  Dbms_Lob.Fileclose(v_bfile);

  COMMIT;
END;
/

EXEC load_file_to_my_docs('test.xls');

exit;

<%@ page import="java.sql.*,java.io.*" %>
<%
    Connection  conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    java.sql.Blob my_blob = null;
    BufferedOutputStream binout = null;
    BufferedInputStream in = null;
    
    try {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      conn =  DriverManager.getConnection("jdbc:oracle:oci8:@","scott", "tiger");
      stmt = conn.createStatement ();
      rs = stmt.executeQuery ("select doc from my_docs where name='test.xls'");
      if (rs.next()) {
       // Get LOB locators into Java wrapper classes.
        my_blob = (java.sql.Blob)rs.getObject( 1 );
      }

      binout = new BufferedOutputStream ( response.getOutputStream() );
      in = new  BufferedInputStream ( (InputStream)my_blob.getBinaryStream());      

      response.setHeader("Content-Type","application/octet-stream;");
      response.setHeader("Content-disposition","attachment;filename=\"test.xls\"");

      int ch = in.read();
      while ( ch!=- 1  ) 
      {
       binout.write(ch);
       ch=in.read();
      }
    } catch ( Exception e ) {
       String errmsg = new String(e.getMessage().getBytes("ISO-8859-1"), "Cp1251");
       throw new Exception (errmsg);    
    } finally {
       try {
          if (rs!= null) rs.close(); 
          if (stmt!= null) stmt.close();
          if (conn!= null) conn.close();
          if (binout!= null) binout.close();
          if (in!= null) in.close();
       } catch ( Exception ignore ) {}
    }    
%>