3. insert into view_sites_info(site_id) values (100);
4. select * from view_sites_info;
5. --************************************** below is write
DECLARE
lobloc CLOB;
buffer VARCHAR2(2000);
amount NUMBER := 20;
offset NUMBER := 1;
BEGIN
buffer := 'This is a writing example';
amount := length(buffer);
SELECT document INTO lobloc FROM view_sites_info WHERE site_id = 100 FOR UPDATE;
dbms_lob.write(lobloc,amount,1,buffer);
COMMIT;
END;
6. --*************************************** below is read
declare
doc_len NUMBER;-- clob length
doc_loc CLOB;--clob content
buffer_read VARCHAR2(2000);
offset NUMBER := 1;
amount NUMBER := 2000;
BEGIN
SELECT length(document), document INTO doc_len, doc_loc FROM view_sites_info WHERE site_id = 100;
dbms_lob.read(doc_loc,amount,offset,buffer_read);
dbms_output.put_line(buffer_read);
END;
7. --****************************************** below is update
declare step_char VARCHAR2(1);
pos NUMBER := 1;-- position
doc_len NUMBER;-- clob length
doc_loc CLOB;--clob location
buffer_read VARCHAR2(2000);
buffer_write VARCHAR2(2000);
offset NUMBER := 1;
amount NUMBER := 2000;
BEGIN
SELECT length(document), document INTO doc_len, doc_loc FROM view_sites_info WHERE site_id = 100 FOR UPDATE;
dbms_lob.read(doc_loc,amount,offset,buffer_read);
WHILE (pos < doc_len + 1) LOOP
--select substr(buffer_read,pos, 1) INTO step_char FROM dual;
step_char := substr(buffer_read,pos, 1);
IF step_char = 'i' Then
step_char := 'a';
END IF;
buffer_write := buffer_write || step_char;
pos := pos + 1;
END LOOP;