Can someone help me put my pl/sql procedure in a package? I’ve tried and I’m struggling with it:
This is what I have, for my package specification:
x
CREATE OR REPLACE PACKAGE film_pkg
IS
title VARCHAR2(100);
PROCEDURE get_films(fname VARCHAR2);
END film_pkg;
--
This is my package body where im running into the problems:
CREATE OR REPLACE PACKAGE BODY film_pkg
IS
PROCEDURE get_films (fname IN film.title%TYPE,
r_date OUT film.release_date%TYPE,
dur OUT film.duration%TYPE)
AS
BEGIN
SELECT release_date, duration
INTO r_date, dur
FROM FILM
WHERE title = fname;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
r_date := '';
dur := '';
END get_films;
END film_pkg;
if anyone could help me fix the errors i’d appreciate it:
Error(4,11): PLS-00323: subprogram or cursor 'GET_FILMS' is declared in a package specification and must be defined in the package body
Advertisement
Answer
Your header and body procedure definitions don’t match
In the header, you have:
PROCEDURE get_films(fname VARCHAR2);
Whereas in the body:
PROCEDURE get_films(fname IN film.title%type,
r_date OUT film.release_date%type, dur OUT film.duration%type)
You probably just need to update the header definition with the two additional OUT params?
To Summarize
- Ensure the header definition matches all parameters of the body implementation (number of parameters, names of parameters, order of parameters, and the parameter types)
- As per Alex’s comment, do not mix and match the custom type (
film.title%type
) with the base type (VARCHAR2
). Choose one or the other.