Help to optimize procedure (concatenation of rows)

[Juraj Longauer]

Hi,

I need to concatenate rows with description of account branch into one
column.

Example:
I have created temporary table to retrieve client and branches for his
accounts.(5 mil. rows)

CUS_NO MSP_ID MSP_DESC
5072130 2112 MsP Bratislava
5072130 2182 MsP Poprad

I would like to concatenate msp_desc into one row so the result will
look like this.

CUS_NO MSP_ID MSP_DESC
5072130 2112 MsP Bratislava :: MsP Poprad

I have created this procedure to perform the challenge. The procedure
is using 2 cursors. 1. cursor retrieves customer number and passes
customer number to 2. cursor. 2. cursor retrieves branches for a
customer and concatenates the branches. Concatenated values are passed
to variable. Then I insert customer number, name .... and variable
with concatenated branches. Its working but, its running toooo long (2
days). I would appreciate ideas to improve performance. I think the
problem is that I open second cursor for each customer.
Many thanks

Juraj


DECLARE
type mycur is ref cursor;
L_TNAME VARCHAR2(30) DEFAULT 'temp_table_' ||
USERENV('sessio nid');
V_UCET_MSP VARCHAR2(200);
CURSOR C1 IS(
SELECT CUS.CUS_NO, CUS.LOB_ID, CUS.NAME, CUS.TAXIDNO,
CUS.AS_OF_DATE
FROM CUSTOMER CUS
WHERE CUS_NO IN (SELECT CUS_NO FROM ACCT)

);
c2 mycur;
v_msp_desc varchar2(300);
i INTEGER;
BEGIN
--create temporary table with client info and branches
EXECUTE IMMEDIATE 'create global temporary table ' || L_TNAME ||
' on commit delete rows
as
SELECT DISTINCT CUS.CUS_NO, ORG.MSP_ID,
ORG.MSP_DESC
FROM CUSTOMER CUS, ACCT A, ICARUS.ORG_HIER _TMP ORG
WHERE 1=0';
--fill temporary table
EXECUTE IMMEDIATE 'insert into ' || L_TNAME ||
' SELECT DISTINCT CUS.CUS_NO, ORG.MSP_ID,
ORG.MSP_DESC
FROM CUSTOMER CUS, ACCT A, ICARUS.ORG_HIER _TMP
ORG
WHERE CUS.CUS_NO = A.CUS_NO AND A.ORG_UNIT_ID = ORG.LEAF_NODE';

--delete target table
DELETE FROM ICARUS.CUSTOMER ;
--insert into target table
FOR C1_REC IN C1 LOOP
OPEN c2 FOR 'select msp_desc from ' || l_tname || ' where cus_no =
'||c1_rec.cus_n o|| '';
LOOP
FETCH c2 INTO v_msp_desc;
exit when c2%notfound;
V_UCET_MSP := V_UCET_MSP || ' :: ' || v_msp_desc;
END LOOP;
CLOSE c2;
INSERT INTO ICARUS.CUSTOMER
VALUES
(C1_REC.CUS_NO,
C1_REC.LOB_ID,
C1_REC.NAME,
C1_REC.TAXIDNO,
V_UCET_MSP,
C1_REC.AS_OF_DA TE);
V_UCET_MSP := NULL;
I := I+1;
END LOOP;
DBMS_OUTPUT.PUT _LINE('Processe d rows: ' || I);
EXECUTE IMMEDIATE 'drop table ' || L_TNAME ||'';
COMMIT;
END;