11 Replies Latest reply: Jan 16, 2013 4:27 AM by Purvesh K RSS

    CLOB Length

    883641
      How do I calculate specific CLOB length when it size is greater than 4000 characters ?
      At the beginning this task seemed to me quite trivial but I'm having realy hard time figuring this one out
      TEST > CREATE TABLE articles
        2  (ID number,
        3   TITLE varchar2(25),
        4   TEXT clob)
        5       lob (text) STORE AS text_extention(TABLESPACE example);
      
      Table created.
      
      TEST > INSERT INTO articles VALUES (1, 'B' , LPAD('*' , 2000 , '*')) ;
      
      1 row created.
      
      TEST >
      TEST > INSERT INTO articles VALUES (1, 'C' , LPAD('*' , 3000 , '*')) ;
      
      1 row created.
      
      TEST >
      TEST > INSERT INTO articles VALUES (1, 'D' , LPAD('*' , 3999, '*')) ;
      
      1 row created.
      
      TEST >
      TEST > INSERT INTO articles VALUES (1, 'D' , LPAD('*' , 4000 , '*')) ;
      
      1 row created.
      
      TEST >
      TEST > INSERT INTO articles VALUES (1, 'E' , LPAD('*' , 4001 , '*')) ;
      
      1 row created.
      
      TEST >
      TEST > INSERT INTO articles VALUES (1, 'F' , LPAD('*' , 5000, '*')) ;
      
      1 row created.
      
      TEST >
      TEST > INSERT INTO articles VALUES (1, 'F' , LPAD('*' , 6000, '*')) ;
      
      1 row created.
      
      TEST >
      TEST > COMMIT ;
      
      Commit complete.
      
      TEST >
      TEST > SELECT id ,title, LENGTH(text)
        2  FROM articles ;
      
              ID TITLE                     LENGTH(TEXT)
      ---------- ------------------------- ------------
               1 B                                 2000
               1 C                                 3000
               1 D                                 3999
               1 D                                 4000
               1 E                                 4000
               1 F                                 4000
               1 F                                 4000
      
      7 rows selected.
      
      TEST >
      TEST > SELECT id ,title, DBMS_LOB.GETLENGTH(text)
        2  FROM articles ;
      
              ID TITLE                     DBMS_LOB.GETLENGTH(TEXT)
      ---------- ------------------------- ------------------------
               1 B                                             2000
               1 C                                             3000
               1 D                                             3999
               1 D                                             4000
               1 E                                             4000
               1 F                                             4000
               1 F                                             4000
      
      7 rows selected.
      
      TEST >
        • 1. Re: CLOB Length
          Osama_Mustafa
          What is DB Version And OS Version Please
          • 2. Re: CLOB Length
            asahide
            Hi,

            "DBMS_LOB.GETLENGTH" is correct.
            Your data has only 4000 bytes.
            If you insert more than 4000 bytes, You should use PL/SQL block..

            Regards,
            • 3. Re: CLOB Length
              Bawer
              try this
              declare
              c clob := EMPTY_CLOB();
              begin
                  dbms_lob.createTemporary(c,true);
                  dbms_lob.append(c, LPAD('*',14000,'*'));
                  insert into articles(text) values(c);
                  dbms_lob.freetemporary( c );
              end;
              • 4. Re: CLOB Length
                asahide
                Check follows.
                <<http://www.orafaq.com/forum/t/48485/0/>>


                Regards,
                • 5. Re: CLOB Length
                  Purvesh K
                  user3646231 wrote:
                  How do I calculate specific CLOB length when it size is greater than 4000 characters ?
                  Character datatypes are limited to 4000 bytes, even if its CLOB. Hence, even after providing the Buffer > 4000 bytes, it is trimmed to 4K.

                  See the Below:
                  drop table articles;
                  
                  CREATE TABLE articles
                      (ID number,
                       title varchar2(25),
                       text clob);
                       
                  INSERT INTO articles VALUES (1, 'E' , LPAD('*' , 4001 , '*')) ;
                  
                  --1 rows inserted.
                  
                  
                  select id, title, length(text)
                    from articles;
                  
                  ID                     TITLE                     LENGTH(TEXT)           
                  ---------------------- ------------------------- ---------------------- 
                  1                      E                         4000
                  
                  declare
                    v_text clob;
                  begin
                  
                    insert into articles values (2, 'F' , lpad('*' , 6001 , '*')) ;
                    
                    select text
                      into v_text
                     from articles
                     where id = 1;
                    
                    dbms_lob.writeappend(v_text, 500, rpad('Later Text', 500, '-'));
                    update articles set text = v_text where id = 1;
                    commit;
                  end;
                  anonymous block completed
                  
                  select id, title, length(text)
                    from articles;
                  ID                     TITLE                     LENGTH(TEXT)           
                  ---------------------- ------------------------- ---------------------- 
                  1                      E                         4500                   
                  2                      F                         4000
                  As you can see, an Insert statment from PL/SQL block was also trimmed to 4K bytes. In order to insert a CLOB > 4K bytes, you will have to use the Temporary LOB and Append the data into the Temporary Lob and then Update/Insert into table.

                  This way:
                  declare
                    v_text clob;
                  begin
                    dbms_lob.createtemporary(v_text, true);
                    dbms_lob.writeappend(v_text, 10000, rpad('Long Length CLOB', 10000, '*'));
                    insert into articles values (3, 'F' , v_text) ;
                    
                    commit;
                  end;
                  
                  select id, title, length(text)
                    from articles;
                  
                  ID                     TITLE                     LENGTH(TEXT)           
                  ---------------------- ------------------------- ---------------------- 
                  1                      E                         4500                   
                  2                      F                         4000                   
                  3                      F                         10000
                  • 6. Re: CLOB Length
                    Mihael
                    There is no need to use dbms_lob.createtemporary and dbms_lob.append, Oracle do it internally when using concatenation operator :

                    SQL> declare c clob; begin for i in 1..10000 loop c:=c||'0'; end loop; dbms_output.put_line(length(c)); end;
                    /

                    10000
                    • 7. Re: CLOB Length
                      Purvesh K
                      Mihael wrote:
                      There is no need to use dbms_lob.createtemporary and dbms_lob.append, Oracle do it internally when using concatenation operator :

                      SQL> declare c clob; begin for i in 1..10000 loop c:=c||'0'; end loop; dbms_output.put_line(length(c)); end;
                      /

                      10000
                      Alright, You can very well use a Spoon to dig a 10 Ft hole instead of using a Spade.

                      Did you ever think about the time that the block will require to execute? The resources? And if Oracle is stupid enough to Introduce LOB packages when it is capable of Internally handling the Concatenation.

                      See the execution time:
                      declare
                        v_text clob;
                      begin
                        dbms_lob.createtemporary(v_text, true);
                        dbms_lob.writeappend(v_text, 10000, rpad('Long Length CLOB', 10000, '*'));
                        insert into articles values (3, 'F' , v_text) ;
                        
                        commit;
                      end;
                      
                      anonymous block completed
                      495 ms
                      
                      declare 
                        c clob; 
                      begin 
                        for i in 1..10000 loop 
                          c:=c||'0'; 
                        end loop; 
                        dbms_output.put_line(length(c)); 
                        insert into articles(text) values (c);
                        commit;
                      end;
                      
                      anonymous block completed
                      4678 ms
                      Thats 10 times slower than using the Packaged functions, isn't it?
                      • 8. Re: CLOB Length
                        Mihael
                        See the execution time:
                        declare
                        v_text clob;
                        begin
                        dbms_lob.createtemporary(v_text, true);
                        dbms_lob.writeappend(v_text, 10000, rpad('Long Length CLOB', 10000, '*'));
                        insert into articles values (3, 'F' , v_text) ;
                        
                        commit;
                        end;
                        
                        anonymous block completed
                        495 ms
                        
                        declare 
                        c clob; 
                        begin 
                        for i in 1..10000 loop 
                        c:=c||'0'; 
                        end loop; 
                        dbms_output.put_line(length(c)); 
                        insert into articles(text) values (c);
                        commit;
                        end;
                        
                        anonymous block completed
                        4678 ms
                        Thats 10 times slower than using the Packaged functions, isn't it?
                        Certainly, for 10000 iterations it will be long. You can try such code:

                        for i in 1..1000 loop c:=c||'0000000000';
                        • 9. Re: CLOB Length
                          Purvesh K
                          Mihael wrote:
                          Certainly, for 10000 iterations it will be long. You can try such code:

                          for i in 1..1000 loop c:=c||'0000000000';
                          That does not fix the Shoddiness of the code. The correct way is to prevent the Iterations and perform the Job in Single SQL Statement.
                          That would give you better performance.
                          • 10. Re: CLOB Length
                            Mihael
                            That does not fix the Shoddiness of the code. The correct way is to prevent the Iterations and perform the Job in Single SQL Statement.
                            That would give you better performance.
                            I just wanted to tell that using operator clob1||clob2 will be equal to using DBMS_LOB procedures because Oracle internally uses the same mechanism. Is it correct ?
                            • 11. Re: CLOB Length
                              Purvesh K
                              Mihael wrote:
                              That does not fix the Shoddiness of the code. The correct way is to prevent the Iterations and perform the Job in Single SQL Statement.
                              That would give you better performance.
                              I just wanted to tell that using operator clob1||clob2 will be equal to using DBMS_LOB procedures because Oracle internally uses the same mechanism. Is it correct ?
                              Correct but not the correct way of doing.