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 |