0

I have problem with my sql needs help. My original code:

DECLARE
    TYPE RecordTyp IS RECORD (v_vendor_name    vendorinvoices.vendor_name%TYPE,
    v_invoice_total   vendorinvoices.invoice_total%TYPE,
    v_invoice_due_date  vendorinvoices.invoice_due_date%TYPE
    );
    rec1 RecordTyp; 
BEGIN
    select vendor_name,invoice_total,to_char(invoice_due_date,'fmMONTH DD YYYY') into rec1
    from VendorInvoices     
    where Percentage(invoice_total,total_invoice_amount) > 70 order by vendor_name ; 
END; 
/

I got an error "exact fetch returns more than requested number of rows". Therefore, I fixed it by:

DECLARE
    TYPE RecordTyp IS RECORD (v_vendor_name    vendorinvoices.vendor_name%TYPE,
    v_invoice_total   vendorinvoices.invoice_total%TYPE,
    v_invoice_due_date  vendorinvoices.invoice_due_date%TYPE
    );   
    rec1 RecordTyp; 
BEGIN   
for rec in (
    select vendor_name,invoice_total,to_char(invoice_due_date,'fmMONTH DD YYYY') into rec1
    from VendorInvoices     
    where Percentage(invoice_total,total_invoice_amount) > 70 order by vendor_name )   
loop
    DBMS_OUTPUT.PUT_LINE(RPAD(rec1.v_VENDOR_NAME,35) || RPAD(rec1.v_invoice_total,35) || RPAD(rec1.v_invoice_due_date,35) );   
end loop; 
END; 
/

However, I don't know how to show up the result of the select statement, or I don't know if I fixed right. I only got a message, "anonymous block completed" even thought my DBMS Output is opened.

Please help me how to show the results or tell me if I did right/ wrong! P/S: I could use CURSOR, but the requirement asks to put the select statement between BEGIN...END

Thanks,

user3723825
  • 21
  • 1
  • 3

0 Answers0