Oracle 18c

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

create table t_emp(
  id     int,
  emp_no int,
  extra  varchar2(30)
);
create table t_task(
  task_id   int, 
  task_name varchar2(30),
  emp_no    int
);
insert into t_emp
  select level-1,level-1,'extra #'||(level-1)
  from dual
  connect by level<=10;
10 rows affected

insert into t_task
  select level, 'task #'||level, mod(level,10) as emp_no
  from dual
  connect by level<=30;
30 rows affected

create or replace type t_emp_type is object(
  id     int,
  emp_no int,
  extra  varchar2(30)
);
create or replace
  function f_get_emp_details(i_emp_no in t_emp.emp_no%type) return t_emp_type is 
       l_emp_row t_emp%rowtype;
    Begin
      select *
      into l_emp_row
      from t_emp
      where t_emp.emp_no = i_emp_no;
    
    return t_emp_type(l_emp_row.id, l_emp_row.emp_no, l_emp_row.extra);
    end;
/
select task_id, 
       task_name,
       (emp_details).id,
       (emp_details).emp_no,
       (emp_details).extra
from (
  select task_id, 
         task_name,
         f_get_emp_details(t_task.emp_no) emp_details
  from t_task
);


TASK_ID
TASK_NAME
(EMP_DETAILS).ID
(EMP_DETAILS).EMP_NO
(EMP_DETAILS).EXTRA


1
task #1
1
1
extra #1

2
task #2
2
2
extra #2

3
task #3
3
3
extra #3

4
task #4
4
4
extra #4

5
task #5
5
5
extra #5

6
task #6
6
6
extra #6

7
task #7
7
7
extra #7

8
task #8
8
8
extra #8

9
task #9
9
9
extra #9

10
task #10
0
0
extra #0

11
task #11
1
1
extra #1

12
task #12
2
2
extra #2

13
task #13
3
3
extra #3

14
task #14
4
4
extra #4

15
task #15
5
5
extra #5

16
task #16
6
6
extra #6

17
task #17
7
7
extra #7

18
task #18
8
8
extra #8

19
task #19
9
9
extra #9

20
task #20
0
0
extra #0

21
task #21
1
1
extra #1

22
task #22
2
2
extra #2

23
task #23
3
3
extra #3

24
task #24
4
4
extra #4

25
task #25
5
5
extra #5

26
task #26
6
6
extra #6

27
task #27
7
7
extra #7

28
task #28
8
8
extra #8

29
task #29
9
9
extra #9

30
task #30
0
0
extra #0

TASK_ID	TASK_NAME	(EMP_DETAILS).ID	(EMP_DETAILS).EMP_NO	(EMP_DETAILS).EXTRA
1	task #1	1	1	extra #1
2	task #2	2	2	extra #2
3	task #3	3	3	extra #3
4	task #4	4	4	extra #4
5	task #5	5	5	extra #5
6	task #6	6	6	extra #6
7	task #7	7	7	extra #7
8	task #8	8	8	extra #8
9	task #9	9	9	extra #9
10	task #10	0	0	extra #0
11	task #11	1	1	extra #1
12	task #12	2	2	extra #2
13	task #13	3	3	extra #3
14	task #14	4	4	extra #4
15	task #15	5	5	extra #5
16	task #16	6	6	extra #6
17	task #17	7	7	extra #7
18	task #18	8	8	extra #8
19	task #19	9	9	extra #9
20	task #20	0	0	extra #0
21	task #21	1	1	extra #1
22	task #22	2	2	extra #2
23	task #23	3	3	extra #3
24	task #24	4	4	extra #4
25	task #25	5	5	extra #5
26	task #26	6	6	extra #6
27	task #27	7	7	extra #7
28	task #28	8	8	extra #8
29	task #29	9	9	extra #9
30	task #30	0	0	extra #0