select h.employee_id, job_id, department_id
from hr.job_history h
where date'2007-12-31' between start_date and end_date
| EMPLOYEE_ID | JOB_ID | DEPARTMENT_ID | 114 | ST_CLERK | 50 | 120 | ST_MAN | 50 | 122 | ST_CLERK | 50 | 176 | SA_MAN | 80 |
|---|
create or replace function jobHistView (onDate date) return varchar2 SQL_MACRO is
begin
return q'[
select h.employee_id, job_id, department_id
from hr.job_history h
where onDate between start_date and end_date]';
end;
Function created.
select *
from jobHistView (date'2007-12-31')
| EMPLOYEE_ID | JOB_ID | DEPARTMENT_ID | 114 | ST_CLERK | 50 | 120 | ST_MAN | 50 | 122 | ST_CLERK | 50 | 176 | SA_MAN | 80 |
|---|