 CLOB - Appending CLOB in for loop. [message #430631] |
Wed, 11 November 2009 04:44  |
|
|
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  |
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 #430648 is a reply to message #430646] |
Wed, 11 November 2009 07:15 |
 |
Michel Cadot
Messages: 68637
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 |
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 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
JRowbottom,