PLS-00201: identifier 'SALARY' must be declared

Question

This is my procedure:

create or replace procedure emp_lookup
 (empno   IN  emp55.empno%TYPE,
  salary  OUT emp55.sal%TYPE,
  empname OUT emp55.ename%TYPE)
is
begin 
  select sal , ename INTO salary, empname
  from   emp55 
  where  empno=empno;

EXCEPTION WHEN NO_DATA_FOUND THEN empname:='null';
  salary:=-1;
END;

This is the calling code:

SET SERVEROUTPUT ON

DECLARE 
  employee_name emp55.ename%TYPE;
  employee_salary emp55.sal%TYPE;
BEGIN
  emp_lookup (3244,salary,empname);
END;
/

It gives this error when executed:

Error starting at line : 3 in command -
DECLARE 
  employee_name emp55.ename%TYPE;
  employee_salary emp55.sal%TYPE;
BEGIN
  emp_lookup (3244,salary,empname);

END;

Error report -
ORA-06550: line 5, column 20:
PLS-00201: identifier 'SALARY' must be declared
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

It runs successfully but when I execute it

execute emp_lookup (3346, salary, empname);

shows this error:

Error starting at line : 16 in command -
BEGIN emp_lookup (3346, salary, empname); END;
Error report -
ORA-06550: line 1, column 27:
PLS-00201: identifier 'SALARY' must be declared
ORA-06550: line 1, column 54:
PL/SQL: Statement ignored

Kaushik Nayak · Answer 1 · 2018-02-22T08:58:15.850

0

you could use bind variables to pass as OUT parameters while calling.

VARIABLE sal NUMBER
VARIABLE name VARCHAR2(20)
execute emp_lookup (3346, :sal, :name)

If you are calling the procedure from within a PL/SQL block, you should have the OUT variables declared appropriately before calling.

DECLARE
salary emp55.sal%TYPE;
empname emp55.ename%TYPE;
BEGIN
   emp_lookup (3346, salary, empname);
END;
/

edited Feb 22 '18 at 08:58

answered Feb 22 '18 at 08:52

Kaushik Nayak

30,772
5
32
45

I did make a declaration SET SERVEROUTPUT ON DECLARE employee_name emp55.ename%TYPE; employee_salary emp55.sal%TYPE; BEGIN emp_lookup (3244,salary,empname); END; – R. Al Feb 22 '18 at 09:01
@R.Al : edit the question add your complete code for procedure invocation. – Kaushik Nayak Feb 22 '18 at 09:02
it didn't work it shows this Error starting at line : 3 in command - DECLARE employee_name emp55.ename%TYPE; employee_salary emp55.sal%TYPE; BEGIN emp_lookup (3244,salary,empname); END; Error report - ORA-06550: line 5, column 20: PLS-00201: identifier 'SALARY' must be declared ORA-06550: line 5, column 1: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action: – R. Al Feb 22 '18 at 09:02
Error starting at line : 20 in command - BEGIN emp_lookup (3346, :sal, :name); END; Error report - ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "SYS.EMP_LOOKUP", line 7 ORA-06512: at line 1 01422. 00000 - "exact fetch returns more than requested number of rows" *Cause: The number specified in exact fetch is less than the rows returned. *Action: Rewrite the query or change number of rows requested – R. Al Feb 22 '18 at 09:20
1

@R.Al `ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "SYS.EMP_LOOKUP` This error is nothing to do with the error you have in relation to the question. What Kaushik has provided with the answer will solve the issue you are having. – Jacob Feb 22 '18 at 09:59
@R.Al : That error doesn't have anything to do with the way you are calling it. Refer https://stackoverflow.com/questions/19779483/pl-sql-ora-01422-exact-fetch-returns-more-than-requested-number-of-rows for how to resolve it – Kaushik Nayak Feb 22 '18 at 10:03

score 0 · Answer 2 · answered Feb 22 '18 at 09:07

var salary number
var empname varchar2(10)
create or replace procedure emp_lookup
(no IN emp55.empno%TYPE,
salary OUT emp55.sal%TYPE,
empname OUT emp55.ename%TYPE)
IS
begin
select sal , ename INTO salary, empname
from emp55
where empno=no;

EXCEPTION WHEN NO_DATA_FOUND THEN empname:='null';
salary:=-1;

execute emp_lookup (3346, :salary,:empname);
print salary

PKey · Answer 3 · 2018-02-22T10:13:10.420

First correct your procedure code (change the name of the input variable)

This correction is needed because your where clause (where empno=empno;) is always true, so every row of the table will be returned - which is clearly not your initial intention.

create or replace procedure emp_lookup
(in_empno IN emp55.empno%TYPE,
 salary OUT emp55.sal%TYPE,
 empname OUT emp55.ename%TYPE)
IS 
begin 
select sal , ename INTO salary, empname
from emp55 
where empno=in_empno;

EXCEPTION WHEN NO_DATA_FOUND THEN empname:='null';
salary:=-1;

END;

Secod run as follows:

DECLARE 
  employee_name emp55.ename%TYPE;
  employee_salary emp55.sal%TYPE;
BEGIN
  emp_lookup (3244,employee_salary ,employee_name);
  dbms_output.put_line(employee_name   || ' ' || employee_salary);
END;

score 0 · Answer 4 · answered Feb 22 '18 at 09:49

You have to declare what 'salary' is: i.e.

SET SERVEROUTPUT ON
DECLARE 
  empname emp55.ename%TYPE;
  salary emp55.sal%TYPE;
BEGIN
  emp_lookup (3244,salary,empname);
END;
/

else,

SET SERVEROUTPUT ON
DECLARE 
  employee_name emp55.ename%TYPE;
  employee_salary emp55.sal%TYPE;
BEGIN
  emp_lookup (3244,employee_salary ,employee_name );
END;
/

I reckon, any of the above solutions would resolve your issue.

PLS-00201: identifier 'SALARY' must be declared

4 Answers4