create or replace procedure get_dept( sname in varchar2, sdept out varchar2)
as
begin
    execute immediate 'select d.dname  from emp e , dept d 
        where e.deptno = d.deptno and e.ename = :1'
        into sdept    
        using sname;
end;
/

declare
  realname varchar2(10);
  realdept varchar2(10);
begin
  realname := 'SMITH';
  get_dept(realname, realdept);
  dbms_output.put_line(realdept);
end;
/

------解决方案--------------------
1)create procedure ... as 后面少了begin
2)select ...语句最后少了分号
3)调用过程前面去掉exec, 那是sqlplus里面调用的方法。

SQL code
create or replace procedure get_dept( sname in varchar2, sdept out varchar2)
as
begin
  select d.dname into sdept from emp e, dept d where e.deptno = d.deptno and e.ename = sname;
end;


declare
  realname varchar2(10);
  realdept varchar2(10);
begin
  realname := 'SMITH';
  get_dept(realname, realdept);
  dbms_output.put_line(realdept);
end;