DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: i have errors for my pl/sql

  1. #1
    Join Date
    Sep 2005
    Posts
    1

    Smile i have errors for my pl/sql

    Hai, this is what i've done...
    i need to display ename and thier manager...the manager is based on the empno...then for the employees who do not have any manager, raise an exception and display an exception message.

    this is the error massage that i've got
    ----------------------------------------------------------
    EXCEPTION
    *
    ERROR at line 37:
    ORA-06550: line 37, column 2:
    PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the
    following:
    begin declare end exit for goto if loop mod null pragma raise
    return select update while
    <<
    close current delete fetch lock insert open rollback
    savepoint set sql execute commit forall

    ORA-06550: line 43, column 1:
    PLS-00103: Encountered the symbol "END" when expecting one of the following:
    begin function package pragma procedure form
    -----------------------------------------------------------------


    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

    if c4.empno = c2.mgr then
    d(i):=c4.ename;
    dbms_output.put_line(d(i) || ' '|| b(i));

    elsif c2.mgr = 'NULL' then
    RAISE no_mgr;
    end if;

    end loop;

    EXCEPTION
    WHEN no_mgr THEN
    d(i):= 'NO MANAGER';
    i := i+1;

    end loop;
    end;
    /

  2. #2
    Join Date
    Dec 2001
    Location
    Slovenia, Kranj
    Posts
    82
    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;

  3. #3
    Join Date
    Dec 2001
    Location
    Slovenia, Kranj
    Posts
    82
    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

  4. #4
    Join Date
    Oct 2005
    Posts
    3
    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;
    /

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Please, tell me that this is purely an academic exercise, and that this is in no way production code
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width