如何将BLOB中的二进制文件倒出到操作系统中

试一下pl/sql软件。可能对你有帮助

你的目的是什么？
如果要导成DMP文件，好象可以通过EXP导出；
如果想查看内容，可以通过PL/SQL工具来看

我以前也出现过雷同的问题，也是别人给我的文档，应对你有帮助的
表结构：
create table products(
productid number(10) not null ,
name varchar2(255) ,
description CLOB) ;
方法：
SELECT productid, name FROM products
WHERE dbms_lob.instr(products.description,'some text',1,1) > 0;
下面列出了DBMS_LOB包中的过程函数：APPEND procedure Appends the contents of the source LOB to the destination LOB. CLOSE procedure Closes a previously opened internal or external LOB. COMPARE function Compares two entire LOBs or parts of two LOBs. COPY procedure Copies all, or part, of the source LOB to the destination LOB. CREATETEMPORARY procedure Creates a temporary BLOB or CLOB and its corresponding index in the user's default temporary tablespace. ERASE procedure Erases all or part of a LOB. FILECLOSE procedure Closes the file. FILECLOSEALL procedure Closes all previously opened files. FILEEXISTS function Checks if the file exists on the server. FILEGETNAME procedure Gets the directory alias and file name. FILEISOPEN function Checks if the file was opened using the input BFILE locators. FILEOPEN procedure Opens a file. FREETEMPORARY procedure Frees the temporary BLOB or CLOB in the user's default temporary tablespace. GETCHUNKSIZE function Returns the amount of space used in the LOB chunk to store the LOB value.GETLENGTH function Gets the length of the LOB value. INSTR function Returns the matching position of the nth occurrence of the pattern in the LOB. ISOPEN function Checks to see if the LOB was already opened using the input locator. ISTEMPORARY function Checks if the locator is pointing to a temporary LOB. LOADFROMFILE procedure Loads BFILE data into an internal LOB. OPEN procedure Opens a LOB (internal, external, or temporary) in the indicated mode. READ procedure Reads data from the LOB starting at the specified offset.SUBSTR function Returns part of the LOB value starting at the specified offset. TRIM procedure Trims the LOB value to the specified shorter length. WRITE procedure Writes data to the LOB from a specified offset. WRITEAPPEND procedure Writes a buffer to the end of a LOB. 在internal这个用户下给scott用户授权如下：
SQL>grant create any directory to scott;
SQL>grant create any library to scott;
在scott这个用户下执行下述语句：
SQL>create table bfile_tab (bfile_column BFILE);
SQL>create table utl_lob_test (blob_column BLOB);
SQL>create or replace directory utllobdir as 'C:\DDS\EXTPROC';
SQL>set serveroutput on
然后执行下面语句就将C:\DDS\EXTPROC目录下的word文件COM.doc存入到utl_lob_test
表中的blob_column字段中了。
declare
  a_blob  BLOB;
  a_bfile BFILE := BFILENAME('UTLLOBDIR','COM.doc'); --用来指向外部操作系统
文件
begin
  insert into bfile_tab values (a_bfile)
    returning bfile_column into a_bfile;
  insert into utl_lob_test values (empty_blob())
    returning blob_column into a_blob;
  dbms_lob.fileopen(a_bfile);
  dbms_lob.loadfromfile(a_blob, a_bfile, dbms_lob.getlength(a_bfile));
  dbms_lob.fileclose(a_bfile);
  commit;
end;
/
SQL>show errors
此时可以使用DBMS_LOB包的getlength这个procedure来检测是否已经将该word文件存入
到blob字段中了。如：
SQL> select dbms_lob.getlength(blob_column) from UTL_LOB_TEST;
结果如下：
DBMS_LOB.GETLENGTH(BLOB_COLUMN)
-------------------------------
               83968
说明该word文件已经存入到blob字段中去了。
下面将就如何取出该word文件到操作系统下作详细解释：
Oracle8.1.7只能用pro*c与OCI来实现该任务，所以Oracle服务器端必须支持pro*c
以及外部library，Oracle8.1.7数据库默认安装为支持pro*c以及外部Procedure，
用户可以自己检查一下listener.ora 和 tnsnames.ora这两个文件。
listener.ora中包含如下语句：
SID_LIST_LISTENER =
(SID_LIST =
   (SID_DESC =
     (SID_NAME = PLSExtProc)
     (ORACLE_HOME = D:\oracle\ora81)
     (PROGRAM = extproc)
   )
   (SID_DESC =
     (GLOBAL_DBNAME = hft)
     (ORACLE_HOME = D:\oracle\ora81)
     (SID_NAME = hft)
   )
)

