create table news(
news_id number(9),
news_title varchar2(80),
news_text clob
);我写了个过程:
CLOB字段操作可传递表名table_name,表的唯一标志字段名field_id,clob字段名field_name,记录号v_id,开始处理字符的位置v_pos,传入的字符串变量v_clob
create or replace procedure updateclob(
table_name in varchar2,
field_id in varchar2,
field_name in varchar2,
v_id in number,
v_pos in number,
v_clob in varchar2)
is
lobloc clob;
c_clob varchar2(32767);
amt binary_integer;
pos binary_integer;
query_str varchar2(1000);
begin
pos:=v_pos*32766+1;
amt := length(v_clob);
c_clob:=v_clob;
query_str :='select '||field_name||' from '||table_name||' where '||field_id||'= :id for update ';
--initialize buffer with data to be inserted or updated
EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;
--from pos position, write 32766 varchar2 into lobloc
dbms_lob.write(lobloc, amt, pos, c_clob);
commit;
exception
when others then
rollback;
end;
调用方法为:
begin
EXECUTE updateclob('news','news_id','news_text',11,1,'sfdfsd');
end;
调用时出错:
ERROR 位于第 2 行:
ORA-06550: 第 2 行, 第 9 列:
PLS-00103: 出现符号 "UPDATE_CLOB"在需要下列之一时:
:=.(@%;immediate
符号 ":=" 被替换为 "UPDATE_CLOB" 后继续。哪里错了?
news_id number(9),
news_title varchar2(80),
news_text clob
);我写了个过程:
CLOB字段操作可传递表名table_name,表的唯一标志字段名field_id,clob字段名field_name,记录号v_id,开始处理字符的位置v_pos,传入的字符串变量v_clob
create or replace procedure updateclob(
table_name in varchar2,
field_id in varchar2,
field_name in varchar2,
v_id in number,
v_pos in number,
v_clob in varchar2)
is
lobloc clob;
c_clob varchar2(32767);
amt binary_integer;
pos binary_integer;
query_str varchar2(1000);
begin
pos:=v_pos*32766+1;
amt := length(v_clob);
c_clob:=v_clob;
query_str :='select '||field_name||' from '||table_name||' where '||field_id||'= :id for update ';
--initialize buffer with data to be inserted or updated
EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;
--from pos position, write 32766 varchar2 into lobloc
dbms_lob.write(lobloc, amt, pos, c_clob);
commit;
exception
when others then
rollback;
end;
调用方法为:
begin
EXECUTE updateclob('news','news_id','news_text',11,1,'sfdfsd');
end;
调用时出错:
ERROR 位于第 2 行:
ORA-06550: 第 2 行, 第 9 列:
PLS-00103: 出现符号 "UPDATE_CLOB"在需要下列之一时:
:=.(@%;immediate
符号 ":=" 被替换为 "UPDATE_CLOB" 后继续。哪里错了?
跟蹤調試一下,看看具體哪里出錯了
ERROR 位于第 2 行:
ORA-06550: 第 2 行, 第 9 列:
PLS-00103: 出现符号 "UPDATECLOB"在需要下列之一时:
:=.(@%;immediate
符号 ":=" 被替换为 "UPDATECLOB" 后继续。
:)
set serveroutput on;
declare i ??;
begin
updataclob('??','??','??','??',??,i);
dbms_output.put_line(i);
end;
/
begin
updateclob('news','news_id','news_text',11,1,'sfdfsd');
end;