create table test  (
    test_id number( 9 )
  , value  number( 9 )
)
/
insert into test values ( 1 ,  200 );
insert into test values ( 2 ,  600 );
insert into test values ( 3 , null);
insert into test values ( 5 , null);
insert into test values ( 6 ,  800 );
insert into test values ( 7 , null);
insert into test values ( 8 ,  900 );
commit;

select t.test_id
     , t2.value
from (select t.test_id
           , (select max(t2.test_id)
              from test t2
              where t2.test_id <= t.test_id
                and t2.value is not null
             ) lag_nn_id
      from test t
     ) t
   , test t2
where t.lag_nn_id = t2.test_id
/

create or replace package pkg_lag_nn as
  procedure clear_lag_nn;
  function get_lag_nn (p_value number) return number ;
end;
/

create or replace package body pkg_lag_nn as

v_lag_nn test.value%type;

procedure clear_lag_nn as begin v_lag_nn := null; end;

function get_lag_nn (p_value number) return number as
begin
  if p_value is not null then
    v_lag_nn := p_value;
  end if;
  return v_lag_nn;
end;

end;
/

exec pkg_lag_nn.clear_lag_nn;

select test_id
     , value
     , pkg_lag_nn.get_lag_nn(value) lag_nn_value
from test;
/