Re: Functions with date inputs

From: Arun Mathur <themathurs_at_gmail.com>
Date: 15 Aug 2005 07:47:20 -0700
Message-ID: <1124117240.516702.79610@g14g2000cwa.googlegroups.com>

Lisa,

Sybrand and IANAL_VISTA hit the nail on the head. See below:

SQL> set serveroutput on size 100000
SQL> create or replace function oltp_number_sold(p_start_date sales.sale_day%type,
  2 p_end in sales.sale_date%type)
  3 return number as
  4 v_count number;
  5 begin
  6 select count(*) into v_count from sales;   7 return v_count;
  8 end;
  9 /

Function created.

SQL> show errors
No errors.
SQL> begin
  2 dbms_output.put_line('No. sold =
'||oltp_number_sold(07-JUL-2005,27-JUL-2005));
  3 end;
  4 /
dbms_output.put_line('No. sold =
'||oltp_number_sold(07-JUL-2005,27-JUL-2005));

                                                        *

ERROR at line 2:

ORA-06550: line 2, column 57:
PLS-00201: identifier 'JUL' must be declared
ORA-06550: line 2, column 1:

PL/SQL: Statement ignored

SQL> begin
  2 dbms_output.put_line('No. sold =
'||oltp_number_sold(to_date('07-JUL-2005','DD-MON-YYYY'),to_date('27-JUL-2005','DD-MON-YYYY')));
  3 end;
  4 /
No. sold = 0

PL/SQL procedure successfully completed.

SQL> select * from v$version;

BANNER

---

Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production

PL/SQL Release 9.2.0.1.0 - Production

CORE 9.2.0.1.0 Production

TNS for Solaris: Version 9.2.0.1.0 - Production

NLSRTL Version 9.2.0.1.0 - Production         

SQL> spool off; Received on Mon Aug 15 2005 - 09:47:20 CDT