SQL Macro - Parameterized Views (since Oracle DB 19.6)

create or replace function dept_job_salaries (
  job varchar2
) return varchar2 sql_macro is
begin
  return '
     select department_id, sum ( salary ) 
     from   employees
     where  job_id = dept_job_salaries.job
     group  by department_id';
end;
/

select * from  dept_job_salaries ( 'FI_ACCOUNT' );

SQL> select * from  dept_job_salaries ( 'FI_ACCOUNT' );

DEPARTMENT_ID SUM(SALARY)
------------- -----------
          100       39600


Execution Plan
----------------------------------------------------------
Plan hash value: 55893400

---------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |            |     4 |    64 |     3  (34)| 00:00:01 |
|   1 |  HASH GROUP BY                       |            |     4 |    64 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES  |     5 |    80 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | EMP_JOB_IX |     5 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


   3 - access("JOB_ID"='FI_ACCOUNT')

Alternative implementation with pipelined table, although context switch between SQL and PL/SQL overhead for such implementation

create or replace type t_depart_role_sal as object (
    department_id       number,
    total_salaries      number
);

create or replace type t_depart_role_sal_tab 
    is table of t_depart_role_sal;

-- Build a pipelined table function.
CREATE OR REPLACE FUNCTION depart_role_sal_ptf(job varchar2)
return t_depart_role_sal_tab pipelined
as
cursor c is 
  select department_id, sum (salary) total_salary 
     from   employees
     where  job_id = job
     group  by department_id;
begin
  for c1 in c
  loop
    PIPE ROW(t_depart_role_sal(c1.department_id, c1.total_salary));
  end loop;
  return;
exception
  when no_data_needed then
    raise;
  when others then
    raise;
end;
/


select * from depart_role_sal_ptf('FI_ACCOUNT');

SQL> select * from depart_role_sal_ptf('FI_ACCOUNT');

DEPARTMENT_ID TOTAL_SALARIES

------------- --------------

          100          39600

Execution Plan

----------------------------------------------------------

Plan hash value: 1610656097

---------------------------------------------------------------------------------------------------------

| Id  | Operation                         | Name                | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                  |                     |  8168 | 16336 |    29   (0)| 00:00:01 |

|   1 |  COLLECTION ITERATOR PICKLER FETCH| DEPART_ROLE_SAL_PTF |  8168 | 16336 |    29   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------------