| Error ORA-06512 [message #128976] |
Thu, 21 July 2005 07:37  |
cutiepie
Messages: 30
Registered: July 2005
|
Member |
|
|
I am writtin the following code....but its givin me this ORA error...n when in the test window i write the date parameters..it tells me...NOT A VALID DATE as well...
create or replace procedure Get_Pays(
p_personal_account number,
p_date_of_pay_from date,
p_date_of_pay_to date,
p_cur_payments out sys_refcursor
) is
begin
OPEN p_cur_payments FOR SELECT p.*
FROM payment_document p join financial_transaction f
on p.payment_document_id = f.payment_document_id
WHERE p.date_of_payment between to_date(p_date_of_pay_from, 'mm/dd/yyyy')
and to_date(p_date_of_pay_to, 'mm/dd/yyyy')
and f.personal_account= p_personal_account
and f.operation_type =1;
end Get_Pays;
|
|
|
|
| Re: Error ORA-06512 [message #128984 is a reply to message #128976] |
Thu, 21 July 2005 07:56  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
You cannot issue a to_date on a date field.
Your parameters already are of datatype date, so remove the to_date's from the query.
The column p.date_of_payment is of type date isn't it?
hth
|
|
|
|
|
|
| Re: Error ORA-06512 [message #202413 is a reply to message #128984] |
Thu, 09 November 2006 11:59 |
nina.camille
Messages: 4
Registered: June 2006
Location: Trinidad
|
Junior Member |
|
|
Hi Frank,
I don't have the same problem but the one I have involves the to_date function. I have a report that is selecting data from a table based on a date.
Example:
where received_date between :start_date and :end_date
Both bind variables are DATE data type. When the SQL statement uses the above code, it excludes data where the received_date = :start_date or :end_date, but when I use the following:
where to_date(received_date) between to_date(:start_date) and to_date(:end_date)
I get the correct data. to_date() function is slowing down report execution time though. Any thoughts or suggestions? From anyone? Thanks
|
|
|
|
| Re: Error ORA-06512 [message #202424 is a reply to message #202413] |
Thu, 09 November 2006 13:10 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
start_date and end_date are parameters. They don't have the time element associated with them, therefore a between is going to treat them as midnight on that day. You will not get any records for your end_date, as they will most likely have a time element associated with them.
Additionally, is your column a DATE column or a VARCHAR2?
I think though what you are looking for is TRUNC on your table column, which as you noted, will not use the index, unless you created a function based index.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
