不足之处大家多补充[@more@]
create or replace procedure p_public_clob(table_name varchar2,
pk number,
CONTENT_1 varchar2,
CONTENT_2 varchar2,
CONTENT_3 varchar2,
CONTENT_4 varchar2,
CONTENT_5 varchar2,
CONTENT_6 varchar2) as
ls_sql varchar2(4000);
lc_clob clob; --使用clob链接字符串参数
/*-------------------------/
/ 把string拼接成clob类型 /
/ author:yaoronghui
/ date: 2009-11-16--17 /
modify :
/ 功能描述: /
/ 参数:对动态表按照主键进行dml处理/
/问题:varchar2无法存放4000字符/
/-------------------------*/
begin
--根据传递的主键 pk修改参数表table_name中CONTENT=CONTENT_1||CONTENT_2...
ls_sql := 'update ' || table_name ||
' set content = empty_clob() where UPLOADID = :1 returning content into :lc_clob';
execute immediate ls_sql
using in pk, out lc_clob;
--change access
if sqlcode = 0 then
--append the lc_clob
dbms_lob.append(lc_clob, to_clob(CONTENT_1));
dbms_lob.append(lc_clob, to_clob(CONTENT_2));
dbms_lob.append(lc_clob, to_clob(CONTENT_3));
dbms_lob.append(lc_clob, to_clob(CONTENT_4));
dbms_lob.append(lc_clob, to_clob(CONTENT_5));
dbms_lob.append(lc_clob, to_clob(CONTENT_6));
commit;
else
rollback;
end if;
end;