分享
 
 
 

在Oracle 8i的SQL*Plus中如何利用LOB字段存取操作系统二进制文件

王朝oracle·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

在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供给用程序调用就可

以了。

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有