Oracle 21c

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

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF TZR';
CREATE TYPE emp_rec IS OBJECT (
  F_NAME VARCHAR(50), 
  M_NAME VARCHAR(1), 
  L_NAME VARCHAR(50), 
  DOB DATE, 
  DEPT_CD INTEGER, 
  HOURLY VARCHAR(1)
);
CREATE TYPE emp_trec IS TABLE OF emp_rec;
CREATE TABLE emp_master (
  F_NAME  VARCHAR2(50),
  M_NAME  VARCHAR2(1),
  L_NAME  VARCHAR(50),
  DOB     DATE,
  DEPT_CD INTEGER,
  HOURLY  VARCHAR(1)
);
INSERT INTO emp_master(f_name, m_name, l_name, dob, dept_cd, hourly)
SELECT 'Alice', 'A', 'Abbot', DATE '1970-01-01', 1, 'Y' FROM DUAL UNION ALL
SELECT 'Betty', 'B', 'Baron', DATE '1980-01-01', 2, 'Y' FROM DUAL UNION ALL
SELECT 'Carol', 'C', 'Count', DATE '1990-01-01', 3, 'Y' FROM DUAL UNION ALL
SELECT 'Debra', 'D', 'Duke',  DATE '2000-01-01', 4, 'Y' FROM DUAL UNION ALL
SELECT 'Emily', 'E', 'Earl',  DATE '2010-01-01', 5, 'Y' FROM DUAL;
5 rows affected

SELECT *
FROM   emp_master
WHERE  (f_name, m_name, l_name, dob, dept_cd, hourly) in (
         SELECT *
         FROM   TABLE(
                  emp_trec(
                    emp_rec('Alice', 'A', 'Abbot', DATE '1970-01-01', 1, 'Y'),
                    emp_rec('Carol', 'C', 'Count', DATE '1990-01-01', 3, 'Y'),
                    emp_rec('Emily', 'E', 'Earl',  DATE '2010-01-01', 5, 'Y')
                  )
                )
       );


F_NAME
M_NAME
L_NAME
DOB
DEPT_CD
HOURLY


Alice
A
Abbot
1970-01-01 00:00:00
1
Y

Carol
C
Count
1990-01-01 00:00:00
3
Y

Emily
E
Earl
2010-01-01 00:00:00
5
Y

F_NAME	M_NAME	L_NAME	DOB	DEPT_CD	HOURLY
Alice	A	Abbot	1970-01-01 00:00:00	1	Y
Carol	C	Count	1990-01-01 00:00:00	3	Y
Emily	E	Earl	2010-01-01 00:00:00	5	Y