Listing 11-9. Revised Code for the FindPreviousNext Process
declare
  v_subquery varchar2(200);
  v_query    varchar2(250);
  v_sort     varchar2(20);
begin
  if :P40_SORTFIELD is null then
    v_sort := 'EName';
  else
    v_sort := :P40_SORTFIELD;
  end if;

  v_subquery :=  
    'select EmpNo, ' ||
           'lag(EmpNo)  over (order by ' || v_sort || ') as PrevEmp, ' ||
           'lead(EmpNo) over (order by ' || v_sort || ') as NextEmp ' ||
    'from EMP';
   

  v_query := 'select PrevEmp, NextEmp ' ||
             'from (' || v_subquery || ') ' ||
             'where EmpNo = :1';

  execute immediate v_query 
  into :P40_PREV, :P40_NEXT 
  using :P40_EMPNO;
end;

