插入，查询Blob类型数据

求 救！！插入，查询Blob类型数据

作者： 阿拉伯酋长     发表时间：2002/12/04 07:29pm

我已经成功创建表：swdb  表结构如下 SVRMGR> desc swdb; Column Name                    Null?    Type ------------------------------ -------- ---- USERID                                  CHAR(20) ZWLEN                                   NUMBER(38) SWDATA                                  BLOB(4000) 当我查询时（其实没有记录）select * from swdb: SVRMGR> select * from swdb; ORA-00932: inconsistent datatypes 不知道什么原因？请各位高手指教！！

此文章相关评论:

该文章有4 个相关评论如下：(点这儿可以发表评论 )

thingsfly 发表于： 2002/12/04 07:32pm 对啊，大字段比如long，long raw和blob、cblob是没有办法在sqlplus和svrmgrl工具中查看的了。 你可以使用其他工具来查看。 或是使用 pfile文件把blob文件存到OS中， 然后你就可以看了。   阿拉伯酋长 发表于： 2002/12/04 07:41pm [quote][b]下面引用由[u]thingsfly[/u]在 [i]2002/12/04 07:32pm[/i] 发表的内容：[/b] 对啊，大字段比如long，long raw和blob、cblob是没有办法在sqlplus和svrmgrl工具中查看的了。 你可以使用其他工具来查看。 或是使用 pfile文件把blob文件存到OS中， 然后你就可以看了。 [/quote] 多写救星，你就是我的天使！ 但如何使用 pfile文件把blob文件存到OS中？ OS指得是什么？pfile中存放的是我的记录内容吗？|userid数据|zwleng数 据|Blob数据|这样的格式吗？ :emn12:  :emn12:   thingsfly 发表于： 2002/12/04 07:53pm 表结构： 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;