使用循環讀寫,例如:
CREATE OR REPLACE PROCEDURE LOBPrint(p_CLOB IN CLOB) AS
v_Buffer VARCHAR2(80);
v_Offset INTEGER := 1;
v_Amount INTEGER := 80;
BEGIN
LOOP
-- Read and output the next 80 characters.
DBMS_LOB.READ(p_CLOB, v_Amount, v_Offset, v_Buffer);
DBMS_OUTPUT.PUT_LINE(v_Buffer); v_Offset := v_Offset + v_Amount;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- End of loop, just return.
NULL;
END LOBPrint;
/
CREATE OR REPLACE PROCEDURE LOBPrint(p_CLOB IN CLOB) AS
v_Buffer VARCHAR2(80);
v_Offset INTEGER := 1;
v_Amount INTEGER := 80;
BEGIN
LOOP
-- Read and output the next 80 characters.
DBMS_LOB.READ(p_CLOB, v_Amount, v_Offset, v_Buffer);
DBMS_OUTPUT.PUT_LINE(v_Buffer); v_Offset := v_Offset + v_Amount;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- End of loop, just return.
NULL;
END LOBPrint;
/
在sqlplus worksheet 中有没有办法直接使用
update语句完成?
我是初学的,能解释一下你上面写的存储过程吗,是要将写进数据库的数据作为一个参数传到存储过程中吗,谢谢!!
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" 后继续。哪里错了?