Stored Procedure for writing to blob fields

Stored procedure for writing to blob fields [SQL] www.2cto.com create or replace procedure write2blob (directions blob, v_blob blob, v_pos integer) is v_1time_maxlength integer; -- maximum amount integer of one read; -- total length v_blob_sub blob; -- the length of one read v_index integer; --- index v_sub_count integer; offset integer; directions2 blob; begin v_1time_maxlength: = 32767; -- allowed length amount: = length (v_blob); -- total blob length v_sub_count: = amount/v_1time_maxlength + 1; -- number of sub-strings v_index: = 1; -- index start from 1 offset: = v_pos; dires2 S2: = directions ctions; loop dbms_output.put_line (length (v_blob); DBMS_LOB.read (v_blob, v_1time_maxlength, offset, v_blob_sub); -- read the cursor (directions2, length (v_blob_sub), v_blob_sub); -- write the content in v_blob_sub to v_index: = v_index + 1; offset: = v_index * v_1time_maxlength + 1; exit when v_index> v_sub_count; end loop; dbms_lob.close (dires2 S2); exception when no_data_found then dbms_output.put_line ('data not found '); end; test code: [SQL] -- Created on 2013-3-8 by ZHANGXL declare -- Local variables here I integer; v_blob; directions blob; v_post_id number (18): = 8814; begin select pt. post_text into v_blob from jforum_posts_text pt where pt. post_id = 8817; -- Test statements here -- execute updateblob (jforum_posts_text, post_id, post_text, 8817,0, v_blob); -- update jforum_posts_text pt -- set pt. post_text = EMPTY_BLOB () -- where pt. post_id = v_post_id; select pt. post_text into directions from jforum_posts_text pt where pt. post_id = v_post_id for update; -- lock dbms_lob.open (directions, dbms_lob.lob_readwrite); -- open the read/write stream write2blob (directions, v_blob, 1); commit; end; table structure: [SQL] -- Create table create table JFORUM_POSTS_TEXT (POST_ID NUMBER (18) not null, POST_TEXT BLOB, POST_SUBJECT VARCHAR2 (200), ISDELETED NUMBER (1) default 0) tablespace XZFY pctfree 10 initrans 1 maxtrans 255 storage (initial 128 minextents 1 maxextents unlimited); -- Create/Recreate primary, unique and foreign key constraints alter table partition add primary key (POST_ID) using index tablespace XZFY pctfree 10 initrans 2 maxtrans 255 storage (initial 64 K minextents 1 maxextents unlimited );