Link to home
Start Free TrialLog in
Avatar of jknj72
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.

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;

Open in new window


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
Avatar of Sean Stuber
Sean Stuber

Dates don't have formats.  Text has a format.


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;
Avatar of jknj72

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
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.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jknj72

ASKER

This worked for me while nothing else did? Thank you for your help