在Oracle 8i的SQL*Plus中如何利用LOB字段存取操作系統(tǒng)二進制文件
廣東省嶺澳核電有限公司工程控制處治理信息科(518124) 黃福同
Oracle 8i數(shù)據(jù)庫系統(tǒng)功能比前面版本更加完善，尤其是出現(xiàn)了BLOB,CLOB,NCLOB,
BFILE這些LOB(大型對象)類型來取代功能有限的LONG、LONGRAW類型。BLOB字段最
大長度為4G(4,294,967,295)字節(jié),而且不再象LONGRAW那樣每個表中只是限制有一
個字段是LONGRAW（最長2G）型的。BLOB,CLOB,NCLOB為內(nèi)部BLOB(數(shù)據(jù)通常在數(shù)據(jù)
庫中存放)，BFILE為外部LOB（所存儲的只是指向外部操作系統(tǒng)文件的指針）,用戶可
以使用PL/SQL的包DBMS_LOB來處理LOB數(shù)據(jù)，但是遺憾的是，DBMS_LOB包只能將二進
制操作系統(tǒng)文件寫入到BLOB字段中，卻無法將BLOB字段中的二進制操作系統(tǒng)文件取回
到操作系統(tǒng)中，估計將來會有所改善。本文將就如何在SQL*Plus將Word文件存入取出
ORACLE中作具體解釋說明，供各位同行參考。實驗的軟件環(huán)境如下：windows 2000 Advanced Server，Oracle 8.1.7，VC++6.0+SP5
硬件環(huán)境如下：雙PIII866 CPU，768M內(nèi)存在internal這個用戶下給scott用戶授權(quán)如下：
SQL>grant create any Directory to scott;
SQL>grant create any library to scott;
在scott這個用戶下執(zhí)行下述語句：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然后執(zhí)行下面語句就將C:/DDS/EXTPROC目錄下的word文件COM.doc存入到utl_lob_test
表中的blob_column字段中了。declare
a_blob BLOB;
a_bfile BFILE := BFILENAME('UTLLOBDIR','COM.doc'); --用來指向外部操作系統(tǒng)文件
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來檢測是否已經(jīng)將該word文件存入
到blob字段中了。如：
SQL> select dbms_lob.getlength(blob_column) from UTL_LOB_TEST;
結(jié)果如下：
DBMS_LOB.GETLENGTH(BLOB_COLUMN)
-------------------------------
83968
說明該word文件已經(jīng)存入到blob字段中去了。
下面將就如何取出該word文件到操作系統(tǒng)下作具體解釋：
Oracle8.1.7只能用pro*c與OCI來實現(xiàn)該任務，所以Oracle服務器端必須支持pro*c
以及外部library，Oracle8.1.7數(shù)據(jù)庫默認安裝為支持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中的二進制文件倒出到操作系統(tǒng)中。
/*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下執(zhí)行下述編譯
語句將該文件編譯成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狀態(tài))執(zhí)行make就可以將lob2file.c編
譯成lob2file.dll文件了。然后用scott連到sql*plus中，執(zhí)行
SQL>CREATE OR REPLACE LIBRARY UTLLOBLIB
AS 'D:/oracle/ora81/plsql/demo/lob2file.dll'
/
SQL>GRANT EXECUTE ON UTLLOBLIB TO PUBLIC然后執(zhí)行下述代碼：
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;
該代碼創(chuàng)建package utl_lob，而utl_lob調(diào)用library utlloblib，我們的測試程序調(diào)用
package utl_lob中的procedure SetLogging和UnloadToFile。在scott用戶下執(zhí)行如下
腳本，就可以將先前保存的COM.doc取出放到C:/DDS/EXTPROC/test.doc這個文件中，當然
C:/DDS/EXTPROC這個目錄必須存在。腳本執(zhí)行完畢后生成兩個文件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;
/
大家對上面測試腳本稍微改動一下，形成一個帶參數(shù)的Procedure供給用程序調(diào)用就可以了。
