jknj72
asked on
Procedure syntax
I have a package with a procedure that I am trying to call from my ASP.NET application. I think my problem is with the Dates im passing.
Here is my SQL Developer code.
My dates im passing in are in mm/dd/yyyy format and I can see in my Oracle table that the format is 01-JAN-17 format. How can I get the correct format to work so I can return a ref_cursor and get that data to my front end?
Any help would be very appreciative
JK
Here is my SQL Developer code.
create or replace package body spectrum_cribeast as
procedure rcgetspectrumdata(startdt in DATE, enddt in DATE, v_userid in varchar2, rcdata out refcursor) as
NEWSTARTDATE date := TRUNC(startdt);
NEWENDDATE date := TRUNC(enddt);
begin
OPEN rcdata FOR
Select * from vwinspectioninfo Where INSPECTION_DT between TO_DATE(NEWSTARTDATE) and TO_DATE(NEWENDDATE) and INSPECTOR = v_userid;
end rcgetspectrumdata;
end spectrum_cribeast;
My dates im passing in are in mm/dd/yyyy format and I can see in my Oracle table that the format is 01-JAN-17 format. How can I get the correct format to work so I can return a ref_cursor and get that data to my front end?
Any help would be very appreciative
JK
ASKER
ok but I think Ive tried every variation or passing the dates in and using the functions to try and get them both in the same format. I will see if it will work.
FYI, they are passed in as DATEand in the table they are DATE
FYI, they are passed in as DATEand in the table they are DATE
if you are passing in dates as DATE type (text that happens to look like a date is NOT a date) , then the problem is solely in the use of TO_DATE() around your date variables.
TO_DATE takes a VARCHAR2 as the first parameter. So, if you give it a date, it will first do an implicit conversion of your date into varchar2, then it will do an implicit conversion of that text back into a date. Again, this may or may not produce results you will want.
TO_DATE takes a VARCHAR2 as the first parameter. So, if you give it a date, it will first do an implicit conversion of your date into varchar2, then it will do an implicit conversion of that text back into a date. Again, this may or may not produce results you will want.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This worked for me while nothing else did? Thank you for your help
If your table has columns of type DATE, and your procedure has parameters of type DATE, then they are completely compatible, no matter how you view their text representation.
If though, you pass in text to your DATE parameter, Oracle will try to convert it for you, that may fail with an error, it may succeed but generate the wrong value, or it might work.
If you are going to pass in text, then you should change the parameter type to VARCHAR2, then internal to the procedure turn it into a DATE with a known and fixed format.
Alternatively pass in a DATE value to the DATE parameter
Also, inside your procedure you do this...
TO_DATE(NEWSTARTDATE) and TO_DATE(NEWENDDATE)
that doesn't make sense. each of those values is already a DATE.
Just use the dates directly:
OPEN rcdata FOR
SELECT *
FROM vwinspectioninfo
WHERE inspection_dt BETWEEN newstartdate AND newenddate
AND inspector = v_userid;