How do I pass a date value to a cursor in plsql?

Question

Basically I would like to pass a date value to a cursor, and print out the entire row/record after for each found. I am having trouble because a) I don't know if my date is being converted properly in the BEGIN section, and b) I am getting "wrong number or types of arguments in call to 'PUT_LINE'" when printing each row.

This is what I have so far:

DEFINE B_HIREDATE = 11-OCT-88

DECLARE
  cursor DATE_CUR (the_date DATE) is
    select * from employees
      where hire_date > to_date(the_date, 'dd-mon-yy')
      order by hire_date;

  r_emp DATE_CUR%ROWTYPE;

BEGIN
  for r_emp IN DATE_CUR('&B_HIREDATE') LOOP
     dbms_output.put_line(r_emp);
  end LOOP;
END;
/

I am getting no output values, even if I change my select statement to a known single field name.

Answer 1

You can't print out the whole row from a single DBMS_OUTPUT call, unfortunately; you'll need to print each column returned by the cursor individually. PUT_LINE expects a VARCHAR2 argument or something that can be implicitly converted. You can concatenate several values into one call. Nice formatting isn't easy though.

The date conversion is almost OK, but you should have the TO_DATE in the cursor call, as the cursor parameter is expecting a DATE; and you should use RR instead of YY in your date mask, or preferably use 4-digit years and mask YYYY.

SET SERVEROUTPUT ON
DEFINE B_HIREDATE = 11-OCT-1988

DECLARE
  cursor DATE_CUR (the_date DATE) is
    select * from employees
      where hire_date > the_date
      order by hire_date;    
BEGIN
  for r_emp IN DATE_CUR(TO_DATE('&B_HIREDATE','DD-MON-YYYY')) LOOP
     dbms_output.put_line(r_emp.hire_date);
  end LOOP;
END;

You don't need to explicitly declare the r_emp as a variable with this cursor syntax (but you would with the OPEN/FETCH/CLOSE version).

If you're running this in SQL*Plus, you need to add SET SERVEROUTPUT ON at the start to allow the DBMS_OUTPUT calls to be displayed. You can do that in SQL Developer too, or there's a separate pane for viewing the output, which you need to enable for the worksheet.