Forum Stats

  • 3,781,253 Users
  • 2,254,496 Discussions
  • 7,879,624 Comments

Discussions

Copying Table Content to CLOB variable

Alwyn D'Souza
Alwyn D'Souza Member Posts: 6
edited May 12, 2016 9:24PM in SQL & PL/SQL

Hi,

I have a process that generates two million records into a temporary table (forecasted to increase to 9 million).The data from the temporary table should then be copied into a CLOB and stored back into a database column.

This report will then be downloaded from the Apex page.

At present I am facing performance issues with copying the data from the table into a CLOB column which takes 12mins to complete the process. Code snippet is given below.

Is there any better way of doing this ?

Code Snippet

DECLARE

  lv_clob_temp  CLOB;

  lv_content_file REPORT_DETAILS.content_file%type;

  lv_rev_header_file REPORT_DETAILS.rev_header_file%type;

  lv_rev_content_file REPORT_DETAILS.rev_content_file%type;

  type rec_report_temp is table of REPORT_TEMP%ROWTYPE;

  tbl_report_temp rec_report_temp;

  lv_bulk_limit pls_integer := 100000;

BEGIN

  -- fetch master data for the report

  SELECT

    content_file

    , rev_content_file

  INTO

    lv_content_file

    , lv_rev_content_file

  FROM   REPORT_DETAILS

  WHERE  rep_id = 1 for update;

dbms_lob.createtemporary(lv_clob_temp, TRUE, DBMS_LOB.SESSION );

--Data for the content extract

  open cur_rec for 'SELECT COL1, COL2, COL3, COL4, COL5, COL6, COL7 from REPORT_TEMP';

  loop

  FETCH cur_rec BULK COLLECT INTO tbl_report_temp LIMIT lv_bulk_limit;

  EXIT WHEN tbl_report_temp.count = 0;

  for i in 1..tbl_report_temp.count

  loop

  lv_clob_temp :=  tbl_report_temp(i).COL1

  ||','||tbl_report_temp(i).COL2

  ||','||to_char(tbl_report_temp(i).COL3,'DD/MM/YYYY')

  ||','||to_char(tbl_report_temp(i).SPAD,'DD/MM/YYYY')

  ||','||to_char(tbl_report_temp(i).COL5,'DD/MM/YYYY')

  ||','||tbl_report_temp(i).COL7

  ||','||to_char(tbl_report_temp(i).COL6,'DD/MM/YYYY')

  || lv_crlf;

  DBMS_LOB.APPEND(lv_content_file, lv_clob_temp);

  lv_clob_temp :=  tbl_report_temp(i).COL1

  ||','||tbl_report_temp(i).COL2

  ||','||to_char(tbl_report_temp(i).COL3,'DD/MM/YYYY')

  ||','||to_char(tbl_report_temp(i).SPAD,'DD/MM/YYYY')

  ||','||to_char(tbl_report_temp(i).COL5,'DD/MM/YYYY')

  || lv_crlf;

  DBMS_LOB.APPEND(lv_rev_content_file, lv_clob_temp);

  end loop;

  end loop;

  UPDATE REPORT_DETAILS

  set content_file = lv_content_file

    , rev_content_file = lv_rev_content_file

  WHERE  rep_id = 1;

  COMMIt;

END;

Tagged:
BluShadow
«1

