How to merge columns data into single CLOB column
Posted on 2003-11-01
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,
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...
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).
v_TITLE T1.TITLE%TYPE; -- VARCHAR2(100)
v_DESCRIPTION T1.DESCRIPTION%TYPE; -- CLOB
CURSOR curs is
fetch curs into
exit when curs%NOTFOUND;
|| '<TITLE>' || NVL(v_TITLE, '') || '</TITLE>'
|| '<DESCRIPTION>' || NVL(v_DESCRIPTION, '') || '</DESCRIPTION>'
insert into SEARCH_TABLE VALUES(v_T1_ID, searchdata);
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.