Student: Simple PL/SQL Question

From: student <kennylim_at_techie.com>
Date: Sat, 28 Apr 2001 11:03:21 -0700
Message-ID: <9cf0ct$8lm$1_at_nntp9.atl.mindspring.net>

[Quoted] Hi guys,

[Quoted] 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. [Quoted]
2. Upon insert, update or delete, trigger t_movierentalcheck will be invoked to call p_movierentalcheck [Quoted]
3. Procedure p_movierentalcheck will then check for outstanding dues based [Quoted] 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

============================================================================

---

[Quoted] --Procedure to update records

[Quoted] 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 Sat Apr 28 2001 - 20:03:21 CEST