Re: Student: Simple PL/SQL Question

From: Student <kennylim_at_techie.com>
Date: Sun, 29 Apr 2001 22:56:52 -0700
Message-ID: <9ciuik$v9s$1@slb1.atl.mindspring.net>

Thank Gentlemens for your guidance.
I am able to get past the problem now
with your great assistance. Esp.
for Mr. Paske.

You all have a good evening yourself.

Student-

"student" <kennylim_at_techie.com> wrote in message news:9cf0ct$8lm$1_at_nntp9.atl.mindspring.net...
>
> Hi guys,
>
> I am just a student experimenting on PL/SQL and was
> wondering if someone will be kind enough to help
> shed some light in getting past this error message.
> This is just a base logic that i am still constructing to
> test on the movies schema. So please bare with me
> if I am missing logics and exception prior to completion.
>
> Thanks and have a nice day guys.
>
> Task:
> -------
>
> 1. Execute p_movierentalupdate to insert new records into "movierental"
 and
> "rentalitem" table.
> 2. Upon insert, update or delete, trigger t_movierentalcheck will be
 invoked
> to call p_movierentalcheck
> 3. Procedure p_movierentalcheck will then check for outstanding dues based
> on the given customerid
> and provide exception if there is balance due else, prompt successful
> completion of transaction.
>
> Problem:
> ------------
>
> When executing or debugging p_movierentalupdate, I received the following
> error while stepping through the end of the routine.
>
> I have no problem executing and getting the proc to work on both
> p_movierentalupdate and p_movierentalcheck
> "separately" if I disable t_movierentalcheck trigger.
>
> 1 8 7 ORA-01403: no data found
> ORA-01403: no data found
> ORA-06512: at "MOVIES.P_MOVIERENTALCHECK", line 8
> ORA-06512: at "MOVIES.T_MOVIERENTALCHECK", line 7
> ORA-04088: error during execution of trigger 'MOVIES.T_MOVIERENTALCHECK'
> ORA-06512: at "MOVIES.MOVIERENTALUPDATE", line 14
> ORA-06512: at line 9
>
>

---

> =====
>
> --Procedure to update records
>
> CREATE OR REPLACE PROCEDURE MOVIES.Rental_Update
> (
> v_rentaldate movierental.rentaldate%TYPE := SYSDATE,
> v_duedate movierental.duedate%TYPE := SYSDATE+3,
> v_totalcharge movierental.totalcharge%TYPE,
> v_customerid movierental.customerid%TYPE,
> v_employeeid movierental.employeeid%TYPE,
> v_itemnumber rentalitem.itemnumber%TYPE,
> v_moviecopyid rentalitem.moviecopyid%TYPE
> )
> AS
> BEGIN
>
> INSERT INTO movierental VALUES
> (rentalidseq.NEXTVAL,v_rentaldate, v_duedate, v_totalcharge, v_customerid,
> v_employeeid, NULL);
>
> INSERT INTO rentalitem VALUES
> (rentalidseq.CURRVAL, v_itemnumber, v_moviecopyid);
>
> COMMIT;
>
> DBMS_OUTPUT.PUT_LINE('MOVIE TRANSACTION HAD BEEN SUCCESSFULLY UPDATED!');
> END;
> /
>
> --Procedure to check for late return
>
> CREATE OR REPLACE PROCEDURE MOVIES.P_MOVIERENTALCHECK
> (v_customerid IN number, v_validate_result OUT number)
>
> AS
> BEGIN
>
>
> SELECT SUM(ROUND(returndate-duedate)*totalcharge)
> INTO v_validate_result
> FROM movierental
> WHERE returndate > duedate
> AND customerid = v_customerid
> GROUP BY customerid;
>
> -- debug message
> dbms_output.put_line(v_Validate_Result);
>
> END ;
> /
>
> --Trigger to call
>
> CREATE OR REPLACE TRIGGER MOVIES.T_MOVIERENTALCHECK
> BEFORE INSERT OR UPDATE OF RETURNDATE OR DELETE
> ON MOVIES.MOVIERENTAL
> REFERENCING OLD AS OLD NEW AS NEW
> FOR EACH ROW
> declare
> v_customer_id number;
> v_validate_result number;
>
> BEGIN
> v_customer_id := :old.customerid;
> p_movierentalcheck (v_customer_id, v_validate_result);
>
> IF v_validate_result > 0
> THEN RAISE_APPLICATION_ERROR (-20001, 'Please Pay Outstanding Dues');
> ROLLBACK;
> ELSE
> COMMIT;
> END IF;
>
> END;
>
> /
>
>
>
Received on Mon Apr 30 2001 - 00:56:52 CDT