My procedure is not returning expected results

Question

Here is my code:

create or replace    
procedure date_report (start_date  timestamp , end_date  timestamp  )
is
  cursor cursor_audit is
    select audit_id, audit_action, audit_user, audit_date
    from customer_audit
    where audit_date between start_date and end_date
    ;
  rec cursor_audit%rowtype;
begin
  open cursor_audit;
  fetch cursor_audit into rec;
  while cursor_audit%found
  loop
    dbms_output.put_line('User : '  || rec.audit_user
                    || ', ID :'     || rec.audit_id
                    || ', Action: ' || rec.audit_action
                    || ', Date : '  || rec.audit_date );
    fetch cursor_audit into rec;
  end loop;
  close cursor_audit;
end;

I want to query all rows between specific dates but it seems to not report anything.

Answer 1

Not exactly what you were asking for, but worth mentioning: You can write better, i.e. more idiomatic PL/SQL like this:

create or replace

procedure date_report (start_date  timestamp , end_date  timestamp  )
is
  cursor cursor_audit is select audit_id, audit_action, audit_user, audit_date
                         from customer_audit
                         where audit_date between start_date and end_date
                     ;
begin
  for rec in cursor_audit
  loop
    dbms_output.put_line('User : '||rec.audit_user ||', ID :' ||rec.audit_id||', Action: ' 
                         || rec.audit_action||', Date : ' ||rec.audit_date );
  end loop;
end;

or even

procedure date_report (start_date  timestamp , end_date  timestamp  )
is
begin
  for rec in (select audit_id, audit_action, audit_user, audit_date
                from customer_audit
                where audit_date between start_date and end_date)
  loop
    dbms_output.put_line('User : '||rec.audit_user ||', ID :' ||rec.audit_id||', Action: ' 
                         || rec.audit_action||', Date : ' ||rec.audit_date );
  end loop;
end;

A possible cause for your problem is that you select e.g. 09-16-2010 to 09-16-2010, which means 09-16-2010 00:00:00 to 09-16-2010 00:00:00. To find all rows from 09-16-2010, you have to explicitely pass a time (or just add 1 to the end_date, which is sufficient in most cases)

Answer 2

Look and see if it is checked.

Answer 3

Firstly, it is advisable to prefix variables and parameters so they do not conflict with column names.

In your example, if "customer_audit" contained start_date and end_date columns then these would be used in preference over PL/SQL variables or parameters of the same name.

Secondly, the original title included "the-parameter-always-null". If the parameters are null then the condition will not be true and no rows will be returned.

If you want to cater for this, you need to code in some logic like:

> select audit_id, audit_action, audit_user, audit_date
> from customer_audit
> where (audit_date >= i_start_date or i_start_date is null)
> and   (audit_date <= i_end_date or i_end_date is null)