Hi Friends,

How can I improve performance of the ff program which runs slow.
Thanks

============
set serveroutput on
spool telnum.lst

DECLARE
v_customer_id NUMBER := 0;
v_phone VARCHAR2(35):=NULL;
v_fax VARCHAR2(35):=NULL;
v_email VARCHAR2(50):=NULL;
v_process VARCHAR2(40):=NULL;

cursor c_customer is
SELECT cca.customer_id
FROM cax_customers_all cca;

BEGIN

select to_char(sysdate,'DD-MON-RR HH24:MI:SS') into v_process from
dual;
dbms_output.put_line('update phone ==>'||v_process);

OPEN c_customer;

LOOP

FETCH c_customer
INTO v_customer_id;

EXIT WHEN c_customer%NOTFOUND;

-- dbms_output.put_line('fetched customer
==>'||v_customer_id);

BEGIN

SELECT
decode(rp.area_code,null,'','(')||rp.area_code||
decode(rp.area_code,null,'',')
')||rp.phone_number
INTO v_phone
FROM ar_phones_v rp
WHERE rp.status = 'A'
AND rp.phone_type = 'GEN'
AND rp.primary_flag = 'Y'
AND ((rp.owner_table_id = v_customer_id
and rp.owner_table_name = 'HZ_PARTIES')
or (rp.owner_table_id in (select
ad.address_id

from ar_addresses_v ad

where ad.customer_id = v_customer_id)
and rp.owner_table_name =
'HZ_PARTY_SITES')
or (rp.owner_table_id in (select
ac.rel_party_id

from ar_contacts_v ac

where ac.customer_id = v_customer_id

and ac.status = 'A')
and rp.owner_table_name =
'HZ_PARTIES'))
AND rownum < 2
;

-- dbms_output.put_line('update customer
==>'||v_customer_id);

UPDATE CAX_CUSTOMERS_ALL
SET PHONE = nvl(v_phone,' ')
WHERE customer_id = v_customer_id
;

EXCEPTION
WHEN NO_DATA_FOUND then null;

END;

END LOOP;

CLOSE c_customer;

select to_char(sysdate,'DD-MON-RR HH24:MI:SS') into
v_process from dual;
dbms_output.put_line('end ==>'||v_process);

END
;
/
spool off