vb开发基于oracle的应用,为了读出clob字段:写了过程,又写了函数,一到dbms_lob.read函数就不对了;
函数如下
create or replace procedure read_clob(
table_name in varchar2,
field_id in varchar2,
field_name in varchar2,
v_id in varchar2,
v_pos in number,
v_out out number
)
is
lob_loc CLOB;-- CHARACTER SET ANY_CS;
amount binary_integer;
offset integer;
buffer VARCHAR2(2000);-- CHARACTER SET lob_loc%CHARSET;
query_str varchar2(200);
begin
offset:=1 +(v_pos-1)*1000;
amount := 1000;
query_str :='select '||field_name||' from '||table_name||' where '||field_id||'= :id ';
--initialize buffer
EXECUTE IMMEDIATE query_str INTO lob_loc USING v_id;
--v_out := dbms_lob.getlength(lob_loc);
--v_out := 33;
--dbms_output.put_line(v_out);
dbms_lob.read(lob_loc, amount, v_pos, buffer);
v_out := buffer;
end;执行
set serveroutput on;
declare i varchar2(2000);
begin
READ_clob('patentinfor','an','cam','CN 85100862',1,i);
dbms_output.put_line(i);
end;
/报错:
declare i varchar2(2000);
*
ERROR 位于第 1 行:
ORA-06502: PL/SQL: 数字或值错误 : invalid LOB locator specified: ORA-22275
ORA-06512: 在"SYS.DBMS_LOB", line 648
ORA-06512: 在"PATENTINFOR.READ_CLOB", line 23
ORA-06512: 在line 3我找不到出路了,请各位大虾帮忙看看吧。
函数如下
create or replace procedure read_clob(
table_name in varchar2,
field_id in varchar2,
field_name in varchar2,
v_id in varchar2,
v_pos in number,
v_out out number
)
is
lob_loc CLOB;-- CHARACTER SET ANY_CS;
amount binary_integer;
offset integer;
buffer VARCHAR2(2000);-- CHARACTER SET lob_loc%CHARSET;
query_str varchar2(200);
begin
offset:=1 +(v_pos-1)*1000;
amount := 1000;
query_str :='select '||field_name||' from '||table_name||' where '||field_id||'= :id ';
--initialize buffer
EXECUTE IMMEDIATE query_str INTO lob_loc USING v_id;
--v_out := dbms_lob.getlength(lob_loc);
--v_out := 33;
--dbms_output.put_line(v_out);
dbms_lob.read(lob_loc, amount, v_pos, buffer);
v_out := buffer;
end;执行
set serveroutput on;
declare i varchar2(2000);
begin
READ_clob('patentinfor','an','cam','CN 85100862',1,i);
dbms_output.put_line(i);
end;
/报错:
declare i varchar2(2000);
*
ERROR 位于第 1 行:
ORA-06502: PL/SQL: 数字或值错误 : invalid LOB locator specified: ORA-22275
ORA-06512: 在"SYS.DBMS_LOB", line 648
ORA-06512: 在"PATENTINFOR.READ_CLOB", line 23
ORA-06512: 在line 3我找不到出路了,请各位大虾帮忙看看吧。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货