CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqlsyntax-error
ram12393
ram12393

Reputation: 1258

Getting error while Executing Package

Table Structure:

Name       Null Type         
---------- ---- ------------ 
DPT_NO          NUMBER       
SALARY          NUMBER(10)   
PERIOD          VARCHAR2(10) 
START_DATE      DATE         
END_DATE        DATE

Package:

CREATE OR REPLACE package body salary_sal AS
   PROCEDURE find_sal(c_dpt_no salary.dpt_no%TYPE) IS
   c_sal salary.salary%TYPE;
   BEGIN
      SELECT salary INTO c_sal
      FROM salary
      WHERE c_dpt_no= 108;
      dbms_output.put_line('Salary: '|| c_sal);
   END find_sal;
END salary_sal;

while executing above I'm getting following error

Error: PL/SQL: Compilation unit analysis terminated
Error(1,14): PLS-00201: identifier 'SALARY_SAL' must be declared
Error(1,14): PLS-00304: cannot compile body of 'SALARY_SAL' without its specification.

Upvotes: 4

Views: 31572

Answers (3)

Avrajit Roy
Avrajit Roy

Reputation: 3303

You neeed to create a package specifications and then only you can create a package body.

You need to execute the package i.e you execute the stored procedures,functions etc in the package.

Upvotes: 0

Mureinik
Mureinik

Reputation: 311123

You're missing the declaration of the package. The idea is to separate the declaration of the package ("the header", if you will), so other packages/procedures/functions can compile against it from the body (the implementation).

In your case, you'd need something like:

CREATE OR REPLACE package salary_sal AS
   PROCEDURE find_sal(c_dpt_no salary.dpt_no%TYPE);
END salary_sal;

Now, once the package is declared, you can create its body:

CREATE OR REPLACE package body salary_sal AS
   PROCEDURE find_sal(c_dpt_no salary.dpt_no%TYPE) IS
   c_sal salary.salary%TYPE;
   BEGIN
      SELECT salary INTO c_sal
      FROM salary
      WHERE c_dpt_no= 108;
      dbms_output.put_line('Salary: '|| c_sal);
   END find_sal;
END salary_sal;

Upvotes: 9

halfbit
halfbit

Reputation: 3464

The package needs a specification (i.e. a separate interface definition) as the error message says. You would have to add something like before the definition of the package body (i.e. its implementation):

CREATE OR REPLACE package salary_sal AS
   PROCEDURE find_sal(c_dpt_no salary.dpt_no%TYPE);
END salary_sal;
/

Upvotes: 0

Related Questions