Answers

  • Unknown
    edited May 9, 2016 9:15PM
     I have a process that generates two million records into a temporary table (forecasted to increase to 9 million).The data from the temporary table should then be copied into a CLOB and stored back into a database column.
    

    Why?

    NOTHING you posted supports ANY need to store that data in a CLOB. Just store the data in a table where it belongs.

    This report will then be downloaded from the Apex page.
    

    What 'report' are you talking about? There has been NO mention of any 'report'. Apex, or another tool, can use the data (now in a table) to create a report as needed.

    At present I am facing performance issues with copying the data from the table into a CLOB column which takes 12mins to complete the process.
    

    And the solution is: DON'T DO THAT!

    There is no reason at all to copy the data anywhere; especially to a CLOB. Put the data in a table where it belongs.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,009 Red Diamond
    edited May 9, 2016 9:50PM

    First of all, your code is flawed. Only ORDER BY guarantees order, so there is no guarantee:

    open cur_rec for 'SELECT COL1, COL2, COL3, COL4, COL5, COL6, COL7 from REPORT_TEMP';

    will select rows in same order they were inserted. Secondly, why PL/SQL? You can do it with SQL:


    UPDATE REPORT_DETAILS

      SET CONTENT_FILE = (

                          WITH T AS (

                                      SELECT  TO_CLOB(COL1) || ',' || COL2 || ',' ||

                                              TO_CHAR(COL3,'DD/MM/YYYY') ||

                                              ',' || TO_CHAR(SPAD,'DD/MM/YYYY') || ',' ||

                                              TO_CHAR(COL5,'DD/MM/YYYY') || ',' || COL7 ||

                                              ',' || TO_CHAR(COL6,'DD/MM/YYYY') || CHR(13) ||

                                              CHR(10) || COL1 || ',' || COL2 || ',' ||

                                              TO_CHAR(COL3,'DD/MM/YYYY') || ',' ||

                                              TO_CHAR(SPAD,'DD/MM/YYYY') || ',' ||

                                              TO_CHAR(COL5,'DD/MM/YYYY') || CHR(13) || CHR(10) LINE,

                                              -- You need some column (I used SEQ) that provides row order.

                                              ROW_NUMBER() OVER(ORDER BY SEQ) RN,

                                              COUNT(*) OVER() CNT

                                        FROM  REPORT_TEMP

                                    ),

                              R(

                                LINES,

                                RN,

                                CNT

                                ) AS (

                                       SELECT  LINE,

                                               RN,

                                               CNT

                                         FROM  T

                                         WHERE RN = 1

                                      UNION ALL

                                       SELECT  R.LINES || T.LINE,

                                               T.RN,

                                               R.CNT

                                         FROM  R,

                                               T

                                         WHERE T.RN = R.RN + 1

                                     )

                          SELECT  LINES

                            FROM  R

                            WHERE RN = CNT

                          )

      WHERE REP_ID = 1

    /

    SY.

  • Alwyn D'Souza
    Alwyn D'Souza Member Posts: 6
    edited May 10, 2016 12:05AM

    "This report will then be downloaded from the Apex page." - The CLOB column will then be referenced by Apex page with a link which will download the CLOB to client machine as a CSV file


    I will check the snippet that you have given below and see how does it perform.

  • Alwyn D'Souza
    Alwyn D'Souza Member Posts: 6
    edited May 10, 2016 12:48AM

    Explain plan looks horrible. Is there any better way

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,644 Red Diamond
    edited May 10, 2016 1:16AM

    Why pre-generated the CSV report as a CLOB?

    Assuming the run of the mill CSV download (typically of an existing data set via a non-complex query), the speed of the network (getting the CSV data from server to client) is the bottleneck - and not the reading of the data (the SQL query).

    In which case the execution time of the query is less than the data transmission time across the network. Which makes an interactive query and CSV download a feasible option. Which is a standard Apex Interactive Report feature.

    Only when the query is quite complex and takes minutes of execution time, it makes sense to pre-generate a CLOB for a CSV download by the client. And this also requires more moving parts as you need management  code to decide when to create a CLOB, when to remove it from the database, etc. So this is not a straight forward option to code and create.

  • Alwyn D'Souza
    Alwyn D'Souza Member Posts: 6
    edited May 10, 2016 2:38AM

    Query didn't work. Got the following error.

    Error report -

    ORA-01489: result of string concatenation is too long

    ORA-06512: at line 2

    01489. 00000 -  "result of string concatenation is too long"

    *Cause:    String concatenation result is more than the maximum size.

    *Action:   Make sure that the result is less than the maximum size.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,611 Red Diamond
    edited May 10, 2016 3:23AM
    Solomon Yakobson wrote:
    
    First of all, your code is flawed. Only ORDER BY guarantees order, so there is no guarantee:
    
     open cur_rec for 'SELECT COL1, COL2, COL3, COL4, COL5, COL6, COL7 from REPORT_TEMP';
    
    will select rows in same order they were inserted.

    I'm surprised you said that Solomon.

    Selecting without an ORDER BY clause will select the rows in a non-guaranteed order, which may or may not be the same order they were inserted.  If data is inserted in gaps in a table, or if the table has been Reorganized then the physical order of the rows can certainly be different from how they were inserted, or even the complexity of the query can alter the order in which results are presented.

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited May 10, 2016 7:51AM
    BluShadow wrote:
    
    
    Solomon Yakobson wrote:
    
    First of all, your code is flawed. Only ORDER BY guarantees order, so there is no guarantee:
    
     open cur_rec for 'SELECT COL1, COL2, COL3, COL4, COL5, COL6, COL7 from REPORT_TEMP';
    
    will select rows in same order they were inserted.
    I'm surprised you said that Solomon. Selecting without an ORDER BY clause will select the rows in a non-guaranteed order, which may or may not be the same order they were inserted.  If data is inserted in gaps in a table, or if the table has been Reorganized then the physical order of the rows can certainly be different from how they were inserted, or even the complexity of the query can alter the order in which results are presented.

    I think you misread the statement, Solomon said "there is no guarantee: query will select rows in same order they were inserted."

    PL/SQL is not the tool I would use to create a csv file. The client side should be able to turn a result set into a csv file if that's what was required. But then why would you want your report to be in csv format?

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,009 Red Diamond
    edited May 10, 2016 8:25AM
    BluShadow wrote:
    
    
    Solomon Yakobson wrote:
    
    First of all, your code is flawed. Only ORDER BY guarantees order, so there is no guarantee:
    
     open cur_rec for 'SELECT COL1, COL2, COL3, COL4, COL5, COL6, COL7 from REPORT_TEMP';
    
    will select rows in same order they were inserted.
    I'm surprised you said that Solomon. Selecting without an ORDER BY clause will select the rows in a non-guaranteed order, which may or may not be the same order they were inserted.  If data is inserted in gaps in a table, or if the table has been Reorganized then the physical order of the rows can certainly be different from how they were inserted, or even the complexity of the query can alter the order in which results are presented.

    Isn't it what I said???

    SY.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,611 Red Diamond
    edited May 10, 2016 8:50AM
    Solomon Yakobson wrote:
    
    
    BluShadow wrote:
    
    
    Solomon Yakobson wrote:
    
    First of all, your code is flawed. Only ORDER BY guarantees order, so there is no guarantee:
    
     open cur_rec for 'SELECT COL1, COL2, COL3, COL4, COL5, COL6, COL7 from REPORT_TEMP';
    
    will select rows in same order they were inserted.
    I'm surprised you said that Solomon. Selecting without an ORDER BY clause will select the rows in a non-guaranteed order, which may or may not be the same order they were inserted.  If data is inserted in gaps in a table, or if the table has been Reorganized then the physical order of the rows can certainly be different from how they were inserted, or even the complexity of the query can alter the order in which results are presented. Isn't it what I said??? SY.

    I think it may have been just the way the punctuation was used.

    I read it as two statements:

    First of all, your code is flawed. Only ORDER BY guarantees order, so there is no guarantee:
    



    which I agree with. but then I read:


    open cur_rec for 'SELECT COL1, COL2, COL3, COL4, COL5, COL6, COL7 from REPORT_TEMP';
    
    will select rows in same order they were inserted.
    


    which is obviously not correct in that context.  That cursor will not (necessarily) select rows in the order they were inserted.


    Perhaps if I'd read it as...


    First of all, your code is flawed. Only ORDER BY guarantees order, so there is no guarantee that this statement:
    ...
    will select rows in same order they were inserted.
    

    Then, yes it makes sense.

This discussion has been closed.