1.clob field Insert value Create or replace procedure Updateclob (
table_name in VARCHAR2,
field_id in Varchar2,
Field_name in Varchar2,
v_id in number,
V_pos in number,
V_clob in VARCHAR2)
Is
-- -------------------
/**//* Dynamic pl/sql, CLOB Field operation can pass table name table_name,
Table's unique flag field name field_id,
CLOB Field name Field_name
Record number v_id,
The position at which to begin processing characters V_pos,
The passed-in string variable V_clob
To modify the Pl/sql process for CLOB: Updateclob
Insert or modify the other fields before inserting or modifying them, and the Clob field is set to empty Empty_clob ().
Then invoke the procedure above to insert more than 2048 to 32,766 characters.
If you need to insert more than 32,767 characters, you can fix the problem by editing a loop.
*/
-- ------------------
Lobloc Clob;
C_clob varchar2 (32767);
AMT Binary_integer;
POS Binary_integer;
Query_str VARCHAR2 (1000);
Begin
POS: = V_pos * 32766 + 1;
AMT: = Length (V_CLOB);
C_clob: = V_clob;
QUERY_STR: = ' SELECT ' | | Field_name | | ' From ' | | table_name | | '
where ' | | field_id | | ' =: ID for update ';
--Initialize buffer with data to is inserted or updated
EXECUTE IMMEDIATE query_str into Lobloc USING v_id;
--from POS position, write 32766 varchar2 into Lobloc
Dbms_lob.write (Lobloc, AMT, POS, C_CLOB);
commit;
exception
When others then
Rollback;
End;
/
2. Read CLOB Create or replace function Getclob (
table_name in VARCHAR2,
field_id in Varchar2,
Field_name in Varchar2,
v_id in number,
V_pos in number) return VARCHAR2
Is
-- ----------------------
/**//* Usage Instructions:
With select Getclob (Table_name,field_id,field_name,v_id,v_pos) as
Partstr from dual;
You can take 2000 characters from the Clob field to Partstr.
A loop can be used to synthesize the PARTSTR group into the dbms_lob.getlength (field_name) length of the target string.
*/
-- ----------------------
Lobloc Clob;
Buffer varchar2 (32767);
Amount Number: = 2000;
Offset number: = 1;
Query_str VARCHAR2 (1000);
Begin
QUERY_STR: = ' SELECT ' | | Field_name | | ' From ' | | table_name | | '
where ' | | field_id | | ' =: id ';
--Initialize buffer with data to is found
EXECUTE IMMEDIATE query_str into Lobloc USING v_id;
Offset: = offset + (v_pos-1) * 2000;
--read varchar2 from the buffer
Dbms_lob. Read (Lobloc,amount,offset,buffer);
return buffer;
exception