Re: Please Help: ORA-01847 for valid date

wdewebserver wrote:
> Hi
>
> I have written the following stored procedure hoping to extract data
from a
> table. When I call the stored proc using a date like 10-11-2004, I
get an
> error ORA-01847: day of month must be between 1 and last day of
month.
>
> Procedure "dynamic"
> (
> vTABLE_NAME IN CHAR,
> vCREATED_DATE IN CHAR,
> vREF_CURSOR OUT GLOA.RCT
> )
> AS
> V_STRING VARCHAR2(1000);
> BEGIN
> V_STRING := 'SELECT XRED, YFER FROM '||vTABLE_NAME||' '||
> 'WHERE CREATED_DATE =

TO_DATE('||vCREATED_DATE||',''DD-MM-YYYY'')'
>
> OPEN vREF_CURSOR FOR V_STRING;
> END dynamic;

Probably because you need quotes around the created_date in the query V_STRING := 'SELECT XREF, YFER FROM '||vTABLE_NAME||' '|| 'WHERE CREATED_DATE = TO_DATE('''||vCREATED_DATE||''',''DD-MM-YYYY'')'

Also are you sure created_date in the table is a truncated date? If it's not you will only get those whose time portion is midnight. You may want to say "WHERE trunc(CREATED_DATE) = ".

Good luck with it
Ken Denny Received on Wed Feb 23 2005 - 14:08:35 CST