SQL & PL/SQL

Below procedure is called by java program
OUT results list contains clob datatype.
While retrieving clob data from java code it is taking lot of time . For each record around 800 milliseconds.

COuld you please let me know is there any way of converting clob data to varchar2 apart from breaking up the column value?

create or replace
TYPE diag_code_rec as object
(
rec_text_href CLOB,
rec_source_code varchar2(10),
rec_description varchar2(300),
rec_scenario CLOB,
rec_flag Varchar2(5),
rec_destination_code varchar2(10),
rec__des_description varchar2(300));

create or replace
type diag_code_table as table of diag_code_rec

create or replace
PROCEDURE file_upload(p_array_code IN diag_code,p_start IN NUMBER,p_end IN NUMBER,p_code_result OUT diag_code_table)
IS
v_count NUMBER;
v_range1 VARCHAR2(8);
v_range2 VARCHAR2(8);
v_range3 VARCHAR2(20);
l_count PLS_INTEGER := 0;
v_stmt VARCHAR2(500);
v_stmt1 VARCHAR2(500);

l_diag_code_table1 DIAG_CODE_TABLE;
l_diag_code_table2 DIAG_CODE_TABLE;

l_loop_diag_code_table1 DIAG_CODE_TABLE;
l_loop_diag_code_table2 DIAG_CODE_TABLE;


BEGIN
l_diag_code_table1 := DIAG_CODE_TABLE();
l_diag_code_table2 := DIAG_CODE_TABLE();


l_loop_diag_code_table1 := DIAG_CODE_TABLE();
l_loop_diag_code_table2 := DIAG_CODE_TABLE();


FOR i IN 1..p_array_code.count
LOOP
SELECT instr(p_array_code(i),'-',1)
INTO v_count
FROM dual;

IF (v_count > 0) THEN

v_range1:= substr(p_array_code(i),1,(instr(p_array_code(i),'-',1)-1));

v_range2:= substr(p_array_code(i),instr(p_array_code(i),'-',1)+1) ;

v_range3 := v_range2||'%';

IF (p_start >0 and p_end >0) THEN


EXECUTE IMMEDIATE'SELECT diag_code_rec(icd9_pcs_text_href,
source_code,
s_description,
scenario,flag,
destination_code,
d_description)
FROM (SELECT rownum as num,
icd9_pcs_text_href,
source_code,
s_description,
scenario,
flag,
destination_code,
d_description
FROM (SELECT S.ICD9_PCS_TEXT_HREF,
S.ICD9_PROCEDURE_DECI_CODE SOURCE_CODE,
S.ICD9_PROCEDURE_LONG_DESC S_DESCRIPTION,
S.ICD9_PCS_GEM_FLAG_DESC SCENARIO,
G.GEM_ICD9_ICD10PCS_FLAG FLAG,
D.ICD10_PROCEDURE_CODE DESTINATION_CODE,
D.ICD10_PROCEDURE_LONG_DESC D_DESCRIPTION
FROM icd9_procedure_codes s,
gem_icd9_icd10pcs g,
icd10_procedure_codes d
WHERE s.icd9_procedure_code=g.gem_icd9_pcs_code
AND g.gem_icd10_pcs_code=d.icd10_procedure_code(+)
AND ((s.icd9_procedure_deci_code between :1 and :2) or (s.icd9_procedure_deci_code like :3))
ORDER BY SOURCE_CODE asc)
)
WHERE num BETWEEN :4 and :5'
bulk collect into l_diag_code_table1
USING v_range1,v_range2,v_range3,p_start,p_end;

ELSE

