太谢谢(阿木)了,没想到你的代码居然让我用到了,小弟在此不胜感激了。
解决方案 »
- oracle 合并记录
- 数据库导入出错!
- sequence是怎么实现的?
- 下面的SQL语句在pl/sql developer中为什么显示:(no result set)?本来应该显示:
- 登陆oms时 无法连接到management server localhost的错误
- 查询数据时提示如下错误:ORA-03212: 无法在本地管理的表空间创建临时段。请问是什么原因造成的,如何解决?谢谢!
- Unique字段的值想通过c#来修改,怎么实现??
- 为什么输入的是汉字 到了数据库里 就变成了? 请大家帮忙
- 怎样维护好
- 为什么我的SQL Explore里面不能正确的显示中文???
- 求ID为03的记录号SQL语句或函数怎样写?
- 创建存储过程,输出参数可以是cursor(游标)类型的么?
exmple 4.
首先是Getting BLOB and CLOB Locators from a Result Set
// Select LOB locator into standard result set.
ResultSet rs =stmt.executeQuery ("SELECT blob_col, clob_col FROM lob_table");
while (rs.next())
{// Get LOB locators into Java wrapper classes.
oracle.jdbc2.Blob blob = (oracle.jdbc2.Blob)rs.getObject(1);
oracle.jdbc2.Clob clob = (oracle.jdbc2.Clob)rs.getObject(2);
[...process...]
}
然后是Read BLOB data from BLOB locator.
InputStream byte_stream = my_blob.getBinaryStream();
byte [] byte_array = new byte [10];
int bytes_read = byte_stream.read(byte_array);
和Writing BLOB Data
java.io.OutputStream outstream;
// read data into a byte array
byte[] data = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9};
// write the array of binary data to a BLOB
outstream = ((BLOB)my_blob).getBinaryOutputStream();
outstream.write(data);
还有Passing a BLOB Locator to a Prepared Statement
OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement
"INSERT INTO blob_table VALUES(?)");
ops.setBLOB(1, my_blob);
ops.execute();
最后应该注意:
insert的时候一定要用empty_blob()初始化
stmt.execute ("insert into my_blob_table values ('row1', empty_blob()"); (四)在pro*c中的处理
PRO*C可以用三种方式对LOB字段处理。
1、The DBMS_LOB package inside PL/SQL blocks.
2、OCI (Oracle Call Interface) function calls.
3、Embedded SQL statements.
Embedded SQL statements.的方式简单而且比较灵活。OTN上提供一个例子:
In this example we will be reading data from a BLOB with an unknown arbitrary length into a buffer and then writing the data from the buffer into an external file.
Our buffer is small, so depending on the size of the BLOB we are reading, we may
be able to read the BLOB value into the buffer in a single READ statement or we
may be required to utilize a standard polling method instead.
First we start off with oci.h and some simple local variable declarations
example 5.
#include <oci.h>
OCIBlobLocator *blob ;
FILE *fp ;
unsigned int amt, offset = 1 ;
Now we need a buffer to store the BLOB value and then write to the file from:
#define MAXBUFLEN 5000
unsigned char buffer[MAXBUFLEN] ;
EXEC SQL VAR buffer IS RAW(MAXBUFLEN) ;
Allocate the BLOB host variable and select a BLOB which we will READ:
EXEC SQL ALLOCATE :blob ;
EXEC SQL SELECT a_blob INTO :blob FROM lob_table WHERE ... ;
We can then open the external file to which we will write the BLOB value:
fp = fopen((const char *)"image.gif", (const char *)"w") ;
If the buffer can hold the entire LOB value in a single READ we need to catch the
NOT FOUND condition to signal LOB READ termination:
EXEC SQL WHENEVER NOT FOUND GOTO end_of_lob ;
Now do our first READ.We set the amount to the maximum value of 4 Gigabytes. It
is larger than our buffer so if the LOB doesn't fit we will READ using a polling
mode:
amt = 4294967295 ;
EXEC SQL LOB READ :amt FROM :blob AT ffset INTO :buffer ;
If we get here then it means that the buffer was not large enough to hold the entire
LOB value, so we must write what we have using binary I/O and continue reading:
(void) fwrite((void *)buffer, (size_t)MAXBUFLEN, (size_t)1, fp) ;
We use a standard polling method to continue reading with the LOB READ inside
of an infinite loop. We can set up the NOT FOUND condition to terminate the loop:
EXEC SQL WHENEVER NOT FOUND DO break ;
while (TRUE)
{
During polling, the offset is not used so we can omit it in subsequent LOB READs.
We need the amount, however, because it will tell us how much was READ in the
last READ invocation
EXEC SQL LOB READ :amt FROM :blob INTO :buffer ;
(void) fwrite((void *)buffer, (size_t)MAXBUFLEN, (size_t)1, fp) ;
}
Here, we have reached the end of the LOB value. The amount holds the amount of
the last piece that was READ. During polling, the amount for each interim piece
was set to MAXBUFLEN, or the maximum size of our buffer:
end_of_lob:
(void) fwrite((void *)buffer, (size_t)amt, (size_t)1, fp) ;