建义使用lob对象类型,对于实现你的要求没办法.
解决方案 »
- 急求!!oracle9i dblink ora-12154:tns无法处理服务器名 问题
- 无法连接虚拟机的ORACLE数据库
- 请教:关于Oracle的高级复制功能
- 请问怎么在oracle的存储过程中执行一个shell脚本,高手指点
- 在命令提示符下用EXP导出数据库,要导出的用户:这项写什么?
- sql语句长度问题
- oracle 10在Administration Assistant for Windows中重启数据库后不能进web管理界面
- 令人困惑的循环和条件语句问题,请帮忙看看,谢谢.
- 等了一下午没有一个人回答我的问题??????
- 导入Access数据库的时候,每次新安装了Oracle系统的导入了一次,就无法再导入了?提示严重错误440
- 不能启动ORACLE AGENTS!
- Linux安装oracle出现了点问题!请高手帮忙!着急中!!!!
表结构:
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;
/
此时可以使用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;
}
} 還有!
可我只能發三次!
論壇這點不好!
即可以保存文本,又可以方便的查看,而且可以实现insert的。