PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ;

Question

I am running the following script -

BEGIN
    select department_name 
    from egpl_department 
    where department_id in (select department_id 
                            from egpl_casemgmt_activity);
END ;

And got the Error -

PLS-00103: Encountered the symbol "end-of-file" when 
expecting one of the following: 
;

Answer 1

In a PL/SQL block select statement should have an into clause:

DECLARE
 v_department egpl_department.department_name%type;
BEGIN 
  select department_name 
  into   v_department
  from   egpl_department 
  where  department_id in (select department_id from egpl_casemgmt_activity); 

  -- Do something useful with v_department
END; 

Answer 2

PLS-00103 always means the compiler has hurled because we have made a syntax error. It would be really neat if the message text said: You have made a syntax error, please check your code but alas it doesn't.

Anyway, in this case the error is that in PL/SQL select statements must populate a variable. This is different from the behaviour of say T-SQL. So you need to define a variable which matches the projection of your query and select INTO that variable.

Oracle's documentation is comprehensive and online. You can find the section on integrating SQL queries into PL/SQL here. I urge you to read it, to forestall your next question. Because once you have fixed the simple syntax bloomer you're going to hit TOO_MANY_ROWS (assuming you have more than one department).

Answer 3

In PL/SQL you cannot just select some data. Where is the result supposed to go?

Your options are:

• Remove BEGIN and END and run the SELECT with SQL*plus or some other tool that can run a SQL statement and present the result somewhere.

• Use SELECT department_name INTO dep_name to put the result into a PL/SQL variable (only works if your SELECT returns a single row)

• Use SELECT department_name BULK COLLECT INTO dep_name_table to put the result into a PL/SQL table (works for several rows)

Or maybe you can describe what you're trying to achieve and in what environment you want to run the SQL or PL/SQL code.

Answer 4

To avoid the too_many_rows problem, you could use a cursor, something like this (I haven't tested this, but along these lines )

DECLARE 

 v_department egpl_department.department_name%type;

 cursor c_dept IS
  select department_name 
  into   v_department
  from   egpl_department 
  where  department_id in (select department_id from egpl_casemgmt_activity)
  order by department_name; 

BEGIN 

  OPEN c_dept;
  FETCH c_dept INTO v_department;
  CLOSE c_dept;

  -- do something with v_department

END;

This will put the first value it finds in the table into v_department. Use the ORDER BY clause to make sure the row returned would be the one you required, assuming there was the possibility of 2 different values.

Answer 5

Most people would not consider the call to be the issue,

but here's an amusing bug in Oracle Sql Developer that may emulate the issue..

exec dbowner.sp1 ( p1, p2, p3); -- notes about the fields

Error report - ORA-06550: line 1, column 362: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

exec dbowner.sp1 ( p1, p2, p3); 
-- notes about the fields

PL/SQL procedure successfully completed.

Answer 6

DECLARE is only used in anonymous PL/SQL blocks and nested PL/SQL blocks.

You do not need to use the DECLARE key word before you 'introduce' a new variable in a Procedure block, unless .... the procedure is a nested PL/SQL block.

This is an example of how you would declare a variable without the 'DECLARE' Key word below.

eg.;

CREATE OR REPLACE PROCEDURE EXAMPLE( A IN NUMBER, B OUT VARCHAR2 )
IS  
  num1 number;
BEGIN  
  num1:=1;
  insert into a (year) values(7);    
END; 

This question/answer explains it better

create procedure in oracle