PURPOSE
-------
This document will guide you to enter values into the table from Forms which
contains the CLOB datatype.
SCOPE AND APPLICATION
---------------------
1. Create a table with 2 fields Number and Clob datatype as given below:
Create table test( A number, B Clob);
2. Create a procedure from the Sql*Plus as given below:
Create or Replace Procedure Insert_Test_Clob(a Number, B Varchar2) as
t_lob clob := empty_clob();
begin
dbms_lob.createtemporary(t_lob, TRUE, dbms_lob.session);
dbms_lob.writeappend(t_lob, length(b), b);
Insert into test values(a,t_lob);
commit;
end;
3. Create a Form with 2 text items A Number and B Varchar2. Make the Field B
as Multiline text item. Create a push button and in the When-Button-Pressed
write the following code:
Begin
Insert_Test_Clob(:A,:B);
Message('Inserted ');Message(' ');
End;
4. From the Sql*Plus session check whether the data is entered.
To check whether the data as inserted properly use the following code from
Sql*Plus:
DECLARE
lobloc CLOB;
buffer VARCHAR2(32000);
amount NUMBER := 80;
amount_in_buffer NUMBER := 0;
offset NUMBER := 1;
BEGIN
--Initialize buffer with data to be inserted
SELECT B
INTO lobloc -- get LOB handle
FROM Test
WHERE A = 2;
Loop
dbms_lob.read(lobloc,amount ,offset,buffer);
dbms_output.put_line(buffer);
offset := offset + amount ;
amount_in_buffer := amount_in_buffer + amount;
End loop;
Exception
WHEN NO_DATA_FOUND THEN
dbms_output.put_line(amount_in_buffer);
DBMS_OUTPUT.PUT_LINE('End of data');
END;
/
Partager