select id, name
   from catalog 
start with
connect by prior

union all

select id, name
   from product where catalog_id in catalog

--  create table
create table catalog (id number, id_parent number ,name varchar2(100));
create table product (id number, id_catalog number ,name varchar2(100));
-- insert into  catalog
insert into  catalog values (1,null,'Каталог 1');
insert into  catalog values (11,1,'Каталог1-1');
insert into  catalog values (12,1,'Каталог1-2');
insert into  catalog values (13,1,'Каталог1-3');
insert into  catalog values (121,12,'Каталог1-2-1');
insert into  catalog values (2,null,'Каталог2');
insert into  catalog values (21,2,'Каталог2-1');
insert into  catalog values (22,2,'Каталог2-2');
-- insert into  product
insert into  product values (1,12,'Продукт1');
insert into  product values (2,121,'Продукт2');
insert into  product values (22,121,'Продукт3');
insert into  product values (23,121,'Продукт4');
insert into  product values (3,13,'Продукт5');
insert into  product values (4,22,'Продукт6');
insert into  product values (6,null,'Продукт7');
insert into  product values (7,212121,'Продукт8');
--
select lpad(' ',level*4)||t.name name
  from (select c.id, c.id_parent, c.name
          from catalog c
        union all
        select null as id, p.id_catalog as id_parent, p.name
          from product p
         where p.id_catalog in (select id from catalog)) t
 start with t.id_parent is null
connect by prior t.id = t.id_parent
order siblings by t.id_parent

----------------
-- Result
----------------
1     Каталог 1
2         Каталог1-1
3         Каталог1-2
4             Каталог1-2-1
5                 Продукт4
6                 Продукт2
7                 Продукт3
8             Продукт1
9         Каталог1-3
10             Продукт5
11     Каталог2
12         Каталог2-1
13         Каталог2-2
14             Продукт6