Activity

1. osalvador commented on Jun 6, 2019

   

   osalvador

   on Jun 6, 2019

   Owner

   More actions

   Hi @xendren

   Could you enumerate the steps to reproduce the issue and the sample code?

   Thanks.
2. MikeKutz commented on Jun 6, 2019

   

   MikeKutz

   on Jun 6, 2019

   Collaborator

   More actions

   Verified Bug Code

   DECLARE
     P_CLOB CLOB;
       
   BEGIN
     P_CLOB := lpad( 'X', 32767, 'X') || 'X';
   --  P_CLOB := P_CLOB || P_CLOB ;
   
     TEPLSQL.output_clob (  P_CLOB => P_CLOB) ;  
   END;

   Code to correct

   I believe the idea of using PUT instead of PUT_LINE was to ensure that you don't have a \n in the middle of a SELECT keyword. Moving the NEW_LINE call to the inside of the loop would undo that design philosophy.

   The split will need to occur on the last \n prior to the 32767 barrier. PUT_LINE should be used instead.

   Oh, that ENABLE( 1000000 ) should probably be changed to ENABLE( NULL ).

   workarounds: (until fixed)

   • Save the CLOB to a table and then run a SELECT statement.
   • Use the "oddgen" plugin (another GitHub project) for SQL*Developer so that the output CLOB goes to a new worksheet.
3. MikeKutz

   added

   bug

   on Jun 6, 2019
4. MikeKutz commented on Jun 10, 2019

   

   MikeKutz

   on Jun 10, 2019

   Collaborator

   More actions

   @xendren
   Any time you report a bug, please include any ORA numbers and a simple example to reproduce.

   When you can, please try this loop within the output_clob body. Make sure you remove the DBMS_OUTPUT.NEW_LINE call that's at the end.

   loop
       exit when v_offset > dbms_lob.getlength(p_clob);
   
       -- find "near last" occurance of '\n' - assumes Code Lines are not > 767 bytes long.
       v_chunk_size := dbms_lob.instr(p_clob,chr(10),v_offset + 32000,1);
   
       -- calculate "correct" chunk size.
       -- a NULL or 0 value is "invalid" - use the entire length or Max size.
       v_chunk_size := least(nvl(nullif(v_chunk_size,0),99999),dbms_lob.getlength(p_clob),32767);
   
       dbms_output.put_line(dbms_lob.substr(p_clob,v_chunk_size,v_offset));
       v_offset := v_offset + v_chunk_size;
   end loop;