2 Replies Latest reply: Jan 2, 2013 10:53 PM by Billy~Verreynne RSS

    Clob conversion to varchar

    user554531
      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
          >
          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
            Billy~Verreynne
            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.