Home » SQL & PL/SQL » SQL & PL/SQL » Function Help
Function Help [message #185972] Fri, 04 August 2006 09:57 Go to next message
DCrash
Messages: 9
Registered: July 2006
Junior Member
Here is a bit of code I made, its just supposed to get movie information based on the id and output how many copies are available, as well as handle any errors regarding having none of aparticular movie in quantity.

CREATE OR REPLACE PROCEDURE MOVIE_STOCK_SF
(p_movie_id mm_movie.movie_id%TYPE)
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;


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;


v_message := v_movie_title + ' is available: ' + v_movie_qty + ' on the shelf.';
DBMS_OUTPUT.PUT_LINE(v_message);
END;

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;
/


I'm getting the error: "27/4 PLS-00103: Encountered the symbol ";" when expecting one of the f ollowing: if"
Not sure why that is happening. Also im curious as to if Im setting up the message the right way, or if there is a better way?
Re: Function Help [message #185973 is a reply to message #185972] Fri, 04 August 2006 10:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're missing the END IF; from following IF statements:

IF (v_count=0) THEN RAISE ex_unknown_movie;

IF v_movie_qty = 0 THEN RAISE ex_movie_not_available;
Re: Function Help [message #185974 is a reply to message #185972] Fri, 04 August 2006 10:00 Go to previous message
DCrash
Messages: 9
Registered: July 2006
Junior Member
oops! Thankyou!
Previous Topic: Slow Insert after Delete
Next Topic: Unable to insert into CLOB column
Goto Forum:
  


Current Time: Sat Dec 03 14:17:27 CST 2016

Total time taken to generate the page: 0.08547 seconds