want to find starting and end point of non-sequential number in oracle stored procedure

Suppose main table is

ID  Usage_flah
1     null
2     null
3     null
4     Yes
5     Yes
6     Null
7     NUll

Now I want start and end position of ID in second result table where usage_flag is null

Like

Start  End
1      3
6      7

Here is one way (demonstrating why knowing your Oracle version is relevant: this solution uses match_recognize, introduced in Oracle 12.1)

The with clause is only to simulate your input data; you should remove it, and use your actual table and column names in the main query (select * .......)

with
  main_table (id, usage_flag) as (
    select 1, null  from dual union all
    select 2, null  from dual union all
    select 3, null  from dual union all
    select 4, 'Yes' from dual union all
    select 5, 'Yes' from dual union all
    select 6, null  from dual union all
    select 7, null  from dual
  )
select *
from   main_table
match_recognize (
  order by id
  measures first(id) as id_start, last(id) as id_end
  pattern  ( n+ )
  define   n as usage_flag is null
);

  ID_START     ID_END
---------- ----------
         1          3
         6          7