正在搞全文索引,需要写一个user_sotre用的procedure。遇到的问题是,操作的字符串可能超过32767。我试着申明一个clob变量,编译通过,但是当执行的时候报错。
create or replace procedure enc_dg_store
(rid in integer ) is
v_length integer;
v_title_buffer varchar2(32767);
v_body_buffer varchar2(32767);
tag_title varchar2(30) := 'TITLE';
tag_body varchar2(30) := 'BODY';
vvc1 varchar2(32767);
vvc2 varchar2(32767);
v_post_id enc_dg_search.post_id%TYPE;
v_post_type enc_dg_search.post_type%TYPE;
v_post_title enc_dg_search.post_title%TYPE;
v_post_disc_grp_id enc_dg_search.disc_grp_id%TYPE;
v_post_forum_topic_id enc_dg_search.forum_topic_id%TYPE;
v_post_del_date enc_dg_search.del_date%TYPE;
v_temp_title varchar2(32767);
v_temp_content varchar2(32767);
v_topic_rec enc_dg_topic%ROWTYPE;
v_question_rec enc_dg_question%ROWTYPE;
v_feedback_rec enc_dg_feedback%ROWTYPE;
v_lob_buffer clob;
cursor c_feedback_by_source (p_source_id enc_dg_topic.topic_id%TYPE) is
select fb_title, fb_content from enc_dg_feedback
where source_id = p_source_id
and del_date is null
and approve_date is not null;
begin
v_lob_buffer :=empty_clob();
SELECT POST_ID, POST_TYPE, POST_TITLE, DISC_GRP_ID, FORUM_TOPIC_ID, DEL_DATE
INTO v_post_id, v_post_type, v_post_title, v_post_disc_grp_id,
v_post_forum_topic_id, v_post_del_date
FROM ENC_DG_SEARCH
WHERE post_id = rid;
-- WHERE rowid = rid;
--Doesn't index soft deleted records.
if v_post_del_date is not null then
goto ext;
end if;
-- Deal with FREE_TOPIC type post. FREE_TOPIC is the topic posted by User in
-- FREE Discussion Area.
if v_post_type = 'FREE_TOPIC' then
select *
into v_topic_rec
from enc_dg_topic
where topic_id = v_post_id;
v_title_buffer := '' ||
v_topic_rec.topic_title ;
v_body_buffer := '' ||
v_topic_rec.topic_content;
v_length := length(v_body_buffer);
Dbms_Lob.Write
(
lob_loc => v_lob_buffer,
amount => v_length,
offset => 1,
buffer => v_body_buffer
);
open c_feedback_by_source(v_post_id);
fetch c_feedback_by_source into v_temp_title, v_temp_content;
while c_feedback_by_source%FOUND loop
v_title_buffer := v_title_buffer || v_temp_title || ' ';
v_body_buffer := v_body_buffer || v_temp_content || ' ';
fetch c_feedback_by_source into v_temp_title, v_temp_content;
end loop;
v_title_buffer := v_title_buffer || '' || tag_title || '>';
v_body_buffer := v_body_buffer || '' || tag_body || '>';
end if;
/*
if v_post_type = 'QUESTION' then
select *
into v_question_rec
from enc_dg_question
where question_id = v_post_id;
v_title_buffer := '' ||
v_question_rec.QUESTION_TITLE ;
v_body_buffer := '' ||
v_question_rec.QUESTION_CONTENT;
open c_feedback_by_source(v_post_id);
fetch c_feedback_by_source into v_temp_title, v_temp_content;
while c_feedback_by_source%FOUND loop
v_title_buffer := v_title_buffer || v_temp_title || ' ';
v_body_buffer := v_body_buffer || v_temp_content || ' ';
fetch c_feedback_by_source into v_temp_title, v_temp_content;
end loop;
v_title_buffer := v_title_buffer || '' || tag_title || '>';
v_body_buffer := v_body_buffer || '' || tag_body || '>';
end if;
*/
--v_body_buffer :='aaa';
-- dbms_output.put_line(substr(v_title_buffer,1,55));
insert into debug_table values(1, v_title_buffer);
insert into debug_table values(2, v_body_buffer);
commit;
-- dbms_output.put_line(v_body_buffer);
-- dbms_output.put_line('ok');
/* Dbms_Lob.Write
(
lob_loc => tlob,
amount => v_length,
offset => 1,
buffer => v_buffer
);
*/
<>
dbms_output.put_line('ss');
--dbms_output.put_line(substr(v_body_buffer,1,255));
end enc_dg_store;
/
show errors;