CODEWITHSUNDEEP
oracle-databaseplsqlpackage
bethsy
bethsy

Reputation: 17

How to use procedure within package in PL/SQL

How to create a Package with Procedure in it? Procedure will take designation and incentive as input and update the employee salary by adding the incentive for the given designation. Display the number of employee records that have got updated, e.g. ‘3 employee record(s) are updated’. I have a table EMPLOYEE, with EMP_ID, EMP_NAME, SALARY and DESIGNATION. The given Functional Requirements are:

Package name as  EMP_DESIGNATION, and
Procedure signature:
EMP_DETAILS(design employee.designation%TYPE, incentive number);

The code which I tried is -

set serveroutput on;
CREATE OR REPLACE PACKAGE EMP_DESIGNATION AS
 PROCEDURE EMP_DETAILS(
design employee.designation%TYPE,
incentive NUMBER) IS
BEGIN
UPDATE employee SET employee.salary = employee.salary + incentive
WHERE employee.designation = design;
dbms_output.put_line(SQL%ROWCOUNT || ' employee record(s) are updated');
END EMP_DESIGNATION;
END;
/

The output is-

Warning: Package Body created with compilation errors.
BEGIN
*
ERROR at line 1:
ORA-04063: package body "P10017.EMP_DESIGNATION" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"P10017.EMP_DESIGNATION"
ORA-06512: at line 2

How to solve this error? Please help. Thank you in advance. PL/SQL: Statement ignored

Upvotes: 1

Views: 4890

Answers (1)

Littlefoot
Littlefoot

Reputation: 143083

You have to create a package specification first, then its body. Something like this:

SQL> CREATE OR REPLACE PACKAGE emp_designation
  2  AS
  3     PROCEDURE emp_details (design employee.designation%TYPE, incentive NUMBER);
  4  END emp_designation;
  5  /

Package created.

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY emp_designation
  2  AS
  3     PROCEDURE emp_details (design employee.designation%TYPE, incentive NUMBER)
  4     IS
  5     BEGIN
  6        UPDATE employee
  7           SET employee.salary = employee.salary + incentive
  8         WHERE employee.designation = design;
  9
 10        DBMS_OUTPUT.put_line (
 11           SQL%ROWCOUNT || ' employee record(s) are updated');
 12     END emp_details;
 13  END emp_designation;
 14  /

Package body created.

SQL>

Upvotes: 1

Related Questions