Recursive query in Oracle until a parent in the hierarchy meets a condition?

I have a table like the following:

ID  PARENT_ID  VALUE_ID
1   NULL       100
2   1          NULL
3   2          200
4   3          NULL 
5   1          300
6   2          NULL
7   6          400
8   7          500

And I’d like to be able to fetch each ID with its corresponding VALUE_ID. I want to do it in such a way that if a row has a VALUE_ID as NULL, it “inherits” the VALUE_ID of the first parent above it in the hierarchy, that has a VALUE_ID as NOT NULL. So that would be the query result:

ID  VALUE_ID
1   100
2   100      // -> inherits the value from PARENT_ID = 1;  
3   200
4   200      // -> inherits the value from PARENT_ID = 3;
5   300
6   100      // -> inherits the value from ID = 1, because the PARENT_ID = 2 also has VALUE_ID as NULL, so it goes deeper in the hierarchy;
7   400    
8   500

Could such a thing be accomplished with only one recursive or hierarchical query? Or be accomplished in general without a procedure, perhaps? With CTE, or CONNECT BY clause?

Answer

You can use a recursive CTE for this:

with cte(id, value_id, parent_value_id) as (
      select id, value_id, value_id as parent_value_id
      from t
      where value_id is not null
      union all
      select t.id, t.value_id, cte.parent_value_id
      from cte join
           t
           on t.parent_id = cte.id
      where t.value_id is null
     )
select *
from cte
order by id;

Here is a db<>fiddle.