Someone please explain why my stored package does not work? I show it its delete

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

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.