write an anonymous block program unit that delete the invoices that are due afte
ID: 3596309 • Letter: W
Question
write an anonymous 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
The goal is to get user input for a specific invoice_due_date and delete the records with dates that are greater than the user_input. Because an implicit cursor is used and the query will return multiple date a "Too Many Rows" exception will be thrown.
Explanation / Answer
Since no data is given and no schema details are given so it is not possible to provide sample output.
Below is the anonymus block:
prompt 'enter date in format mm-dd-yyyy';
/
accept dt;
/
DECLARE
idate varchar2(20) := &dt;
v_invoiceid number(10);
BEGIN
select invoiceid into v_invoiceid from invoices where invoice_due_date > to_date(idate,'mm-dd-yyyy');
IF SQL%FOUND THEN -- delete succeeded
delete from invoices where invoiceid = v_invoiceid;
dbms_output.put_line('invoices have been deleted successfully');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No invoices found for given date');
WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Multiple invoiceid returned for given date');
END;
/
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.