write an anonymous PL/SQL block program unit that delete the invoices that are d
ID: 3595795 • Letter: W
Question
write an anonymous PL/SQL block program unit that delete the invoices that are due after a user-input date and print a message 'invoices have been deleted successfully'. The program raises an exception if there are no invoices due after that specific date. Also it raise the exception ‘Too many row’.
Hints:
1-You will use select query to get invoice_id into an implicit cursor(Only 1 Value) if the invoice_due_date > user-input
2- you may use the to_date() function to cast the user-input variable.
3- you will delete the record that have same invoice_id
4-do not use explicit cursor
5- ‘no data found’ cannot be triggered if you use explicit cursor.
Code so Far:
DECLARE
date_id INVOICES.invoice_id%type;
BEGIN
SELECT invoice_id INTO date_id
FROM INVOICES
WHERE invoice_due_date = :u_date;
DELETE FROM INVOICES
IF user_date < invoice_due_date
WHERE invoice_id = date_id
dbms_output.put_line('Invoices have been successfully deleted');
EXCEPTION
WHEN too_many_rows THEN
dbms_output.put_line('ERROR: Too Many Rows' );
WHEN others then
dbms_output.put_line('Unexpected Exception' );
Keep getting an "end-of-file" error.
Explanation / Answer
Verified your PL/SQL progrm.. code is good but you missed inclusing 'END;' statement to complete the program. That's why It was throwing error 'end-of-file' error...
DECLARE
date_id INVOICES.invoice_id%type;
BEGIN
SELECT invoice_id INTO date_id
FROM INVOICES
WHERE invoice_due_date = :u_date;
DELETE FROM INVOICES
IF user_date < invoice_due_date
WHERE invoice_id = date_id
dbms_output.put_line('Invoices have been successfully deleted');
EXCEPTION
WHEN too_many_rows THEN
dbms_output.put_line('ERROR: Too Many Rows' );
WHEN others then
dbms_output.put_line('Unexpected Exception' );
END;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.