CODEWITHSUNDEEP
sqloracle-databaseplsql
sqlovers
sqlovers

Reputation: 67

create package with function and procedure pl sql

So I've made a function and a procedure. The function is to return a year with the highest number of employees in a department(input by user). Then the procedure will display list of employees in a department with a specific year (the year is used from the return of the function). Then i compile them into a package. But when i called them, it's not working. it says "not executed, package body "SQL_CXYSUJQLPSOQZJEYHEXHDIXUZ.MY_PCKG" does not exist ORA-06512: at line 5". Any ideas? Thanks!

create or replace package my_pckg as 
function check_date (my_name departments.department_name%type) RETURN INT;
procedure check_name(P_IN_DEPT_NAME IN VARCHAR2,P_IN_YEAR IN NUMBER);
END;
/

declare
my_var int;
my_var2 departments.department_name%type:='Shipping';
begin
my_var:=my_pckg.check_date(my_var2);
dbms_output.put_line(my_var);
my_pckg.check_name(my_var2,my_var);
end;
/

Upvotes: 0

Views: 2313

Answers (1)

Popeye
Popeye

Reputation: 35900

Looking at your issue, It seems that you have not created the package body.

  • Package (header) contains the only declaration of function/procedure within it.
  • Actual implementation of the function/procedure must be included in the package body.

Something like follows:

CREATE OR REPLACE PACKAGE PKG_NAME AS
  -- DECLARATION OF FUNCTION AND PROCEDURE WILL GO HERE
  FUNCTION FUN1(....);
  PROCEDURE PROC1(....);
END PKG_NAME;
/

CREATE OR REPLACE PACKAGE BODY PKG_NAME AS 
  -- ACTUAL IMPLEMENTATION OF FUNCTION AND PROCEDURE WILL GO HERE
  FUNCTION FUN1(....) AS
  ....
  ....
  END FUN1;

  PROCEDURE PROC1(....) AS
  ....
  ....
  END PROC1;
END PKG_NAME;
/

Upvotes: 1

Related Questions