i have errors for my pl/sql

Code:

---

declare
  TYPE a IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
  b a;
  TYPE c IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
  d c;
  cursor c1 is select ename,empno,mgr from emp;
  cursor c3 is select ename,empno,mgr from emp;
  i number(2):=0;
  no_mgr EXCEPTION;
begin
  dbms_output.put_line('Manager' ||' '|| 'Emp');
  dbms_output.put_line('______________________________________');
  for c2 in c1 loop
    b(i):=c2.ename;
    for c4 in c3 loop
      if c4.empno = c2.mgr then
        d(i):=c4.ename;
        dbms_output.put_line(d(i) || ' '|| b(i));
      elsif c2.mgr is NULL then
        d(i):='NO MANAGER';
        dbms_output.put_line(d(i));
      end if;
    end loop;
  end loop;
END;

---

You could try like this

Code:

---

select
  ename, (select NVL(MIN(ename),'NO MANAGER') from emp b where b.empno = a.mgr) MANAGER
from emp a

---

The reason for your error is that your exception section is inside your loop. The exception section must come after the last executable statement of your PL/SQL block's body.

Another problem with your code is that you seem to expect it to resume were it left off after handling an exception. This is not how exception handling works. Your code returns to the calling block after executing an exception handler. If it finds no appropriate handler for the exception it propagates the exception to the calling block.

Yet another problem is that your exception section is intended to handle rows that have a NULL manager. But you are building output from the manager point of view... you are asking who the employees of a manager are... so you will never encounter a null manager.

I've made a working version of your code which appears at the bottom of this post. However, it produces the same output as this SQL query:

Code:

---

SELECT mgr.ename manager,
      e.ename employee
FROM emp mgr, emp e
WHERE mgr.empno = e.mgr
ORDER BY mgr.ename

---

To query from the employee point of view, you need to use an outer join in order to see employees with a NULL manager.

Code:

---

SELECT e.ename employee,
      mgr.ename manager
FROM emp e, emp mgr
WHERE e.mgr = mgr.empno(+)
ORDER BY e.ename

---

Here is a working version of your PL/SQL block. In order to use an exception handler to handle NULL managers the section that makes the test and raises the exception had to be put in its own sub-block with its exception handler. However, remember that this code encounters no NULL managers because it looks from manager to employee, not from employee to manager.

Code:

---

set serveroutput on
declare
  TYPE a IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
  b a;

  TYPE c IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
  d c;

  cursor c1 is select ename,empno,mgr from emp;
  cursor c3 is select ename,empno,mgr from emp;

  i number(2):=0;
  no_mgr EXCEPTION;

begin
  dbms_output.put_line('Manager' ||' '|| 'Emp');
  dbms_output.put_line('______________________________________');
  for c2 in c1 loop
    b(i):=c2.ename;
    for c4 in c3 loop
      BEGIN
        if c4.empno = c2.mgr then
          d(i):=c4.ename;
          dbms_output.put_line(d(i) || ' - '|| b(i));
        elsif c2.mgr IS NULL then
          RAISE no_mgr;
        end if;
      EXCEPTION
        WHEN no_mgr THEN
          d(i):= 'NO MANAGER';
          i := i+1;
      END;
    end loop;
end loop;
end;
/

---

Please, tell me that this is purely an academic exercise, and that this is in no way production code :)