Read data from the clob field in Oracle Using Stored Procedures

When developing a database, you may sometimes need to read data of the clob type in the Oracle database. For the purpose of code reuse, I wrote the following stored procedure: Read the data of the clob field in the database. I hope to share with you.

Create or replace procedure prc_read_clob (
Table_name IN VARCHAR2,
Clob_column_name IN VARCHAR2,
Primary_Key_Column_names IN VARCHAR2,
Primary_key_values IN VARCHAR2,
Offset_ I IN NUMBER,
Read_length_ I IN NUMBER,
Res out VARCHAR2,
Total_length OUT NUMBER
)
/**
Autor: Hanks_gao.
Create Date: 2008/12/10
Description: This procedure is to read clob value by conditions
--------------------------------------------------------------
----------------- Parameters descritption ----------------------
Table_name: The table that contains clob/blob columns (table name)
Clob_column_name: Clob/blob column name of table_name (type: clob field name)
Primary_key_column_names: The columns seperated by '}' that can fix only one row data (that is primary key) (primary key name, string separated)
Primary_key_values: The primary keyes values that seperated by '}' (primary key value, string separated)
Offset_ I: The offset of reading clob data (The amount of data to be read)
Read_length_ I: The length of reading clob data per times (The length to be read)
Res: Return value that can be referenced by application (read result)
Total_length: The total length of readed clob data (total length of clob data queried by The database)
----------------- End Parameters descritption ------------------
*/

TmpPrimaryKeys VARCHAR2 (2000); -- To save primary_Key_Column_names temporarily (the primary key is a string separated)
TmpPrimaryKeyValues VARCHAR2 (2000); -- To save primary_key_values temporarily (temporary master key value, string separated)
I NUMBER; -- cyclic control variable
TmpReadLength NUMBER; -- temporary storage of the length to be read
SqlStr VARCHAR2 (6000); -- Query string (Query string)
SqlCon VARCHAR2 (5000); -- Query condition (Query condition)

TYPE tmparray is table of VARCHAR2 (5000) index by BINARY_INTEGER;
ArrayPrimaryKeys tmparray; -- To save the analyze result of primary_Key_Column_names (primary key name obtained after temporary Analysis)
ArrayPrimaryKeyValues tmparray; -- To save the analyze result of primary_key_values (primary key value obtained after temporary Analysis)
BEGIN
Total_length: = 0;
RES: = '';
DECLARE
Clobvar CLOB: = EMPTY_CLOB;
BEGIN
TmpPrimaryKeys: = primary_Key_Column_names;
TmpPrimaryKeyValues: = primary_key_values;

I: = 0;
While instr (tmpPrimaryKeys, '}')> 0 LOOP -- Analyse the column names of primary key (separate the primary key, equivalent to arrayPrimaryKeys = tmpPrimaryKeys. split ("}"))
ArrayPrimaryKeys (I): = subSTR (tmpPrimaryKeys, 1, (INSTR (tmpPrimaryKeys, '}')-1 ));
TmpPrimaryKeys: = subSTR (tmpPrimaryKeys, (INSTR (tmpPrimaryKeys, '}') + 1 ));
I: = I + 1;
End loop;

I: = 0;
While instr (tmpPrimaryKeyValues, '}')> 0 LOOP -- Analyse the values of primary key
ArrayPrimaryKeyValues (I): = subSTR (tmpPrimaryKeyValues, 1, (INSTR (tmpPrimaryKeyValues, '}')-1 ));
TmpPrimaryKeyValues: = subSTR (tmpPrimaryKeyValues, (INSTR (tmpPrimaryKeyValues, '}') + 1 ));
I: = I + 1;
End loop;

IF arrayPrimaryKeys. COUNT () <> arrayPrimaryKeyValues. COUNT () THEN -- determines whether the key matches the key value.
Res: = 'key-value not Match ';
RETURN;
End if;

I: = 0;
SqlCon: = '';
WHILE I <arrayPrimaryKeys. COUNT () LOOP
SqlCon: = sqlCon | 'and' | arrayPrimaryKeys (I) | '= '''
| Replace (arrayPrimaryKeyValues (I), ''', ''') | '''';
I: = I + 1;
End loop;

SqlStr: = 'select' | clob_column_name | 'from' | table_name
| 'Where 1 = 1' | sqlCon | 'and rownum = 1'; -- group query string

Dbms_lob.createtemporary (clobvar, TRUE );
Dbms_lob.OPEN (clobvar, dbms_lob.lob_readwrite );

Execute immediate trim (sqlStr) INTO clobvar; -- EXECUTE the query

IF offset_ I <= 1 THEN
Total_length: = dbms_lob.getlength (clobvar );
End if;

IF read_length_ I <= 0 THEN
TmpReadLength: = 4000;
ELSE
TmpReadLength: = read_length_ I;
End if;

Dbms_lob.READ (clobvar, tmpReadLength, offset_ I, res); -- read data

IF dbms_lob.ISOPEN (clobvar) = 1 THEN
Dbms_lob.CLOSE (clobvar );
End if;

END;
EXCEPTION
WHEN OTHERS THEN
Res: = '';
Total_length: = 0;
END;