This discussion is archived
2 Replies Latest reply: Jan 2, 2013 8:53 PM by BillyVerreynne RSS

Clob conversion to varchar

557534 Newbie
Currently Being Moderated
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
  • 1. Re: Clob conversion to varchar
    rp0428 Guru
    Currently Being Moderated
    >
    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?
    >
    Isn't 800 milliseconds pretty fast for retrieving 10GB of data?

    Post the tests you have done and the results that show it is Java and NOT the procedure that is taking the time.

    And if the problem is really in the Java code you should post the question in the JDBC forum along with the Java code you are using to process the result set with the CLOB data. In Java you need to use streams to get CLOB data properly.
    https://forums.oracle.com/forums/category.jspa?categoryID=288

    And since those streams pretty much have to read two a file for each of the CLOBs in every record you are reading two files for every record. File processing is going to be slow.

    The slow speed is most likely the result of having to read the file system twice for every record. Unless the CLOB data is <= 4000 bytes you won't be able to convert it to VARCHAR2 that Java can read directly. Any larger VARCHAR2 would need to be split into pieces.

    Another question is why you are using dynamic sql rather than just use Java to query the data directly.
  • 2. Re: Clob conversion to varchar
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    CLOBs are not "slow". Poorly designed and written code is.

    And with gems like:
    SELECT instr(p_array_code(i),'-',1)
    INTO v_count
    FROM dual;
    ..using expensive context switching to the SQL engine to execute a function that is natively available in PL/SQL, to assign a value to a variable in PL/SQL via SQL binding - it is pretty clear that the code is neither written or designed well.

    And blaming the code's poor performance on the CLOB data type? It is like blaming the hammer when you hit your thumb with it. It's not the hammer's fault.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points