Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE ITEM(ID int, ITEM_NUMBER varchar2(10));
begin
INSERT INTO ITEM VALUES (1, 'ITEM-A');
INSERT INTO ITEM VALUES (2, 'ITEM-A1');
INSERT INTO ITEM VALUES (3, 'ITEM-A2');
INSERT INTO ITEM VALUES (4, 'ITEM-A3');
INSERT INTO ITEM VALUES (5, 'ITEM-A3-1');
INSERT INTO ITEM VALUES (6, 'ITEM-A3-2');
end;
/
1 rows affected

CREATE TABLE BOM(ID int, PARENT_ITEM int, COMPONENT int);
begin
INSERT INTO BOM VALUES (1, 1, 2);
INSERT INTO BOM VALUES (2, 1, 3);
INSERT INTO BOM VALUES (3, 1, 4);
INSERT INTO BOM VALUES (4, 4, 5);
INSERT INTO BOM VALUES (5, 4, 6);
end;
/
1 rows affected

    select ii.item_number, ic.item_number 
      from bom 
      join item ii on ii.id = bom.parent_item
      join item ic on ic.id = bom.component
      connect by prior component = parent_item
      start with parent_item = (select id from item where item_number = 'ITEM-A')


ITEM_NUMBER
ITEM_NUMBER


ITEM-A
ITEM-A1

ITEM-A
ITEM-A2

ITEM-A
ITEM-A3

ITEM-A3
ITEM-A3-1

ITEM-A3
ITEM-A3-2

ITEM_NUMBER	ITEM_NUMBER
ITEM-A	ITEM-A1
ITEM-A	ITEM-A2
ITEM-A	ITEM-A3
ITEM-A3	ITEM-A3-1
ITEM-A3	ITEM-A3-2