Home » SQL & PL/SQL » SQL & PL/SQL » CLOB - Appending CLOB in for loop. (Oracle 9i)
icon4.gif  CLOB - Appending CLOB in for loop. [message #430631] Wed, 11 November 2009 04:44 Go to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Gurus here i need your kind help about CLOB's. Let have a look @ below query



declare
i number;
c clob;
x number;
begin
for i in 1 .. 400000 loop
x:=i;
c:=c||to_char(i);

end loop;
dbms_output.put_line(x);
end;


I am trying to append a clob column in a for loop, it take lot of time when data is huge.(About 16hrs for 4lakhs rows)
I cannot use Varchar2 insted because the volume of data is exceeding 32K.
Expertise needed with CLOB in a loop, please refer the query above.

Jak

[Updated on: Wed, 11 November 2009 04:59]

Report message to a moderator

Re: CLOB - Appending CLOB in for loop. [message #430637 is a reply to message #430631] Wed, 11 November 2009 05:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The code you posted took about 21 seconds to run on my local pc.

Is that the code that is causing you problems, or are you having problems with a piece of code that you haven't shown us?
Re: CLOB - Appending CLOB in for loop. [message #430643 is a reply to message #430637] Wed, 11 November 2009 06:22 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Yes ,
the same code when i am running (Toad or Prompt) its taking longer then usual. All i can say that for me i discarded the process after waiting more then 1 Hour.

Jak
Re: CLOB - Appending CLOB in for loop. [message #430645 is a reply to message #430643] Wed, 11 November 2009 06:58 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
In my case it is taking 33 seconds...

regards,
Delna
Re: CLOB - Appending CLOB in for loop. [message #430646 is a reply to message #430645] Wed, 11 November 2009 07:05 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

I am using Oracle 9i, however i dont think there's anything to do with version.I believe you are not changing the loop counters to any other value.In my case its 1 to 400000.
I had tried the same code on 11i, still its taking time.May iget the screen shot if your code executes in less than 40 sec.
./fa/7006/0/
  • Attachment: Img.JPG
    (Size: 35.46KB, Downloaded 6540 times)

[Updated on: Wed, 11 November 2009 07:10]

Report message to a moderator

Re: CLOB - Appending CLOB in for loop. [message #430648 is a reply to message #430646] Wed, 11 November 2009 07:15 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> set timing on
SQL> declare
  2  i number;
  3  c clob;
  4  x number;
  5  begin
  6  for i in 1 .. 400000 loop
  7  x:=i;
  8  c:=c||to_char(i);
  9  
 10  end loop;
 11  dbms_output.put_line(x);
 12  end;
 13  /
400000

PL/SQL procedure successfully completed.

Elapsed: 00:00:23.37

Try with SQL*Plus and post the result.

Regards
Michel

[Updated on: Wed, 11 November 2009 07:16]

Report message to a moderator

Re: CLOB - Appending CLOB in for loop. [message #430649 is a reply to message #430645] Wed, 11 November 2009 07:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
On 9i, you do seem to get a creeping death for large numbers.

It looks like an issue with Clob handling in 9i - if you alter the code to use a properly defined temporary clob, and not to do deep copies of clobs, then it works fine:
declare
i number;
c clob;
x number;
begin
dbms_lob.createtemporary(c,true);
for i in 1 .. 400000 loop
dbms_lob.append(c,to_clob(i));
end loop;
dbms_output.put_line(length(c));
end;
/
Re: CLOB - Appending CLOB in for loop. [message #430650 is a reply to message #430646] Wed, 11 November 2009 07:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
May I ask why you think we'd mislead you about our results? (I can't think of another reason for asking us to prove what we're saying - we're the ones trying to help you)
Still, if it eases your paranoia, here's what I get from 10g
SQL> conn dev/dev@dev10g
Connected.
SQL> set serveroutput on 
SQL> set timing on
SQL> declare
  2  i number;
  3  c clob;
  4  x number;
  5  begin
  6  for i in 1 .. 400000 loop
  7  x:=i;
  8  c:=c||to_clob(i);
  9  dbms_application_info.set_client_info('Row '||i);
 10  end loop;
 11  dbms_output.put_line(length(c));
 12  end;
 13  /
2288895

PL/SQL procedure successfully completed.

Elapsed: 00:00:22.82

Re: CLOB - Appending CLOB in for loop.(Resolved) [message #430651 is a reply to message #430650] Wed, 11 November 2009 07:27 Go to previous message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Smile JRowbottom,

You Rock man... This code piece

declare
i number;
c clob;
x number;
begin
dbms_lob.createtemporary(c,true);
for i in 1 .. 400000 loop
dbms_lob.append(c,to_clob(i));
end loop;
dbms_output.put_line(length(c));
end;



Working great ...took exactly what you are saying
Many many many thanks to you...Now please smile ...

Jak

[Updated on: Wed, 11 November 2009 07:34]

Report message to a moderator

Previous Topic: Use CLOB XML coming from Oracle
Next Topic: materialized view and summary table
Goto Forum:
  


Current Time: Wed Sep 28 10:52:35 CDT 2016

Total time taken to generate the page: 0.22179 seconds