PL/SQL Variable Syntax Confusion

Question

I am having trouble figuring out the syntax of passing variables in PL/SQL. I have 2 variables, startDate and endDate, that I want to populate from a table then pass as parameters to a function. I can declare and populate the variables but I cannot seem to figure out how to pass the suckers.

This is all happening inside a SQL query window in PL/SQL developer. The code will eventually get stored as an ad-hoc query for others to load into an IDE and run on demand.

I have tried the following two methods both of which fail with different exceptions.

First Try

DECLARE startDate DATE; endDate DATE;
 
 BEGIN
  SELECT MIN(e.start_date) INTO startDate FROM employees e WHERE e.state = 'NY';
  SELECT MAX(e.end_date) INTO endDate FROM employees e WHERE e.state = 'NY';
  
  SELECT * FROM my_package.my_function(i_start_date => :startDate, i_end_date => :endDate);
 END;

Exception: PLS-00428: an INTO clause is expected in this SELECT statement

Second Try

 DECLARE startDate DATE; endDate DATE;
 
 BEGIN
  SELECT MIN(e.start_date) INTO startDate FROM employees e WHERE e.state = 'NY';
  SELECT MAX(e.end_date) INTO endDate FROM employees e WHERE e.state = 'NY';
 END;
 SELECT * FROM my_package.my_function(i_start_date => :startDate, i_end_date => :endDate);

Exception: PLS-00103: Encountered the symbol "SELECT"

Answer 1

You do not need to use PL/SQL and can do it all in SQL:

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;

If you did want to use PL/SQL then you can use a cursor FOR loop:

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;
/

Or just assign the collection returned by the function to a variable (and skip the SQL query). For example, if the function returns a SYS.ODCINUMBERLIST type then:

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;
/

fiddle

---

Or, if you are using SQL*Plus or SQL Developer then you can use bind variables in a script:

VARIABLE startDate VARCHAR2(20)
VARIABLE endDate VARCHAR2(20)

BEGIN
  SELECT TO_CHAR(MIN(e.start_date), 'YYYY-MM-DD HH24:MI:SS'),
         TO_CHAR(MAX(e.end_date), 'YYYY-MM-DD HH24:MI:SS')
  INTO   :startDate,
         :endDate
  FROM   employees e
  WHERE  e.state = 'NY';
END;
/

SELECT *
FROM   my_package.my_function(
         i_start_date => TO_DATE(:startDate, 'YYYY-MM-DD HH24:MI:SS'),
         i_end_date   => TO_DATE(:endDate, 'YYYY-MM-DD HH24:MI:SS')
       );