title: Subquery returns more than one in row in PL/SQL stored procedure
body: <p>I am a complete novice to PL/SQL and this is an attempt to start writing stored procedures . The case is i want to simply find out inside my stored procedure what is the name of the employee who is getting the highest salary for a particular department (which is passed as a parameter to the stored procedure).</p>
  
  <p>Below is a screen shot of my tables:</p>
  
  <p><a href="https://i.stack.imgur.com/CPDOf.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/CPDOf.png" alt="Department tables"></a></p>
  
  <p><a href="https://i.stack.imgur.com/CnQIf.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/CnQIf.png" alt="Employee table"></a></p>
  
  <p>The code of my stored procedure is as follows :</p>
  
  <pre><code>create or replace procedure High_salary(Dept_Name IN varchar2)
  /*RETURN varchar2 */
  AS
  EMP_NAME_var varchar2(100) := '';
  Begin
  dbms_output.put_line('****'||Dept_Name);
  select EMP_NAME INTO EMP_NAME_var
  from(
  select EMP_NAME,rank() over(order by salary desc) rn from employee 
  where DEPT_CD=(select DEPT_CD from DEPARTMENT where DEPT_NAME=Dept_Name)) a where rn=1;
  /*RETURN EMP_NAME_var;*/
  END;
  </code></pre>
  
  <p>when i run this i get this error:</p>
  
  <pre><code>Connecting to the database LOCAL_DEV_DB.
  ORA-01427: single-row subquery returns more than one row
  ORA-06512: at "LOCAL_DEV_DB.HIGH_SALARY", line 7
  ORA-06512: at line 6
  ****'Technology'
  Process exited.
  Disconnecting from the database LOCAL_DEV_DB.
  </code></pre>
  
  <p>However when i run the subquery separate it gets only one row as expected :</p>
  
  <pre><code>select EMP_NAME 
  from(
  select EMP_NAME,rank() over(order by salary desc) rn from employee 
  where DEPT_CD=(select DEPT_CD from DEPARTMENT where DEPT_NAME='Technology')) a where rn=1;
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/xqSlU.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xqSlU.png" alt="Result"></a></p>
  
  <p>Can someone please point out what i am missing here.</p>