Oracle Connect By Root Troubleshooting

You must execute the code again to save those values in the database.

-- Create tables
CREATE TABLE t1 (
  TOOL VARCHAR2(50),
  PART VARCHAR2(50),
  PART_ID NUMBER
);

CREATE TABLE item_mstr (
  ITEM_ID NUMBER,
  ITEM VARCHAR2(50)
);

CREATE TABLE item_str (
  PARENT_ID NUMBER,
  CHILD_ID NUMBER
);

INSERT INTO t1(TOOL, PART, PART_ID) VALUES ('tool1', 'assy1', 201);
INSERT INTO t1(TOOL, PART, PART_ID) VALUES ('tool2', 'assy2', 202);
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (101, 'part1');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (102, 'part2');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (201, 'assy1');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (202, 'assy2');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (301, 'parent1');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (302, 'parent2');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (401, 'gparent1');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (402, 'gparent2');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (501, 'top_parent1');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (502, 'top_parent2');
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (201, 101);
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (202, 102);
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (301, 201);
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (302, 202);
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (401, 301);
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (402, 302);
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (501, 401);
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (502, 402);

-- Query
SELECT DISTINCT
t1.TOOL,
t1.PART,
CONNECT_BY_ROOT itm.ITEM TOP_PARENT
FROM item_str str
JOIN t1 on t1.PART_ID = str.CHILD_ID
JOIN item_mstr itm on itm.ITEM_ID = str.PARENT_ID
CONNECT BY PRIOR str.PARENT_ID = str.CHILD_ID
START WITH str.CHILD_ID = t1.PART_ID;

TOOL	PART	TOP_PARENT
tool1	assy1	parent1
tool2	assy2	parent2