1FUNCTION INVHL_READ_CLOB_DATA(
2 p_table_name IN VARCHAR2,
3 p_clob_field_name IN VARCHAR2,
4 p_where_condition IN VARCHAR2,
5 p_position IN NUMBER )
6 RETURN VARCHAR2
7IS
8 v_lobloc CLOB;
9 v_buffer VARCHAR2( 32767 );
10 v_amount NUMBER := 2000;
11 v_offset NUMBER := 1;
12 v_query_string VARCHAR2( 1000 );
13BEGIN
14 v_query_string :=
15 'SELECT '
16 || p_clob_field_name
17 || ' FROM '
18 || p_table_name
19 || ' WHERE '
20 || p_where_condition;
21
22 --initialize buffer with data to be found
23 EXECUTE IMMEDIATE v_query_string
24 INTO v_lobloc;
25
26 v_offset := v_offset + ( p_position - 1 ) * 2000;
27--read 2000 varchar2 from the buffer
28 DBMS_LOB.READ( v_lobloc,
29 v_amount,
30 v_offset,
31 v_buffer );
32 RETURN v_buffer;
33EXCEPTION
34 WHEN NO_DATA_FOUND THEN
35 RETURN v_buffer;
36END;