Home » SQL & PL/SQL » SQL & PL/SQL » Package Problems
Package Problems [message #186187] Sun, 06 August 2006 20:53 Go to next message
DCrash
Messages: 9
Registered: July 2006
Junior Member
This ties in the the post I made earlier, hmm maybe it doesnt... that was an exception problem.. *ahem* anyway! I'm having a problem making my package work. I know that the procedures and function work independantly, but i cant figure out how to put them into the package. Here is my attempt:
CREATE OR REPLACE PACKAGE MM_RENTALS_PKG
IS
PROCEDURE movie_rent_PP
(p_movie_id mm_movie.movie_id%TYPE,
    p_member_id mm_member.member_id%TYPE,
    p_payment_method mm_rental.payment_methods_id%TYPE);
PROCEDURE movie_return_PP
(p_rental_id mm_rental.rental_id%TYPE);
FUNCTION MOVIE_STOCK_PF
(p_movie_id mm_movie.movie_id%TYPE);
END;
/

CREATE OR REPLACE PACKAGE BODY MM_RENTALS_PKG IS
FUNCTION MOVIE_STOCK_PP
(p_movie_id mm_movie.movie_id%TYPE)
RETURN VARCHAR2
IS
v_movie_title mm_movie.movie_title%TYPE;
v_movie_qty mm_movie.movie_qty%TYPE;

ex_unknown_movie EXCEPTION;
ex_movie_not_available EXCEPTION;


v_message VARCHAR2(20);
v_count INTEGER;

BEGIN
SELECT COUNT(movie_title) into v_count
FROM mm_movie WHERE movie_id =p_movie_id;
IF (v_count=0) THEN RAISE ex_unknown_movie;
END IF;

SELECT movie_qty into v_movie_qty
FROM mm_movie WHERE movie_id= p_movie_id;
IF v_movie_qty = 0 THEN RAISE ex_movie_not_available;
END IF;

v_message := v_movie_title + ' is available: ' + v_movie_qty + ' on the shelf.';
RETURN v_message;

EXCEPTION
WHEN ex_movie_not_available THEN
DBMS_OUTPUT.PUT_LINE('Movie ' || p_movie_id || ' is not available at this time');
WHEN ex_unknown_movie THEN
DBMS_OUTPUT.PUT_LINE('There is no movie with id: ' || p_movie_id);
END MOVIE_STOCK_PP;

PROCEDURE movie_rent_PP
   (p_movie_id mm_movie.movie_id%TYPE,
    p_member_id mm_member.member_id%TYPE,
    p_payment_method mm_rental.payment_methods_id%TYPE)
IS
	v_count INTEGER;
	v_qty_available mm_movie.movie_qty%TYPE;
	ex_unknown_movie EXCEPTION;
	ex_unknown_member EXCEPTION;
	ex_unknown_pay EXCEPTION;
	ex_movie_not_available EXCEPTION;
	v_rental_id mm_rental.rental_id%TYPE;
BEGIN
	-- validate parameters
	SELECT COUNT(movie_id) INTO v_count
	FROM mm_movie WHERE movie_id = p_movie_id;
	IF(v_count = 0) THEN RAISE ex_unknown_movie; 
	END IF;

	SELECT COUNT(member_id) INTO v_count
	FROM mm_member WHERE member_id = p_member_id;
	IF(v_count = 0) THEN RAISE ex_unknown_member; 
	END IF;

	SELECT COUNT(payment_methods_id) INTO v_count
	FROM mm_pay_type WHERE payment_methods_id = p_payment_method;
	IF(v_count = 0) THEN RAISE ex_unknown_pay;
	END IF;

	-- check if the movie is available
	SELECT movie_qty INTO v_qty_available
	FROM mm_movie WHERE movie_id = p_movie_id;
	IF v_qty_available = 0 THEN RAISE ex_movie_not_available;
	END IF;

	-- generate a new rental id
	BEGIN
		-- get the largest rental id so far
		SELECT MAX(rental_id) INTO v_rental_id
		FROM mm_rental;
		-- make the new id one greater than the largest
		v_rental_id := v_rental_id + 1;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN -- no rows in the MM_RENTAL table
			v_rental_id := 1;
	END;

	-- insert a row of data into the MM_RENTAL table
	INSERT INTO mm_rental(rental_id, member_id, movie_id, 
                              	checkout_date, Checkin_date, payment_methods_id)
	VALUES(v_rental_id, p_member_id, p_movie_id, 
				SYSDATE, NULL, p_payment_method);
	-- update movie inventory
	UPDATE mm_movie
	SET movie_qty = movie_qty - 1
	WHERE movie_id = p_movie_id;

EXCEPTION
WHEN ex_unknown_movie THEN
	DBMS_OUTPUT.PUT_LINE('There is no movie with id: ' || p_movie_id);
	DBMS_OUTPUT.PUT_LINE('Cannot proceed with rental');
WHEN ex_unknown_member THEN
	DBMS_OUTPUT.PUT_LINE('There is no member with id: ' || p_member_id);
	DBMS_OUTPUT.PUT_LINE('Cannot proceed with rental');
WHEN ex_unknown_pay THEN
	DBMS_OUTPUT.PUT_LINE('There is no payment method with id: ' 
		|| p_payment_method);
	DBMS_OUTPUT.PUT_LINE('Cannot proceed with rental');
WHEN ex_movie_not_available THEN
	DBMS_OUTPUT.PUT_LINE('Movie ' || p_movie_id || 
		' is not available at this time');
	DBMS_OUTPUT.PUT_LINE('Cannot proceed with rental');
WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE('Unexpected error occured:');
	DBMS_OUTPUT.PUT_LINE('Error code: ' || SQLCODE);
	DBMS_OUTPUT.PUT_LINE('Error message: ' || SQLERRM);
END MOVIE_RENT_PP;
PROCEDURE movie_return_PP 
   (p_rental_id mm_rental.rental_id%TYPE)
IS
	ex_already_returned EXCEPTION;
	v_movie_id mm_movie.movie_id%TYPE;
	v_checkin_date DATE;
BEGIN
	
	-- get the checkin_date for this rental record
	SELECT checkin_date 
	INTO v_checkin_date
	FROM mm_rental
	WHERE rental_id =  p_rental_id;

	-- if already returned (there is a checkin_date), raise error
	IF v_checkin_date IS NOT NULL THEN
		RAISE ex_already_returned;
	END IF;
	
	-- update checkin_date in the rental record
	UPDATE mm_rental
	SET checkin_date = SYSDATE
	WHERE rental_id = p_rental_id;

	-- get movie id for this rental record
	SELECT movie_id INTO v_movie_id
	FROM mm_rental 
	WHERE rental_id = p_rental_id;
		

	-- upadate movie inventory
	UPDATE mm_movie
	SET movie_qty = movie_qty + 1
	WHERE movie_id = v_movie_id;

EXCEPTION
	WHEN NO_DATA_FOUND THEN
		DBMS_OUTPUT.PUT_LINE('There is no rental record ' || 
			p_rental_id);
	WHEN ex_already_returned THEN
		DBMS_OUTPUT.PUT_LINE('Movie has already been returned');
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('Unexpected error occured:'|| SQLERRM);
END MOVIE_RETURN_PP;
END;
/


I get the error:
0/0 PL/SQL: Compilation unit analysis terminated
1/14 PLS-00905: object D00741655.MM_RENTALS_PKG is invalid
1/14 PLS-00304: cannot compile body of 'MM_RENTALS_PKG' without its sp ecification
Re: Package Problems [message #186192 is a reply to message #186187] Sun, 06 August 2006 21:30 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
Get the package specification to compile successfully first, then the body.

Here the return type of function MOVIE_STOCK_PF is missing from the specification, and this function is declared but it is not defined in the package body.
Re: Package Problems [message #186194 is a reply to message #186187] Sun, 06 August 2006 21:35 Go to previous message
DCrash
Messages: 9
Registered: July 2006
Junior Member
Thanks, it created sucessfully. I just forgot to fix the naming convention when i switched them over, thankyou for pointing that out.
Previous Topic: how to get the Max
Next Topic: Fast delete
Goto Forum:
  


Current Time: Wed Dec 07 14:39:41 CST 2016

Total time taken to generate the page: 0.25895 seconds