drop table t;
create table t (
   c1    number(3),
   c2    varchar2(3),
   c3    number(3)   
);
 
 
insert into t values ( 1,  'A', 12 );
insert into t values ( 4,  'B', 11 );
insert into t values ( 7,  'C', 12 );
insert into t values ( 10, 'B', 19 );
insert into t values ( 10, 'B', 17 );
insert into t values ( 10, 'C', 18 );
insert into t values ( 13, 'D', 15 );
insert into t values ( 13, 'D', 14 );
insert into t values ( 16, 'B', 18 );
insert into t values ( 19, 'A', 21 );
 
 
-- for example this order by clause defines the prior and next row
select * from t
   order by c1, c2, c3 desc;
   
        C1 C2          C3
---------- --- ----------
         1 A           12
         4 B           11
         7 C           12
        10 B           19
        10 B           17
        10 C           18
        13 D           15
        13 D           14
        16 B           18
        19 A           21
        
set verify off
undefine where_clause
 
select c1, c2, c3 from (
   select * from (
      select rownum rn, c1, c2, c3 from
         (
            select c1, c2, c3 from t
               order by c1, c2, c3 desc
         )
   )
   where rn-1 in
      (
         select rn from
            (
            select rownum rn, c1, c2, c3 from
               (
                  select c1, c2, c3 from t
                     order by c1, c2, c3 desc
               )
            )
            where &&where_clause
      )
   union
   select * from (
      select rownum rn, c1, c2, c3 from
         (
            select c1, c2, c3 from t
               order by c1, c2, c3 desc
         )
   )
   where rn in
      (
         select rn from
            (
            select rownum rn, c1, c2, c3 from
               (
                  select c1, c2, c3 from t
                     order by c1, c2, c3 desc
               )
            )
            where &&where_clause
      )
   union
   select * from (
      select rownum rn, c1, c2, c3 from
         (
            select c1, c2, c3 from t
               order by c1, c2, c3 desc
         )
   )
   where rn+1 in
      (
         select rn from
            (
            select rownum rn, c1, c2, c3 from
               (
                  select c1, c2, c3 from t
                     order by c1, c2, c3 desc
               )
            )
            where &&where_clause
      )
)
order by c1, c2, c3 desc;
 
 
 
 
Enter value for where_clause: c1 in (4,13)
 
        C1 C2          C3
---------- --- ----------
         1 A           12
         4 B           11
         7 C           12
        10 C           18
        13 D           15
        13 D           14
        16 B           18
 
7 rows selected.
 
 
 
Enter value for where_clause: mod(c1,4)=0
 
        C1 C2          C3
---------- --- ----------
         1 A           12
         4 B           11
         7 C           12
        13 D           14
        16 B           18
        19 A           21
 
6 rows selected.