CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqlpackage
pratteek shaurya
pratteek shaurya

Reputation: 960

How to execute package with parameter

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

Upvotes: 1

Views: 6421

Answers (3)

Sentinel
Sentinel

Reputation: 6459

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.

Upvotes: 1

Abdulmohsen Almasoud
Abdulmohsen Almasoud

Reputation: 66

Remove the IN after the variable name , and you need to run the PROCEDURE not the package also why you are defining a procedure in your case its better to make it function

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143153

If you use SQL*Plus, execute

SQL> show err

after compiling the package; it'll point you to the line that causes the error. For example:

SQL> create or replace procedure p_test is
  2  begin
  3    select 1 from dual;
  4  end;
  5  /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE P_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3      PLS-00428: an INTO clause is expected in this SELECT statement
SQL>

If you use a GUI, it probably contains the "Errors" tab you can examine (and get the same info).

As of calling a procedure with parameters: just include them, such as

variable x refcursor;
exec package_name.procedure_name (100, :x );
print x;                           ^
                                   |
                                  this is the first procedure's parameter

Upvotes: 0

Related Questions