Someone please explain why my stored package does not work? I show it its delete
ID: 3716992 • Letter: S
Question
Someone please explain why my stored package does not work? I show it its deleted, but i query the results, the movie is still in the table.
1. Create a package to hold the two procedures from instructions 4 & 5 above. All program units should be public. Overload the package with a 2nd version of the procedure from instruction 5 which allows the clerk to also delete a movie from inventory based on just the name of the movie. Test your package and overload procedure by deleting a movie from inventory by inputting the movie_title only.
PART 3: Creating a Package Body
SQL
CREATE OR REPLACE PACKAGE BODY update_movie_pkg
IS
PROCEDURE delete_movie_sp/*procedure to create an overload */
(p_movie_title IN mm_movie.movie_title%TYPE)
IS
BEGIN
DELETE FROM MM_MOVIE
WHERE mm_movie_title = p_movie_title;
END;
PROCEDURE delete_movie_sp /* a procedure to allow inventory to be set to zero*/
(p_movie_id NUMBER,
p_movie_qty NUMBER)
IS
BEGIN
DELETE FROM MM_MOVIE
WHERE mm_movie_id = p_movie_id;
END;
PROCEDURE new_movie_sp /* a procedure named new_movie_sp */
(p_movie_id IN mm_movie.movie_id%TYPE,
p_title IN mm_movie.movie_title%TYPE,
p_cat IN mm_movie.movie_cat_id%TYPE,
p_value IN mm_movie.movie_value%TYPE,
p_qty IN mm_movie.movie_qty%TYPE)
IS
BEGIN
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
VALUES (movie_id_seq.NEXTVAL, p_title, p_cat, p_value, p_qty);
UPDATE mm_movie
SET
movie_qty = movie_qty + 1
WHERE mm_movie.movie_id = p_movie_id;
END;
END;
Explanation / Answer
Hi,
Your procedure code is correct, but the reason you are not seeing the changes in DB is because you are not calling commit.
Stored procedures and PL/SQL blocks generally dont have auto commit, so to commit changes you need to change the procedure to
CREATE OR REPLACE PACKAGE BODY update_movie_pkg
IS
PROCEDURE delete_movie_sp/*procedure to create an overload */
(p_movie_title IN mm_movie.movie_title%TYPE)
IS
BEGIN
DELETE FROM MM_MOVIE
WHERE mm_movie_title = p_movie_title;
COMMIT;
END;
similarly, for other packages too, then you can see the changes in database.
Thumbs up if this was helpful, otherwise let me know in comments