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

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..

inline notes throw the error..

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

-- no inline notes: PL/SQL procedure successfully completed.

Answer 2

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

Answer 3

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 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

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 6

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;