Oracle 18c

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';
CREATE TABLE employees (start_date, end_date, state) AS
SELECT DATE '2023-01-01', SYSDATE, 'NY' FROM DUAL;
1 rows affected

CREATE PACKAGE my_package IS
  FUNCTION my_function(
    i_start_date DATE,
    i_end_date   DATE
  ) RETURN SYS.ODCINUMBERLIST;
END;
/
CREATE PACKAGE BODY my_package IS
  FUNCTION my_function(
    i_start_date DATE,
    i_end_date   DATE
  ) RETURN SYS.ODCINUMBERLIST
  IS
  BEGIN
    RETURN SYS.ODCINUMBERLIST(42);
  END;
END;
/
SELECT mf.*
FROM   ( SELECT MIN(e.start_date) AS min_start_date,
                MAX(e.end_date) AS max_end_date
         FROM   employees e
         WHERE e.state = 'NY'
       ) e
       CROSS APPLY my_package.my_function(
         i_start_date => e.min_start_date,
         i_end_date   => e.max_end_date
       ) mf;


COLUMN_VALUE


42


DECLARE
  startDate DATE;
  endDate DATE;
BEGIN
  SELECT MIN(e.start_date),
         MAX(e.end_date)
  INTO   startDate,
         endDate
  FROM   employees e
  WHERE  e.state = 'NY';

  FOR r IN (SELECT *
            FROM   my_package.my_function(
                     i_start_date => startDate,
                     i_end_date   => endDate
                   ))
  LOOP
    DBMS_OUTPUT.PUT_LINE(r.column_value);
  END LOOP;
END;
/
1 rows affected

dbms_output:
42

DECLARE
  startDate DATE;
  endDate   DATE;
  v_values  SYS.ODCINUMBERLIST;
BEGIN
  SELECT MIN(e.start_date),
         MAX(e.end_date)
  INTO   startDate,
         endDate
  FROM   employees e
  WHERE  e.state = 'NY';

  v_values := my_package.my_function(
    i_start_date => startDate,
    i_end_date   => endDate
  );

  FOR i IN 1 .. v_values.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_values(i));
  END LOOP;
END;
/
1 rows affected

dbms_output:
42