在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只能用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;
/
大家對上面測試腳本稍微改動一下，形成一個帶參數的procedure供應用程序調用就可
以了。
