How to execute package with parameter

Question

I have two tables:

students(student_id, first_name, last_name)
classes(classid, incharge_student_id)

I have written a package which will take classid as a parameter and then print the student_id, first_name, last_name from students table for every incharge_student_id of classes table

CREATE OR REPLACE PACKAGE pack1
AS
PROCEDURE show_info(c_id classes.classid%TYPE DEFAULT 1, show_info_recordset OUT SYS_REFCURSOR);

END pack1;
/
CREATE OR REPLACE PACKAGE BODY pack1
AS
    PROCEDURE show_info 
    (   
    c_id   NUMBER DEFAULT 1,
    show_info_recordset OUT SYS_REFCURSOR
    )
    IS
       v_first_name students.first_name%TYPE;
       v_last_name students.last_name%TYPE;
       v_students students.student_id%TYPE;
    BEGIN
       SELECT students.first_name, students.last_name, students.student_id
         INTO v_first_name, v_last_name, v_students
         FROM students, classes
         WHERE (classes.classid = c_id AND classes.incharge_student_id=students.student_id);
       DBMS_OUTPUT.PUT_LINE('Class ID: ' || c_id);
       DBMS_OUTPUT.PUT_LINE('FIRST NAME: ' || v_first_name);
       DBMS_OUTPUT.PUT_LINE('LAST NAME: ' || v_last_name);
       DBMS_OUTPUT.PUT_LINE('student_id: ' || v_students);
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          DBMS_OUTPUT.PUT_LINE('Class ID: ' || c_id || ' not found.');
    END;
END pack1;
/

I am getting warning message

Package Body created with compilation errors.

I am very new to pl/sql and want to know why I am getting error message and then also want to know how will I execute a package which has a parameter.

I know how to execute normal package:

variable x refcursor;
exec package_name.procedure_name ( :x );
print x;

But how will I execute my above package with a parameter

Answer 1

You've got several errors in your code see below:

CREATE OR REPLACE PACKAGE pack1
AS
PROCEDURE show_info(c_id classes.classid%TYPE, show_info_recordset OUT SYS_REFCURSOR);

END pack1;
/
CREATE OR REPLACE PACKAGE BODY pack1
AS
    PROCEDURE show_info 
    (   
    c_id   NUMBER DEFAULT -1,

The specification of the c_id parameter in the body does not match the specification in the spec. Change it to c_id classes.classid%type default 1, and add the default 1 qualifier to the spec above a well.

    show_info_recordset OUT SYS_REFCURSOR
    )
    IS
       v_first_name IN students.first_name%TYPE;
       v_last_name IN students.last_name%TYPE;
       v_students IN students.student_id%TYPE;

Remove the "IN" keywords in the variable declarations. It's only used for parameter declarations.

    BEGIN
       SELECT students.first_name, students.last_name, students.student_id
         INTO v_first_name, v_last_name, v_students.student_id

Reference v_students in you into clause not v_students.student_id

         FROM students, classes
         WHERE (classes.classid = c_id AND classes.incharge_student_id=students.student_id;);

You have an extra semicolon (;) inside the closing parenthesis. Remove it.

       DBMS_OUTPUT.PUT_LINE('Class ID: ' || c_id);
       DBMS_OUTPUT.PUT_LINE('FIRST NAME: ' || v_first_name);
       DBMS_OUTPUT.PUT_LINE('LAST NAME: ' || v_last_name);
       DBMS_OUTPUT.PUT_LINE('student_id: ' || v_students);
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          DBMS_OUTPUT.PUT_LINE('Class ID: ' || c_id || ' not found.');
    END;
END pack1;
/

To execute this code you just need to pass in the additional parameter, or used named parameters as mentioned by @Littlefoot though since you aren't assigning a cursor to the out parameter, there won't be anything to print.