oracleblob的读写操作

oracle blob的读写操作

 

--blob 的读写

CREATE OR REPLACE PROCEDURE P_IMG_INSERT (v_filename VARCHAR2)

IS  www.2cto.com  

  v_bfile BFILE;--文件指针

  v_blob BLOB;

  DIR CONSTANT VARCHAR2(20) := &＃39;TEST&＃39;;--文件存放DIRECTORY,区分大小写

   V_DEST NUMBER := 1;  

    V_LANG NUMBER := 1;  

 

BEGIN

  /*通过empty_blob()函数将类型为blob的列初始化为空以便以后填充*/

  INSERT INTO res_info (res_blob)

  VALUES (EMPTY_BLOB ()) RETURN res_blob INTO v_blob;

 

  v_bfile:= BFILENAME (DIR, v_filename);

 

  IF (dbms_lob.fileexists(v_bfile)!=0) THEN 

    dbms_lob.fileopen(v_bfile,dbms_lob.file_readonly); --打开目标文件

    /*将文件字数据加载到指定的LOB类型变量*/

    dbms_lob.loadblobfromfile(v_blob,  www.2cto.com  

                                                      v_bfile,

                                                      dbms_lob.getlength(v_bfile),

                                                      V_DEST,

                              V_LANG);

     -- dbms_lob.loadblobfromfile 

    dbms_lob.fileclose(v_bfile);--关闭文件

    COMMIT;

    dbms_output.put_line(&＃39;已经从&＃39;||DIR||&＃39;目录中读取了文件&＃39;||v_filename||&＃39;向表中插入&＃39;);

 

  ELSE--如果文件定位器指向的文件不存在

    dbms_output.put_line(&＃39;文件没找到&＃39;);

  END IF;

  EXCEPTION WHEN OTHERS THEN

  dbms_output.put_line(SQLERRM);

END;  www.2cto.com  

 

说明下:

DBMS_LOB.LOADBLOBFROMFILE (

   dest_lob    IN OUT NOCOPY BLOB,

   src_bfile   IN            BFILE,

   amount      IN            INTEGER,

   dest_offset IN OUT        INTEGER,

   src_offset  IN OUT        INTEGER);

   www.2cto.com  

Parameter Description

dest_lob BLOB locator of the target for the load.

src_bfile BFILE locator of the source for the load.

amount Number of bytes to load from the BFILE. You can also use DBMS_LOB.LOBMAXSIZE to load until the end of the BFILE.

dest_offset (IN) Offset in bytbes in the destination BLOB (origin: 1) for the start of the write. (OUT) New offset in bytes in the destination BLOB right after the end of this write, which is also where the next write should begin.

src_offset (IN) Offset in bytes in the source BFILE (origin: 1) for the start of the read .(OUT) Offset in bytes in the source BFILE right after the end of this read, which is also where the next read should begin.

 

 

作者 chen11-1