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

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; 

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

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.