Oracle modifies the clob field and queries the clob Field

 

How to handle clob
Dynamic PL/SQL processing of clob Fields
2001-03

Dynamic PL/SQL. For clob field operations, you can pass the table name table_name, the unique table Identifier Field name field_id, clob field name field_name, record number v_id, start to process the character location v_pos, passed string variable v_clob

Update the PL/SQL PROCESS OF clob: updateclob

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
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 be 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;
/

Usage instructions:
Before inserting or modifying, insert or modify other fields. Set the clob field to empty empty_clob (),
Then, call the above process to insert more than 2048 to 32766 characters.
If you want to insert more than 32767 characters, compile a loop to solve the problem.

Query the PL/SQL function of clob: getclob

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
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 be found
Execute immediate query_str into lobloc using v_id;
Offset: = offset + (v_pos-1) * 2000;
-- Read 2000 varchar2 from the buffer
Dbms_lob.read (lobloc, amount, offset, buffer );
Return buffer;
Exception
When no_data_found then
Return buffer;
End;
/

Usage instructions:

Use select getclob (table_name, field_id, field_name, v_id, v_pos) as partstr from dual;

The clob field can contain 2000 characters to partstr,
Compile a loop to combine partstr into a target string of the length of dbms_lob.getlength (field_name.