How to merge columns data into single CLOB column

Oracle9iR2 / Windows2000 Pro
----------------------------

There are 10 transactional tables having few columns as VARCHAR2 and few as CLOB. There are only 35000 records in total. For each record I have to generate a table named SEARCH_TABLE by concatenating the VARCHAR2 and CLOB fields data into one 'TEXT' CLOB column. The structure of SEARCH_TABLE is...

CREATE TABLE SEARCH_TABLE
(ID VARCHAR2(8) PRIMARY KEY,
TEXT CLOB);

The values are concatenated as a XML data. So if I have say 4 columns (C1, C2, C3, C4) in Table T1 then the TEXT field of SEARCH_TABLE is polulated as...

<SEARCH_DATA><C1>...data...</C1><C2>...data...</C2><C3>...data...</C3><C4>...data...</C4></SEARCH_DATA>

To do this I have written the PL/SQL block that opens a cursor on the each table and does the concatenation of the fields including the XML tags (Column names).

declare
v_T1_ID T1.T1_ID%TYPE;
v_TITLE T1.TITLE%TYPE; -- VARCHAR2(100)
v_DESCRIPTION T1.DESCRIPTION%TYPE; -- CLOB
searchdata CLOB;

CURSOR curs is
SELECT
T1_ID,
TITLE,
DESCRIPTION
FROM
T1
ORDER BY
T1_ID;
begin
open curs;
loop
fetch curs into
v_T1_ID,
v_TITLE,
v_DESCRIPTION;

exit when curs%NOTFOUND;

dbms_output.put_line(v_T1_ID);

searchdata :=
'<SEARCH_DATA>'
|| '<TITLE>' || NVL(v_TITLE, '') || '</TITLE>'
|| '<DESCRIPTION>' || NVL(v_DESCRIPTION, '') || '</DESCRIPTION>'
||
'</SEARCH_DATA>';

insert into SEARCH_TABLE VALUES(v_T1_ID, searchdata);
end loop;
close curs;
commit;
end;
/

Problem is that it takes lot of time to do the population. After some investigation I found that more the fields I have to concatenate the more time it takes (and also many XML tags I concatenate, two for each column). For 1500 records and 6 columns it took me 11 minutes. Not good.

Is String Concatenation in Oracle is costly (something similar to Java String Vs StringBuffer). What is the right way of doing this.

Thanks

Not sure what's making that so slow but I wonder if you have tried it without pl/sql.

with what you're doing that could be done with a simple insert..select statement, I wonder if that would be any faster.

hedge,

Thanks for your response. No I have not tried with the single non pl/sql command. Could you please help how to write that single command that does what I am trying.

Incidently for another table of mine having 5500 records and 8 VARCHAR2 and CLOB fields it took 2 Hrs and 30 Mins. Just not acceptable.

Just to give you one observation is that when I login into another SQL*Plus to check if the table is getting populating I come to know that the table gets populated much much earlier (eg 5 mins) but it took as much as 2:30 Hrs to get the SQL> prompt. Don't know what is going.

Please help. Thanks

I believe if you can do it as a create table as statement then redo logging will be disabled, this will probably increase performance as well: