熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

使用PL/SQL從數據庫中讀取BLOB對象

2013-11-13 15:25:40  來源: Oracle 

  確認現有對象
  
  SQL> col fdesc for a
  SQL> select fidfnamefdesc from eygle_blob;
  
  FID FNAME                       FDESC
  
   ShaoLinjpg                    少林寺康熙手書
   DaoYingjpg                    倒映
  
  創建存儲Directory
  
  SQL> connect / as sysdba
  Connected
  SQL> create or replace directory BLOBDIR as D:\oradata\Pic;
  
  Directory created
  
  SQL>
  SQL> grant readwrite on directory BLOBDIR to eygle;
  
  Grant succeeded
  
  SQL>
  
  創建存儲過程
  
  SQL> connect eygle/eygle
  Connected
  SQL>
  SQL> CREATE OR REPLACE PROCEDURE eygle_dump_blob (piname varcharponame varchar) IS
    l_file   UTL_FILEFILE_TYPE;
    l_buffer  RAW();
    l_amount  BINARY_INTEGER := ;
    l_pos    INTEGER := ;
    l_blob   BLOB;
    l_blob_len INTEGER;
   BEGIN
    SELECT FPIC
    INTO   l_blob
    FROM   eygle_blob
    WHERE FNAME = piname;
  
    l_blob_len := DBMS_LOBGETLENGTH(l_blob);
    l_file := UTL_FILEFOPEN(BLOBDIRponamewb );
  
    WHILE l_pos < l_blob_len LOOP
     DBMS_LOBREAD (l_blob l_amount l_pos l_buffer);
     UTL_FILEPUT_RAW(l_file l_buffer TRUE);
     l_pos := l_pos + l_amount;
    END LOOP;
  
    UTL_FILEFCLOSE(l_file);
  
   EXCEPTION
    WHEN OTHERS THEN
     IF UTL_FILEIS_OPEN(l_file) THEN
      UTL_FILEFCLOSE(l_file);
     END IF;
     RAISE;
   END;
   /
  
  Procedure created
  
  取出數據
  
  SQL> host ls l d:\oradata\Pic
  total
  rwxrwxrwa  gqgai      None       Apr : DaoYingjpg
  rwxrwxrwa  gqgai      None       Apr : ShaoLinjpg
  
  SQL> exec eygle_dump_blob(ShaoLinjpgjpg)
  
  PL/SQL procedure successfully completed
  
  SQL> host ls l d:\oradata\Pic
  total
  rwxrwxrwa  Administrators SYSTEM      Apr : jpg
  rwxrwxrwa  gqgai      None       Apr : DaoYingjpg
  rwxrwxrwa  gqgai      None       Apr : ShaoLinjpg
  
  SQL>
  SQL> exec eygle_dump_blob(DaoYingjpgjpg)
  
  PL/SQL procedure successfully completed
  
  SQL> host ls l d:\oradata\Pic
  total
  rwxrwxrwa  Administrators SYSTEM      Apr : jpg
  rwxrwxrwa  Administrators SYSTEM      Apr : jpg
  rwxrwxrwa  gqgai      None       Apr : DaoYingjpg
  rwxrwxrwa  gqgai      None       Apr : ShaoLinjpg
From:http://tw.wingwit.com/Article/program/Oracle/201311/16690.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.