Home » Other » Test » PLSQL Curser help
PLSQL Curser help [message #406170] Tue, 02 June 2009 11:20 Go to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: PLSQL Curser help [message #406266 is a reply to message #406174] Wed, 03 June 2009 01:24 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
explain what that last exeption handler is supposed to add to Oracle's default functionality

Hide the original errored line... for security reason. Wink

@jokaur,
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: Test new version
Next Topic: testing url
Goto Forum:
  


Current Time: Sat Dec 03 09:43:25 CST 2016

Total time taken to generate the page: 0.15099 seconds