| PLSQL Curser help [message #406170] |
Tue, 02 June 2009 11:20  |
jokaur
Messages: 1
Registered: June 2009
Location: USA
|
Junior Member |
|
|
I need a help in PLSQL SCRIPT. Looks to me it is not effecient.
Please help me.
DECLARE
l_bs_id NUMBER;
l_string1 VARCHAR2(1000);
l_filept UTL_FILE.FILE_TYPE;
x_v_tax_id VARCHAR2(50);
x_v_name VARCHAR2(100);
x_v_number NUMBER;
x_v_creation_date DATE;
x_s_site_code VARCHAR2(50);
x_s_site_id NUMBER;
x_s_creation_date DATE;
x_s_purchasing_site_flag VARCHAR2(2);
x_p_last_date DATE;
x_s_pay_site_flag VARCHAR2(2);
x_i_last_date DATE;
x_r_last_date DATE;
x_s_default_pay_site_id varchar2(2);
CURSOR c1 IS
SELECT vendor_site_id, v.segment1, s.creation_date
FROM po_vendors v, po_vendor_sites_all S
WHERE v.vendor_id = s.vendor_id
AND v.segment1 = 1
and v.end_date_active is null
and s.inactive_date is null
AND s.creation_date < '01-JAN-2007'
and s.org_id = 2 ;
BEGIN
l_filept := UTL_FILE.FOPEN('/tempdata','supplier_inactive.txt','W');
FOR c1_rec IN c1
LOOP
x_v_tax_id := NULL;
x_v_name := NULL;
x_v_number := NULL;
x_v_creation_date := NULL;
x_s_site_code := NULL;
x_s_site_id := NULL;
x_s_creation_date := NULL;
x_s_purchasing_site_flag := NULL;
x_p_last_date := NULL;
x_s_pay_site_flag := NULL;
x_i_last_date := NULL;
x_r_last_date := NULL;
x_s_default_pay_site_id := NULL;
BEGIN
SELECT
v.num_1099 AS tax_id,
v.vendor_name AS vendor_name,
v.segment1 AS vendor_number,
v.creation_date AS v_creation_date,
s.vendor_site_code AS v_site_code,
s.vendor_site_id AS vendor_site_id,
s.creation_date AS s_creation_date,
s.PURCHASING_SITE_FLAG AS Purchase_site,
s.PAY_SITE_FLAG AS Pay_site,
s.default_pay_site_id
INTO
x_v_tax_id,
x_v_name,
x_v_number,
x_v_creation_date,
x_s_site_code,
x_s_site_id,
x_s_creation_date,
x_s_purchasing_site_flag,
x_s_pay_site_flag,
x_s_default_pay_site_id
FROM po_vendors v, po_vendor_sites_all s
WHERE v.vendor_id = s.vendor_id
AND s.vendor_site_id = c1_rec.vendor_site_id;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line ('item not found: '|| c1_rec.vendor_site_id);
END;
BEGIN
SELECT MAX(creation_date)
INTO x_p_last_date
FROM po_headers_all
WHERE vendor_site_id = c1_rec.vendor_site_id;
EXCEPTION
WHEN OTHERS THEN
x_p_last_date := NULL;
END;
BEGIN
SELECT MAX(CREATION_DATE)
INTO x_i_last_date
FROM ap_invoices_all
WHERE vendor_site_id = c1_rec.vendor_site_id;
EXCEPTION
WHEN OTHERS THEN
x_i_last_date := NULL;
END;
BEGIN
SELECT MAX(CREATION_DATE)
INTO x_r_last_date
FROM rcv_transactions
WHERE vendor_site_id = c1_rec.vendor_site_id;
EXCEPTION
WHEN OTHERS THEN
x_r_last_date := NULL;
END;
l_string1 := x_v_tax_id||'|'||
x_v_name||'|'||
x_v_number||'|'||
x_v_creation_date||'|'||
x_s_site_code||'|'||
x_s_site_id||'|'||
x_s_creation_date||'|'||
x_s_purchasing_site_flag||'|'||
x_p_last_date||'|'||
x_s_pay_site_flag||'|'||
x_i_last_date||'|'||
x_r_last_date||'|'||
x_s_default_pay_site_id;
UTL_FILE.PUT_LINE(l_filept,l_string1);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR ('-20001', SQLERRM);
END;
/
|
|
|
|
| Re: PLSQL Curser help [message #406174 is a reply to message #406170] |
Tue, 02 June 2009 11:44  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| jokaur wrote on Tue, 02 June 2009 18:20 |
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line ('item not found: '|| c1_rec.vendor_site_id);
END;
...
...
EXCEPTION
WHEN OTHERS THEN
x_p_last_date := NULL;
...
...
EXCEPTION
WHEN OTHERS THEN
x_i_last_date := NULL;
...
...
EXCEPTION
WHEN OTHERS THEN
x_r_last_date := NULL;
...
...
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR ('-20001', SQLERRM);
END;
|
There, found your bugs.
Only handle exceptions you WANT to handle. Only handle exceptions you know can occur.
Please please explain what that last exeption handler is supposed to add to Oracle's default functionality.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
