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,