Retrieving user-defined record from procedure PLSQL

Question

I've finally cracked. Here's our school assignment:

Add another procedure to the college_package called get_class_infor. The get_class_infor procedure is used to fetch and return the class id, instructor name, course title, and department for a specific class. Pass CLASS_ID as an IN parameter. Define a user-defined record TYPE for the record variables in the procedure.

Create an anonymous block to call the get_class_infor procedure and display information about a class. The following example uses class id 01.

** Class id:     1
** Instructor:   Gunther Haas
** Course Title: Algebra I
** Department:   Mathematics

I created this anonymous block to make sure I understood how to send a parameter and retrieve a user-defined record. This works perfectly fine.

ANONYMOUS BLOCK:

DECLARE
   TYPE class_type IS RECORD (
     class_id  classes.class_id%TYPE,
     i_first   instructors.first_name%TYPE,
     i_last    instructors.last_name%TYPE,
     course    courses.title%TYPE,
     dept      sections.title%TYPE
   );
   p_rec class_type;
   PROCEDURE get_class_infor(
      p_id IN NUMBER,
      p_rec OUT class_type
   ) IS
   BEGIN
      SELECT cl.class_id, i.first_name, i.last_name, cr.title, s.title 
      INTO p_rec 
      FROM classes cl 
         JOIN instructors i ON cl.instr_id = i.instructor_id
         JOIN courses cr    ON cl.course_id = cr.course_id
         JOIN sections s    ON cr.section_code = s.section_code
         WHERE class_id = p_id;
   END get_class_infor;
BEGIN
   get_class_infor(:class_id, p_rec);
   DBMS_OUTPUT.PUT_LINE('** Class id:     ' || p_rec.class_id);
   DBMS_OUTPUT.PUT_LINE('** Instructor:   ' || p_rec.i_first || ' ' || p_rec.i_last);
   DBMS_OUTPUT.PUT_LINE('** Course Title: ' || p_rec.course);
   DBMS_OUTPUT.PUT_LINE('** Department:   ' || p_rec.dept);
END;

Where I'm stuck is creating the package spec/body properly, or so I assume. I don't know what I'm missing. Here's what I got.

PACKAGE SPEC:

-- Example 4_2A
CREATE OR REPLACE PACKAGE college_package IS
  TYPE class_type IS RECORD (
    class_id  classes.class_id%TYPE,
    i_first   instructors.first_name%TYPE,
    i_last    instructors.last_name%TYPE,
    course    courses.title%TYPE,
    dept      sections.title%TYPE
  );
  PROCEDURE get_class_infor (
     p_id IN NUMBER, 
     p_rec OUT class_type
  );
END college_package;

PACKAGE BODY:

-- Example 4_2B
CREATE OR REPLACE PACKAGE BODY college_package IS
  PROCEDURE get_class_infor (
    p_id IN NUMBER,
    p_rec OUT class_type
  ) IS
  BEGIN
    SELECT cl.class_id, i.first_name, i.last_name, cr.title, s.title
    INTO p_rec 
    FROM classes cl 
      JOIN instructors i  ON cl.instr_id = i.instructor_id
      JOIN courses cr     ON cl.course_id = cr.course_id
      JOIN sections s     ON cr.section_code = s.section_code
      WHERE class_id = p_id;
  END;
END college_package;

ANONYMOUS BLOCK:

-- Example 4_2C
DECLARE
  TYPE class_type IS RECORD  (
    class_id  classes.class_id%TYPE,
    i_first   instructors.first_name%TYPE,
    i_last    instructors.last_name%TYPE,
    course    courses.title%TYPE,
    dept      sections.title%TYPE
  ); 
  v_rec class_type;
BEGIN
   college_package.get_class_infor(:class_id, v_rec);
   DBMS_OUTPUT.PUT_LINE('** Class id:     ' || v_rec.class_id);
   DBMS_OUTPUT.PUT_LINE('** Instructor:   ' || v_rec.i_first || ' ' || v_rec.i_last);
   DBMS_OUTPUT.PUT_LINE('** Course Title: ' || v_rec.course);
   DBMS_OUTPUT.PUT_LINE('** Department:   ' || v_rec.dept);
END;

The error message I get is:

ORA-06550: line 12, column 4:
PLS-00306: wrong number or types of arguments in call to 'GET_CLASS_INFOR'
ORA-06550: line 12, column 4:
PL/SQL: Statement ignored

Is there something wrong with how I'm creating the package spec/body? I'd like to find the answer myself so a hint in the right direction would be appreciated.

Answer 1

You have defined a record type in the package spec. That means you can use it in other programs, just like you can call the procedure.

But what you've done is declare a second record type in your anonymous block. They look the same to you, because they've got the same names and structure. But to the compiler they're different, because they are different things. The procedure wants the record type defined in the package.

So the fix is quite simple:

DECLARE
  v_rec college_package.class_type;
BEGIN
   college_package.get_class_infor(:class_id, v_rec);
   DBMS_OUTPUT.PUT_LINE('** Class id:     ' || v_rec.class_id);
   DBMS_OUTPUT.PUT_LINE('** Instructor:   ' || v_rec.i_first || ' ' || v_rec.i_last);
   DBMS_OUTPUT.PUT_LINE('** Course Title: ' || v_rec.course);
   DBMS_OUTPUT.PUT_LINE('** Department:   ' || v_rec.dept);
END;