EXECUTE IMMEDIATE'SELECT diag_code_rec(icd9_pcs_text_href,
source_code,
s_description,
scenario,flag,
destination_code,
d_description)
FROM (SELECT rownum as num,
icd9_pcs_text_href,
source_code,
s_description,
scenario,
flag,
destination_code,
d_description
FROM (SELECT S.ICD9_PCS_TEXT_HREF,
S.ICD9_PROCEDURE_DECI_CODE SOURCE_CODE,
S.ICD9_PROCEDURE_LONG_DESC S_DESCRIPTION,
S.ICD9_PCS_GEM_FLAG_DESC SCENARIO,
G.GEM_ICD9_ICD10PCS_FLAG FLAG,
D.ICD10_PROCEDURE_CODE DESTINATION_CODE,
D.ICD10_PROCEDURE_LONG_DESC D_DESCRIPTION
FROM icd9_procedure_codes s,
gem_icd9_icd10pcs g,
icd10_procedure_codes d
WHERE s.icd9_procedure_code=g.gem_icd9_pcs_code
AND g.gem_icd10_pcs_code=d.icd10_procedure_code(+)
AND ((s.icd9_procedure_deci_code between :1 and :2) or (s.icd9_procedure_deci_code like :3)) v_stmt||
ORDER BY SOURCE_CODE asc)
) '
BULK COLLECT INTO l_diag_code_table1
USING v_range1,v_range2,v_range3,p_start,p_end;
END IF;


FOR j IN 1..l_diag_code_table1.count
LOOP
l_loop_diag_code_table1.extend;
l_loop_diag_code_table1 (l_loop_diag_code_table1.last):= l_diag_code_table1(j) ;
END LOOP;

ELSE
v_range3 := p_array_code(i)||'%';

IF (p_start >0 and p_end >0) THEN

EXECUTE IMMEDIATE'SELECT diag_code_rec(icd9_pcs_text_href,
source_code,
s_description,
scenario,flag,
destination_code,
d_description)
FROM (SELECT rownum as num,
icd9_pcs_text_href,
source_code,
s_description,
scenario,
flag,
destination_code,
d_description
FROM (SELECT S.ICD9_PCS_TEXT_HREF,
S.ICD9_PROCEDURE_DECI_CODE SOURCE_CODE,
S.ICD9_PROCEDURE_LONG_DESC S_DESCRIPTION,
S.ICD9_PCS_GEM_FLAG_DESC SCENARIO,
G.GEM_ICD9_ICD10PCS_FLAG FLAG,
D.ICD10_PROCEDURE_CODE DESTINATION_CODE,
D.ICD10_PROCEDURE_LONG_DESC D_DESCRIPTION
FROM icd9_procedure_codes s,
gem_icd9_icd10pcs g,
icd10_procedure_codes d
WHERE s.icd9_procedure_code=g.gem_icd9_pcs_code
AND g.gem_icd10_pcs_code=d.icd10_procedure_code(+)
AND (s.icd9_procedure_deci_code like :1)
ORDER BY SOURCE_CODE asc)
)
WHERE num BETWEEN :2 and :3'
BULK COLLECT INTO l_diag_code_table2
using v_range3,p_start,p_end;

ELSE

EXECUTE IMMEDIATE'SELECT diag_code_rec(icd9_pcs_text_href,
source_code,
s_description,
scenario,flag,
destination_code,
d_description)

FROM (SELECT rownum as num,
icd9_pcs_text_href,
source_code,
s_description,
scenario,
flag,
destination_code,
d_description

FROM (SELECT S.ICD9_PCS_TEXT_HREF,
S.ICD9_PROCEDURE_DECI_CODE SOURCE_CODE,
S.ICD9_PROCEDURE_LONG_DESC S_DESCRIPTION,
S.ICD9_PCS_GEM_FLAG_DESC SCENARIO,
G.GEM_ICD9_ICD10PCS_FLAG FLAG,
D.ICD10_PROCEDURE_CODE DESTINATION_CODE,
D.ICD10_PROCEDURE_LONG_DESC D_DESCRIPTION

FROM icd9_procedure_codes s,
gem_icd9_icd10pcs g,
icd10_procedure_codes d
WHERE s.icd9_procedure_code=g.gem_icd9_pcs_code
AND g.gem_icd10_pcs_code=d.icd10_procedure_code(+)
AND (s.icd9_procedure_deci_code like :1)
ORDER BY SOURCE_CODE asc)
) '

BULK COLLECT INTO l_diag_code_table2
using v_range3;

END IF;

FOR k IN 1..l_diag_code_table2.count
LOOP
l_loop_diag_code_table1.extend;
l_loop_diag_code_table1 (l_loop_diag_code_table1.LAST):= l_diag_code_table2(k);
END LOOP;

END IF;

END LOOP;
p_code_result := l_loop_diag_code_table1 ;
END;
/

Thankyou for any advice