title: Oracle SQL "exact fetch returns more than requested number of rows"
body: <p>I'm running some Oracle SQL and receiving the error "ORA-01422: exact fetch returns more than requested number of rows". I understand why this error would normally be thrown, but I'm requesting exactly one row and only one row is actually being returned. <em>(I am unable to create an SQL Fiddle for this, as I keep receiving an error that my schema creation syntax is incorrect despite the fact that it runs successfully in SQL Plus)</em></p>
  
  <p>My code is below. The function creates successfully, no errors are thrown. When I try to run the verification code, however, I receive the above error. Manually running the select statement returns only one row even though the error claims more than one row is being returned. What's wrong with my code to cause this error?</p>
  
  <p>The database schema:</p>
  
  <pre><code>create table Employee 
  (
      fname varchar(15),
      minit varchar(1),
      lname varchar(15),
      ssn char(9),
      bdate date,
      address varchar(30),
      sex varchar(1)  CHECK(sex = 'M' or sex = 'F'),
      salary number   CHECK(salary &gt;= 20000 and salary &lt;= 100000),
      super_ssn char(9),
      dno number  DEFAULT 0,
      constraint EMPPK
          primary key(ssn),
      constraint EMPSUPERVRFK
          foreign key(super_ssn) references Employee(ssn)
              ON DELETE SET NULL
  );
  
  create table Department 
  (
      dname varchar(15)   NOT NULL,
      dnumber number,
      mgr_ssn char(9)     DEFAULT '000000000',
      mgr_start_date date,
      constraint DEPTPK
          primary key(dnumber),
      constraint DEPTMGRFK
          foreign key(mgr_ssn) references Employee(ssn)
              ON DELETE SET NULL
  );
  
  insert into Employee values ('James','E','Borg','888665555','10-NOV-1937','450 Stone, Houston, TX','M',55000,null,1);
  insert into Employee values ('Franklin','T','Wong','333445555','08-DEC-1955','638 Voss, Houston, TX','M',40000,'888665555',5);
  insert into Employee values ('John','B','Smith','123456789','09-JAN-1965','731 Fondren, Houston, TX','M',30000,'333445555',5);
  insert into Employee values ('Jennifer','S','Wallace','987654321','20-JUN-1941','291 Berry, Bellaire, TX','F',43000,'888665555',4);
  insert into Employee values ('Alicia','J','Zelaya','999887777','19-JAN-1968','3321 Castle, Spring, TX','F',25000,'987654321',4);
  insert into Employee values ('Ramesh','K','Narayan','666884444','15-SEP-1962','975 Fire Oak, Humble, TX','M',38000,'333445555',5);
  insert into Employee values ('Joyce','A','English','453453453','31-JUL-1972','5631 Rice, Houston, TX','F',25000,'333445555',5);
  insert into Employee values ('Ahmad','V','Jabbar','987987987','29-MAR-1969','980 Dallas, Houston, TX','M',25000,'987654321',4);
  
  insert into Department values ('Research',5,'333445555','22-MAY-1988');
  insert into Department values ('Administration',4,'987654321','01-JAN-1995');
  insert into Department values ('Headquarters',1,'888665555','19-JUN-1981');
  </code></pre>
  
  <p>Function (created with no errors):</p>
  
  <pre><code>create or replace function getManagerNameDnameFunc(DNAME in department.dname%type)
      return varchar
  is
      name varchar(50);
  begin
      name := '';
  
      select e.fname || ' ' || e.lname into name from employee e, department d
      where d.dname = DNAME and d.mgr_ssn = e.ssn;
  
      return(name);
  
      exception
          when NO_DATA_FOUND then
              dbms_output.put_line('No data found');
              return(name);
  end;
  /
  show errors
  </code></pre>
  
  <p>Test code:</p>
  
  <pre><code>declare
      rs varchar(10);
      pr varchar(10);
  begin
      rs := 'Research';
      pr := 'Printing';
      dbms_output.put_line('Manager of Research department is ' || getManagerNameDnameFunc(rs));
      dbms_output.put_line('Manager of Printing department is ' || getManagerNameDnameFunc(pr));
  end;
  /
  </code></pre>
  
  <p>Exact output of <code>select e.fname || ' ' || e.lname from employee e, department d where d.dname = 'Research' and d.mgr_ssn = e.ssn;</code> (this name is what the function <em>should</em> output for the manager of the Research department):</p>
  
  <pre><code>E.FNAME||''||E.LNAME
  -------------------------------
  Franklin Wong
  </code></pre>