在Oracle 8i的SQL*Plus中如何利用LOB字段存取操作系统二进制文件
广东省岭澳核电有限公司工程控制处管理信息科(518124) 黄福同
Oracle 8i数据库系统功能比前面版本更加完善，尤其是出现了BLOB,CLOB,NCLOB,
BFILE这些LOB(大型对象)类型来取代功能有限的LONG、LONGRAW类型。BLOB字段最
大长度为4G(4,294,967,295)字节,而且不再象LONGRAW那样每个表中只是限制有一
个字段是LONGRAW（最长2G）型的。BLOB,CLOB,NCLOB为内部BLOB(数据通常在数据
库中存放)，BFILE为外部LOB（所存储的只是指向外部操作系统文件的指针）,用户可
以使用PL/SQL的包DBMS_LOB来处理LOB数据，但是遗憾的是，DBMS_LOB包只能将二进
制操作系统文件写入到BLOB字段中，却无法将BLOB字段中的二进制操作系统文件取回
到操作系统中，估计将来会有所改善。本文将就如何在SQL*Plus将WORD文件存入取出
ORACLE中作详细解释说明，供各位同行参考。
实验的软件环境如下：Windows 2000 Advanced Server，Oracle 8.1.7，VC++6.0+SP5
硬件环境如下：双PIII866 CPU，768M内存
在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只能用proc与OCI来实现该任务，所以Oracle服务器端必须支持proc
以及外部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
1<stdio.h>
2#include <stdlib.h>
3#include <errno.h>
4#include <oci.h>
5#include <ociextp.h>
6#define DEFAULT_CHUNK_SIZE 1024
7static int logging;
8static char logfile[512];
9static FILE *logfilep = NULL;
10int lob2file ( OCILobLocator *a_lob, /* the LOB */
11short lbind, /* LOB indicator */
12char *path, /* file to write */
13short pind, /* file indicator */
14int plen, /* filename length */
15char *lpath, /* logfile name */
16short lpind, /* logfile indicator */
17int lplen, /* logfile name length */
18int logit, /* logging enabled? */
19OCIExtProcContext *ctxt /* OCI Context */
20)
21{
22sword errnum = 0;
23OCIEnv *envhp = NULL;
24OCISvcCtx *svchp = NULL;
25OCIError *errhp = NULL;
26char lobfile[512];
27FILE *lobfilep = NULL;
28/*
29* If required, open the log file for writing
30* Use the user provided logfile name if possible
31* Otherwise, default the logfile to lob2file.log
32*/
33logging = logit;
34if (logging)
35{
36if (lpind == -1 || lplen == 0 || lplen >= 512)
37{
38strcpy(logfile, "lob2file.log");
39}
40else
41{
42strncpy(logfile, lpath, lplen);
43logfile[lplen] = '[内容]';
44}
45logfilep = fopen(logfile, "w");
46if (logfilep == NULL)
47{
48if ((logfilep = fopen("lob2file.log", "w")) != NULL)
49{
50fprintf(logfilep, "Error: Unable to open logfile %s\n",
51
52logfile);
53fprintf(logfilep, "Error: errno = %d\n", errno);
54}
55}
56}
57/*
58* Retrieve the environment, service context, and error handles
59*/
60if ((errnum = OCIExtProcGetEnv(ctxt, &envhp,
61&svchp, &errhp)) != OCIEXTPROC_SUCCESS)
62{
63if (logging && logfilep != NULL)
64{
65fprintf(logfilep, "Error: Call to OCIExtProcGetEnv failed\n");
66fprintf(logfilep, "Error: OCIExtProcGetEnv returned %d\n",
67
68errnum);
69fclose(logfilep);
70return -1;
71}
72}
73/*
74* Verify that the user has provided a name for the output file
75*/
76if (pind == -1 || plen == 0)
77{
78char *errmsg = "Pathname is null or empty string";
79if (logging && logfilep != NULL)
80{
81fprintf(logfilep, "Error: %s\n", errmsg);
82fclose(logfilep);
83}
84errnum = 20001;
85OCIExtProcRaiseExcpWithMsg(ctxt, errnum, (text *)errmsg,
86
87strlen(errmsg));
88return -1;
89}
90else /* Use the provided name */
91{
92strncpy(lobfile, path, plen);
93lobfile[plen] = '[内容]';
94}
95/*
96* Verify that the user has provided a valid LOB locator
97*/
98if (lbind == -1)
99{
100char *errmsg = "LOB locator is null";
101if (logging && logfilep != NULL)
102{
103fprintf(logfilep, "Error: %s\n", errmsg);
104fclose(logfilep);
105}
106errnum = 20002;
107OCIExtProcRaiseExcpWithMsg(ctxt, errnum, (text *)errmsg,
108
109strlen(errmsg));
110return -1;
111}
112if (logging && logfilep != NULL)
113fprintf(logfilep, "Opening OS file in write mode\n");
114/*
115* Open the output file for writing
116*/
117if ((lobfilep = fopen(lobfile, "wb")) != NULL)
118{
119dvoid *chunk;
120ub4 cksz = 0, totsz = 0;
121if (logging && logfilep != NULL)
122fprintf(logfilep, "Getting total size for LOB\n");
123if (checkerr(ctxt, errhp,
124OCILobGetLength(svchp, errhp, a_lob, &totsz)) != 0)
125return -1;
126/*
127* For 8.0.X the OCILogGetChunkSize will not have been called.
128* IN this case, reset the chunk size to 1K.
129*/
130if (cksz == 0) cksz = DEFAULT_CHUNK_SIZE;
131if (logging && logfilep != NULL)
132fprintf(logfilep,
133"Allocating %d bytes of memory for LOB chunks\n",
134(int) cksz );
135/*
136* Dynamically allocate enough memory to hold a single chunk
137*/
138if ((chunk = OCIExtProcAllocCallMemory(ctxt, (size_t) cksz)) != NULL)
139{
140int cnt = 1;
141ub4 amt = cksz, offset = 1;
142/*
143* Read data from the LOB and write it to the file while
144* more data remains.
145*/
146while (offset < (int)totsz)
147{
148if (logging && logfilep != NULL)
149fprintf(logfilep,
150"Reading chunk %d starting at %d for max %d
151
152bytes\n",
153cnt, (int) offset, (int) amt);
154errnum = OCILobRead(svchp, errhp, a_lob, &amt, offset,
155chunk, cksz, (dvoid *) 0,
156(sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 0,
157(ub2) 0, (ub1)SQLCS_IMPLICIT);
158if (checkerr(ctxt, errhp, errnum) != 0) return -1;
159if (logging && logfilep != NULL)
160fprintf(logfilep,
161"Successfully read chunk containing %d bytes\n",
162(int) amt);
163if (logging && logfilep != NULL)
164fprintf(logfilep,
165"Writing %d bytes of chunk %d to file %s\n",
166(int) amt, cnt, lobfile);
167if (fwrite((void *)chunk, (size_t)1, (size_t)amt, lobfilep) ==
168
169amt)
170{
171if (logging && logfilep != NULL)
172fprintf(logfilep, "Successfully wrote %d bytes to file
173
174%s\n",
175(int) amt, lobfile);
176}
177else
178{
179char *errmsg = "Write to OS file failed";
180if (logging && logfilep != NULL)
181{
182fprintf(logfilep, "Error: %s\n", errmsg);
183fprintf(logfilep, "Error: errno = %d\n", errno);
184}
185errnum = 20003;
186OCIExtProcRaiseExcpWithMsg(ctxt, errnum,
187(text *)errmsg, strlen(errmsg));
188return -1;
189}
190cnt++;
191offset += amt;
192}
193if (logfilep != NULL) fclose(logfilep);
194fclose(lobfilep);
195return 0;
196}
197else
198{
199if (logging && logfilep != NULL)
200{
201fprintf(logfilep, "Error: Unable to allocate memory\n");
202fclose(logfilep);
203}
204return -1;
205}
206}
207else
208{
209char *errmsg = "Unable to open file";
210if (logging && logfilep != NULL)
211{
212fprintf(logfilep, "Error: %s %s\n", errmsg, lobfile);
213fprintf(logfilep, "Error: errno = %d\n", errno);
214fclose(logfilep);
215}
216errnum = 20003;
217OCIExtProcRaiseExcpWithMsg(ctxt, errnum,
218(text *)errmsg, strlen(errmsg));
219return -1;
220}
221}
222
223int checkerr(OCIExtProcContext *ctxt, OCIError *errhp, sword status)
224{
225sword errnum = 0;
226text errbuf[512];
227switch (status)
228{
229case OCI_SUCCESS_WITH_INFO:
230errnum = 20004;
231strcpy((char *)errbuf, "Error: OCI_SUCCESS_WITH_INFO");
232break;
233case OCI_NO_DATA:
234errnum = 20005;
235strcpy((char *)errbuf, "Error: OCI_NO_DATA");
236break;
237case OCI_NEED_DATA:
238errnum = 20006;
239strcpy((char *)errbuf, "Error: OCI_NEED_DATA");
240break;
241case OCI_INVALID_HANDLE:
242errnum = 20007;
243strcpy((char *)errbuf, "Error: OCI_INVALID_HANDLE");
244break;
245case OCI_STILL_EXECUTING:
246errnum = 20008;
247strcpy((char *)errbuf, "Error: OCI_STILL_EXECUTING");
248break;
249case OCI_CONTINUE:
250errnum = 20009;
251strcpy((char *)errbuf, "Error: OCI_CONTINUE");
252break;
253case OCI_ERROR:
254(void)OCIErrorGet((dvoid *) errhp, (ub4) 1, (text *) NULL,
255(sb4 *) &errnum, (text *) errbuf,
256(ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
257break;
258default:
259break;
260}
261if (errnum != 0)
262{
263if (logging && logfilep != NULL)
264{
265fprintf(logfilep, "Error: %d %s\n", errnum, errbuf);
266fclose(logfilep);
267}
268(void)OCIExtProcRaiseExcpWithMsg(ctxt, errnum, errbuf,
269
270strlen(errbuf));
271}
272return errnum;
273}
274/*end of file lob2file.c*/
275
276将文件lob2file.c放到D:\oracle\ora81\plsql\demo目录下：然后在dos下执行下述编
277
278译
279语句将该文件编译成lob2file.dll文件，make.bat文件包含如下：
280
281@echo off
282cl -ID:\oracle\ora81\oci\include -D_DLL -D_MT /LD -Zi lob2file.c /link
283D:\oracle\ora81\oci\lib\msvc\oci.lib msvcrt.lib /nod:libcmt /DLL
284/EXPORT:lob2file /EXPORT:checkerr
285
286进入D:\oracle\ora81\plsql\demo目录(DOS状态)执行make就可以将lob2file.c编
287译成lob2file.dll文件了。
288
289然后用scott连到sql*plus中，执行
290SQL>CREATE OR REPLACE LIBRARY UTLLOBLIB
291AS 'D:\oracle\ora81\plsql\demo\lob2file.dll'
292/
293SQL>GRANT EXECUTE ON UTLLOBLIB TO PUBLIC
294
295然后执行下述代码：
296create or replace package utl_lob is
297procedure SetLogging(which BOOLEAN, a_log VARCHAR2);
298procedure UnloadToFile(a_lob BLOB, a_file VARCHAR2, status OUT NUMBER);
299end utl_lob;
300/
301show errors
302create or replace package body utl_lob is
303logSetting BOOLEAN := FALSE;
304logFileName VARCHAR2(512) := NULL;
305procedure SetLogging(which BOOLEAN, a_log VARCHAR2) is
306begin
307logSetting := which;
308if (logSetting = TRUE) then
309logFileName := a_log;
310else
311logFileName := NULL;
312end if;
313end;
314
315function LobToFile(a_lob BLOB, a_file VARCHAR2,a_log VARCHAR2, logging
316
317BOOLEAN)
318return BINARY_INTEGER as external
319name "lob2file"
320library utlloblib
321LANGUAGE C
322with context
323parameters ( a_lob OCILOBLOCATOR,
324a_lob INDICATOR SHORT,
325a_file STRING,
326a_file INDICATOR SHORT,
327a_file LENGTH INT,
328a_log STRING,
329a_log INDICATOR SHORT,
330a_log LENGTH INT,
331logging INT,
332CONTEXT,
333RETURN );
334procedure UnloadToFile(a_lob BLOB, a_file VARCHAR2, status OUT NUMBER) is
335begin
336status := LobToFile(a_lob, a_file, logFileName, logSetting);
337end;
338end utl_lob;
339/
340show errors
341grant execute on utl_lob to public;
342
343该代码创建package utl_lob，而utl_lob调用library utlloblib，我们的测试程序调
344
345用
346package utl_lob中的procedure SetLogging和UnloadToFile。在scott用户下执行如下
347脚本，就可以将先前保存的COM.doc取出放到C:\DDS\EXTPROC\test.doc这个文件中，当
348
349然
350C:\DDS\EXTPROC这个目录必须存在。脚本执行完毕后生成两个文件test.log与test.doc
351
352，
353test.log纪录了取出的详细信息，test.doc是COM.doc的复制品，取出82K大小的文件大
354
355约用了4秒。
356
357\--以下为测试脚本
358set serveroutput on
359declare
360a_blob BLOB;
361status NUMBER;
362begin
363select blob_column into a_blob from utl_lob_test;
364utl_lob.SetLogging(TRUE, 'C:\DDS\EXTPROC\test.log');
365utl_lob.UnloadToFile(a_blob, 'C:\DDS\EXTPROC\test.doc', status);
366dbms_output.put_line('Exit status = ' || status);
367end;
368/
369大家对上面测试脚本稍微改动一下，形成一个带参数的Procedure供应用程序调用就可
370
371以了。</ociextp.h></oci.h></errno.h></stdlib.h></stdio.h>