PLS-00103: Encountered the symbol “;” when expecting one of the following:

Question

What is wrong with my PLSQL?

I get this error message:

ORA-06550: line 4, column 0:PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:* & = - + ; < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset

|

<<outer_block>>
DECLARE
mgr_id NUMBER(6) :='&current_manager_id';
dept_count number := 0;
BEGIN
SELECT count(*) INTO dept_count FROM EMP WHERE EMPNO = outer_block.mgr_id;
IF dept_count > 0 THEN
    << inner_block >>
    DECLARE
        dept_name VARCHAR2(30);
        mgr_id NUMBER(6) := &new_manager_id;
    BEGIN
        SELECT EMPNO INTO dept_name FROM EMP WHERE manager_id = outer_block.mgr_id; 
        UPDATE EMP SET MGR = inner_block.mgr_id WHERE MGR = outer_block.mgr_id;
        DBMS_OUTPUT.PUT_LINE('Department manager ID has been changed for ' || dept_name);
    END inner_block;
ELSE
    DBMS_OUTPUT.PUT_LINE('There are no departments listed for the manager');
END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('There are no departments listed for the manager');
END outer_block;
/

How does outer_block and inner_block looks like? What type of window are you trying to run this in? SQL-window? Test Window? — President Camacho, Jun 17 '15 at 06:53

score 0 · Accepted Answer · answered Jun 19 '15 at 08:57

0

No need (and not valid) to add a block label outside of the code. Try something like:

DECLARE
  o_mgr_id NUMBER(6) := 1;
  dept_count number := 0;
BEGIN
  SELECT count(*) 
  INTO dept_count 
  FROM EMP 
  WHERE EMPNO = o_mgr_id;

  IF dept_count > 0 THEN
    <<inner_block>>
    DECLARE
        dept_name VARCHAR2(30);
        i_mgr_id NUMBER(6) := 1;
    BEGIN
        SELECT EMPNO INTO dept_name FROM EMP WHERE mgr = o_mgr_id; 
        UPDATE EMP SET MGR = i_mgr_id WHERE MGR = o_mgr_id;
        DBMS_OUTPUT.PUT_LINE('Department manager ID has been changed for ' || dept_name);
    END inner_block;
  ELSE
    DBMS_OUTPUT.PUT_LINE('There are no departments listed for the manager');
  END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('There are no departments listed for the manager');
END outer_block;
/

answered Jun 19 '15 at 08:57

kayakpim

985
2
8
28

Yes, I can running without error but now I can't come into "inner block" why does it happen? – Abdurrahman Jun 19 '15 at 14:55
What does can't come into the inner block mean? You can nest as many blocks as you like and you don't need to give them labels (that's just for ease of reading) unless you want to use goto etc. – kayakpim Jun 22 '15 at 08:59
The reason you are not going into the inner block is because there are no departments . If you do the select into in the inner block where no rows returned you'll get an error which will go out to the outer exception block – kayakpim Jun 22 '15 at 09:02

PLS-00103: Encountered the symbol “;” when expecting one of the following:

1 Answers1