SYS_REFCURSOR is returning all the rows from table without considering the IN parameter

Question

I am facing a weird problem here.

PROCEDURE USL_EMPLOYEEBYID (  
  EMPLOYEE_ID IN NUMBER,
  EMPIDCURSOR OUT SYS_REFCURSOR  
)  
AS  
BEGIN  
OPEN EMPIDCURSOR FOR  
   SELECT emp.employee_id,emp.employee_name,emp.present_address,emp.permanent_address,emp.status 
   FROM Employee_Info emp 
   WHERE emp.employee_id = EMPLOYEE_ID;
END;

This procedure should give me a single employee upon entering the employee Id. But it is returning all the employees.

What am I doing wrong here?

Answer 1

In your query, Oracle interprets EMPLOYEE_ID as the column EMPLOYEE_ID, not the input parameter, here you find something more; in this way, your where condition is something like a=a.

Change the parameter name to distinguish it from the table column:

PROCEDURE USL_EMPLOYEEBYID (  
  p_EMPLOYEE_ID IN NUMBER,
  po_EMPIDCURSOR OUT SYS_REFCURSOR  
)  
AS  
BEGIN  
OPEN po_EMPIDCURSOR FOR  
   SELECT emp.employee_id,emp.employee_name,emp.present_address,emp.permanent_address,emp.status 
   FROM Employee_Info emp 
   WHERE emp.employee_id = p_EMPLOYEE_ID;
END;

this is a good practice, to always know in your code whether you are handling an input parameter, a local variable, a column and so on