need a query 1

[whtisthat]

I need a query please any help
if you do a select * from all_tables I need to find out similar table which ends with _all and _tl
the reason I want to make a view _VL. eg:
oe_headers_types_all
oe_headers_types_tl

Th

 

[nagornyi]

select name1 from
(select substr(table_name,1,length(table_name)-4) name1 from all_tables
where table_name like '%_ALL'),
(select substr(table_name,1,length(table_name)-3) name2 from all_tables
where table_name like '%_TL')
where name1 = name2