Clob datatype- very slow while appending
580488Jan 4 2008 — edited Jan 4 2008Hi,
please help on below pl/sql block.
I have written below code, but the response time is very slow.
the table - aux_comm_3_to_1 has more than 1 Lakh records.
and I have append all the record and send the record as clob OUT parameter to GUI.
Pls suggest me.
----------------------------------------------------
declare
TEMP_XML clob;
XML_OW clob;
begin
FOR rec IN (SELECT
FRAMEREFERENCEDATE,
EU_LEU_ID_OWNER,
EU_LEU_ID_SUBSIDIARY,
DIRECT_PERCENT,
QUALITY_IND_DIRECT_PERCENT,
SOURCE_DIRECT_PERCENT,
REF_DATE_DIRECT_PERCENT,
KIND_OF_CONTROL,
SOURCE_KIND_OF_CONTROL,
REF_DATE_KIND_OF_CONTROL,
DATE_OF_COMMENCEMENT,
SOURCE_DATE_OF_COMMENCEMENT,
REF_DATE_OF_COMMENCEMENT,
DATE_OF_CESSATION,
SOURCE_DATE_OF_CESSATION,
REF_DATE_OF_CESSATION,
SOURCE_TA_OWNERSHIP,
REF_DATE_TA_OWNERSHIP
FROM aux_comm_3_to_1 )
LOOP
TEMP_XML :=
'<TARGET_OWNERSHIP="'
|| idcounter
||'"FRAMEREFERENCEDATE="'||rec.FRAMEREFERENCEDATE
||'"EU_LEU_ID_OWNER="'||rec.EU_LEU_ID_OWNER
||'"EU_LEU_ID_SUBSIDIARY="'||rec.EU_LEU_ID_SUBSIDIARY
||'"DIRECT_PERCENT="'||rec.DIRECT_PERCENT
||'"QUALITY_IND_DIRECT_PERCENT="'||rec.QUALITY_IND_DIRECT_PERCENT
||'"SOURCE_DIRECT_PERCENT="'||rec.SOURCE_DIRECT_PERCENT
||'"REF_DATE_DIRECT_PERCENT="'||rec.REF_DATE_DIRECT_PERCENT
||'"KIND_OF_CONTROL="'||rec.KIND_OF_CONTROL
||'"SOURCE_KIND_OF_CONTROL="'||rec.SOURCE_KIND_OF_CONTROL
||'"REF_DATE_KIND_OF_CONTROL="'||rec.REF_DATE_KIND_OF_CONTROL
||'"DATE_OF_COMMENCEMENT="'||rec.DATE_OF_COMMENCEMENT
||'"SOURCE_DATE_OF_COMMENCEMENT="'||rec.SOURCE_DATE_OF_COMMENCEMENT
||'"REF_DATE_OF_COMMENCEMENT="'||rec.REF_DATE_OF_COMMENCEMENT
||'"DATE_OF_CESSATION="'||rec.DATE_OF_CESSATION
||'"SOURCE_DATE_OF_CESSATION="'||rec.SOURCE_DATE_OF_CESSATION
||'"REF_DATE_OF_CESSATION="'||rec.REF_DATE_OF_CESSATION
||'"SOURCE_TA_OWNERSHIP="'||rec.SOURCE_TA_OWNERSHIP
||'"REF_DATE_TA_OWNERSHIP="'||rec.REF_DATE_TA_OWNERSHIP
|| '"/>'
|| CHR (10);
XML_OW:=XML_OW||CHR(10)||TEMP_XML;
idcounter := idcounter + 1;
END LOOP;
end;
----------------------