blob的插入和讀取

-- 創建測試表

CREATE TABLE ZETA_BLOB
(
  FID   NUMBER,
  FPIC  BLOB
);

 

-- 創建insert的procedure，參考了蓋國強老師的http://www.eygle.com/archives/2005/04/eoaiplsqlieoeao.html

 CREATE OR REPLACE PROCEDURE zeta_load_blob (pfname VARCHAR2,pid number)
 IS
 src_file BFILE;
 dst_file BLOB;
 lgh_file BINARY_INTEGER;
 BEGIN
    src_file := bfilename('SOURCE_DIR', pfname);

    INSERT INTO zeta_blob (fid,fpic)
    VALUES (pid,EMPTY_BLOB())
    RETURNING fpic INTO dst_file;

    SELECT fpic INTO dst_file
    FROM zeta_blob  WHERE fid = pid FOR UPDATE;

    dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
    lgh_file := dbms_lob.getlength(src_file);
    dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

   UPDATE zeta_blob  SET fpic = dst_file
   WHERE fid = pid;

   dbms_lob.fileclose(src_file);
   commit;
 END zeta_load_blob;

 

-- 加入一條記錄

exec zeta_load_blob('100005.pdf',1);

 

-- 通過procedure來獲得blob

create or replace procedure proc_test_get_pdf(p_bill_id in number, p_pdf out blob)
is
begin
  select fpic into p_pdf
  from zeta_blob where fid = p_bill_id;
exception
  when others then
     dbms_output.put_line(sqlerrm);
end;

 

-- 簡單的pl/sql的測試

declare
  b blob;
begin
  proc_test_get_pdf(1,b);
  dbms_output.put_line('begin');
  dbms_output.put_line(DBMS_LOB.getlength(b));
end;

 

-- JDBC調用代碼

<%@page import="oracle.sql.BLOB"%>
<%@ page contentType="text/html;charset=Big5"%>
<%@ page import="java.io.ByteArrayOutputStream" %>
<%@ page import="java.sql.*" %>

<%
 try {
     session = request.getSession(true);
     Class.forName("oracle.jdbc.driver.OracleDriver");
     Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@oracletest02:1525:zeta", "hr", "oracle");
 
     ByteArrayOutputStream baos = new ByteArrayOutputStream();
    
     String sql = "{call proc_test_get_pdf(?,?)}";

 

     //String sql = "{call proc_test_get_pdf@zeta(?,?)}"; // Oracle不允許遠程調用含有lob參數的procedure。 PLS-00564: lob arguments are not permitted in calls to remote server
     
     System.out.println("1");

     oracle.jdbc.driver.OracleCallableStatement stmt = (oracle.jdbc.driver.OracleCallableStatement)conn.prepareCall(sql);
     stmt.setInt(1, 1);
     stmt.registerOutParameter(2, oracle.jdbc.driver.OracleTypes.BLOB);

     System.out.println("2");
  stmt.execute();

     byte[] bytes = stmt.getBytes(2);
     System.out.println("3");
     System.out.println("4=>" + bytes.length);
     baos.write(bytes);
     stmt.close();
     conn.close();
 
     response.setHeader("Content-Disposition", " attachment; filename=/"zeta.pdf/"");

     response.setContentType("application/pdf");
     response.setContentLength(baos.size());
     ServletOutputStream sos = response.getOutputStream();
     baos.writeTo(sos);
     sos.flush();
     session.invalidate();
   } catch(Exception err) {
     err.printStackTrace();
   }
%>