tnsnames.ora中包含如下语句：
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
   )
   (CONNECT_DATA =
     (SID = PLSExtProc)
     (PRESENTATION = RO)
   )
)下面这个文件为lob2file.c,具体作用是将BLOB中的二进制文件倒出到操作系统中。
/*begin of  lob2file.c*/
#include <stdio.h>
#include <stdlib.h>
#include <errno.h>
#include <oci.h>
#include <ociextp.h>
#define  DEFAULT_CHUNK_SIZE  1024
static   int     logging;
static   char    logfile[512];
static   FILE   *logfilep = NULL;
int lob2file ( OCILobLocator     *a_lob,    /* the LOB */
              short              lbind,    /* LOB indicator */
              char              *path,     /* file to write */
              short              pind,     /* file indicator */
              int                plen,     /* filename length */
              char              *lpath,    /* logfile name */
              short              lpind,    /* logfile indicator */
              int                lplen,    /* logfile name length */
              int                logit,    /* logging enabled? */
              OCIExtProcContext *ctxt      /* OCI Context */
            )
{
  sword      errnum = 0;
  OCIEnv    *envhp = NULL;
  OCISvcCtx *svchp = NULL;
  OCIError  *errhp = NULL;
  char       lobfile[512];
  FILE      *lobfilep = NULL;
  /*
   * If required, open the log file for writing
   * Use the user provided logfile name if possible
   * Otherwise, default the logfile to lob2file.log
   */
  logging = logit;
  if (logging)
  {
     if (lpind == -1 || lplen == 0 || lplen >= 512)
     {
        strcpy(logfile, "lob2file.log");
     }
     else
     {
        strncpy(logfile, lpath, lplen);
        logfile[lplen] = '\0';
     }
     logfilep = fopen(logfile, "w");
     if (logfilep == NULL)
     {
        if ((logfilep = fopen("lob2file.log", "w")) != NULL)
        {
           fprintf(logfilep, "Error: Unable to open logfile %s\n", logfile);
           fprintf(logfilep, "Error: errno = %d\n", errno);
        }
     }
  }
  /*
   * Retrieve the environment, service context, and error handles
   */
  if ((errnum = OCIExtProcGetEnv(ctxt, &envhp,
                                 &svchp, &errhp)) != OCIEXTPROC_SUCCESS)
  {
     if (logging && logfilep != NULL)
     {
         fprintf(logfilep, "Error: Call to OCIExtProcGetEnv failed\n");
         fprintf(logfilep, "Error: OCIExtProcGetEnv returned %d\n", errnum);
         fclose(logfilep);
         return -1;
     }
  }
  /*
   * Verify that the user has provided a name for the output file
   */
  if (pind == -1 || plen == 0)
  {
     char *errmsg = "Pathname is null or empty string";
     if (logging && logfilep != NULL)
     {
        fprintf(logfilep, "Error: %s\n", errmsg);
        fclose(logfilep);
     }
     errnum = 20001;
     OCIExtProcRaiseExcpWithMsg(ctxt, errnum, (text *)errmsg, strlen(errmsg));
     return -1;
  }
  else /* Use the provided name */
  {
     strncpy(lobfile, path, plen);
     lobfile[plen] = '\0';
  }
  /*
   * Verify that the user has provided a valid LOB locator
   */
  if (lbind == -1)
  {
     char *errmsg = "LOB locator is null";
     if (logging && logfilep != NULL)
     {
        fprintf(logfilep, "Error: %s\n", errmsg);
        fclose(logfilep);
     }
     errnum = 20002;
     OCIExtProcRaiseExcpWithMsg(ctxt, errnum, (text *)errmsg, strlen(errmsg));
     return -1;
}

if (logging && logfilep != NULL)
     fprintf(logfilep, "Opening OS file in write mode\n");
  /*
   * Open the output file for writing
   */
  if ((lobfilep = fopen(lobfile, "wb")) != NULL)
  {
     dvoid *chunk;
     ub4    cksz = 0, totsz = 0;
     if (logging && logfilep != NULL)
        fprintf(logfilep, "Getting total size for LOB\n");
     if (checkerr(ctxt, errhp,
                  OCILobGetLength(svchp, errhp, a_lob, &totsz)) != 0)
        return -1;/*
      * For 8.0.X the OCILogGetChunkSize will not have been called.
      * IN this case, reset the chunk size to 1K.
      */
     if (cksz == 0) cksz = DEFAULT_CHUNK_SIZE;
     if (logging && logfilep != NULL)
        fprintf(logfilep,
                 "Allocating %d bytes of memory for LOB chunks\n",
                  (int) cksz );
     /*
      * Dynamically allocate enough memory to hold a single chunk
      */
     if ((chunk = OCIExtProcAllocCallMemory(ctxt, (size_t) cksz)) != NULL)
     {
        int cnt = 1;
        ub4 amt = cksz, offset = 1;
        /*
         * Read data from the LOB and write it to the file while
         * more data remains.
         */
        while (offset < (int)totsz)
        {
           if (logging && logfilep != NULL)
              fprintf(logfilep,
                       "Reading chunk %d starting at %d for max %d bytes\n",
                        cnt, (int) offset, (int) amt);
           errnum = OCILobRead(svchp, errhp, a_lob, &amt, offset,
                               chunk, cksz, (dvoid *) 0,
                               (sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 0,
                               (ub2) 0, (ub1)SQLCS_IMPLICIT);
           if (checkerr(ctxt, errhp, errnum) != 0) return -1;
           if (logging && logfilep != NULL)
              fprintf(logfilep,
                       "Successfully read chunk containing %d bytes\n",
                        (int) amt);
           if (logging && logfilep != NULL)
              fprintf(logfilep,
                       "Writing %d bytes of chunk %d to file %s\n",
                        (int) amt, cnt, lobfile);
           if (fwrite((void *)chunk, (size_t)1, (size_t)amt, lobfilep) == amt)
           {
              if (logging && logfilep != NULL)
                 fprintf(logfilep, "Successfully wrote %d bytes to file %s\n",
                         (int) amt, lobfile);
           }
           else
           {
              char *errmsg = "Write to OS file failed";
              if (logging && logfilep != NULL)
              {
                 fprintf(logfilep, "Error: %s\n", errmsg);
                 fprintf(logfilep, "Error: errno = %d\n", errno);
              }
              errnum = 20003;
              OCIExtProcRaiseExcpWithMsg(ctxt, errnum,
                                         (text *)errmsg, strlen(errmsg));
              return -1;
           }
           cnt++;
           offset += amt;
        }
        if (logfilep != NULL) fclose(logfilep);
        fclose(lobfilep);
        return 0;
     }
     else
     {
        if (logging && logfilep != NULL)
        {
           fprintf(logfilep, "Error: Unable to allocate memory\n");
           fclose(logfilep);
        }
        return -1;
     }
  }
  else
  {
     char *errmsg = "Unable to open file";
     if (logging && logfilep != NULL)
     {
        fprintf(logfilep, "Error: %s %s\n", errmsg, lobfile);
        fprintf(logfilep, "Error: errno = %d\n", errno);
        fclose(logfilep);
     }
     errnum = 20003;
     OCIExtProcRaiseExcpWithMsg(ctxt, errnum,
                                (text *)errmsg, strlen(errmsg));
     return -1;
  }
}int checkerr(OCIExtProcContext *ctxt, OCIError *errhp, sword status)
{
  sword errnum = 0;
  text errbuf[512];
  switch (status)
  {
    case OCI_SUCCESS_WITH_INFO:
       errnum = 20004;
       strcpy((char *)errbuf, "Error: OCI_SUCCESS_WITH_INFO");
       break;
    case OCI_NO_DATA:
       errnum = 20005;
       strcpy((char *)errbuf, "Error: OCI_NO_DATA");
       break;
    case OCI_NEED_DATA:
       errnum = 20006;
       strcpy((char *)errbuf, "Error: OCI_NEED_DATA");
       break;
    case OCI_INVALID_HANDLE:
       errnum = 20007;
       strcpy((char *)errbuf, "Error: OCI_INVALID_HANDLE");
       break;
    case OCI_STILL_EXECUTING:
       errnum = 20008;
       strcpy((char *)errbuf, "Error: OCI_STILL_EXECUTING");
       break;
    case OCI_CONTINUE:
       errnum = 20009;
       strcpy((char *)errbuf, "Error: OCI_CONTINUE");
       break;
    case OCI_ERROR:
       (void)OCIErrorGet((dvoid *) errhp, (ub4) 1, (text *) NULL,
                         (sb4 *) &errnum, (text *) errbuf,
                         (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
       break;
    default:
       break;
  }
  if (errnum != 0)
  {
     if (logging && logfilep != NULL)
     {
        fprintf(logfilep, "Error: %d %s\n", errnum, errbuf);
        fclose(logfilep);
     }
     (void)OCIExtProcRaiseExcpWithMsg(ctxt, errnum, errbuf, strlen(errbuf));
  }
  return errnum;
}
/*end of file lob2file.c*/将文件lob2file.c放到D:\oracle\ora81\plsql\demo目录下：然后在dos下执行下述编译语句将该文件编译成lob2file.dll文件，make.bat文件包含如下：
@echo off
cl -ID:\oracle\ora81\oci\include -D_DLL -D_MT /LD -Zi lob2file.c /link
D:\oracle\ora81\oci\lib\msvc\oci.lib msvcrt.lib  /nod:libcmt  /DLL
/EXPORT:lob2file  /EXPORT:checkerr
进入D:\oracle\ora81\plsql\demo目录(DOS状态)执行make就可以将lob2file.c编
译成lob2file.dll文件了。
然后用scott连到sql*plus中，执行
SQL>CREATE OR REPLACE LIBRARY  UTLLOBLIB
    AS 'D:\oracle\ora81\plsql\demo\lob2file.dll'
/
SQL>GRANT EXECUTE ON UTLLOBLIB TO PUBLIC
然后执行下述代码：
create or replace package utl_lob is
procedure SetLogging(which BOOLEAN, a_log VARCHAR2);
procedure UnloadToFile(a_lob BLOB, a_file VARCHAR2, status OUT NUMBER);
end utl_lob;
/
show errors
create or replace package body utl_lob is
logSetting  BOOLEAN := FALSE;
logFileName VARCHAR2(512) := NULL;
procedure SetLogging(which BOOLEAN, a_log VARCHAR2) is
begin
    logSetting := which;
    if (logSetting = TRUE) then
       logFileName := a_log;
    else
       logFileName := NULL;
    end if;
end; function LobToFile(a_lob BLOB, a_file VARCHAR2,a_log VARCHAR2, logging BOOLEAN)
return BINARY_INTEGER   as external
name "lob2file"
library utlloblib
LANGUAGE C
with context
parameters ( a_lob OCILOBLOCATOR,
              a_lob INDICATOR SHORT,
              a_file STRING,
              a_file INDICATOR SHORT,
              a_file LENGTH INT,
              a_log STRING,
              a_log INDICATOR SHORT,
              a_log LENGTH INT,
              logging INT,
              CONTEXT,
              RETURN );
procedure UnloadToFile(a_lob BLOB, a_file VARCHAR2, status OUT NUMBER) is
begin
    status := LobToFile(a_lob, a_file, logFileName, logSetting);
end;
end utl_lob;
/
show errors
grant execute on utl_lob to public;该代码创建package utl_lob，而utl_lob调用library utlloblib，我们的测试程序调用
package utl_lob中的procedure SetLogging和UnloadToFile。在scott用户下执行如下
脚本，就可以将先前保存的COM.doc取出放到C:\DDS\EXTPROC\test.doc这个文件中，当然
C:\DDS\EXTPROC这个目录必须存在。脚本执行完毕后生成两个文件test.log与test.doc，
test.log纪录了取出的详细信息，test.doc是COM.doc的复制品，取出82K大小的文件大
约用了4秒。
--以下为测试脚本
set serveroutput on
declare
  a_blob BLOB;
  status NUMBER;
begin
  select blob_column into a_blob  from utl_lob_test;
  utl_lob.SetLogging(TRUE, 'C:\DDS\EXTPROC\test.log');
  utl_lob.UnloadToFile(a_blob, 'C:\DDS\EXTPROC\test.doc', status);
  dbms_output.put_line('Exit status = ' || status);
end;

调试易

如何将BLOB中的二进制文件倒出到操作系统中

解决方案 »