解决方案 »
- oracle创建索引和复合索引
- 查询记录显示自然序数
- 麻烦帮忙写个sql语句
- 存储过程中如何查询动态表并插入一个游标
- A'||i||' into '||sz_readdb(i)||' from o_inapp_v where 医院ID=''医院ID''' 返回为找回任何数据
- 新手提问:关于字段更新
- VB如何来读取包含有BLOB类型的表,用select * from 表名会提示"未指定错误"
- 如何完全删除数据,
- 计算1+2+3+...+100=?并显示从第10行第10列起显示其结果。急急!!!
- 再向高手请教一个out join的问题
- 请教一sql语句的写法
- 求助:proc调用存储过程写二进制数据
dbms_lob.write(i_lob_loc, p_size, 1, p_buffer);
你这里为什么要传RAW 进去啊,这样的TYPE传进去应该不行吧,直接传i_data啊不需要转换成RAW,你可以试试,我能够成功,如果传RAW会报错
-> dbms_lob.write(i_lob_loc, p_size, 1, i_data);
直接用dbms_lob.write(i_lob_loc, p_size, 1, i_data);的话会报错:
直接用dbms_lob.write(i_lob_loc, p_size, 1, i_data);的话会报错:
ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
我在pro c中是这么处理的:
unsigned char data[16384] = {..........};///byte随机数
varchar i_data[16384];
memcpy(i_data.arr, data, 16384);
i_data.len = 16384;
或者另一解决方案:将我的存储过程输入参数改为blob型,但问题是在pro*c中如何将blob locator变量指向我的byte数据????
------------------------------------------------------------------------------
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_lob.htm#i999593WRITE ProceduresThis procedure writes a specified amount of data into an internal LOB, starting from an absolute offset from the beginning of the LOB. The data is written from the buffer parameter.WRITE replaces (overwrites) any data that already exists in the LOB at the offset, for the length you specify.SyntaxDBMS_LOB.WRITE (
lob_loc IN OUT NOCOPY BLOB,
amount IN INTEGER,
offset IN INTEGER,
buffer IN RAW);DBMS_LOB.WRITE (
lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
amount IN INTEGER,
offset IN INTEGER,
buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET);
--创建测试表
CREATE Table T_BLOB
(ID NUMBER,
BLB Blob);
--编写PLSQL
declare
v_blob BLOB;
amount BINARY_INTEGER;
offset INTEGER;
v_char VARCHAR2(1000);
Begin
--往BLOB类型的字段插入记录,不能直接填入值,必须先往BLOB字段插入一个EMPTY_BLOB()
INSERT INTO T_BLOB VALUES(1,EMPTY_BLOB());
COMMIT;
--更新和新增一样要将BLOB字段设置为EMPTY_BLOB()
update T_BLOB set blb = empty_blob() where id = 1;
select blb into v_blob from T_BLOB where id = 1 for update;
DBMS_LOB.OPEN(v_blob,DBMS_LOB.LOB_READWRITE);
v_char := 'This is an sample!sadfsa';
amount := LENGTHB(v_char);
offset := 1;
--UTL_RAW.cast_to_raw函数将字符串转换成二进制数
DBMS_LOB.WRITE(v_blob,amount,offset,UTL_RAW.cast_to_raw(v_char));
DBMS_LOB.CLOSE(v_blob);
commit;
end;
proc中调用存储过程写byte数据时,传入参数使用raw型就可以了。proc中:
unsigned char buffer[BUFFER_SIZE];
EXEC SQL VAR bufferIS RAW(BUFFER_SIZE);///必须的,可以将buffer当做raw型使用了procedure中直接调用dbms_lob.write就可以了。