Home » SQL & PL/SQL » SQL & PL/SQL » Error ORA-06512
Error ORA-06512 [message #128976] Thu, 21 July 2005 07:37 Go to next message
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...Sad

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 Go to previous messageGo to next message
Frank
Messages: 7880
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 #128994 is a reply to message #128984] Thu, 21 July 2005 08:14 Go to previous messageGo to next message
cutiepie
Messages: 30
Registered: July 2005
Member
Thank you sooo much...it really worked...i was using sql in pl/sql...thanks...Smile
Re: Error ORA-06512 [message #202413 is a reply to message #128984] Thu, 09 November 2006 11:59 Go to previous messageGo to next message
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 Go to previous message
joy_division
Messages: 4643
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.
Previous Topic: Converting SQL file to csv file
Next Topic: problem in creating user-defined aggregate function.
Goto Forum:
  


Current Time: Thu Dec 08 18:11:05 CST 2016

Total time taken to generate the page: 0.10329 seconds