Hi,
There are several examples of hierarchal queries using the employees-manager example. Recently i came across hierarchal scenario where the table was storing multiple hierarchies. example data:

CREATE TABLE example (
    Title VARCHAR2(50),
    ID NUMBER,
    Link_id NUMBER
);

INSERT INTO your_table_name (Title, ID, Link_id) VALUES ('A', 1, NULL);
INSERT INTO example (Title, ID, Link_id) VALUES ('B', 2, 1);
INSERT INTO example(Title, ID, Link_id) VALUES ('C', 3, 2);
INSERT INTO example (Title, ID, Link_id) VALUES ('D', 4, 3);
INSERT INTO example (Title, ID, Link_id) VALUES ('E', 5, NULL);
INSERT INTO example (Title, ID, Link_id) VALUES ('F', 6, 5);
INSERT INTO example (Title, ID, Link_id) VALUES ('G', 7, 6);
INSERT INTO example (Title, ID, Link_id) VALUES ('H', 8, NULL);
INSERT INTO example (Title, ID, Link_id) VALUES ('I', 9, NULL);
INSERT INTO example (Title, ID, Link_id) VALUES ('J', 10, 9);

Title |ID |Link_id

A | 1 | null
B | 2 | 1
C | 3 | 2
D | 4 | 3
E | 5 | null
F | 6 | 5
G | 7 | 6
H | 8 | null
I | 9 |null
J | 10 | 9

and i wanted retrieve the whole hierarchy given any node i.e. passing ID 3 should return:
A
B
C
D

i wrote the following function to get the root id:

create or replace FUNCTION find_root(
    p_id IN example.id%TYPE
) RETURN example.id%TYPE
AS
    v_given_id example.id%TYPE := p_id;
    v_root_id example.id%TYPE;
BEGIN
    LOOP
        SELECT Link_id INTO v_root_id
        FROM example
        WHERE id = v_given_id;

        IF v_root_id IS NULL THEN
            EXIT; -- Exit the loop if Link_id is null
        ELSE
            v_given_id := v_root_id; -- Update v_given_id with Link_id
        END IF;
    END LOOP;

    RETURN v_given_id;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL; -- Return NULL if no record found for the given id
    WHEN OTHERS THEN
        RETURN NULL; -- Handle other errors by returning NULL
END;

The following stored procedure will then call the above function and return the hierarchy based on the id returned by the function:

create or replace PROCEDURE get_hierarchy(
    p_given_id IN example.id%TYPE,
    hmm OUT SYS_REFCURSOR
)
IS
    v_root_id example.id%TYPE;
BEGIN
    
    SELECT find_root_id(p_given_id) INTO v_root_id FROM dual;

    -- Check if final case ID is not null
    IF v_final_case_id IS NOT NULL THEN
        -- Use explicit cursor declaration
        OPEN hmm FOR
            SELECT 
               Title, ID, Link_id from example
            START WITH  
                example.id = v_root_id
            CONNECT BY 
                PRIOR example.id = example.link_id;
    ELSE
        -- Use RAISE_APPLICATION_ERROR for customized error messages
        RAISE_APPLICATION_ERROR(-20001, 'No record found for the given id');
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- Handle no data found exception separately
        RAISE_APPLICATION_ERROR(-20002, 'No data found for the given id');
    WHEN OTHERS THEN
        -- Handle other exceptions
        RAISE_APPLICATION_ERROR(-20003, 'An error occurred: ' || SQLERRM);
END;

I know there will be a shortcut.

SQL> variable id number
SQL> exec :id := 3

PL/SQL procedure successfully completed.

SQL> select t.id, t.title, t.link_id, level lev
  2  from t
  3  start with id = :id
  4  connect by prior link_id = id;

        ID TITLE                                                 LINK_ID        LEV
---------- -------------------------------------------------- ---------- ----------
         3 C                                                           2          1
         2 B                                                           1          2
         1 A                                                                      3

SQL> select t.id, t.title, t.link_id, level lev
  2  from t
  3  start with id = :id
  4  connect by prior link_id = id
  5  order by lev desc fetch first row only;

        ID TITLE                                                 LINK_ID        LEV
---------- -------------------------------------------------- ---------- ----------
         1 A                                                                      3

SQL> with top_of_hier as
  2  (
  3  select t.id, t.title, t.link_id, level lev
  4  from t
  5  start with id = :id
  6  connect by prior link_id = id
  7  order by lev desc fetch first row only
  8  )
  9  select t.*
 10  from t, top_of_hier
 11  start with t.id = top_of_hier.id
 12  connect by prior t.id = t.link_id;

TITLE                                                      ID    LINK_ID
-------------------------------------------------- ---------- ----------
A                                                           1
B                                                           2          1
C                                                           3          2
D                                                           4          3