Take the 2-minute tour ×

I would like to create a procedure which returns the MAX Date from the employee table and then allows me to call the max date again in select statements.

Thank you.

NOTE: I will be using the max_date_PARAM in many select statements for reporting.

CREATE OR REPLACE PROCEDURE max_date_proc (max_date_PARAM out DATE) 
    SELECT  max(start_date)  INTO max_date_PARAM
     FROM employee 
   END max_date_proc;
select * from benefits where claim > = max_date_PARAM;
share|improve this question
Database is ORACLE –  Lynn Apr 7 '11 at 18:32
how about select * from benefits where claim >= (select max(start_date) from employee); oh wait, justin beat me to the punch ;) –  tbone Apr 7 '11 at 19:27

3 Answers 3

If you created a function

  l_max_date DATE;
  SELECT MAX(start_date)
    INTO l_max_date
    FROM employee;
  RETURN l_max_date;
END get_max_start_date;

Then you could query

  FROM benefits
 WHERE claim >= get_max_start_date();

Of course, you could do this without a function as well

  FROM benefits
 WHERE claim >= (SELECT MAX(start_date)
                   FROM employee)
share|improve this answer
Both work great! Thank you. I was told originally to create a procedure from someone I work with. Thanks for pointing me in the right direction! –  Lynn Apr 7 '11 at 19:45

You should use a function instead of a procedure. http://psoug.org/reference/functions.html

share|improve this answer
Thank you, if I used function instead of procedure. How do I tell Oracle to hold the value and then pass it to the SQL statement. Thaks for your suggestion. –  Lynn Apr 7 '11 at 19:01

If you're running your statements in SQL*Plus (or SQL Developer I think, not sure about others) you can use a bind variable:

variable l_max_date date;
exec max_date_proc(:l_max_date);
select * from benefits where claim >= :l_max_date;

Same with a function, which is more suitable here; using Justin's:

variable l_max_date date;
exec :l_max_date := get_max_start_date();
select * from benefits where claim >= :l_max_date;

Of course, there isn't much point making this a function or procedure if you're only using the result once, but I'm assuming you want to reuse the same value multiple times and not recalculate it each time.

Yet another option, using SQL*Plus' column handling to define a variable:

column max_date new_value l_max_date noprint;
select max(start_date) max_date from employee;
select * from benefits where claim >= &l_max_date;
share|improve this answer

Your Answer


